Development | Database

SQL procedures in the CDB database??
Written by Jim Poe  [author's bio]  [read 47530 times]
Edited by Derek

   

Page 1  Page 2
[Download the code] [Discuss this tutorial]

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 

Back to Database | [Article Index]