Skip to content

Cross-database queries

As mentioned before, the SQLite engine in QueryStorm can access data from the external world via custom table-valued functions. With this mechanism, we can expose data from other databases to our SQLite engine. This chapter explains how.

To see an example of cross-database querying, download the sample workbook.

1. Define an embedded query to the external database

Suppose we have a SQL Server database and we want to expose a table called Departments to SQLite. The first step is to define a query that returns the data we're interested in.

select * from HumanResources.Department d

In order to expose this data to SQLite, we need to embed this query into the workbook. I'll name the embedded query Get departments.

2. Call the embedded query from C#

Now that the query is embedded, we can run it from C# like so:

Query("Get departments").Run().Items

calling embedded query from C#

The above script will run the query and return the results as a dynamic[] object. We can expose this data to SQLite as a table-valued function, but we first need to map the rows to a specific type, since QueryStorm needs to know what columns the function will return (i.e. we can't return dynamically typed objects).

We could write the new type and the mapping code ourselves, but there's a handy context menu command that does it for us:

generate api command

This command will run the query in the background to determine which columns are returned. Based on that information, it generates a strongly typed class that represents rows, and a function that calls the query and exposes the results to SQLite. It embeds this code in a script in the APIs folder, and gives it a name based on the name of the original query.

generate api result

3. Call the query from SQLite

We can now disconnect from our current connection and reconnect using SQLite. Once we do, we'll see the new table-valued function in autocomplete, and we'll be able to use it:

generate api result

The above query is executed by SQLite, but the table valued function internally loads data from an SQL Server database.

We can repeat the same procedure with multiple embedded queries. This will make data from multiple databases available to SQLite. If we like, we can join workbook tables with data from web services and multiple external databases and then dump the results into Excel or a SQLite file database.

Limitations

Currently, it is not possible to pass parameters to embedded queries. This is not yet implemented but is planned. Once ready, it will enable selectively loading data from external databases which will minimize the amount of data that needs to be loaded when e.g. running joins with data from external databases.

Comments