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
In parse.com – nosql database for VBA, I described how to use cParseCom to be able to use parse.com directly from VBA. You can find the implementation details here parse.com api class for VBA
Here’s a few examples – we’ll walk through the code shown below
Page Content
hide
The Code
Option Explicit
'v1.2
Private Sub firstTimeParseCom()
Dim parseCom As cParseCom
Set parseCom = New cParseCom
With parseCom.init("ColorTable", , , "your app id", _
"your restapikey")
.tearDown
End With
End Sub
Private Sub populates()
' copy two sheets to parse.com
populateFromSheet "VBAParseCustomers"
populateFromSheet "VBAParseData"
End Sub
Private Sub populateFromSheet(sheetName As String)
Dim parseCom As cParseCom, job As cJobject, dset As cDataSet
' this will clear out an existing parse class, and create a new one from a worksheet
' we'll use batch mode throughout
Set parseCom = getParsed(sheetName).batch
' clear out existing any existing data
parseCom.deleteObjects
'get the data from the sheet and populate database
Set dset = New cDataSet
With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True)
For Each job In .children
With parseCom.createObject(job)
' clear this error handling up
Debug.Assert .isOk
End With
Next job
' clear up
.tearDown
End With
' commit any outstanding and clean up
With parseCom.flush.batch(False)
Debug.Assert .isOk
' show how many are there now
Debug.Print .count & " in class" & sheetName
.tearDown
End With
End Sub
Private Sub testGetItemByUniqueId()
' get an item by unique object ID
With getParsed("VBAParseCustomers")
.getObjectById ("SmnyjZKs9m")
' test if it worked, and do something with the results
If .isOk Then
Debug.Print .jObject.stringify(True)
Else
Debug.Print "failed to get object:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
End If
.tearDown
End With
End Sub
Private Sub testparseUpdate()
' get some items by query and change the scheme name to something else
With getParsed("VBAParseData").batch
With .updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))
' test if it worked, and do something with the results
If .isOk Then
Debug.Print "all is good", .jObject.stringify
Else
Debug.Print "failed to update:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
End If
End With
.flush.tearDown
End With
End Sub
Private Sub testparsequery()
' get a number of items that match a query by example
With getParsed("VBAParseData")
With .getObjectsByQuery(JSONParse("{'customerid':1}"), JSONParse("{'limit':2}"))
'test if it worked, and do something with the results
If .isOk Then
Debug.Print "all is ok", .jObject.stringify(True)
Else
Debug.Print "failed to do query:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
End If
End With
.tearDown
End With
End Sub
Private Sub testparseCount()
' get a number of items that match a query by example
Debug.Print getParsed("VBAParseData").count(JSONParse("{'customerid':1}"))
End Sub
Private Sub parseMatch()
Dim pCustomer As New cParseCom, pData As cParseCom, job As cJobject, joc As cJobject, queryJob As cJobject
' look up data in another table based on data in another
' data abot the pantone colors of the year
Set pCustomer = getParsed("VBAParseCustomers")
Set pData = getParsed("VBAParseData")
' set up a query by example, restricting to a particular customer
Set queryJob = New cJobject
queryJob.init(Nothing).add "country", "United States"
' go through all matching customers
With pCustomer.getObjectsByQuery(queryJob)
If .isOk Then
With .jObject.child("results")
For Each job In .children
With pData.getObjectsByQuery(job.child("customerid"))
If .isOk Then
With .jObject.child("results")
For Each joc In .children
Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value
Next joc
End With
End If
End With
Next
End With
End If
End With
' clean up
queryJob.tearDown
pCustomer.tearDown
pData.tearDown
End Sub
Public Function getParsed(parseClass As String) As cParseCom
Dim p As cParseCom
Set p = New cParseCom
Set getParsed = p.init(parseClass)
End Function
Initial authentication
Before you can start, you need to get your own credentials from parse.com. It’s free and easy. As described in parse.com – nosql database for VBA, once you have those you should register yourself on this PC. You won’t need to run this again after the first time.
Private Sub firstTimeParseCom()
Dim parseCom As cParseCom
Set parseCom = New cParseCom
With parseCom.init("ColorTable", , , "your app id", _
"your restapikey")
.tearDown
End With
End Sub
Afterwards we use getParsed(‘classname’), which looks like this.
Public Function getParsed(parseClass As String) As cParseCom
Dim p As cParseCom
Set p = New cParseCom
Set getParsed = p.init(parseClass)
End Function
Populate from a sheet – walkthrough
Private Sub populateFromSheet(sheetName As String)
Dim parseCom As cParseCom, job As cJobject, dset As cDataSet
' this will clear out an existing parse class, and create a new one from a worksheet
' we'll use batch mode throughout
Set parseCom = getParsed(sheetName).batch
' clear out existing any existing data
parseCom.deleteObjects
'get the data from the sheet and populate database
Set dset = New cDataSet
With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True)
For Each job In .children
With parseCom.createObject(job)
' clear this error handling up
Debug.Assert .isOk
End With
Next job
' clear up
.tearDown
End With
' commit any outstanding and clean up
With parseCom.flush.batch(False)
Debug.Assert .isOk
' show how many are there now
Debug.Print .count & " in class" & sheetName
.tearDown
End With
End Sub
Getting credentials from the registry
Before we can do anything, we have to pick up the encrypted credentials we stored as a one off exercise. The should be one instance of cParseCom for every parse.com class – in this case I’m creating or updating a class with the same name as the sheet I’m importing .Adding .batch() will set this instance to batch Mode. That means that rest Queries will be automatically batched up as much as possible to minimize fetches.
Set parseCom = getParsed(sheetName).batch
Deleting objects
Since we are populating sheets, we want to empty out the current contents. deleteObjects with no arguments will do that
parseCom.deleteObjects
Getting Data
We can use cDataSet (see How to use cDataSet) to minimize playing around with spreadsheet contents. It also has a useful property (.JObject) which returns a useful JSON friendly cJobject (see How to use cJobject), each child of which represents a worksheet row, already in the correct format for createObject() to create a row in the parse.com class database.
'get the data from the sheet and populate database
Set dset = New cDataSet
With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True)
For Each job In .children
With parseCom.createObject(job)
' clear this error handling up
Debug.Assert .isOk
End With
Next job
' clear up
.tearDown
End With
Clearing up
As with most classes on this site, cParseCom features a .tearDown function which ensures that any memory it is using gets cleaned up. Since we using batch Mode, we have to flush, before exiting to clear any outstanding requests. Finally .count() will show how many rows are in the newly created table
' commit any outstanding and clean up
With parseCom.flush.batch(False)
Debug.Assert .isOk
' show how many are there now
Debug.Print .count & " in class" & sheetName
.tearDown
End With
Get an object by its unique ID – walkthrough
Private Sub testGetItemByUniqueId()
' get an item by unique object ID
With getParsed("VBAParseCustomers")
.getObjectById ("3RGpNBMncB")
' test if it worked, and do something with the results
If .isOk Then
Debug.Print .jObject.stringify
Else
Debug.Print "failed to get object:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
End If
.tearDown
End With
End Sub
Each object (row) has a unique ID. Here’s how to get it (you’ll need to look at your parse.com dashboard to get a valid objectID for the data you’ve just loaded).
getParsed("VBAParseCustomers").getObjectById ("3RGpNBMncB")
which, when you do this;
Debug.Print .jObject.stringify(True)
looks like this
{
"address":"584-5478 Et Road",
"city":"Hastings",
"company":"Eu Accumsan Sed Inc.",
"coordinates":"38.2264, 75.04849",
"country":"Comoros",
"customerid":100,
"email":"tincidunt.nibh@Curabitur.net",
"name":"Contreras",
"region":"NI",
"zip":"12396",
"createdAt":"2013-11-26T14:36:40.517Z",
"updatedAt":"2013-11-26T14:36:40.517Z",
"objectId":"SmnyjZKs9m"
}
Do a query- walkthrough
Queries in parse are by example. You can see the details here. You can set up any query by making a cJobject with the filters required. Here Im using JSONParse to create a jobject which will select all objects with customerid = 1.
.getObjectsByQuery(JSONParse("{'customerid':1}"))
Here’s the whole thing,
Private Sub testparsequery()
' get a number of items that match a query by example
With getParsed("VBAParseData")
With .getObjectsByQuery(JSONParse("{'customerid':1}"))
'test if it worked, and do something with the results
If .isOk Then
Debug.Print "all is ok", .jObject.stringify(True)
Else
Debug.Print "failed to do query:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
End If
End With
.tearDown
End With
End Sub
Which returns something that starts like this.
{
"results":[ {
"customerid":1,
"quantity":9,
"value":4230,
"invoiceid":145,
"date":{
"__type":"Date",
"iso":"2013-10-08T00:00:00.000Z"
},
"createdAt":"2013-11-26T14:36:43.286Z",
"updatedAt":"2013-11-26T14:36:43.286Z",
"objectId":"TXOAXukaLS"
},
{
"customerid":1,
"quantity":3,
"value":1560,
"invoiceid":151,
"date":{
"__type":"Date",
"iso":"2013-05-21T00:00:00.000Z"
},
"createdAt":"2013-11-26T14:36:43.430Z",
"updatedAt":"2013-11-26T14:36:43.430Z",
"objectId":"8IF3UNIy69"
},
Counting objects – walkthrough
The .count() function will take the same arguments as getObjectsByQuery() and return the number of objects that match
Private Sub testparseCount()
' get a number of items that match a query by example
Debug.Print getParsed("VBAParseData").count(JSONParse("{'customerid':1}"))
End Sub
Updating objects – walkthrough
The .updateObjects() function will take the same arguments as getObjectsByQuery() and return the objects that match, and then update any fields in those objects mentioned in the second argument; so this updates the customerId for all objects belonging to customerId 39 form 39 to 1.
.updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))
Here’s the whole thing. Note that I specify .batch() here. Updates, Creates and Deletes can, and should be batched for better performance. And when you batch, don’t forget to flush to commit the updates.
Private Sub testparseUpdate()
' get some items by query and change the scheme name to something else
With getParsed("VBAParseData").batch
With .updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))
' test if it worked, and do something with the results
If .isOk Then
Debug.Print "all is good", .jObject.stringify
Else
Debug.Print "failed to update:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
End If
End With
.flush.tearDown
End With
End Sub
A small application – walkthrough
In this example, we’ll find any customers in United States, look up their orders, and print out some data about them.
We need 2 instances of cParseCom
Set pCustomer = getParsed("VBAParseCustomers")
Set pData = getParsed("VBAParseData")
Set up a query for US
Set queryJob = New cJobject queryJob.init(Nothing).add "country", "United States"
Find Them
With pCustomer.getObjectsByQuery(queryJob)
Look through each one
With .jObject.child("results")
For Each job In .children
Do a query on their orders
With pData.getObjectsByQuery(job.child("customerid"))
Print out each of their orders
With .jObject.child("results")
For Each joc In .children
Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value
Next joc
End With
Here’s the whole thing
Private Sub parseMatch()
Dim pCustomer As New cParseCom, pData As cParseCom, job As cJobject, joc As cJobject, queryJob As cJobject
' look up data in another table based on data in another
' data abot the pantone colors of the year
Set pCustomer = getParsed("VBAParseCustomers")
Set pData = getParsed("VBAParseData")
' set up a query by example, restricting to a particular customer
Set queryJob = New cJobject
queryJob.init(Nothing).add "country", "United States"
' go through all matching customers
With pCustomer.getObjectsByQuery(queryJob)
If .isOk Then
With .jObject.child("results")
For Each job In .children
With pData.getObjectsByQuery(job.child("customerid"))
If .isOk Then
With .jObject.child("results")
For Each joc In .children
Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value
Next joc
End With
End If
End With
Next
End With
End If
End With
' clean up
queryJob.tearDown
pCustomer.tearDown
pData.tearDown
End Sub
And the results
United States Hammond 85 161 United States Nash 97 282 United States Nash 97 391 Get involved
See parse.com – nosql database for VBA for more on this topic
For help and more information join our forum, follow the blog or follow me on Twitter