cParseCom examples

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

The Code



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

You can get me on Google plus, Twitter or this forum. See parse.com - 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.


Comments