more complex parse API topics

We've dealt with - nosql database for VBAcParseCom examples and shown the code at 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 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 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

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

You can get me on Google plus, Twitter or this forum. See - nosql database for VBA for more on this topic

For help and more information join our forumfollow the blogfollow me on twitter

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'ReillyAmazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.