|
Stored Procedure-like
Scripts with SQLitePlus
SQLite lacks built-in support for stored procedures. All of the "big"
databases support them. SQLitePlus supports 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 any available Windows scripting language, but
the most commonly used are VBScript and JScript. The stored scripts are stored in a
special table of the database named sqlite_scripts.
You execute a stored script via the SqliteDb.RunScript method (or you can
also use the SqliteDb.Exec method). Here is the C++ RunScript method's
signature:
VARIANT RunScript( BSTR
sScriptName, IVariantCollection* pInParams,
IVariantCollection* pOutParams, BOOL bCache );
Here is the Visual Basic syntax:
Variant SqliteDb.RunScript(
String sScriptName, VariantCollection inParams,
VariantCollection outParams, Boolean bCache )
Suppose you wanted to implement an AddCustomer stored script. First
lets look at how you would implement it as a SQL Server stored procedure. Here is the
pseudo SQL:
INSERT
INTO Customers (CompanyName, ContactName, Address, Phone) VALUES(p1, p2,
p3, p4);
-- return the new CustomerID
|
P1, P2, P3 and P4 are to be
input parameters. With Microsoft SQL Server, you
would create the stored procedure something like this:
CREATE Procedure AddCustomer
(
@CompanyName varchar(100),
@ContactName varchar(50),
@Address varchar(250),
@Phone varchar(20),
@CustNo int OUTPUT
)
AS
INSERT INTO Customers
( CompanyName, ContactName, Address, Phone )
VALUES
( @CompanyName, @ContactName, @Address, @Phone )
SELECT @CustNo = @@Identity
|
With SQLitePlus stored scripts, you would create a script that issues the above SQL
command using the intrinsic SqliteDb object. There
are 4 intrinsic objects available to your
script code when it runs. These variables are:
DB
InParams
OutParams
RetVal
The DB object is a reference to the containing
SqliteDb object. All of the methods and properties of the SqliteDb object
are available to your script code. InParams and OutParams are references to the input and output
collections passed in the RunScript method, if provided. And the RetVal
object will be the return value of 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 RetVal for. Its meant to be
the primary return value from a stored script. If you need more output
parameters, then use the OutParams collection. InParams and OutParams can
be named, but they don't have to be. You could access them by ordinal
position in the collection. But its best to name them for the sake of
clarity.
You would pass in all of the needed
parameters to the script via the InParams collection.
Here is the VBScript code for the stored script:
dim sql,
ds
sql = "INSERT INTO Customers (CompanyName, ContactName, Address, Phone) "
& _
"VALUES('" & InParams("CompanyName") & "','" & InParams("ContactName") &
"','" & InParams("Address") & "','" & InParams("Phone") & "')"
db.Exec "BEGIN"
db.Exec sql
'retrieve the new CustomerID by using the last inserted RowID
set ds = db.Exec("SELECT CustomerID FROM Customers WHERE RowID=" & db.LastInsertRowID)
db.Exec "COMMIT"
RetVal = ds(0)
set ds = nothing
|
Notice we set the new CustomerID
as the primary output parameter RetVal. We
could have returned it as a Dataset, but since we only
need a single value, we can simplify things by just
returning the new CustomerID. We have left-out
error handling for clarity.
Note: We
have provided a built-in StringBuilder class for efficient
string manipulation.
Now you can call this stored script from your code, like
this:
Dim iCustno As Integer
Dim inParams As New SQLITEPLUS.VariantCollection
inParams("CompanyName") = "Acme Software"
inParams("ContactName") = "Lisa Smith"
inParams("Address") = "1 Commerce St."
inParams("Phone") = "509-392-2393"
iCustno =
m_SqliteDb.RunScript("AddCustomer", inParams)
|
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. Note that if you use stored
scripts in your application, you must ship the Windows
script DLL, eg. vbscript.dll or jscript.dll. |
Database Products
