Update Nov2017
UPDATE:  parse.com as now been closed and moved to parseplatform.org. I will update the content of this page an move the back-end to this platform

We’ve dealt with parse.com – nosql database for VBA, cParseCom examples and shown the code at parse.com api class for VBA. Here’s a few selected topics to help you understand a lottle more about how the API works

Query parameters

Any parse.com query parameters such as count, skip, limit, order etc can be added to any query as an additional argument. Here’s how .count() deals with modifying any parameters you supply (in the queryParams argument).

Public Function getCount(Optional queryJob As cJobject = Nothing, Optional queryParams As cJobject = Nothing) As cParseCom
    Set getCount = getStuff(vbNullString, _
        constructQueryString(queryJob, mergeParameters(queryParams, JSONParse("{'count':1,'limit':0}"))))
End Function


There are various limits, such as how many results can be returned , and how many records can be batched at once. Most of this is dealt with automatically (for example if you delete 100 records and the batch limit is 25, the API will automatically make 4 batched calls). Update and Create work the same way. However, you need to deal with limits when doing a Query yourself. This is to protect against memory leaks with big classes. Here’s how to change the limit to a query.

.getObjectsByQuery(JSONParse("{'customerid':1}"), JSONParse("{'limit':2}"))

A Parse query has a maximum of 1000, and a default of 100. If your query has or might have more than the limit, you need to deal with it using skip. Here’s how cParseCom deals with updating more objects than the limit by doing multiple queries using the skip parameter.

Public Function updateObjects(Optional queryJob As cJobject = Nothing, _
        Optional updateJob As cJobject = Nothing, Optional queryParameters As cJobject = Nothing) As cParseCom
    ' does a query, then update all matching
    Dim queryResponse As cJobject, skip As Long, jobSkip As cJobject, number As Long
    skip = 0
    Set jobSkip = JSONParse("{'skip':0}")
    ' we'll just use the default limit
        With getObjectsByQuery(queryJob, mergeParameters(queryParameters, jobSkip)).jObject
            ' this is how many were returned this time
            number = resultsLength(.self)
            ' if there were any then do soemthing with it
            If number > 0 Then
                ' skip what we've already had
                skip = skip + number
                jobSkip.child("skip").value = skip
                ' update the contents of the query we just did
                updateObjectsPart .self, updateJob
            End If
            ' clear out these results
        End With
        ' if there were any errors or there's no more to do then exit
    Loop While isOk And number > 0

    Set updateObjects = Me
	End Function

Date formats

Parse.com can usually make sense of data types from the data it gets sent. One exception is dates, which it expects in ISO format. Here’s how cDataSet.jobject deals with converting Excel dates to ISO. In addition parse needs the date field to be explicitly typed.

ElseIf datesToIso And columns(dc.column).googleType = "date" Then
                If includeParseTypes Then
                    With .add(dh.toString)
                        .add "__type", "Date"
                        .add "iso", toISODateTime(dc.value)
                    End With
                    .add dh.toString, toISODateTime(dc.value)
                End If

The .jobject property of cDataSet takes a couple of arguments that tell it whether its doing stuff for parse.com.

Public Property Get jObject(Optional jSonConv As eJsonConv = eJsonConvPropertyNames, _
                Optional datesToIso As Boolean = False, _
				Optional includeParseTypes As Boolean = False) As cJobject

Converting back and forwards (and preserving the number of milliseconds) is done with this (I haven’t done timezones yet)

Public Function fromISODateTime(iso As String) As Date
    Dim rx As RegExp, matches As MatchCollection, d As Date, ms As Double, sec As Double
    Set rx = New RegExp
    With rx
        .ignorecase = True
        .Global = True
        .Pattern = "(\d{4})-([01]\d)-([0-3]\d)T([0-2]\d):([0-5]\d):(\d*\.?\d*)Z"
    End With
    Set matches = rx.execute(iso)
    ' TODO -- timeszone
    If matches.count = 1 And matches.item(0).SubMatches.count = 6 Then

        With matches.item(0)
            sec = CDbl(.SubMatches(5))
            ms = sec - Int(sec)
            d = DateSerial(.SubMatches(0), .SubMatches(1), .SubMatches(2)) + _
                TimeSerial(.SubMatches(3), .SubMatches(4), Int(sec)) + ms / 86400
        End With
        d = 0
    End If
    fromISODateTime = d
   End Function
Public Function toISODateTime(d As Date) As String
    Dim s As String, ms As Double, adjustSecond As Long
    ' need to adjust if seconds are going to be rounded up
    ms = milliseconds(d)
    adjustSecond = 0
    If (ms >= 0.5) Then adjustSecond = -1
    ' TODO - timezone
    toISODateTime = Format(Year(d), "0000") & "-" & Format(Month(d), "00") & "-" & Format(Day(d), "00T") & _
            Format(d, "hh:mm:") & Format(DateAdd("s", adjustSecond, d), "ss") & Format(ms, ".000Z")

	End Function
Public Function milliseconds(d As Date) As Double
    ' extract the milliseconds from the time
    Dim t As Date
    t = (d - DateSerial(Year(d), Month(d), Day(d)) - TimeSerial(Hour(d), Minute(d), Second(d)))
    If t < 0 Then
        ' the millsecond rounded it up
        t = (d - DateSerial(Year(d), Month(d), Day(d)) - TimeSerial(Hour(d), Minute(d), Second(d) - 1))
    End If
    milliseconds = t * 86400
End Function

Get involved

For help and more information join our forum, follow the blog or follow me on Twitter