Accessing parse.com and scriptDB noSQL databases from Excel

I figured it would be pretty nice to be able use one of the cloud based noSQL databases directly from VBA, so that I could share data easily between various platforms. On Excel Liberation , I recently released a few APIs to open up cloud based noSQL databases to VBA access to make this possible.

In this post, I’m going to use the VBA APIs for Google Apps Script ScriptDB and Parse.com to copy data between the two databases using VBA as the agent.

The APIs have very similar interfaces, so the coding is exactly the same once the connection has been made.

Here’s the main procedure.

The test will be to copy an entire class from parse.com to scriptDb and back again, and do a few counts and queries to compare before and after.

Private Sub scriptDBandParseCopy()
    ' copy from scriptDB to Parse
    Dim dbParse As cParseCom, dbScriptDb As cScriptDbCom, Class As String
    
    Class = "VBAParseCustomers"
    Set dbParse = getParsed(Class)
    Set dbScriptDb = getScriptDb(Class, "dbTest")
    
    ' copy from scriptdb to parse
    dbCopyAny dbScriptDb, dbParse
    
    ' see what we have
    Debug.Print dbParse.count
    Debug.Print dbParse.getObjectsByQuery(JSONParse("{'country':'Turkey'}")).jObject.stringify(True)
    
    ' copy back again
    dbCopyAny dbParse, dbScriptDb
    
    ' see what we have
    Debug.Print dbScriptDb.count
    Debug.Print dbScriptDb.getObjectsByQuery(JSONParse("{'country':'Turkey'}")).jObject.stringify(True)
    
    ' clean up
    dbParse.tearDown
    dbScriptDb.tearDown
    
End Sub

And here’s the common copy procedure – which remains oblivious to the flavor of data base being used.

Private Sub dbCopyAny(dbSource As Object, dbTarget As Object)
    Dim jobSkip As cJobject, job As cJobject
    
    ' delete everything in target db of this class
    dbTarget.batch.deleteObjects
    
    'we have to do it in chunks because of potential query limits
    Set jobSkip = JSONParse("{'skip':0}")


    ' we'll just use the default limit for a big query
    Do
        With dbSource.getObjectsByQuery(Nothing, jobSkip).jObject.child("results")
            If .children.count = 0 Or Not dbSource.isOk Or Not dbTarget.isOk Then Exit Do
            ' There are special reserved fields we need to delete between databases
            For Each job In .children
                dbTarget.createObject _
                    job.deleteChild("objectId").deleteChild("updatedAt").deleteChild("createdAt").deleteChild("siloId")
            Next job
            jobSkip.child("skip").value = jobSkip.child("skip").value + .children.count
        End With
    Loop
    
    ' clean up
    dbTarget.batch (False)
    
  
End Sub

And here’s the result of the query – they match

100 
   {
      "status":"good",
      "count":3,
      "results":[         {
            "region":"SO",
            "zip":"25163",
            "country":"Turkey",
            "city":"Bridgwater",
            "customerid":50,
            "email":"aliquet.molestie.tellus@inaliquetlobortis.edu",
            "address":"Ap #157-9046 At, Rd.",
            "name":"Harris",
            "company":"Vulputate Eu Odio Limited",
            "coordinates":"65.15408, -59.50909",
            "objectId":"S321392559781"
         },
         {
            "region":"Pays de la Loire",
            "zip":"55125",
            "country":"Turkey",
            "city":"La Roche-sur-Yon",
            "customerid":63,
            "email":"quis@parturientmontesnascetur.edu",
            "address":"Ap #433-7194 Ante St.",
            "name":"Jacobs",
            "company":"Vestibulum Company",
            "coordinates":"23.12752, -77.15718",
            "objectId":"S321468256370"
         },
         {
            "region":"Northern Territory",
            "zip":"5199",
            "country":"Turkey",
            "city":"Palmerston",
            "customerid":91,
            "email":"condimentum.Donec.at@Morbi.com",
            "address":"251-8346 Vestibulum, Av.",
            "name":"Reeves",
            "company":"Mi Tempor Lorem Incorporated",
            "coordinates":"-80.71595, -106.84",
            "objectId":"S321468256341"
         }
       ]
   }

Here’s a couple of primer decks to get some background on how all this works.