QueryStorm's VBA API

QueryStorm provides an API that you can consume from VBA. The API is rather simple at the moment and will evolve as necessary. You can use it to run an embedded query, or a SQL command (against any engine). If you need to process the results, you can register success and error callback subroutines (by name). The callback approach is used because execution engines in QueryStorm are asynchronous.

A few examples

To get a sense of how the API is used, let's see some examples before moving on to the API reference.

Example 1: running an Embedded query, and processing the results

SubButton1_Click()'get the root API objectSetapi=Application.COMAddIns("QueryStorm").Object'get the embedded query we want to runSetq=api.GetQuery("Query1")'Run the embedded query, specifying the success and error callbacks. 'Pass in Nothing, or "" if no result processing is needed.'IMPORTANT NOTE: If the callbacks are not in a module (e.g. they're in a sheet) they 'need to be qualified, e.g. Sheet1.Yay, Sheet1.BooCallq.Run("Yay","Boo")EndSubSubYay(dataAsVariant)Setresult=data.Fields.Item(0)MsgBox("The result is "&result)EndSubSubBoo(messageAsString)MsgBox("Error: "&message)EndSub

Example 2: running SQL queries (non-embedded) with LiveMode engine

SubButton1_Click()'get the root API objectSetapi=Application.COMAddIns("QueryStorm").Object'create a runner objectSetrunner=api.CreateRunner()'register queries -> runners don't run queries directly, they give you promisesSetpromise1=runner.AddQuery("select 123")Setpromise2=runner.AddQuery("select * from table1")'set callbacks using the promise objectspromise1.OnSuccess("SuccessCallback1").OnError("ErrorCallback")promise2.OnSuccess("SuccessCallback2").OnError("ErrorCallback")'Open connection, run all queries (in this case 2 queries), close connection'Execution is done asynchronously in the backgroundCallrunner.GoEndSubSubSuccessCallback1(dataAsVariant)'process as neededEndSubSubSuccessCallback2(dataAsVariant)'process as needed'just here to illustrate that each promise can specify its own callbackEndSubSubErrorCallback(messageAsString)MsgBox("Error: "&message)EndSub

SubButton1_Click()'get the root API objectSetapi=Application.COMAddIns("QueryStorm").Object'create an SQL Server runnerSetrunner=api.CreateRunnerMS("Server=.\sqlexpress; Database=AdventureWorks2012; Trusted_Connection=True;")'register table to copy to server as temp tablerunner.AddTable("Table1")'register querySetpromise=runner.AddQuery("select top {Sheet1!B1} * from production.product")'set query execution callbackspromise.OnSuccess("OnSuccess").OnError("OnError")'also add another parameter that will get passed to callbackpromise.SetParameter("Some dummy parameter")'run all queries (in this case, just 1 query)Callrunner.GoEndSub'The success callback can specify 0, 1 or 2 parameters'1st parameter is the data (ADO Recordset)'2nd paramater is arbitraty data you can pass in using promise.SetParameter(...)SubOnSuccess(dataAsVariant,parameterAsVariant)'process as neededEndSub'Error callback can specify 0, 1 or 2 parameters'1st parameter is the error message (String)'2nd paramater is arbitraty data you can pass in via the promise objectSubOnError(messageAsString)MsgBox("Error: "&message)EndSub

API Reference

The root API object

You can get a reference to the root QueryStorm API object as follows:

Setapi=Application.COMAddIns("QueryStorm").Object

This object exposes the methods shown below:

//gets an embedded query by nameQueryGetQuery(stringname);
//creates a runner for running SQL commands in the Live modeLiveRunnerCreateRunner();
//creates a runner for running SQL commands with an Sql Server engineSnapshotRunnerCreateRunnerMS(stringconnectionString);
//creates a runner for running SQL commands with a PostreSQL engineSnapshotRunnerCreateRunnerPG(stringconnectionString);
//creates a runner for running SQL commands with a SQLite engineSnapshotRunnerCreateRunnerSL(stringconnectionString);
//creates a runner for running SQL commands with an Access engineSnapshotRunnerCreateRunnerAC(stringconnectionString);
//creates a runner for running SQL commands with a MySql engineSnapshotRunnerCreateRunnerMY(stringconnectionString);

Running embedded queries

The Query object that is returned by GetQuery(name)represents an embedded query, and has the following methods:

//gets the contents of the embedded querystring GetContents();
//sets the contents of the embedded queryvoidSetContents(stringvalue);
//Runs the queryvoidRun(stringsuccessCallback, stringfailCallback);

The Run method is asynchronous, it starts the (background) execution of the query and immediately returns. If you need to process the results, pass in the names of the VBA subroutines that should be called upon successful/failed execution of the query.

The successCallback subroutine can have zero parameters if it doesn't care about the results. Alternatively, it can specify one parameter, and that parameter must be declared as Variant or Recordset, as it will be used to pass the results into the callback.

The failCallback subroutine must specify one parameter declared as String. This parameter will be used to pass the error message into the callback.

You can pass in Nothing or "" as a callback if you don't want to process success or failure.

Since embedded queries specify the engine configuration themselves (which you can see/change in the automation dialog), nothing about the connection needs to be specified.

Running non-embedded queries

Usually, embedded queries are the way to go as far as automation is concerned. Embedded queries have their own connection data, but if we're executing arbitrary non-embedded queries, we need to specify and configure the connection using VBA. We do this by getting the appropriate runner from the root API object and configuring it.

There is a method for creating a runner for each type of engine. For example, here's how we would create a runner for a SqlServer database:

It's worth noting that its fine to have preprocessor expressions and directives in the queries that the runner will run.

Runners execute a little bit differently than embedded queries. A runner can queue up several queries and, when ready, execute them all in one batch using a single connection.

When adding a query to the runner, the runner will return a promise for the execution of that query. The promise object can be used to configure callbacks, and additional parameters.

Nothing gets executed until you call Go on the runner. Calling Go kicks off the background execution of the entire batch of queries.

There are two types of runners that correspond to the two modes of querying in QueryStorm: LiveRunner and SnapshotRunner. They differ only slightly in their API.

1. The LiveRunner type

LiveRunner is a runner specific to the LiveMode engine. It has the following methods:

//scopeId: 0-ActiveSheet, 1-ActiveWorkbook, 2-AllOpenWorkbooksvoidSetScope(intscopeId);
//schedule a query for execution, and return a promise//that can be used to configure callbacks and other parametersIResultPromiseAddQuery(stringquery);
//Connect, run all scheduled queries (along with executing callbacks), disconnectvoidGo();

2. The SnapshotRunner type

SnapshotRunner object is used to run queries using an external engine. It has the following methods:

//methods to set tables to copy to the destination server as temp tables voidAddAllTables();
voidAddTable(stringtable);
voidAddTablesFromSheet(stringsheetName);
voidAddTablesFromWorkbook();
voidRemoveTable(stringtable);
//schedule a query for execution, and return a promise//that can be used to configure callbacks and other parametersIResultPromiseAddQuery(stringquery);
//Connect, run all scheduled queries (along with callbacks), disconnectvoidGo();

Note: it's called SnapshotRunner because it takes a snapshot of the data in Excel tables, and copies it into temp tables in an external database, it basically represents an external database engine.

3. The ResultPromise type

When registering a query with runner.AddQuery(queryText), a runner will return a promise. This promise can be used to configure callbacks (and more) for the query. The promise object has the following methods:

Note: The methods return the same promise object just to enable method chaining, e.g. promise.OnSuccess('...').OnError(...)

The success subroutine should specify 2 parameters. The first parameter is the data (ADO Recordset), the 2nd parameter is arbitrary data you can pass in via the promise object. It can optionally skip the second or both parameters.

The error subroutine can specify 2 parameters. The first parameter is the error message (String), the 2nd parameter is arbitrary data you can pass in via the promise object. It can optionally skip the second or both parameters.