Stored Procedure-like Scripts with SQLitePlus

SQLite lacks built-in support for stored procedures.  All of the "big" databases support them.  SQLitePlus features stored scripts, which are like stored procedures except that instead of executing SQL directly you use script code to execute SQL through an intrinsic SqliteDb object.  The syntax is much like ASP or Windows Script Host.  You can choose to use VBScript or JScript.  The stored scripts are stored in a special table of the database named sqlite_scripts (encrypted and compressed).

Scripting with SQLitePlus

Automated scripts are the ideal tool for repetetive tasks. SqlitePlus uses the built-in Microsoft Scripting Engines - VbScript and JScript/Javascript (part of Internet Explorer, installed with Windows). SQLitePlus now features both Stored Scripts and external Script files. Stored Scripts are actually stored with the database - that is, in the SQLite database file. This allows you to conveniently keep scripts with a database file. Script files, on the other hand, allow you to keep scripts separate from the database file. This is useful, for example, if you want to have a library of automated scripts which you could use on any database file. Both the SQLitePlus Explorer and the COM DLL support each type (again, the SQLitePlus Explorer uses the SqliteDb.RunScript and SqliteDb.RunScriptFile functions on the COM DLL).

Here are examples of how to call these functions in code:

// run a stored script
db.RunScript( "LoadCsvFile.js",... )

// run a script file
db.RunScriptFile( "LoadCsvFile.js",... );

Advanced Scripting Features

But it keeps getting better. We have added some advanced features for your scripting needs. First, we have added the ability to embed text within your script code, much the same as is done with PHP and Microsoft ASP webserver pages. For those not familiar with this, it means you can write text to an output object which is returned to the caller. In the case of a webserver page, the text is returned to the Web browser. In the case of a script, it is returned from the RunScript functions.

To write text to the output, you use either Output.Write or intermix text using delimiters within the script, as shown in the following example:

var ds; ds = DB.Exec( "SELECT * FROM Invoices" ); // use Output.Write to write text to the output Output.Write( "There are " + ds.NumRows + " invoices in this database file" ); // use text delimeters to do the same - this code switches from script mode to text mode and <%There are %> = ds.NumRows <% invoices in this database file%>

Both lines above output the same text, but using different methods. But we will demonstrate why the second method is superior for things like generating XML/HTML documents. BTW, in case you are wondering what the = ds.NumRows does, its just shorthand for Output.Write. So its equivalent to Output.Write( ds.NumRows ).

Now anyone who has ever had to create XML documents in code using standard procedural languages knows that its very un-intuitive. You can't really see what the document is going to look like until you actually run the code and generate the file and look at the results. But using embedded text, you can keep the structure of your document in the actual script file. We have provided a real example of a script function that generates several HTML documents from the sample Northwind database using a SqlitePlus Scripting (get the download).

Built-in Scripting Objects

With SQLitePlus stored scripts, you would create a script that issues the above SQL command using the intrinsic SqliteDb object.  Just like PHP and ASP have their "intrinsic" objects, such as ASP's Request and Response objects, SQLitePlus has several of its own. These object can be called directly in the code (like a static, global object). There are 5 intrinsic objects available to your script code when it runs.  These variables are:

DB
Inputs
Outputs
Host
System

The DB object is a reference to the SqliteDb object which is running this script.  All of the methods and properties of the SqliteDb object are available to your script code.  Inputs and Outputs are references to the input and output parameter collections.  You can access an input parameter like this:

var myVal = Inputs("param1") // assumes there is an named parameter with name = param1

You can create output parameters like this:

Outputs.Add( myVal )

You use the Host object to write output text and to set the return value of the ScriptResults object returned from the RunScript method.  Most stored scripts would only need to return a single value or object, such as a RowID or a Dataset.  This is what you would use the Host.ReturnVal for.  Its meant to be the primary return value from a stored script.  If you need more output parameters, then use the Outputs collection.  Inputs and Outputs can be named, but they don't need to be.  You could also access them by ordinal position in the collection.  But it may be best to name them for the sake of clarity.

The Host.ReturnValue can hold any type of return value type: string, int, float, COM object, etc.

Host.ReturnVal = ds;

This value is available to you in the ScriptResults object.

Calling Other Scripts

Sometimes you may want to call another script from a running script. Remember, the DB object is the SqliteDb object which was used to call the running script. That means you can call RunScript (or RunScriptFile) using this object. Example:

DB.RunScript( "Do something script.vbs",...)

Including Other Scripts

And we can also include other scripts, as may be needed if you have built up a library of script functions, like this:

#include "math.js"

As you can see, creating stored scripts with SQLitePlus is very easy, not to mention powerful.  One could even argue that a script procedure is more powerful than a conventional stored procedure in other DBMS systems, when you consider that you can do pretty much anything within script code, such as create COM objects, call a Web Service, or whatever.