|
Simulated Stored
Procedures
Access lacks built-in support for stored procedures. The EzScript
ScriptObject is a very powerful tool for running VBScript, JScript, PERL, or any
Windows Scripting Engine. The ScriptObject can be used to run pre-defined
database scripts, which can be stored in a table of the database.
Suppose you want to implement an AddCustomer stored script. 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 to the stored script.
You would create a script that issues the above SQL
commands using an ADO Connection object. This object can
either be created in the script itself, or passed in as a
parameter. You would also pass in all of the needed
parameters to the stored script. For this example,
we'll pass in the ADO Connection object as the primary input
(already opened on the database file),
and the value parameters in the InParams collection.
We'll use JScript as the script language.
var cnn, sb,
rs;
cnn= Host.Input;
sb = Host.CreateObject("EzScript.StringBuilder");
sb.Write("INSERT INTO Customers (CompanyName, ContactName, Address, Phone) ");
sb.Write("VALUES('");
// the named parameters are in the InParams collection
sb.Write Host.InParams("CompanyName");
sb.Write("','");
sb.Write Host.InParams("ContactName");
sb.Write("','");
sb.Write Host.InParams("Address");
sb.Write("','");
sb.Write Host.InParams("Phone");
sb.Write("')");
// execute the
Insert SQL
cnn.Execute(
sb.String );
// get the new
RowID
rs = db.Exec(
"SELECT MAX(CustomerID) FROM Customers" );
// set the new
CustomerID as the primary output
Host.Ouptut =
rs(0);
|
OK, there you have it. Notice we used the EzScript
StringBuilder object to efficiently build the SQL string
from the input parameters. We set the new CustomerID
as the primary output parameter. We have left-out
error handling for clarity.
To instantiate a ScriptObject in your code and call the
above script is easy. Lets assume you have a table
named "Scripts" containing all of your stored scripts. Here is how to
invoke the AddCustomer stored script with VB:
Dim cnn As New
Connection
Dim rs As Recordset
Dim so As New ScriptObject
'open the
connection.
cnn.Open
"Provider=Microsoft.Jet.OLEDB.4.0;DATA Source=" + App.Path() + "\\Northwind.mdb;"
'retrieve the script from the database
Set rs =
cnn.Execute("SELECT script FROM Scripts WHERE
name = 'AddCustomer'");
'set the
Connection object as the main input
so.Input = cnn
'set the other input parameters
so.Inparams.Add "CompanyName", "Acme Sports Equipment"
so.Inparams.Add "ContactName", "Sam Jockey"
so.Inparams.Add "Address", "123 Commerce Drive"
so.Inparams.Add "Phone", "(208) 322-4382"
'now run the
script
so.RunScript rs(0)
'display the
new CustomerID
Debug.Print
so.Output
cnn.Close
|
As you can see, setting up a stored procedure system
using EzScript and Access is very easy, not to mention
powerful. One could even argue that a script
procedure is more powerful than stored procs in other
DBMS systems, like SQL Server and Oracle, when you
consider that you can pretty much do anything with script
code. You can even create ScriptObjects in a script,
which can also create their own ScriptObjects any level
deep. |
Database Products
