cParseCom implementation

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

I use scriptDB a lot , both with Google Apps Script and from other sources, including VBA, and in this blog post, I  compare parse.com performance with script DB. One of the things that Parse.com has going for it is that there are APIs for IOS, Python, Javascript and other languages, so by choosing parse.com as your cloud based nosql database you are able to share the same data with many platforms.

I decided to put together a basic API for VBA so you could use Parse directly from Excel. It needs some refinement, and I’ll add capabilities to it if necessary. Let me know on the forum if you are using it, and how its working out. This will be based around the parse.com rest API – in other words calls to the custom VBA cParseCom class will be translated in rest API calls to Parse. All the code referenced here is available in the cDataSet.xlsm workbook, which you can download from Project Downloads

Primer slides

Authentication

Before we get started, you’ll need to get logged on to parse.com, create an application and get some authentication keys. The 2 keys we will need for the current incarnation of the VBA parse API, are the application ID and the restAPI key.
Let’s get the topic of credential storage out of the way, before we dive into using Parse from VBA. Knowing what to do with these API keys in VBA in always a pain – you don’t want to have to duplicate them in every workbook that needs to access your data – so I’m going to take the same approach as I took for OAUTH2 and VBA, namely to encrypt theses and store them in the registry. This means you only have to register your application once on a pc, and then any other workbooks using the same credentials on that PC will get them from the encrypted registry. Again I recommend you have a separate workbook for your keys, and run the authentication process just once.
It looks like this. The optional arguments are so you can have multiple applications and scopes on the same PC. We’ll look at that in more detail later, and just use the defaults for now.
Private Sub firstTimeParseCom()
    Dim parseCom As cParseCom
    Set parseCom = New cParseCom
    With parseCom.init("ColorTable", , , "your application id",  "your restapi key")
        .tearDown
    End With
End Sub

Every workbook that wants to use parse.com on this PC will just have to do this from then on.

With getParsed("a class name")
    .. do something
end with

Credentials encryption

These credentials are encrypted in the registry. If you don’t have the CAPICOM encryption dll loaded on your PC, you’ll need to get it from Microsoft as described in Google Oauth2 VBA authentication

The VBA parse.com API

It’s probably too grand to call it an API, but for the lack of a better term, let’s stick with that

Getting started

You’ll need to get registered with Parse, download cdataset.xlsm, and run the initial authentication as described on this page, and create some classes to play around with. A parse.com class is like a table, and you can look at them through your parse.com dashboard. We’ll work through the examples in the parseCom module of cDataSet.xslm.

Populating a parse.com class

Since this is Excel, the first job with the new API is to see how to create and populate a couple of classes from some given worksheets. Here’s the start of the first sheet – some customer data.
and here’s some order data
I’ve provided a general-purpose sub, populateFromSheet, for this, which we’ll look at in a moment. This will create a class for each sheet, with each object named the same as the columns in the table.
Private Sub populates()
    ' copy two sheets to parse.com
    populateFromSheet "VBAParseCustomers"
    populateFromSheet "VBAParseData"
    
End Sub
Here’s the parse.com dashboard, after I’ve run this – we have the two new classes, populated with the appropriate records
And here’s the code to do it.
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

More examples and detail

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