Trying out your new scriptDB environment

If you’ve followed Creating your own scriptDB environment we are ready to start accessing it from VBA.

Page Content hide

1 Registering authentication

2 Adding some data to your test environment

3 More Tests

4 Populating dbProduction.

Registering authentication

There are 2 things you need to do, both of which are one-off things. You may have done this in the previous section

Register this PC for oAuth2. If you have already done this to “drive” scope with some other things on this site, as described in Google Oauth2 VBA authentication you won’t need to bother doing that again.
Register this PC for your scriptDB environment.

Substitute your google credentials, let’s do everything at once, authorizing google, full access to the dbTest environment and dbProduction environment.

Private Sub firstTimescriptdbComdbTest()
    Dim scriptdbCom As cScriptDbCom, url As String
    Set scriptdbCom = New cScriptDbCom
    
    ' you only need to run this once per PC. Once you've done it, delete it from here and keep a safe private copy  somewhere
    
    ' this url is for the dbHandler google apps script webapp
    url = "https://script.google.com/a/macros/mcpher.com/s/AKfycbyro1-9LNOnaykvBJ4_6pRZJwNkgf-VFpeQ8drJBqzOK3QZIhU/exec"
    
    'substitute in your google oauth2 credentials (clientid/secret) from the google cloud console
    getGoogled("drive", , "xxxx.apps.googleusercontent.com", "xxx").tearDown
    
    ' get full access to the test environment
    With scriptdbCom.init(, "dbTest", , _
                "yourApp", _
                "yourKey", , , True, _
                "https://script.google.com/a/macros/mcpher.com/s/AKfycbyro1-9LNOnaykvBJ4_6pRZJwNkgf-VFpeQ8drJBqzOK3QZIhU/exec")
        .tearDown
    End With
    
    ' and full access to the production environment
    With scriptdbCom.init(, "dbProduction", , _
                "yourApp", _
                "yourKey", , "dbProduction", True, _
                "https://script.google.com/a/macros/mcpher.com/s/AKfycbyro1-9LNOnaykvBJ4_6pRZJwNkgf-VFpeQ8drJBqzOK3QZIhU/exec")
        .tearDown
    End With
End Sub

You’ll get something like this pop-up – this will allow this PC to access your dbHandler webApp

You can now delete this function (or keep a copy in another workbook), since you won’t need it again unless you change PC.

Adding some data to your test environment

Before that, first, let’s check we have a connection, and that you have no data in either of your scriptDB.

Private Sub testdbCount()
    Debug.Print getScriptDb("VBAParseCustomers", "dbProduction").count(); " objects  in customers class: production DB"
    Debug.Print getScriptDb("VBAParseData", "dbProduction").count(); " objects  in data class: production DB"
    
    Debug.Print getScriptDb("VBAParseCustomers", "dbTest").count(); " objects  in customers class: test DB"
    Debug.Print getScriptDb("VBAParseData", "dbTest").count(); " objects  in data class: test DB"
    
End Sub

result

 0  objects  in customers class: production DB
 0  objects  in data class: production DB
 0  objects  in customers class: test DB
 0  objects  in data class: test DB

Now we can add data from the 2 test data sheets in cDataSet.xlsm

Private Sub dbTestPopulates()
    ' copy two sheets to scriptdb.com
    populateFromSheet "VBAParseData", "dbTest"
    populateFromSheet "VBAParseCustomers", "dbTest"
End Sub

result

308 in classVBAParseData
100 in classVBAParseCustomers

More Tests

Why not go back to Getting started with cScriptDbCom and run the query tests, this time using dbTest as your authentication entry. You should get the same results as you did with my primer database.

Populating dbProduction.

We could simply repeat by populating dbProduction from the sheets as we did with dbTest. However, in real life, there might not be any sheets involved. Here’s how to copy a class from one database to another.

Private Sub dbTestCopy()
    dbCopy "dbTest", "dbProduction", "VBAParseData"
    dbCopy "dbTest", "dbProduction", "VBAParseCustomers"
End Sub

Private Sub dbCopy(source As String, target As String, Class As String)
    Dim dbSource As cScriptDbCom, dbTarget As cScriptDbCom, _
        jobSkip As cJobject, job As cJobject
    ' copying class from one database to another
    Set dbSource = getScriptDb(Class, source)
    Set dbTarget = getScriptDb(Class, target).batch(True)
    
    ' delete everything in source db of this class
    dbTarget.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
            For Each job In .children
                dbTarget.createObject job
            Next job
            jobSkip.child("skip").value = jobSkip.child("skip").value + .children.count
        End With
    Loop
    
    ' clean up
    dbTarget.flush.tearDown
    dbSource.tearDown
  
End Sub

and we can now check the counts on everything

Private Sub testdbCount()
    Debug.Print getScriptDb("VBAParseCustomers", "dbProduction").count(); " objects  in customers class: production DB"
    Debug.Print getScriptDb("VBAParseData", "dbProduction").count(); " objects  in data class: production DB"
    
    Debug.Print getScriptDb("VBAParseCustomers", "dbTest").count(); " objects  in customers class: test DB"
    Debug.Print getScriptDb("VBAParseData", "dbTest").count(); " objects  in data class: test DB"
    
End Sub

result

 100  objects  in customers class: production DB
 308  objects  in data class: production DB
 100  objects  in customers class: test DB
 308  objects  in data class: test DB

all Done!

You can get me on Twitter, or the forum and see more about this particular topic at A VBA API for scriptDB