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
Limits
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
Do
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
.tearDown
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
Else
.add dh.toString, toISODateTime(dc.value)
End If
Else
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
Else
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