Page 1
Page 2
Parameterized Stored Procedures
As you've probably guessed by now,
MSysProcs does not provide any inherent support for adding and
executing parameterized stored procedures. This is, of course,
one of the big advantages of using stored procedures in the first
place. For example, using the Northwind database, you might want
to retrieve information for a particular employee by passing EmployeeID
to a stored procedure. Unfortunately, to accomplish this with
MSysProcs, you'll have to roll your own. This can be achieved
by inserting a placeholder in the SQL string for each parameter.
Then, to append parameters the procedure, call the VB Replace
function to insert a real value for each placeholder. Take the
following Northwind SQL statement as an example:
SELECT * FROM Employees WHERE EmployeeID = @EmployeeID@
Now look at the code below that illustrates
a simple, homegrown method of appending parameters to this statement
and then executing the procedure:
Dim SQLText As String
' open connection code omitted
Set Rs = CreateObject("ADOCE.Recordset.3.0")
Rs.Open "Select SQLText FROM MSysProcs WHERE ProcName='"
& ProcName & "'", Conn, adOpenStatic, adLockReadOnly,
adCmdText
SQLText = Rs("SQLText")
' Replace each parameter placeholder with real values
SQLText = Replace(SQLText,"@EmployeeID@",2)
Rs.Open SQLText, Conn, adOpenStatic, adLockReadOnly, adCmdText
As I've done in the example above, I encourage
you to use a placeholder with some sort of delimiter so that parameters
are not confused with field names. For string and date parameters,
single quotes need to be placed on both sides of the placeholder:
SELECT * FROM Employees WHERE LastName = '@LastName@'
The Procedure Manager
By now, I'm sure you're wondering how
to administer stored procedures without writing a program to do
it. To make it easy for you, I have provided source code for a
crude, "no frills" utility that provides this functionality.
The program gives you the ability to browse existing stored procedures
in a CDB file and allows you to add new procedures by specifying
a file containing your query or, if you prefer torture, you can
type it out on your Pocket PC. Once a procedure has been inserted,
it can then be executed to inspect the results. The easiest way
to use this tool is to write and test the query in the desktop
version of Access. Next, save the SQL statement as a text file
on your Pocket PC, and then use the tool to add it to the database.
After that, you are ready to begin executing stored procedures
in your application.
Advantages
There are two main advantages to using
this method. One is that you won't have to recompile or modify
your code in order to make minor changes to SQL statements. For
example, to change the sort order for a select statement, all
you'll have to do is modify the query and use the tool to add
the updated procedure to the database. Of course, big changes
like adding a parameter to a stored procedure will involve code
changes. The second advantage is code cleanliness. MSysProcs allows
you to contain all the SQL statements for your program in a central
repository so you won't have SQL interspersed throughout your
code and this will make locating buggy SQL statements much easier.
Disadvantages
I didn't create any performance tests,
but I assume that executing CDB stored procedures is probably
not much faster, if at all, than hard coding the SQL statements
in the eVB code. Furthermore, if you decide to use parameterized
procedures, you'll pay a performance penalty, because two statements
have to be executed instead of one and the SQL string also has
to be parsed for each parameter. Another shortcoming is that introducing
new procedures to an already deployed application will require
that you devise a way of preserving the data of the previous version.
However, this is also true of any new version of a CE application
that requires structural changes to the database. Unfortunately,
there's no way to achieve this without writing a setup program
capable of applying database changes while preserving the data.
Editor's Addendum - Using the sample code
1) Import the Employees table from the Northwind
database - see this
article on transferring data into Pocket Access on your Pocket
PC if you are unsure how to do this.
2) Next copy the file 'EmployeeByID.sql'
from the zipped
download to your Pocket PC using the ActiveSync Explorer.
3) Now run the eVB project and load the database
and file you created in step 1.
4) After adding a name you should be at this
screen:
5) Click Add, then highlight the procedure
and click Run.
6) Enter 1 as the 'parameter' and you should
see the following screen:
Author Bio
Jim Poe is an application developer for Plural,
Inc. an e-business consulting and development firm that helps
companies drive business value through the creation and development
of innovative technology solutions. Since 1989, Plural has delivered
more than 1,500 business critical solutions for global 2000 companies,
with 30% of the Fortune 100 companies leveraging Plural-built
systems. The firm is headquartered in New York City with offices
in eight major cities across the US.
Previous Page