Page 1
Page 2
One disappointing aspect of using ActiveSync's
Pocket Access conversion utility is that queries stored in the desktop
version of an Access database are not converted. Of course, it's
not likely that such a conversion would be very successful anyway
since CE databases run a stripped down version of SQL that does
not support many Access-specific statements such as accessing the
value of a control on an Access form. This leaves two options for
specifying queries in eVB applications that use CDB files for storing
data:
1) Hard code SQL statements in the eVB code
or
2) Use the CDB system table MSysProcs which stores procedures
in the CDB file itself.
In this tutorial, I'll investigate the second
approach and look at how to:
- add stored procedures to a CE database
- execute stored procedures with ADOCE
- create parameterized stored procedures
MSysProcs Table Definition
In the screen shot below, I've modified
the Database Viewer sample application (included in the Embedded
Visual Tools SDK) to display the details of the MSysProcs table.
As you can see, MSysProcs contains two fields,
ProcName and SQLText. ProcName is the name of the
stored procedure and SQLText is the command that will be executed
when the stored procedure is called. The ADOCE documentation cites
a few limitations that apply to all SQL statements, and therefore,
to any stored procedures added to MSysProcs (see the "SQL
Statement Syntax" section of the help file for more information):
- Procedure names are limited to 31
characters.
- SQL statements are restricted to 2,048
characters.
- Up to three fields can be sorted in an
ORDER BY clause.
- Statements are case sensitive when assigning
data to a field
- Query results are limited to 64 KB
- An empty SQL string generates the DB_E_NOCOMMAND
error value
Despite the fact that CE SQL statements have
these requirements, the MSysProcs table does not have any constraints
to prevent you from breaking these rules, so use caution when
adding stored procedures or you might end up with some unexpected
results. For example, it's perfectly legal to insert records into
MSysProcs that have the same ProcName. I tested this and found
that if you add two procedures with the same ProcName and attempt
to execute that ProcName, ADOCE will simply choose the first procedure
in MSysProcs with that name. MSysProcs will also allow you to
insert records without a value for ProcName and/or without a value
for SQLText. However, you'll get an error message, if you try
to execute a procedure without a value for these fields.
Using MSysProcs
Now that you've seen the structure
of MSysProcs, take a look at these pretty straightforward examples
for retrieving, adding, deleting, and executing stored procedures:
'Retrieve stored procedures:
' open connection code omitted
Set Rs = CreateObject("ADOCE.Recordset.3.0")
Rs.Open "MSysProcs", Conn, adOpenStatic, adLockReadOnly,
adCmdTableDirect
'Add a stored procedure:
' open connection code omitted
Set Rs = CreateObject("ADOCE.Recordset.3.0")
Rs.Open "MSysProcs", Conn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
Rs.AddNew
Rs.Fields("ProcName") = ProcName
Rs.Fields("SQLText") = SQLText
Rs.Update
'Delete a stored procedure:
' open connection code omitted
Conn.Execute "DELETE FROM MSysProcs WHERE ProcName = '"
& ProcName & "'"
'Execute a stored procedure:
' open connection code omitted
Rs.Open ProcName, Conn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Next Page