The 32-bit Remote Data Object (RDO) and its associated Remote Data control (RDC) are designed to maximize the performance of 32-bit Visual Basic 4.0 front-ends for client/server RDBMSs. Microsoft aptly characterizes the RDO as "a thin layer over
ODBC." The RDO bypasses the Jet 3.0 database engine and relies on SQL passthrough techniques for query processing. What's different about SQL passthrough with the RDO and RDC is that, unlike all Recordset objects created by Jet with SQL passthrough,
RDO query result sets can be updatable. The RDC is identical to Visual Basic 4.0's Data control in appearance and functionality, but many of the properties of the RDC are specific to client/server data access through the ODBC API. This chapter begins with
examples of the use of the RDO/RDC combination and includes comparisons of the performance of controls bound to the RDC and the Jet 3.0 DAO.
The Remote Data Object and Remote Data control are included with the Enterprise Edition of Visual Basic 4.0. Therefore, you must own the Enterprise Edition to work with the demonstration applications of this chapter. Applications that use the RDO or RDC run only under Windows 95 or Windows NT 3.5+.
The RDO also is intended to take full advantage of the versatility of server-side stored procedures. Jet 3.0's dbPassThrough option of the OpenRecordset method lets you execute simple stored procedures to create a Recordset object or to update table
data. If a query or stored procedure returns more than one query result set, Jet 3.0 creates a persistent table for each of the additional result sets. The RDO creates a Recordset equivalent (a temporary rdoResultSet object) for each of the result sets.
The RDO also streamlines handling of parameters passed to stored procedures. This chapter also shows you how to take advantage of the more sophisticated features of the RDO, such as rdoPreparedStatements.
The quickest way to evaluate the performance of the Remote Data Object is to use the RDO/RDC combination with a sample database. The examples in the following sections, which compare the 32-bit DAO/Data control and the RDO/RDC combination, are derived
from the applications of Chapter 20, "Creating Front-Ends for Client/Server Databases." To use the sample applications, you must first create the Northwind 32-bit ODBC data source by exporting the Northwind.mdb sample database of Access 95 to the
Northwind database of Microsoft SQL Server or another client/server RDBMS having a 32-bit ODBC driver. The "Adding the Northwind Database as an ODBC Data Source" and "Exporting Tables from Access 95 to the ODBC Data Source" sections of
Chapter 20 describe how to create the Northwind data source.
You must add the Microsoft Remote Data control (MSRDC32.OCX) to the 32-bit Visual Basic toolbox in order to create your own RDO applications. Although the Microsoft Remote Data Object 1.0 (MSRDO32.DLL) appears in the list of the Custom Control dialog's Available Controls, you don't add a reference to MSRDO32.DLL from the Custom Control dialog. (You receive a C:\Win95\System\MSRDO32.DLL could not be loaded message if you try.) To bind the RDO to applications that don't rely on the RDC exclusively, use the References dialog to create a reference to MSRDO32.DLL. The RDO and Jet DAO libraries coexist peacefully.
You can use the built-in properties sheet of the RDO or Visual Basic's Properties window to set up the connection to your ODBC data source. Figure 22.1 shows the Remote Data Control Properties sheet with settings for the default system administrator
(sa) to establish a connection to the SQL Server northwind database with the Northwind data source. In addition to the data source name (DSN), user ID (UID), and password (PWD), you must supply an SQL statement as the value of the SQL property of the RDC.
The SQL property of the RDC corresponds to the RecordSource property of the Data control, but you cannot supply only a table name as the value of the SQL property; a valid SQL statement is required.
Figure 22.1. Setting the connection-related properties of the Remote Data control.
Using the Remote Data Control Properties sheet sets values for the DataSourceName, Password, and UserName properties. Alternatively, you can substitute a conventional ODBC connect string (ODBC;DSN=DataSourceName;UID=UserName;PWD=Password) as the value of the ConnectString property.
For initial trials with the RDO/RDC combination, all but one of the default property values of the RDC are adequate. To avoid the appearance of the SQL Server Login dialog, set in Visual Basic's Properties window the value of the Prompt property to 1 -
No Prompt. The RDO's Resultset property corresponds to the Recordset property of the Data control. A detailed comparison of the object hierarchies of the DAO and the RDO appears in the "Exploring the Properties and Methods of the RDO" section,
later in this chapter.
Chapter 20's Rem_locl.vbp project compared the performance of a local copy of Northwind.mdb with that of server tables linked to a Jet 3.0 database. The RDO_DAO.vbp project in the \DDG_VB4\32_bit\Chaptr22 folder of the accompanying CD-ROM uses the
Northwind ODBC data source for both the conventional Data control (dtcDAO) and the Remote Data control (rdcRDO). When you open frmRDO_DAO, each of the controls establishes a connection to the Northwind ODBC data source. You can use Move Last and Scroll
buttons for each DBGrid control to compare the relative performance of a DBGrid control with the DAO/Data control or the RDO/RDC combination. Figure 22.2 illustrates the times (in seconds) for the first MoveLast operation after opening the form and the
times required to scroll through the 1,000+ records of the Order Details table by invoking the MoveNext method. Figure 22.2 also demonstrates that both the DAO's Recordset object of the Dynaset type and the DTC's Resultset object are updatable; the
tentative append record appears in the DBGrid control after the last record.
The times shown in Figure 22.2 indicate that the RDO/RDC needs only about 70 percent of the time required by the DAO/Data control to traverse the Resultset and Recordset, respectively, by the MoveNext method. Table 22.1 lists typical times for the
following three conditions:
| Type of Test | DAO/DTC Move Last | RDO/RDC Move Last | DAO/DTC Scroll | RDO/RDC Scroll |
| DBGrid | 1.59 | 1.21 | 55.97 | 39.32 |
| No Data | 1.82 | 1.71 | 4.72 | 5.17 |
| Data to Variables | 1.32 | 2.25 | 58.93 | 7.96 |
For consistency between the comparative timing tests, the Connect property value of the Data control and the RDC is ODBC;DSN=Northwind;UID=sa;PWD= in order to open the ODBC data source directly. Using an ODBC table attached to a Jet 3.0 database with the Data control gives results similar to those shown for the DAO/DTC combination in Table 22.1.
Listing 22.1 shows the code used to perform the comparative timing tests, the outcome of which appears in Table 22.1.
Listing 22.1. Code to compare the performance of the DAO/Data control and the RDO/RDC combination with and without bound DBGrid controls.
Option Explicit
Dim sngTime As Single
Dim lngOrderID As Long
Dim intProductID As Integer
Dim curUnitPrice As Currency
Const fUseVariables = False
'Note: Delete DataSource property value when setting True
Private Sub cmdDAOMoveLast_Click()
'DAO/Data control test
dtcDAO.Recordset.MoveFirst
DoEvents
txtDAOTime.Text = 0
If fUseVariables Then
lngOrderID = dtcDAO.Recordset.Fields(0)
intProductID = dtcDAO.Recordset.Fields(1)
curUnitPrice = dtcDAO.Recordset.Fields(2)
End If
sngTime = Timer
dtcDAO.Recordset.MoveLast
If fUseVariables Then
lngOrderID = dtcDAO.Recordset.Fields(0)
intProductID = dtcDAO.Recordset.Fields(1)
curUnitPrice = dtcDAO.Recordset.Fields(2)
End If
txtDAOTime.Text = Format(Timer - sngTime, "0.00")
End Sub
Private Sub cmdDAOScroll_Click()
'DAO/Data control test
dtcDAO.Recordset.MoveFirst
txtDAOScroll.Text = 0
sngTime = Timer
Do Until dtcDAO.Recordset.EOF
If fUseVariables Then
lngOrderID = dtcDAO.Recordset.Fields(0)
intProductID = dtcDAO.Recordset.Fields(1)
curUnitPrice = dtcDAO.Recordset.Fields(2)
End If
dtcDAO.Recordset.MoveNext
'DoEvents
Loop
txtDAOScroll.Text = Format(Timer - sngTime, "0.00")
End Sub
Private Sub cmdRDCMoveLast_Click()
'RDO/RDC test
rdcRDO.Resultset.MoveFirst
DoEvents
txtRDCTime.Text = 0
sngTime = Timer
If fUseVariables Then
lngOrderID = rdcRDO.Resultset.rdoColumns(0)
intProductID = rdcRDO.Resultset.rdoColumns(1)
curUnitPrice = rdcRDO.Resultset.rdoColumns(2)
End If
rdcRDO.Resultset.MoveLast
If fUseVariables Then
lngOrderID = rdcRDO.Resultset.rdoColumns(0)
intProductID = rdcRDO.Resultset.rdoColumns(1)
curUnitPrice = rdcRDO.Resultset.rdoColumns(2)
End If
txtRDCTime.Text = Format(Timer - sngTime, "0.00")
End Sub
Private Sub cmdRDCScroll_Click()
'RDO/RDC test
rdcRDO.Resultset.MoveFirst
DoEvents
txtRDCScroll.Text = 0
sngTime = Timer
Do Until rdcRDO.Resultset.EOF
If fUseVariables Then
lngOrderID = rdcRDO.Resultset.rdoColumns(0)
intProductID = rdcRDO.Resultset.rdoColumns(1)
curUnitPrice = rdcRDO.Resultset.rdoColumns(2)
End If
rdcRDO.Resultset.MoveNext
'DoEvents
Loop
txtRDCScroll.Text = Format(Timer - sngTime, "0.00")
End Sub
The Remote Data Object and its Remote Data control offer substantially better performance than Jet 3.0 and the Data control in typical, multi-query decision-support applications. The Picklist project developed in Chapter 20 is easily adapted to a
project that compares the performance of the DAO/Data control and RDO/RDC combinations. The PickTest.vbp project and its PickTest.frm are located on the accompanying CD-ROM in the \DDG_VB4\32_bit\Chaptr22 folder.
Figure 22.3 shows the frmPick_DAO form that uses Data controls to provide the RecordSource for the DBCombo (dbcCustomers) and two DBGrid controls (dbgOrders and dbgOrderDetails). Figure 22.4 shows frmPick_RDO, a clone of frmPick_DAO that substitutes
RDCs for Jet 3.0 Data controls. The two forms each include a pair of text boxes that report the time required to display all of the orders placed by the customer selected in the DBCombo control and the line items that comprise each order. Table 22.2 lists
the comparative times to populate the two DBGrid controls.
Using the rdcCustomers control to position the record pointer of the Recordset over the Customers table results in an error when you query a case-sensitive SQL Server. There is a bug in the first release of the RDC that sends table names to SQL server as an all-lowercase string. You do not encounter this problem when repositioning the record pointer with the rdcOrders or rdcOrderDetails controls. This problem does not occur when using Oracle RDBMSs.
| Operation | DAO/DTC | RDO/RDC | Ratio |
| Find All Orders | 1.598 | 0.277 | 5.7:1 |
| Find Details | 0.871 | 0.168 | 5.2:1 |
The times shown in Table 22.2 are for the single instance of the comparison illustrated by Figures 22.3 and 22.4. The average times for multiple comparisons do not differ substantially from the typical elapsed times shown in Table 22.2. Changing the DTC's ResultsetType property value from Keyset (1), the default, to Static (3) provides slightly shorter elapsed times.
The VBA code and SQL statements to populate the two DBGrid controls from queries against the Orders and Order_Details tables appear in Listing 22.2. The only significant difference in the code for use with Data controls and RDCs is the substitution of
the RDC's SQL property for the Data control's RecordSource property. Unlike a DBCombo control bound to a Data control, you need to activate the dbcCustomers combo box to display the initial member of the underlying Recordset. The Form_Activate subprocedure
uses SendKeys to open the dbcCustomers combo box with the {F4} argument. The RDC accommodates invoking the Refresh method on a Recordset having zero rows; the Data control triggers a trappable error when Refresh is applied to a Recordset with zero
rows. You can compare the behavior of the Data control and RDC with a zero-row Recordset by selecting FISSA in the dbcCustomer control.
Listing 22.2. VBA code to determine the comparative performance of a more complex set of RDCs and bound controls.
Option Explicit
Private Sub dbcCustomers_Change()
'Find latest orders for CustomerID picked in combo box
Dim strSQL As String
Dim sngTime As Single
txtFindOrders.Text = ""
sngTime = Timer
strSQL = "SELECT OrderID, OrderDate AS Ordered, "
strSQL = strSQL & "ShippedDate AS Shipped FROM Orders "
strSQL = strSQL & "WHERE CustomerID = '"
strSQL = strSQL & dbcCustomers.BoundText
strSQL = strSQL & "' ORDER BY OrderDate DESC"
rdcOrders.SQL = strSQL
rdcOrders.Refresh
'DoEvents 'DoEvents causes strange behavior of the dbcCustomer
txtFindOrders.Text = Format(Timer - sngTime, "0.000")
If rdcOrders.Resultset.RowCount = 0 Then
MsgBox "No orders found for '" & dbcCustomers.BoundText & "'."
Exit Sub
Else
Call rdcOrders_Reposition
End If
End Sub
Private Sub rdcOrders_Reposition()
'Find detail records for order number clicked in grid
Dim strSQL As String
Dim sngTime As Single
txtFindDetails.Text = ""
sngTime = Timer
strSQL = "SELECT Order_Details.Quantity AS Quan, "
strSQL = strSQL & "Products.ProductName AS Product, "
strSQL = strSQL & "Order_Details.UnitPrice AS Price "
strSQL = strSQL & "FROM Order_Details, Products "
strSQL = strSQL & "WHERE OrderID = " & rdcOrders.Resultset(0)
strSQL = strSQL & " AND Products.ProductID = "
strSQL = strSQL & "Order_Details.ProductID"
rdcOrderDetails.SQL = strSQL
rdcOrderDetails.Refresh
txtFindDetails.Text = Format(Timer - sngTime, "0.000")
End Sub
Private Sub cmdJet_Click()
'Show the DAO form
Me.Hide
frmPick_DAO.Show
End Sub
Private Sub Form_Load()
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2
End Sub
Private Sub Form_Activate()
'Select the first entry in the customers list
dbcCustomers.SetFocus
SendKeys "{F4}{Enter}", True
dbgOrders.SetFocus
End Sub
DBGrid and DBCombo controls bound to RDCs are useful for testing the performance of the RDO and quickly prototyping high-performance Visual Basic client/server front-ends. Using unbound controls and manipulating the RDO with VBA code, however, is a
much better approach for production decision support and online transaction-processing applications. Writing code to manage the RDO offers better control of connections to the RDBMS and lets you take advantage of prepared statements and server stored
procedures to speed the processing of SELECT, INSERT, UPDATE, and DELETE queries. The sections that follow compare the object structure of RDO 1.0 and Jet 3.0's DAO and describe the properties and methods that are unique to the RDO.
You can create a reference to and use the Microsoft Remote Data Object 1.0 in any 32-bit application that supports VBA. As an example, it is possible to use the RDO with Microsoft Access 95, Excel 95, and Project 4.1. Redistribution of the RDO library (Msrdo32.dll), however, is restricted to use in conjunction with 32-bit runtime Visual Basic 4.0 applications. If you want to use the RDO in a commercial application other than Visual Basic 4.0, contact Microsoft for licensing requirements.
The rdoEngine object, which is modeled on Jet 3.0's DBEngine object, has only a total of eight different objects, each of which is contained within a corresponding collection. Figure 22.5 shows the relatively simple object hierarchy of the RDO. RDO
uses query result set column and row terminology in place of Jet's references to fields and records. The rdoColumns collection, which is the default collection for rdoPreparedStatement, rdoTable, and rdoResultset objects, has
identical properties and methods for each of these three object types. Value is the default property for rdoColumn objects.
Figure 22.5. The object hierarchy of the Remote Data Object 1.0.
The primary collections below the rdoEngine object are comparable to objects of the Jet 3.0 DAO, as illustrated by Figure 22.6. Like DAO's Workspaces collection, each member of RDO's rdoEnvironments collection supports an isolated transaction space to
allow multiple, simultaneous transactions. The rdoEnvironment object is defined by an ODBC environment (hEnv) handle, accessed by the hEnv property. Each member of the rdoConnections collection represents a single connection within an rdoEnvironment object
and the corresponding ODBC database connection (hDbc) handle. You apply the OpenResultset method to a rdoConnection object in the same way you invoke the OpenRecordset method of Jet's Database object. DAO Recordsets and rdoResultsets have a superficial
similarity; rdoResultset objects, however, offer a variety of new features that make SQL passthrough much more flexible, especially if you've upgraded to SQL Server 6+. Unlike many DAO collections, members of RDO collections are not persistent in the long
term and cannot be saved as named objects in a database.
Figure 22.6. Correspondence of the object collections of the Jet 3.0 DAO and RDO 1.0.
Several objects in the RDO hierarchy, such as rdoEnvironment, rdoConnection, and rdoPreparedStatement objects, exhibit short-term persistence. Short-term persistence is defined as the lifetime of an instance of a compiled application or an instance of Visual Basic in design/run mode. Short-term persistence is discussed in the "Opening a Connection and Applying the OpenResultset Method" and "Using the rdoPreparedStatement Object with Parameters" sections, later in this chapter.
Most Visual Basic developers first are likely to use the RDC to explore the features of the RDO, then write VBA code to implement RDO operations.(See Table 22.3.)
| Property/Object | Purpose |
|
CursorDriver
rdoEnvironment | Specifies whether to use engine-driven (server-side) or ODBC cursors. SQL Server 6.0's engine-driven cursors provide better performance with large rdoResultset objects. The default (rdUseIfNeeded) uses server-side cursors if the RDBMS supports them. |
|
DataSourceName
RDC only | The name of the ODBC data source. Not required if you use the Connect property of the RDC to specify the DSN= value. |
|
ErrorThreshold
rdoPrepared-Statement | Specifies the level of error severity that causes a trappable error, if the ODBC driver supports this feature. (The 32-bit ODBC 2.5 SQL Server driver supports error severity.) All errors, regardless of severity, appear as members of the rdoErrors collection. The default (-1) causes all errors to be trapped. |
|
KeysetSize
rdoPrepared-Statement | The default (0) specifies keyset-driven cursors only. Values greater than the RowsetSize value create mixed cursors, which are not supported by ODBC 2.5 but are expected to be added to the forthcoming ODBC 3.0. (Setting KeysetSize to a value other than 0 generates an error with ODBC 2.5.) |
|
LockType
rdoPrepared-Statement | Determines if the cursor is read-only (rdReadOnly, 1) or uses pessimistic (rdConcurLock, 2), optimistic row-ID (rdConcurRowVer, 3), or optimistic row-value (rdConcurValues, 4) locking. |
| LogMessagesrdo Prepared-Statement | A "True" (string) value causes ODBC messages to be logged to a text file; an empty string turns off logging. |
| MaxRows rdoPrepared-Statement | Specifies the maximum number of rows of a rdoResultset, regardless of the number of rows generated by a query. The default value (-1) returns all rows. |
| Options RDC only | query; the StillExecuting property returns True while the query is executing. The QueryCompleted event triggers after the first rdoResultset is returned. |
| Password rdoEnvironment | A string entry specifies the value of the Password of the current rdoEnvironment object (which is not required if you use the Connect property to specify the PWD= value). The rdoDefaultPassword property of the rdoEngine object sets the password for all rdoEnvironment objects if no PWD= value is provided in the connect string. (Both properties are write-only for security purposes.) |
| Prompt RDC only | Determines the behavior of the ODBC administrative layer. If you supply all required information to the driver manager, using the rdDriverNoPrompt value (1) completes the connection without displaying a dialog. Several other prompt options are provided that require entry of user ID and password values. |
| QueryTimeout rdoPrepared-Statement, rdoConnection | Specifies the time in seconds beyond which an error occurs if the query has not begun executing. |
| ResultsetType RDC only | Specifies the type of cursor. A keyset cursor (rdOpenKeyset, 1) is unique in that updates made to underlying tables by other users appear in the rdoResultset as pairs of "holes" (empty rows) and new rows. A static cursor (rdOpenStatic, 3) is similar to a DAO Recordset of the Snapshot type, except that the rdoResultset is updatable; changes made by others to the underlying tables don't appear in a static rdoResultset. A dynamic cursor (rdOpenDynamic, 2) is equivalent to a DAO Dynaset; changes made by others appear in the data. Omitting the ResultsetType value or setting the value to 0 (rdOpenForwardOnly) specifies a forward-only rdoResultset object. The RDC is restricted to keyset or forward-only cursors. |
| RowsetSize rdoPrepared- Statement | Specifies the number of rows of data in the cursor that are returned to the rdoResultset object as a result of cursor navigation operations. The default value is 100. |
| SQL rdoPrepared-Statement | member of the rdoPreparedStatements collection. If you bind the RDC to a control, you ordinarily supply a conventional SQL SELECT statement. |
| UserName rdoEnvironment | The user ID for logging into the database of the specified data source, a property of the current rdoEnvironment object (not required if you use the Connect property to specify the UID= value). |
The LockType property for an rdoResultset object is implemented by the intLockType argument of the OpenResultset method. Similarly, the ResultsetType property of the RDC is implemented by OpenResultset's intType argument, and the Options property value is set by the intOption argument. The SQL property of the RDC is implemented by the strName property of the OpenResultset method applied to a rdoConnection object. The Connect and Prompt properties of the RDC are equivalent to the strConnect and intPrompt arguments, respectively, of the OpenConnection method of the rdoEnvironment object.
The formal method for opening a connection is to create an new rdoEnvironment object and then open a rdoConnection within the new environment with the following two statements:
Set envEnvironment = rdoCreateEnvironment(strName, _ strUserID, strPassword) Set conConnect = envEnvironment.OpenConnection(strName _ [, intPrompt[, blnReadonly[, strConnect]]])
The strName argument lets you create persistent rdoEnvironment and rdoConnection objects that you can refer to by strName, rather than by ordinal position in the rdoEnvironments and rdoConnections collections, respectively. If you provide
a logon dialog that supplies the values of the strUserID and strPassword arguments to rdoCreateEnvironment, use rdDriverNoPrompt as the value of the intPrompt argument of the OpenConnection method. Setting the blnReadonly argument to
True speeds execution of queries, but you cannot execute action queries over a read-only connection. You need only supply DSN=Datasource as the value of the strConnect argument if you provide the strUserID and strPassword
values when you create a new user environment. (You must add ;DATABASE=DatabaseName to the connect string if your ODBC data source doesn't specify a particular database.) The easiest way to gain familiarity with rdoEnvironment and rdoConnect objects
is to use the Object Browser to explore the objects' property values and methods (see Figure 22.7).
Alternatively, you can use Default_Environment (rdoEnvironments(0)), which is automatically provided by the rdoEngine object, with the following single instruction:
Set conConnect = rdoEngine.Environments(0).OpenConnection(strDSN [, _
intPrompt[, blnReadonly[, strConnect]]])
You can use the preceding single-line instruction if you don't need to isolate transactions within multiple environments. In this case, add ;UID=Username;PWD=Password to the DSN=Datasource connect string.
The RDO is designed for use with ODBC connections only, so don't include ODBC; at the beginning of strConnect. RDO doesn't support the ODBC; connect string prefix.
The OpenResultset method of the rdoConnection object offers the following two syntax alternatives:
Set rslResultset = conConnect.OpenResultset(strName[, intType[, _
intLockType[, intOption]]])
Set rslResultset = {tblTable|prsPrepStatement}.OpenResultset([intType [, _
intLocktype[, intOption]]])
Use the first of the preceding instructions with a SQL statement as the value of the strName argument. The Name property of the rdoResultset object is derived from the first 255 characters of the SQL statement. The intType argument
specifies the type of cursor, as described in the ResultsetType item of Table 22.3. Permissible values of the intLockType argument appear in Table 22.3's LockType item. Use the second OpenResultset instruction to create a rdoResultset object over a
rdoTable object or a previously created rdoPreparedStatement object.
The rdoTable object corresponds to the Jet 3.0 DAO's TableDef object. When you establish a connection to a data source, the rdoTables collection automatically is populated with a rdoTable object for each table of the database.
One of the primary objectives of the RDO is to improve the performance of client/server front-end applications. Although DBCombo and DBGrid controls bound to RDCs are faster in operation than the same controls bound to Data controls, unbound ComboBox
and Grid controls usually offer better performance. In addition, the unbound Grid control provides a degree of content flexibility not offered by the DBGrid control. The sections that follow describe a modification of the PickTest decision-support
front-end that uses the RDO and substitutes unbound for bound controls. The RDO_Test.vbp project and its associated frmLogOn and frmRDO_Test forms are located in the \DDG_VB4\32_bit\Chaptr22 folder of the accompanying CD-ROM.
It's necessary in most cases to provide a user ID and password to log users onto the server RDBMS. Figure 22.8 shows the simple Logon to SQL Server dialog (frmLogOn) that requests a user ID and password to provide values to the strUserID and
strPassword of the rdoCreateEnvironment method. Listing 22.3 shows the VBA code that creates the user's rdoEnvironment and rdoConnection objects. The connection is opened for read-only access, which improves overall performance by a small margin.
If your DBA uses Windows NT Server's domain-managed security (integrated or mixed security), which uses your Windows NT logon ID and password for SQL Server access, you assign empty strings for the user ID and password (strUserID = "" and strPassword = "", or DSN=Datasource;UID=;PWD=). In this case, you don't need a logon dialog, and you create the rdoEnvironment and rdoConnection objects in the Form_Load or Form_Activate event-handling subprocedures.
Listing 22.3. VBA code for a simple logon dialog to create rdoEnvironment and rdoConnection objects for the user.
Option Explicit
Private Sub Form_Load()
'Position the logon dialog
Me.Top = (Screen.Height - Me.Height) / 3
Me.Left = (Screen.Width - Me.Width) / 2
End Sub
Private Sub cmdOK_Click()
'Attempt to create a user environment and connection
Dim strConn As String
strConn = "DSN=Northwind"
On Error GoTo LogOnError
'Create an environment (hEnv) for the user
Set envUser = rdoCreateEnvironment("User", _
txtUID.Text, txtPWD.Text)
On Error GoTo ConnectError
'Create a read-only connection (hDbc) for the user
Set conUser = envUser.OpenConnection("Northwind", _
rdDriverNoPrompt, True, strConn)
Unload Me
frmRDO_Test.Show
Exit Sub
LogOnError:
MsgBox Error$, vbExclamation, "Log On Error"
If rdoEngine.rdoEnvironments.Count > 1 Then
rdoEngine.rdoEnvironments("User").Close
End If
Exit Sub
ConnectError:
MsgBox Error$, vbExclamation, "Connect Error"
If rdoEngine.rdoEnvironments.Count > 1 Then
rdoEngine.rdoEnvironments("User").Close
End If
Exit Sub
End Sub
Private Sub cmdCancel_Click()
'Close the user's environment and connection, if created
If rdoEngine.rdoEnvironments.Count > 1 Then
If rdoEngine.rdoEnvironments("User") _
.rdoConnections.Count > 0 Then
rdoEngine.rdoEnvironments("User") _
.rdoConnections("Northwind").Close
End If
rdoEngine.rdoEnvironments("User").Close
End If
End
End Sub
You must close named environments and named connections during development because named objects are persistent for the current instance of Visual Basic. As an example, if you don't close the User environment when canceling a logon, the next time you attempt to log on, you receive an Object with name 'User' exists in collection message.
Here are two of the guiding principles of client/server front-end development:
Unlike the RDC, using RDO code gives you precise control over connection management. The frmRDO_Test form uses unbound ComboBox and Grid controls, in conjunction with arrays that store undisplayed values, to provide a noticeably faster response time
than the PickTest application that uses RDCs and bound controls. Another advantage of the frmRDO_Test form is that it opens faster than frmPickTest. The improved performance of frmRDO_Test is due to use of fewer and relatively "lightweight"
unbound controls, plus a reduction in the network traffic necessary to fill the controls. Figure 22.9 shows typical times to populate the cboCustomers ComboBox control, and the grdOrders and grdDetails Grid controls. The addition of a calculated column for
extended unit prices and rows to display Subtotal, Freight, and Order Total values demonstrates the added versatility of Grid controls in decision-support applications.
Figure 22.9. The frmRDO_Test form displaying typical times to execute rdoResultset queries.
The initial times to open the frmRDO_Test form and load the cboCustomers combo box, as well as to populate the two Grid controls depends on the time required to establish a connection to the server, plus server data caching that occurs in conjunction with the first execution of the query set. You can use the Refresh button to recreate the rdoResultset objects without the need to reopen the connection to the server.
Listing 22.4 shows the code required by frmRDO_Test to emulate the operation of frmPickList, plus display the time required to load the cboCustomers combo box. The Form_Load event handler formats the two Grid controls, and the Form_Activate event
handler contains the code to populate the combo box with the list of all customer names. Unlike DBCombo controls, you can't specify separate fields for values in the list and in the BoundText property. Thus, the astrCustID array is populated with
CustomerID values corresponding to the customer names in the combo box. When you select a customer in cboCustomers, the cboCustomers_Click event-handler populates the grdOrders grid and the asngFreight array that stores values of the Freight field for the
selected orders, in a manner similar to populating astrCustID. Clicking a row of the grdOrders control returns the detail records for the selected order and computes order summary amounts.
Listing 22.4. VBA code to populate unbound controls with data from multiple rdoResultset objects.
Option Explicit
Dim astrCustID() As String 'CustomerID array
Dim asngFreight() As Single 'Freight charge array
Dim rslCustomers As rdoResultset 'Fills customers combo
Dim rslOrders As rdoResultset 'Fills orders grid
Dim rslDetails As rdoResultset 'Fills order details grid
Dim fInProcess As Boolean 'Don't process details flag
Private Sub Form_Load()
'Set up grid headings and formatting
grdOrders.Row = 0
grdOrders.Col = 0
grdOrders.Text = "Order ID"
grdOrders.Col = 1
grdOrders.Text = "Ordered"
grdOrders.Col = 2
grdOrders.Text = "Shipped"
grdOrders.ColWidth(0) = 600
grdOrders.ColWidth(1) = 720
grdOrders.ColWidth(2) = 720
grdOrders.ColAlignment(1) = 1
grdOrders.ColAlignment(2) = 1
grdDetails.Row = 0
grdDetails.Col = 0
grdDetails.Text = "Quan"
grdDetails.Col = 1
grdDetails.Text = "Product"
grdDetails.Col = 2
grdDetails.Text = "Price"
grdDetails.Col = 3
grdDetails.Text = "Exten."
grdDetails.ColWidth(0) = 440
grdDetails.ColWidth(1) = 1440
grdDetails.ColWidth(2) = 600
grdDetails.ColWidth(3) = 750
grdDetails.ColAlignment(0) = 1
grdDetails.ColAlignment(2) = 1
grdDetails.ColAlignment(3) = 1
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2
End Sub
Private Sub Form_Activate()
'Load the combo with CompanyNames
Dim sngTime As Single
Dim strSQL As String
sngTime = Timer
strSQL = "SELECT CustomerID, CompanyName FROM Customers"
Set rslCustomers = conUser.OpenResultset(strSQL, rdOpenStatic)
'Get RowCount from resultset
rslCustomers.MoveLast
ReDim astrCustID(rslCustomers.RowCount)
rslCustomers.MoveFirst
'Required for refresh operation
cboCustomers.Clear
'Add the combo box items and load the CustomerID array
Do Until rslCustomers.EOF
cboCustomers.AddItem rslCustomers(1)
astrCustID(cboCustomers.ListCount - 1) = rslCustomers(0)
rslCustomers.MoveNext
Loop
'Load the first customer into the combo box
cboCustomers.ListIndex = 0
rslCustomers.Close
Set rslCustomers = Nothing
txtCustTime.Text = Format(Timer - sngTime, "0.000")
End Sub
Private Sub cboCustomers_Click()
'Find latest orders for CustomerID picked in combo box
fInProcess = True
Dim strSQL As String
Dim intCol As Integer
Dim sngTime As Single
txtOrdersTime.Text = ""
sngTime = Timer
strSQL = "SELECT OrderID, OrderDate, "
strSQL = strSQL & "ShippedDate, Freight FROM Orders "
strSQL = strSQL & "WHERE CustomerID = '"
strSQL = strSQL & astrCustID(cboCustomers.ListIndex)
strSQL = strSQL & "' ORDER BY OrderDate DESC"
Set rslOrders = conUser.OpenResultset(strSQL, rdOpenStatic)
If rslOrders.RowCount = 0 Then
grdOrders.Rows = 1
grdDetails.Rows = 1
MsgBox "No orders found for '" & _
astrCustID(cboCustomers.ListIndex) & "'."
Exit Sub
ElseIf rslOrders.RowCount > 13 Then
'Provide space for vertical scroll bar
grdOrders.Width = 2360
Else
grdOrders.Width = 2108
End If
'Move last to obtain valid RowCount
rslOrders.MoveLast
grdOrders.Rows = rslOrders.RowCount + 1
ReDim asngFreight(grdOrders.Rows)
rslOrders.MoveFirst
grdOrders.Row = 0
Do Until rslOrders.EOF
grdOrders.Row = grdOrders.Row + 1
For intCol = 0 To 2
grdOrders.Col = intCol
'Grid won't accept Null ShippedDate value
If Not IsNull(rslOrders(intCol)) Then
grdOrders.Text = rslOrders(intCol)
End If
Next intCol
'Store freight charge in array
asngFreight(grdOrders.Row) = rslOrders(3)
rslOrders.MoveNext
Loop
'Formal method of closing rdoResultset
rslOrders.Close
Set rslOrders = Nothing
txtOrdersTime.Text = Format(Timer - sngTime, "0.000")
'Set the selection to the first order
grdOrders.SelStartRow = 1
grdOrders.SelStartCol = 0
grdOrders.SelEndRow = 1
grdOrders.SelEndCol = 0
fInProcess = False
Call grdOrders_SelChange
End Sub
Private Sub grdOrders_SelChange()
'Find detail records for order number clicked in grid
If fInProcess Then
'Don't process during loading operation
Exit Sub
End If
Dim strSQL As String
Dim intCol As Integer
Dim sngTotal As Single
Dim sngTime As Single
sngTime = Timer
grdOrders.Col = 0
txtDetailsTime.Text = ""
'Get details for selected order
strSQL = "SELECT Order_Details.Quantity, "
strSQL = strSQL & "Products.ProductName, "
strSQL = strSQL & "Order_Details.UnitPrice "
strSQL = strSQL & "FROM Order_Details, Products "
strSQL = strSQL & "WHERE OrderID = " & grdOrders.Text
strSQL = strSQL & " AND Products.ProductID = "
strSQL = strSQL & "Order_Details.ProductID"
Set rslDetails = conUser.OpenResultset(strSQL, rdOpenStatic)
rslDetails.MoveLast
'Clear the detail grid, but save fixed row backcolor
grdDetails.Rows = 2
'Extra rows are for Subtotal, freight, and order total
grdDetails.Rows = rslDetails.RowCount + 4
rslDetails.MoveFirst
grdDetails.Row = 0
'Load the details grid and calculate order subtotal
Do Until rslDetails.EOF
grdDetails.Row = grdDetails.Row + 1
For intCol = 0 To 3
grdDetails.Col = intCol
If intCol = 2 Then
'Format unit price currency column
If grdDetails.Row = 1 Then
grdDetails.Text = Format(rslDetails(2), "$0.00")
Else
grdDetails.Text = Format(rslDetails(2), "0.00")
End If
ElseIf intCol = 3 Then
'Format extended amount column
If grdDetails.Row = 1 Then
grdDetails.Text = Format(rslDetails(2) * _
rslDetails(0), "$0.00")
Else
grdDetails.Text = Format(rslDetails(2) * _
rslDetails(0), "0.00")
End If
Else
grdDetails.Text = rslDetails(intCol) & " "
End If
Next intCol
'Accumulate subtotal
sngTotal = sngTotal + (rslDetails(2) * rslDetails(0))
rslDetails.MoveNext
Loop
'Add subtotal, freight, and order total to detail grid
grdDetails.Row = grdDetails.Row + 1
grdDetails.Col = 1
grdDetails.Text = "Subtotal"
grdDetails.Col = 3
grdDetails.Text = Format(sngTotal, "$0.00")
grdDetails.Row = grdDetails.Row + 1
grdDetails.Col = 1
grdDetails.Text = "Freight"
grdDetails.Col = 3
grdDetails.Text = Format(asngFreight(grdOrders.Row), "0.00")
grdDetails.Row = grdDetails.Row + 1
grdDetails.Col = 1
grdDetails.Text = "Order Total"
grdDetails.Col = 3
grdDetails.Text = Format(asngFreight(grdOrders.Row) _
+ sngTotal, "$0.00")
rslDetails.Close
Set rslDetails = Nothing
txtDetailsTime.Text = Format(Timer - sngTime, "0.000")
End Sub
Private Sub Form_Unload(Cancel As Integer)
'Formally close the connection and environment
conUser.Close
envUser.Close
End Sub
Private Sub Command1_Click()
Call Form_Activate
End Sub
Static (or keyset) cursors are needed in the preceding code because you can't determine the RowCount value of a rdoResultset object without traversing the result set, which prevents the use of a faster forward-only cursor. A valid RowCount value is required to ReDim arrays and to set the Rows property value of the Grid controls. If you don't need an array to hold additional values, you can populate combo and list boxes with a rdoResultset that uses a forward-only cursor.
The rdoPreparedStatement object is similar in concept to the QueryDef object of the Jet DAO. Both rdoPreparedStatement and QueryDef objects are preprocessed (also called precompiled) queries that are designed to improve execution time,
especially of parameterized queries. QueryDef objects are persistent for the lifetime of the .mdb file in which the query is store or until explicitly deleted from the QueryDefs collection. The persistence of rdoPreparedStatement objects is limited to the
lifetime of an instance of a Visual Basic executable application or, in design/run mode, the current instance of Visual Basic 4.0, unless you write code to delete an object from its collection. The sections that follow describe the characteristics of
rdoPreparedStatement objects and how you use preprocessed queries to improve client/server front-end performance.
The primary benefit of taking the extra step to create a rdoPreparedStatement object and then opening an rdoResultset based on the rdoPreparedStatement object is that you create the prepared statement once, regardless of the number of times it is
executed during your application's lifetime. The syntax for the CreatePreparedStatement method of the rdoConnection object is as follows:
Set prsPrepStmt = _ conConnection.CreatePreparedStatement(strName, strSQL)
The strName and strSQL arguments are required, but may be empty strings (""). If you use an empty string as the value of strSQL, you must set the value of the SQL property of the rdoPreparedStatement prior to executing
the prepared statement by opening a rdoResultset or applying the Execute method.
Few applications require repetitive execution of identical queries, but many front-ends involve queries that are identical except for the value of one or more WHERE clause criteria. Therefore, most developers use rdoPreparedStatement objects in
conjunction with one or more replaceable parameters (designated by ?) in the passthrough strSQL statement, as in the following example:
SELECT OrderID, OrderDate, ShippedDate, Freight FROM Orders WHERE CustomerID = ? ORDER BY OrderDate DESC
Each ? in the strSQL statement (or the SQL statement assigned to the value of the rdoPreparedStatement's SQL property) automatically adds a member to the object's Parameters collection, beginning with the Parameter(0) member. The instruction for
assigning a value to a Parameter object is
prsPrepStmt.Parameters(n) = typValue
where n is the ordinal position of each ? in the SQL statement, beginning with 0 for the first ?. As an example, to return the orders for Alfred Futterkiste (customer code ALFKI), the first customer in the Customers table of Northwind.mdb, the
instruction to set the parameter value of a prepared statement having the preceding SQL property value is this:
prsOrders.Parameters(0) = "ALFKI"
You don't need to be concerned with data types when assigning parameter values. The Type property of a rdoParameter object is read-only. All parameters are passed to the ODBC driver as Variant data. Numeric values are not enclosed within double quotes, but you must use quoted date values for literals in a format that's accepted by the server. Using Jet SQL's # date delimiter, as in #01/01/95#, returns a syntax error message from SQL server.
To execute the parameterized query, you apply the OpenResultset method to the rdoPreparedStatement object as in the following example:
prsOrders.OpenResultset(rdOpenForwardOnly, _ rdConcurReadOnly, rdAsyncEnable)
You can omit the OpenResultset arguments, but not the empty parenthesis, to create a default read-only keyset result set that operates synchronously.
The rdoPreparedStatement object has variety of properties, many of which let you control the behavior of rdoResultset objects created from rdoPreparedStatement objects. Table 22.4 lists the properties of the rdoPreparedStatement object, the data type
of the property, and a brief description of the purpose of the property, including default values, where applicable. Table 22.5 lists the four methods applicable to the rdoPreparedStatement object.
| Property Name | Data Type | Purpose |
| BindThreshold | Long | Sets or returns the maximum number of bytes returned by a column without using the GetChunk method. The default value is 1,024 bytes. |
| Connect | String | The ODBC connect string (without the ODBC; prefix). The value is read-only. |
| ErrorThreshold | Long | A value representing the level of severity of an error necessary to trigger a trappable error. The default value is -1, which ignores the error threshold. |
| hStmt | Long | The handle to the ODBC statement for the object. |
| KeysetSize | Long | The number of rows included in the keyset or static cursor for the rdoResultset object. The default is 0 (all rows use the keyset cursor). |
| LockType | Long | The type of locking employed by the rdoResultset object specified by the constants rdConsurReadOnly (default), rdConcurLock (pessimistic locking), rdConcurRowVer (optimistic locking based on row ID), rdConcurValues (optimistic locking based on row values). |
| LogMessages | String | The path to and name of a text file to log ODBC messages. A Null value turns off logging. |
| MaxRows | Long | The maximum number of rows to be returned by a query. The default value is -1, which returns all rows. |
| Name | String | The name of the object specified by the strName argument of the CreatePreparedStatement method. |
| QueryTimeout | Long | The number of seconds before an error is generated if execution is not complete. The default value is 0 (unlimited). |
| RowsAffected | Long | The number of rows modified by application of the Execute method (for action queries only). |
| RowsetSize | Long | The number of keyset rows buffered by the applications. The default value is 100. |
| SQL | String | A valid SQL statement or ODBC escape statement that determines the content of the rdoResultset object, the action to be performed, or the stored procedure to execute preceded by EXEC. |
| StillExecuting | Boolean | True if the query is in the process of execution; False otherwise. (Valid only if the rdOpenAsynch option is specified.) |
| Type | Integer | rdQSelect (0) for a SELECT query, rdQAction (1) for an action query, rdQProcedure (2) for execution of a stored procedure. The value is read-only. |
| Updatable | Boolean | True if the rdoResultset object is updatable; False otherwise. The value is read-only. |
| Method Name | Purpose |
| Cancel | Cancels processing of an asynchronous query (specified by the rdOpenAsync option). |
| Close | Closes an open rdoPreparedStatement object. |
| Execute | Executes a rdoPreparedStatement object of the rdQAction type. |
| OpenResultset | Opens a rdoResultset based on the SQL property of the object for the rdQSelect type or, for the rdQProcedure type, a stored procedure that returns rows. |
Prepared statements without parameters create a temporary stored procedure in the database specified by the ODBC datasource or the DATABASE=DatabaseName entry in the connect string of the rdoConnection object. The following is an example of a
temporary stored procedure (also called a server prepared statement) created in the Northwind SQL Server database by a rdoPreparedStatement object without a replaceable parameter:
/* Remove the following three lines if you do not want _
the object dropped. */
IF EXISTS (SELECT * FROM sysobjects WHERE id = _
object_id('dbo.odbc#saddf6693'))
DROP procedure dbo.odbc#saddf6693
GO
create proc odbc#saddf6693 as SELECT CustomerID, _
CompanyName FROM Customers
Visual Basic line continuation pairs appear in the preceding SQL statement because line breaks are added to meet publishing line length limitations. The SQL statement displayed in the Manage Stored Procedures window of SQL Object Manager or SQL Enterprise Manager has a total of six lines, one of which is empty.
Once the odbc#saddf6693 procedure is created, subsequent execution of the query occurs with a Transact-SQL EXEC odbc#saddf6693 statement, instead of the SELECT statement. (Transact-SQL is the extended SQL dialect of SQL Server.) When you close the
Visual Basic executable application or close the current design/run instance of Visual Basic, the temporary stored procedure is DROPped. The advantages of the use of server stored procedures are discussed in the "Using Parameterized Server Stored
Procedures with Prepared Statements" section, near the end of this chapter.
If your application or Visual Basic crashes after having created the server stored procedure, "orphan" stored procedures will remain in the database. Periodically use the Manage | Stored Procedures command of SQL Object Manager or SQL Enterprise Manager to check for orphan stored procedures when developing Visual Basic applications that use the rdoPreparedStatement object.
It's a relatively simple process to alter the code of the frmRDO_Test form to use parameterized rdoPreparedStatement objects to retrieve orders (prsOrders) for a selected customer and to display order details (prsDetails). The step-by-step
modifications are as follows:
Listing 22.5 shows the code representing the changes described in the preceding steps, except the added Call statement. Code that's the same (as in the cboCustomers_Click() and grdOrders_SelChange() event-handlers of frmRDO_Test) is replaced by
an ellipsis to minimize duplication. The code of Listing 22.5 is incorporated in frmRDO_Prep of the RDO_Prep.vbp project in the \DDG_VB4\32_bit\Chaptr22 folder of the accompanying CD-ROM.
Listing 22.5. Code modifications required to use parameterized rdoPreparedStatement objects.
Private Sub PrepareStatements()
'Create rdoPreparedStatements
'Called at end of Form_Load subprocedure
Dim strSQL As String
strSQL = "SELECT OrderID, OrderDate, "
strSQL = strSQL & "ShippedDate, Freight FROM Orders "
strSQL = strSQL & "WHERE CustomerID = ? "
strSQL = strSQL & "ORDER BY OrderDate DESC"
Set prsOrders = conUser.CreatePreparedStatement("Orders", _
strSQL)
strSQL = "SELECT Order_Details.Quantity, "
strSQL = strSQL & "Products.ProductName, "
strSQL = strSQL & "Order_Details.UnitPrice "
strSQL = strSQL & "FROM Order_Details, Products "
strSQL = strSQL & "WHERE OrderID = ? "
strSQL = strSQL & "AND Products.ProductID = "
strSQL = strSQL & "Order_Details.ProductID"
Set prsDetails = conUser.CreatePreparedStatement("Details", _
strSQL)
End Sub
Private Sub cboCustomers_Click()
'Find latest orders for CustomerID picked in combo box
fInProcess = True
Dim intCol As Integer
Dim sngTime As Single
txtOrdersTime.Text = ""
sngTime = Timer
'Set parameter value for the selected customer
prsOrders.rdoParameters(0) = astrCustID(cboCustomers.ListIndex)
Set rslOrders = prsOrders.OpenResultset(rdOpenStatic)
... (Same code as for frmRDO_Test)
End Sub
Private Sub grdOrders_SelChange()
'Find detail records for order number clicked in grid
If fInProcess Then
'Don't process during loading operation
Exit Sub
End If
Dim intCol As Integer
Dim sngTotal As Single
Dim sngTime As Single
sngTime = Timer
grdOrders.Col = 0
txtDetailsTime.Text = ""
'Set parameter value for selected order
prsDetails.rdoParameters(0) = grdOrders.Text
Set rslDetails = prsDetails.OpenResultset(rdOpenStatic)
... (Same code as for frmRDO_Test)
End Sub
Figure 22.10 illustrates the improved performance achieved by using parameterized rdoPreparedStatement objects in conjunction with rdoResultset objects. Query execution speed of frmRDO_Prep to populate the two Grid controls is faster than frmRDO_Test
by a significant margin. The much better refresh time for grdDetails is attributable to the more complex SQL statement required to populate the order details grid.
Figure 22.10. The frmRDO_Prep form displaying typical times to execute parameterized rdoPreparedStatement queries.
Server stored procedures are the client/server counterpart of Jet's QueryDef object. Stored procedures are compiled by the server's operating system and, like QueryDefs, are optimized in accordance with an execution plan. Also like QueryDefs, stored
procedures are saved in the database that contains the associated tables; thus, stored procedures have long-term persistence. (SQL Server's system stored procedures are stored in the master database.) Stored procedures offer the following advantages
compared with sending conventional SQL statements to the server:
Although you can execute passthrough queries with the Jet DAO, Jet passthrough syntax only supports input parameters that pass values to the stored procedure. The rdoPreparedStatement object and its Parameters collection are designed specifically to
accommodate all three types of stored procedure parameters: input, output, and return value. The sections that follow describe how to create rdoPreparedStatement objects that use input parameters and the ODBC syntax for dealing with input, output, and
return value parameters. The RDO_SPs.vbp project, which contains demonstration code for using SQL Server stored procedures, is included in the \DDG_VB4\32_bit\Chaptr22 folder of the accompanying CD-ROM.
Before you can run the RDO_SPs.vbp project, you must add server stored procedures to return the rdoResultset objects that populate the ComboBox and two Grid controls of the frmRDO_SPs form. The following are the three Transact-SQL statements required
to create the sp_customers, sp_orders, and sp_details stored procedures:
CREATE PROCEDURE sp_customers AS
SELECT CustomerID, CompanyName FROM Customers
CREATE PROCEDURE sp_orders @customerid varchar(5) AS
SELECT OrderID, OrderDate, ShippedDate, Freight
FROM Orders WHERE CustomerID = @customerid
ORDER BY OrderDate DESC
CREATE PROCEDURE sp_details @orderid int AS
SELECT Order_Details.Quantity, Products.ProductName,
Order_Details.UnitPrice
FROM Order_Details, Products
WHERE OrderID = @orderid
AND Products.ProductID = Order_Details.ProductID
The first of the preceding statements creates a simple, non-parameterized stored procedure, sp_customers, which is used to create the rslCustomers result set that populates the cboCustomers combo box. The sp_orders and sp_details procedures populate
the two Grid controls. The sp_orders and sp_details procedures each have a single parameter specified by the @paramname datatype prefix to the AS reserved word, after which appears the SQL statement for the procedure. A datatype keyword, such as
char(n), varchar(n), or int, is required for each parameter. (Multiple parameters are separated by commas.) You can specify a default value by appending an equal sign and the default value, as in
CREATE PROCEDURE sp_orders @customerid varchar(5) = '%' AS ...
which returns orders for all customers if the parameter value is not supplied. You specify an output parameter by appending OUT[put], as in
CREATE PROCEDURE sp_orders ..., @count int OUT AS ...
The sp_ prefix ordinarily is used to specify SQL Server system stored procedures. In the examples of this chapter, the sp_ prefix denotes stored procedure. When creating production stored procedures, avoid using the sp_ prefix. Many developers use as a prefix an abbreviation of the database name, such as nw_orders.
You can execute a non-parameterized stored procedure from the isql prompt by the procedure's name only; however, you must use the EXEC(ute) prefix with rdoPreparedStatement object, as in EXEC sp_name. When executing parameterized stored
procedures, ODBC uses escape syntax. ODBC escape syntax, which surrounds non-conforming SQL statements with French braces ({}), is briefly described in the "Differences Between Jet SQL and ANSI/ODBC SQL Statements Created with Microsoft Query" of
Chapter 19, "Using the Open Database Connectivity API." The general syntax for the SQL property of a rdoPreparedStatement object with one or more parameters is
strSQL = "{ [? = ]call proc_name (? [, ?[, ?]]) }"
where [ ? = ] represents a return value parameter and the remaining ? symbol(s) represent one or more input or output parameters. Input and/or output parameters must be enclosed within parentheses and separated by commas. The ordinal value n of
the Parameters(n) collection is determined by the left to right position of the ? in the statement.
The call keyword of the ODBC escape syntax is the equivalent of the EXEC(ute) reserved word of Transact-SQL. You must include call in the SQL property value for rdoPreparedStatements that execute parameterized stored procedures.
You must specify the Direction property of the rdoParameter object when using output or return value parameters. For an input/output or output-only parameter, set the Direction property value to rdParamInputOutput or rdParamOutput; for a return value
parameter, use rdParamReturnValue. The default Direction property value is input (rdParamInput, 0).
The changes to adapt the code of the frmRDO_Prep form to substitute stored parameterized procedure calls for conventional SQL statements is shown in Listing 22.6. The PrepareStatements subprocedure of the frmRDO_SPs form of the RDO_SPs.vbp project is
much simpler than that of the frmRDO_Prep form. To conserve space, Listing 22.6 includes only the changes to the VBA code of frmRDO_Prep required to accommodate the three stored procedures. The parameters passed to the prsOrders and prsDetails prepared
statements are the same for rdoPreparedStatement objects that use stored procedures or conventional SQL statements.
Listing 22.6. Code modifications required to use parameterized rdoPreparedStatement objects to execute stored procedures.
Private Sub PrepareStatements()
'Create rdoPreparedStatements
'Called at end of Form_Load subprocedure
Dim strSQL As String
strSQL = "EXEC sp_customers"
Set prsCustomers = conUser.CreatePreparedStatement("Customers", _
strSQL)
strSQL = "{ call sp_orders (?) }"
Set prsOrders = conUser.CreatePreparedStatement("Orders", strSQL)
strSQL = "{ call sp_details (?) }"
Set prsDetails = conUser.CreatePreparedStatement("Details", _
strSQL)
End Sub
Private Sub Form_Activate()
'Load the combo with CompanyNames
Dim sngTime As Single
sngTime = Timer
'Execute sp_customers stored procedure
Set rslCustomers = prsCustomers.OpenResultset(rdOpenStatic)
... (Same code as for frmRDO_Test)
End Sub
Figure 22.11 shows the improved query execution speed when filling the grdOrders and grdDetails Grid controls with rdoResultset objects based on server stored procedures. The time to fill the cboCustomers ComboBox control is about the same as that for
a rdoPreparedStatement that uses an SQL statement, because both prepared statements are stored on the server.
Figure 22.11. Improved query execution times resulting from execution of SQL Server stored procedures.
You can apply the Edit, AddNew, Delete, and Update methods to any rdoResultset object that returns for the corresponding rdoPreparedStatement object an Updatable property value of True. There are no substantial differences in the application of
methods to updatable rdoResultset and to DAO's Recordset objects. As a general practice, however, it is a more common practice to employ SQL action queries, such as UPDATE, INSERT, and DELETE queries, wrapped in BEGIN TRANS(action) and COMMIT TRANS(action)
statements. For simple, single-table INSERT, UPDATE, and DELETE operations, parameterized stored procedures offer a slight performance edge. Listing 22.7 is typical of the VBA code to add a new order and its associated line items to the Orders and
Order_Details table of the Northwind data source by executing an rdoPreparedStatement object.
Listing 22.7. An example of VBA code for adding a new order that contains multiple line items stored in a temporary local Recordset.
Private Sub AddNewOrder ()
'INSERT the order information (one very long SQL statement)
strSQL = "BEGIN TRAN INSERT Orders VALUES(" & txtOrderID & ", '"
strSQL = strSQL & txtCustomerID & "', "
strSQL = strSQL & cboEmployeeID & ", '"
strSQL = strSQL & txtShipName & "', '"
strSQL = strSQL & txtShipAddress & "', '"
strSQL = strSQL & txtShipCity & "', '"
strSQL = strSQL & txtShipRegion & "', '"
strSQL = strSQL & txtShipPostalCode & "', '"
strSQL = strSQL & txtShipCountry & "', "
strSQL = strSQL & cboShipVia & ", '"
strSQL = strSQL & txtOrderDate & "', '"
strSQL = strSQL & txtRequiredDate & "', "
strSQL = strSQL & "null, null) "
'INSERT the line item information for each record
rsLineItems.MoveFirst
Do Until rsLineItems.EOF
strSQL = strSQL & "INSERT Order_Details VALUES(" & _
lngOrder_ID & ", "
strSQL = strSQL & rsLineItems!ProductID & ", "
strSQL = strSQL & rsLineItems!UnitPrice & ", "
strSQL = strSQL & rsLineItems!Quantity & ", "
strSQL = strSQL & rsLineItems!Discount & ") "
rsLineItems.MoveNext
If rsLineItems.EOF Then
'Finish the transaction
strSQL = strSQL & "COMMIT TRAN"
End If
Loop
'Execute the SQL statement
Set prsNewOrder = conUser.CreatePreparedStatement("NewOrder", _
strSQL)
prsNewOrder.Execute
End Sub
The 32-bit Remote Data Object of Visual Basic 4.0's Enterprise Edition demonstrates improved performance compared with Jet 3.0's Data Access Object when accessing client/server databases. This chapter provided a series of demonstration applications to
compare the speed of query processing using the Remote Data control with DBCombo and DBGrid controls and the RDO's rdoResultset object to populate unbound ComboBox and Grid controls. The chapter closed with examples of the use of server stored procedures
with rdoPreparedStatement objects and how to write code to execute an INSERT-type rdoPreparedStatement.
The next two chapters cover subjects that are of primary interest to developers of enterprise-scale applications: designing three-tier client/server architecture that uses Visual Basic 4.0 OLE servers as the middle tier and taking advantage of the
project management features of Visual SourceSafe 4.0 in a multi-developer environment.
