A First Look at Remote Data Objects and the RemoteData Control
William R. Vaughn
Microsoft Corporation
February 1996
More and more Visual Basic® developers are creating front-end applications to remote database servers. In previous versions of Visual Basic, the options available were limited to either Jet Data Access Objects (DAO) or an application programming interface (API) –based interface such as VBSQL. Visual Basic Version 4.0 Enterprise Edition now includes an entirely new way to access data in intelligent servers such as Microsoft® SQL Server: the Remote Data Objects (RDO). Remote Data Objects implement a set of objects to deal with the special requirements of remote data access. RDO implements a thin code layer over the Open Database Connectivity (ODBC) API and driver manager that establishes connections, creates result sets and cursors, and executes complex procedures using minimal workstation resources. Because bound controls sometimes play an important role in these applications, the Enterprise Edition also includes the RemoteData control that takes on the same functionality as the Data control.
This article provides an overview of the features of the Remote Data Objects programming model and the RemoteData control. This information can help you make a decision regarding which data access programming model to use in your client/server application.
With Remote Data Objects (RDO) and the RemoteData control, your applications can access Open Database Connectivity (ODBC) data sources without using a local query processor. This can mean significantly higher performance and more flexibility when accessing remote database engines. Although you can access any ODBC data source with RDO and the RemoteData control, these features are designed to take advantage of database servers, such as Microsoft® SQL Server and Oracle, that use sophisticated query engines.
By using RDO, you can create simple cursorless result sets, or more complex cursors—even using SQL Server "server-side" cursors. You can also run queries that return any number of result sets, or execute stored procedures that return result sets with or without output parameters and return values. You can limit the number of rows returned or updated and monitor all of the messages and errors generated by the remote data source without compromising the executing query. RDO also permits either synchronous or asynchronous operation so your application doesn't need to be blocked while lengthy queries are executed.
Note RDO and the RemoteData control are features of Visual Basic® Version 4.0, Enterprise Edition. You cannot develop code or use the RDO object library or RemoteData control in the Professional or Standard Editions of Visual Basic.
RDO and the RemoteData control can help you meet a specific set of client/server requirements. By using these remote data access features, you can:
Basically, you can use the Remote Data Objects in the same way you use the Microsoft Jet database engine Data Access Objects (DAO), and the RemoteData control is similar to the Data control. With RDO, you can submit queries, create a result set or cursor, and process the results from the query using database-independent object-oriented code. Using the RemoteData control, you can create a form containing the same bound controls recognized by the Data control, and process a result set with little or no code. Table 1 lists RDO objects and their equivalent DAO objects.
Table 1. RDO Objects and Equivalent DAO Objects
| RDO Object | Equivalent DAO Object |
| rdoEngine | DBEngine |
| Not implemented | User, Group |
| rdoEnvironment | Workspace |
| rdoConnection | Database |
| rdoTable | TableDef |
| Not Implemented | Index |
| rdoResultset | Recordset |
| Not implemented | Table-type |
| Keyset-type | Dynaset-type |
| Static-type | Snapshot-type |
| Dynamic-type | (none) |
| Forward-only – type | Similar to Forward-Only Snapshot |
| rdoColumn | Field |
| rdoPreparedStatement | QueryDef |
| rdoParameter | Parameter |
RDO refers to table rows instead of records, and columns instead of fields—the generally accepted terminology for relational databases. The data returned from a query is in the form of result sets, which can contain zero or more data rows composed of one or more columns.
Some DAO objects, methods, and properties are designed to implement and support the Indexed Sequential Access Method (ISAM) structure of Jet and installable ISAM databases. For example, you can use the Index object and the Seek method to manage ISAM indexes and locate rows based on those indexes. Because the RDO and relational databases manage indexes in an entirely different manner, these objects and methods are not needed.
Jet also supports the creation and modification of the database schema through DAO methods and properties. RDO does not support any type of schema modification because this is fully supported in the tools and utilities provided with the server systems. You can still run make-table queries or execute action queries that create, modify, or delete databases and tables using native Structured Query Language (SQL, pronounced "sequel") statements. You can also execute complex stored procedures that manage the database schema or perform maintenance operations that are not possible with DAO.
In most respects, Remote Data Objects are used like Jet Data Access Objects. This means that, for the most part, you can take your existing applications that use DAO and the Data control and convert them to use RDO and the RemoteData control with a minimum number of changes. There are some differences, however, because of the way RDO is implemented and designed for use in relational databases. RDO has no query processor; it depends on the data source to process all queries and create the result sets. The data objects themselves are built from the result sets and cursors returned by the ODBC driver.
You can use the RemoteData control to create RDO objects, just as you can use the Data control to create Jet DAO objects. You can also create result sets using RDO methods and pass these to the RemoteData control for management and editing by associated bound controls.
RDO objects and collections provide a framework for using code to create and manipulate components of a remote ODBC database system. Objects and collections have properties that describe the characteristics of database components and methods that you use to manipulate them. Using the containment framework, you create relationships among objects and collections, and these relationships represent the logical structure of your database system.
With the exception of the rdoEngine object, each of these objects is maintained in an associated collection. When RDO is initialized on first access, RDO automatically creates an instance of the rdoEngine and the default rdoEnvironments(0).
Note RDO is only supported on 32-bit operating systems (Windows® 95 and Windows NT®). To use the Remote Data Objects, you must set a reference to the Microsoft Remote Data Object 1.0 object library in the Visual Basic References dialog box.
To establish a physical link to an ODBC data source and a specific database, your code must create an rdoConnection object. You can define the data source by using the rdoRegisterDataSource method in code or by using the ODBC Data Sources settings in the Windows Control Panel. The database associated with the rdoConnection is determined by connect string arguments or the default database assigned to the user.
Basically, there are two approaches to executing queries and retrieving result sets:
Using arguments of the OpenResultset method or properties of the rdoPreparedStatement object, you can specify the type of cursor (if any) and other attributes of the rdoResultset object.
You can use the rdoTable object to map the tables and columns of a data source or create rdoResultset objects from all rows in the table—which is not recommended with RDO. There are very few cases in which it is necessary to retrieve all rows from a database table into workstation memory. Many remote database tables are far too large to be downloaded to the workstation.
To reposition the current row pointer in an rdoResultset, use the Move methods, or the AbsolutePosition and PercentPosition properties. You can also save and use bookmarks to position to selected locations in an rdoResultset. To locate a value in an rdoResultset, refine the WHERE clause and resubmit the query.
The rdoEngine object represents the remote data source and is created automatically when you make any reference to RDO or the RemoteData control. As the top-level object, it contains all other objects in the hierarchy of Remote Data Objects. The rdoEngine object is a predefined object, therefore you can't create additional rdoEngine objects, and it isn't an element of any collection. You can use the rdoEngine to set data source parameters and the default rdoEnvironment.
Even though the rdoEngine is shared among applications that use it, the rdoEngine default properties are not shared between multiple applications that are using it. Each instance of your application is provided with its own set of default values that have no effect on other applications that also use RDO or the RemoteData control.
Note Adding the RemoteData control to your Toolbox does not automatically set a reference to the Microsoft Remote Data Object 1.0 object library. To use the rdoEngine and Remote Data Objects, you must set a reference to the Microsoft Remote Data Object 1.0 object library in the References dialog box (available from the Tools menu); otherwise, you will get compilation errors.
For more information, search Visual Basic Books Online or online Help for "rdoEngine object".
When you create new rdoEnvironment, rdoConnection, or rdoResultset objects, the characteristics of the new objects are determined by the arguments in the rdoCreateEnvironment or OpenResultset method or by the default values in the default properties of the rdoEngine. These properties are listed in Table 2.
Table 2. Properties of the rdoEngine
| Property | Specifies | Default |
| rdoDefaultCursorDriver | Cursor location (ODBC or server-side) | rdUseIfNeeded |
| rdoDefaultPassword | User password | "" (empty string) |
| rdoDefaultUser | User name | "" (empty string) |
| rdoDefaultErrorThreshold | Error severity above which errors are fatal | -1 (disabled) |
| rdoDefaultLoginTimeout | Time to wait before abandoning connection attempt | 15 seconds |
You can change any of these default value properties before creating new rdoEnvironment, rdoConnection, or rdoResultset objects. However, rdoEnvironments(0) is created using the defaults shown in Table 2. This is because it is created automatically on the first reference to RDO or the RemoteData control. If the default values are not appropriate for your application, you can change the properties of rdoEnvironments(0) or the rdoEngine before opening a connection.
All RDO operations require that you first connect to a suitable ODBC data source. A data source can be any database system or file that is supported with an ODBC driver. You can access any ODBC data source with RDO; however, a number of optimizations have been made to support specific features of Microsoft SQL Server and Oracle databases. Because of this, some features provided by RDO may not be suitable for use with ISAM or other flat-file data sources.
Before you can access a data source, you must first use the ODBC Data Sources dialog box in the Windows Control Panel to create a data source entry in the Windows Registry. You can also use the rdoRegisterDataSource method to register a new data source. Registering a data source is a one-time operation that should take place when the application is first installed with Setup.
If your application expects to support more than one transaction scope, or separate user name and password contexts, you should use the rdoCreateEnvironment method to create a new rdoEnvironment object with specific user name and password values. This method accepts a unique name, a user name, and password. If the name you choose matches the name of an existing member of the rdoEnvironments collection, a trappable error results.
The default rdoEnvironments(0) is created automatically when the RemoteData control is initialized, or when the first RDO object is referenced in code. The Name property of rdoEnvironments(0) is "Default_Environment". The user name and password for rdoEnvironments(0) are both zero-length strings ("").
Newly created rdoEnvironment objects are automatically appended to the rdoEnvironments collection if you provide a unique name. You can also use a zero-length string for the name argument of the rdoCreateEnvironment method. In this case, the new rdoEnvironment is not appended to the rdoEnvironments collection.
The user name and password information from the rdoEnvironment is used to establish the connection if these values are not supplied in the connect argument of the OpenConnection method, or in the Connect property of the RemoteData control. For example, the default user name (Fred) and password (Blond) can be used to establish a connection in the En environment:
Dim En As rdoEnvironment
Set En = rdoCreateEnvironment("", "Fred", "Blond")
An rdoEnvironment object logically corresponds to an ODBC environment. You can refer to an rdoEnvironment object by using the ODBC API functions, accessible through the rdoEnvironment object's hEnv property. However, because ODBC allows only one environment handle per application, the actual lifetime of the ODBC environment handle is tied to the lifetime of the rdoEngine. Actual ODBC connections take place only when an rdoConnection object is opened.
The rdoEnvironment also determines transaction scope. Committing an rdoEnvironment transaction commits all open rdoConnection databases with pending transactions (which are opened on that rdoEnvironment object, and their corresponding open rdoResultset objects). This does not imply a two-phase commit operation—it simply means that individual rdoConnection objects are instructed to commit any pending transactions.
Use the rdoEnvironment object to start an additional connection. In an rdoEnvironment, you can open multiple databases, manage transactions, and establish security based on user names and passwords. For example, you can:
Note The ODBC transaction model does not support nested transactions. That is, you cannot execute a second BeginTrans method before the previous transaction is either committed or rolled back. However, if your ODBC data source supports it, you can use SQL statements to execute nested transactions.
When you use ODBC transactions, all databases in the specified rdoEnvironment are affected—even if multiple rdoConnection objects are opened in the rdoEnvironment. For example, if you use a BeginTrans method against one of the databases visible from the connection, you should update several rows in the database, and then delete rows in another rdoConnection database. When you use the RollbackTrans method, both the update and delete operations are rolled back. You can create additional rdoEnvironment objects to manage transactions independently across rdoConnection objects. Transactions executed by multiple rdoEnvironment objects are serialized and are not atomic operations. Because of this, their successes or failures are not interdependent.
Once a data source is identified, you use the OpenConnection method to create an rdoConnection object, with which you establish a physical link to the data source. To establish a connection, the data source entry must identify the network location of the data source, as well as the driver type. Your code provides a number of additional parameters used to log the user onto the data source. By choosing an appropriate prompt argument, you can program the ODBC driver manager to prompt the user for missing arguments and prevent the use of alternate arguments. The OpenConnection method accepts the following arguments:
For example, you can use the following code to establish a connection to a Microsoft SQL Server database with an existing data source name of MyRemote:
Dim Cn As rdoConnection, En as rdoEnvironment, Conn As String
Set En = rdoEnvironments(0)
Conn$ = "DSN=MyRemote;UID=Holly;PWD=Huskador;DATABASE=MyDb;"
Set Cn = En.OpenConnection("", rdDriverPrompt, False, Conn$)
The dsName argument of the OpenConnection method is an empty string. In this case, the data source name is taken from the connect argument.
Note Each of the RDO methods supports named arguments, so it is possible to specify each argument of the method using the argument:= syntax. For example, the previous OpenConnection method could be coded:
Set Cn = En.OpenConnection(prompt:=rdDriverPrompt, _
readonly:=False,Connect:=Conn$)
If you choose to use domain-managed security, you should use empty arguments for the UID and PWD parameters of the connect argument. This type of security passes your Windows NT logon ID and password to the data source. If your database administrator has implemented integrated or mixed security, this technique should permit you to log on to the data source—assuming you have been granted permission to do so. For example, using the preceding sample code, a domain-managed security connect argument is coded as follows:
Conn$ = "DSN=MyRemote;UID=;PWD=;DATABASE=MyDb;"
You might be unable to establish a connection for a variety of reasons, including lack of permission on the data source, improper network connection or permissions, or a missing or disabled data source. When connecting to Microsoft SQL Server, Oracle, or other data sources, the number of simultaneous connections permitted might be limited by license agreements, resource constraints, or by database settings. Check with your server administrator if you suspect that all available connections are allocated.
Once the rdoConnection object is created, you can use it to:
When using remote data objects against a SQL Server that has case-sensitivity enabled, if your table name contains any upper-case letters the Update method will fail with an 'invalid object <table name>' error. This will occur only when using the ODBC Cursor Library against a case-sensitive SQL Server. To work around this problem you have several options:
If the above options are not available, you can issue update SQL statements to the server using the Execute method.
This does not affect Oracle servers.
To extract data from the data source, use the OpenResultset method against an rdoConnection object. You can also create rdoResultset objects by using parameterized queries with rdoPreparedStatement.OpenResultset.
When you use the OpenResultset method against an rdoConnection or rdoPreparedStatement, and assign the result to an existing rdoResultset object, the existing object is maintained and a new rdoResultset object is appended to the rdoResultsets collection. When performing similar operations using the Microsoft Jet database engine, existing recordsets are automatically closed when the variable is assigned, and no two Recordsets collection members can have the same name. For example, using RDO:
Dim rs as rdoResultset
Dim cn as rdoConnection
Set cn = OpenConnection....
Set rs = cn.OpenResultset("Select * from Authors", rdOpenStatic)
Set rs = cn.OpenResultset("Select * from Titles", rdOpenDynamic)
This code opens two separate rdoResultset objects; both are stored in the rdoResultsets collection. After this code runs, the second query—which is stored in rdoResultsets(1)—is assigned to the rdoResultset variable rs. The first query is available and its cursor is still available by referencing rdoResultsets(0). Because of this implementation, more than one member of the rdoResultsets collection can have the same name.
This behavior permits you to maintain existing rdoResultset objects, which are maintained in the rdoResultsets collection, or close them as needed. In other words, you must explicitly close any rdoResultset objects that are no longer needed. Simply assigning another rdoResultset to a rdoResultset-type variable has no affect on the existing rdoResultset formerly referenced by the variable. Note that the procedures and other temporary objects created to manage the rdoResultset are maintained on the remote server as long as the rdoResultset remains open.
If you write an application that does not close each rdoResultset before opening additional rdoResultset objects, the number of procedures maintained in TempDB or elsewhere on the server increase each time another rdoResultset object is opened. Over time, this behavior can overflow the capacity of the server or workstation resources.
The OpenResultset method accepts the following arguments when you use it with an rdoConnection object:
In some cases, when you use the OpenResultset method and the SQL statement specified contains invalid SQL syntax, the ODBC driver fails with an untrappable general protection fault (GPF). Not all syntax errors cause this. The following SQL query does cause this type of GPF:
" Select * from authors where name '%' (?) '%' "
To avoid this problem, verify all SQL statements for accuracy and correct syntax before using them in your application. In addition, do not permit users to enter SQL statements directly, because these might be prone to failure.
The rdoResultset is returned either as a forward-only type of result set or as one of the three types of cursors: static, keyset, or dynamic—based on the type available and the capability of the ODBC driver manager and data source driver. Using the CursorDriver property of the rdoEnvironment object, you can also choose where the cursor keyset will be created—on the workstation or on the server.
Forward-Only Result Sets
In cases where you need to retrieve data quickly with the least amount of overhead, use a forward-only type of rdoResultset. This type of result set can be updatable, but it only exposes one row at a time. No cursor keyset is created, and data values are not updated as they change on the data source. However, because of its efficiency, it might be faster to rebuild a forward-only type of rdoResultset than to build and maintain a keyset type of rdoResultset.
Static Cursors
A static-type rdoResultset is similar to a Jet snapshot-type Recordset object. Static cursor data appears to be static. The membership, order, and values in the result set used by a static cursor are generally fixed when the cursor is opened. Rows updated, deleted, or inserted by other users (including other cursors in the same application) are not detected by the cursor until it is closed and reopened.
Keyset Cursors
A keyset-type rdoResultset is similar to a Jet dynaset-type Recordset object. A key is built and saved for each row in the cursor and stored either on the client workstation or on the server machine. When you access each row, the key is used to fetch the current data values from the data source. In a keyset-driven cursor, membership is frozen once the keyset is fully populated, therefore, additions or updates that affect membership are not made a part of the cursor until it is rebuilt.
On some servers, modifications or additions made directly to keyset cursors using the AddNew and Edit methods are included in the result set, but additions or modifications made using Execute do not affect the cursor. Consult your server manual for details.
To build a keyset-driven cursor, you must provide sufficient resources on the client or server to hold the keys and a block of buffered data rows.
Dynamic Cursors
A dynamic-type rdoResultset is identical to a keyset-driven cursor except that membership is not frozen. Because RDO constantly checks that all qualified rows are included in the membership, this type of cursor carries the largest burden of overhead. However, a dynamic cursor may be faster to initiate than a keyset cursor, because the keyset cursor carries the overhead of building the initial keyset.
When a keyset-type or static-type rdoResultset is first created, the RowCount property is set to the number of rows in the result set if rows are returned, 0 if no rows are returned by the query, or -1 if RowCount is not available. RowCount is not applicable to dynamic cursors where the number of rows can change, or to forward-only result sets that expose only one row.
Sequencing Operations
If there is an unpopulated rdoResultset pending on a data source that can only support a single operation on an rdoConnection object, you can neither create additional rdoPreparedStatement or rdoResultset objects, nor use the Refresh method on the rdoTable object until the rdoResultset is flushed, closed, or fully populated. For example, when using SQL Server 4.2 as a data source, you cannot create an additional rdoResultset object until you move to the last row of the current rdoResultset object. To populate the result set, use the MoreResults method to move through all pending result sets, or use the Cancel or Close method on the rdoResultset to flush all pending result sets.
For more information, search Visual Basic Books Online or online Help for "rdoResultset object", "understanding cursors", and "OpenResultset method".
At any given time, only one row in an rdoResultset is exposed for data retrieval or modification—the row addressed by the current row pointer. You can move the current row pointer by using the Move methods, or the AbsolutePosition and PercentPosition properties. The rdoResultset object supports bookmarks that can be used to save the current location in a Variant variable. You can subsequently go back to a saved location in the rdoResultset by setting the Bookmark property with a bookmark.
When using the forward-only type of rdoResultset objects, you can only use the MoveNext method—not the MovePrevious, MoveFirst, or Move method with a negative argument.
Note This is a change from the documentation and help file, which says you can use any of the forward-moving methods on a Forward-Only type cursor. The ODBC driver does not support anything but the MoveNext method—not even MoveLast.
When positioning the current row pointer, it is possible to go past either end of the result set or to a row that has been deleted. RDO may also leave the current row pointer positioned over an invalid row, as when a row is deleted. Be sure to check the EOF and BOF properties to determine if the current row is positioned beyond the end or beginning of the result set. When using the Bookmark property to reposition the current row, it is possible to go to a row that has been deleted by another user. If this occurs, a trappable error results.
For more information, search Visual Basic Books Online or online Help for "BOF property", "EOF property", "AbsolutePosition property", "PercentPosition property", and "Move".
You can use the rdoResultset object to change columns in a specific row, add a row, or delete a row. Data columns can be changed if the Updatable property is True for both the rdoResultset and rdoColumn objects.
To change columns in a specific data row in the rdoResultset:
If you move the current row pointer to another row before using the Update method, any changes are abandoned. You can also abandon an edit and refresh the current row by using the Move method with a 0 argument or by using the CancelUpdate method. After the edit, the current row pointer is positioned on the modified row—which might be located at the end of the rdoResultset. To revisit the row that was just changed, use the bookmark provided in the LastModified property.
If you use the BeginTrans method, changes made to the database with the Update method are deferred until you use the CommitTrans method to save the changes, or the RollbackTrans method to discard the changes.
In addition to changing columns in a specific row, you can add a row to an rdoResultset.
To add a row to an rdoResultset:
After the row is added, the current row pointer is positioned on the row that was current before the new row was added. You can use the LastModified bookmark to move to the newly added row at the end of the rdoResultset.
Once you've added rows, you can also delete them from the result set.
To delete a specific row in an rdoResultset:
Once a row is deleted, the current row is no longer valid, so you must reposition to another valid row in the rdoResultset.
You can control how the data source manages locking while you make changes to the data by changing the LockType option of the OpenResultset method. Table 3 describes the four types of locking.
Table 3. Four LockType Options
| LockType option | Description |
| rdConcurLock | Pessimistic concurrency. This technique uses the lowest level of locking sufficient to ensure that the row can be updated. This option locks the row or the data page containing the row(s) as soon as the Edit or AddNew method is executed, and holds it until the Update method has written the changes to the data source. |
| rdConcurRowver | Optimistic concurrency using row versions. The ODBC cursor library and the data source compare row ID or TIMESTAMP values to determine if the row has changed. |
| rdConcurValues | Optimistic concurrency using row values. The ODBC cursor library and the data source compare data values. |
| rdReadOnly | (Default) The cursor is read-only. No updates are permitted. |
When values or row versions are compared to test concurrency, the original row as it was prior to the Edit or AddNew method is compared with the row as it exists in the database prior to being changed by the Update method. If the row data changed in this timeframe, a trappable error results. At this point, your temporary row buffer is lost. To carry out the changes, you must refresh the current row using the Move0 technique, use the Edit or AddNew method, fill in the new values, and repeat the Update method again.
An alternative to using the Edit, AddNew, Delete, and Update methods is to use the Execute method. By executing a SQL query that contains one or more UPDATE, INSERT, or DELETE statements, you can make changes to the database without using the RDO methods. Depending on the type of data source, and its ability to support complex multi-statement operations, these SQL statements can contain logic that performs so-called "make-table" or SELECT INTO queries that create new permanent or temporary tables, or perform other complex operations. You must manage the errors and concurrency yourself. You can also submit transaction statements that bind the operations into one or more atomic sets using the SQL syntax supported by your data source.
The Execute method is not designed to execute queries that return rows. If you execute a stored procedure that performs a mix of "action" operations and row-returning operations, you must use the OpenResultset method and parse the result sets that are generated.
If your data source supports server-side cursors, the remote database engine builds cursor keysets on the server and extends the SQL dialect to support creation and management of data using cursors. Microsoft SQL Server 6.0 supports server-side cursors, but not all data sources do. Use the rdoDefaultCursorDriver property of the rdoEngine or the CursorDriver property of the rdoEnvironment object to choose the type of cursor driver to use. Table 4 describes the options available with these properties.
Table 4. Options Available to CursorDriver or rdoDefaultCursorDriver
| Option | Description |
| rdUseIfNeeded | (Default) The ODBC driver chooses the appropriate style of cursor and uses server-side cursors if available. |
| rdUseODBC | RDO will use the ODBC cursor library. This gives better performance for small result sets, but degrades quickly for larger result sets. |
| rdUseServer | Server-side cursors are always used if available. |
Server-side cursors reduce the amount of memory and disk space required on the client machines, but shift that load to the server. For Microsoft SQL Server 6.0, cursor keysets are maintained in the TempDB database on the server, so it must be sized to meet the needs of additional temporary cursor keysets generated by multiple clients.
Many DBMSs, including Microsoft SQL Server, support Binary Large Object (BLOB) data types. These types are most often used to store large amounts of text or image data. Due to limitations in the ODBC cursor library, special rules apply to the use of these kinds of data types when using ODBC cursors.
The ColumnSize property on the rdoColumn object represents the actual length of the data in a BLOB column. When using the ODBC cursor library, this value will always be -1, indicating that the data length is not available. When using server-side cursors, the ColumnSize property will always return the actual data length of a BLOB column.
To get the data from a BLOB column, the user must use the GetChunk methods, which take a number of bytes to retrieve at a time. When using server-side cursors, the user can pass the value of the ColumnSize property as the number of bytes to retrieve to get all the data at once. Because the ColumnSize property is not available when using the ODBC cursor library, the user should call GetChunk repeatedly until no more data is returned. Following is a code sample that shows how to do this:
Dim s As String
Dim sTemp As String
Dim lColSize As Long
lColSize = MyResultset!MyBLOBColumn.ColumnSize
If lColSize = -1 Then
' Column size is not available.
' Loop getting chunks until no more data.
sTemp = MyResultset!MyBLOBColumn.GetChunk(50)
Do
s = s & sTemp
sTemp = MyResultset!MyBLOBColumn.GetChunk(50)
Loop While Len(sTemp) > 0
Else
' Get all of it.
If lColSize > 0 Then
s = MyResultset!MyBLOBColumn.GetChunk(lColSize)
End If
End If
In addition, when using ODBC cursor library and BLOB data types, you must select at least one non-BLOB column in your result set so that RDO can use SQLExtendedFetch to retrieve the data. This would be the common case anyway, because you need to include a key field in the result set if you want to update the data.
You can limit the number of rows returned by a query by creating an rdoPreparedStatement and setting the MaxRows property. Once the query processor returns MaxRows rows, it stops working on the query. You can also limit the amount of time the query processor works on a query by setting the QueryTimeout property on the rdoConnection or rdoPreparedStatement.
Note When the SQL_MAX_ROWS ODBC statement option is set to a nonzero value, the maximum number of rows processed by Microsoft SQL Server is limited to n rows. This means that only n rows are returned by a query, or only n rows are inserted, updated, or deleted by an action query. SQL_MAX_ROWS is set indirectly by using the rdoPreparedStatement object's MaxRows property. If you share the hStmt created for an rdoPreparedStatement that has MaxRows set, the operations executed against the hStmt are also affected by the limitation imposed by SQL_MAX_ROWS on both the number of rows returned from a query and the number of rows processed in an action query.
In addition, if you reuse an rdoPreparedStatement that has MaxRows set, the number of rows affected by any update, delete, or insert action query will be limited to n rows.
Any SQL statement can include multiple SELECT statements or stored procedures that invoke one or more SELECT statements. Each SELECT statement generates a result set that must be processed by your code or discarded before the RDO resources are released and the next result set is made available.
Note When executing Remote Data Object (RDO) queries that return more than one set of results, you can use only the ODBC cursor drivers. The Microsoft SQL Server server-side cursors do not support result sets that return more than a single set of results. To enable the ODBC cursor driver, set the rdoEnvironment object's CursorDriver property to rdUseODBC before creating the cursor.
Action queries also generate row-less result sets that must also be processed. For example, if you submit a query that includes four SELECT queries to populate four local ListBox controls, and a stored procedure that updates a table, your code must deal with at least five result sets. Because you might not know how many result sets can be generated by a stored procedure, your code must be prepared to process n sets of results.
There are two approaches to executing queries with multiple result sets:
Both are processed in similar ways, but if you use the rdoPreparedStatement, you can examine the RowsAffected property to determine the number of rows affected by action queries. Although it is possible to execute a multiple result set query using the Execute method, it is not possible to retrieve the rows affected from individual statements, and a trappable error results if any of the queries returns rows.
Multiple Result Sets: An Example
This section takes you through a step-by-step procedure that demonstrates how to execute a query with multiple result sets by using the rdoPreparedStatement.
Dim MySQL As String
MySQL = "Select Name from Authors; "
MySQL = MySQL & " Select City from Publishers; "
MySQL = MySQL & " Update MyTable Set Age = 16 Where Name = 'Fred' "
Dim MyPs As rdoPreparedStatement
Set MyPs = Cn.CreatePreparedStatement("MyPS1", "")
MyPs.SQL = MySQL
Dim MyRs As rdoResultset
Set MyRs = MyPs.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)
The following example fills a ListBox control called NameList1 with the results of the query:
While Not MyRs.EOF ' Loop through all rows.
NameList1.AddItem = MyRs(0) ' Use the first column.
MyRs.MoveNext ' Position to the next row in the result set.
Wend
' Activate the next set of results.
If (MyRs.MoreResults) Then ...
' Loop through some rows.
Do While Not MyRs.EOF and MyRs(0) < "B"
' Use the first column.
NameList1.AddItem = MyRs(0)
MyRs.MoveNext
Loop
' Activate the next set of results and discard remaining rows.
If (MyRs.MoreResults) Then ...
If MyPs.RowsAffected = 0 Then
MsgBox "No rows were updated"
End If
' Activate the next set of results.
If (MyRs.MoreResults) Then ...
When you use the MoreResults method against the last result set, it should return False and other resources required to process the query are released. At this point the rdoPreparedStatement object can be reused. If you use the Close method against the rdoPreparedStatement, the rdoPreparedStatement is removed from the rdoPreparedStatements collection.
If the SQL query you need to execute includes one or more parameters, it is necessary to create a new rdoPreparedStatement or use an existing rdoPreparedStatement to run the query. In addition, the SQL statement you submit must use ODBC SQL syntax that includes special arguments that act as placeholders for input and output query parameters. RDO parses these parameters, which you can manipulate with the rdoParameter objects.
You can use a parameter as either an input, output, or both input and output parameter. For example, to execute the sp_password procedure (which can be used in Microsoft SQL Server systems to change a user's password), use the Transact-SQL statement as follows:
execute sp_password clyde, framis
This procedure query accepts two input parameters and passes back a return value. You could use the Execute method to run this query, but the return value would be lost. To capture the return value, and create an rdoPreparedStatement that can be used repeatedly to change user passwords, write code like the following:
Dim CPw As rdoPreparedStatement, QSQL As String
QSQL$ = "{ ? = call sp_password (?, ?) }"
The ODBC syntax for the parameter query uses question marks as placeholders for both the input and the output parameters. ODBC syntax requires the use of call rather than the commonly used execute keyword. Stored procedure calls should be surrounded by braces ({}) as shown in the preceding code fragment. Failure to use the correct syntax may not prevent the procedure from being executed; however, unless this syntax is used, ODBC may not be able to identify the parameter positions or markers.
The next line of code creates the rdoPreparedStatement and names it SetPassword. The SQL property is set with the ODBC syntax SQL query. You only need to execute this line once. The new rdoPreparedStatement object is automatically appended to the rdoPreparedStatements collection where it can be recalled later.
Set CPw = cn.CreatePreparedStatement("SetPassword",QSQL$)
When the ODBC interface executes a SQL statement, it creates one or more stored procedures on the server. These procedures contain the SQL statement specified in the rdoPreparedStatement object or the OpenResultset method and are designed to accept any parameters that might be specified for the statement. Depending on the version of the server, these procedures are either created in the current database or in the TempDB database. In some cases, several stored procedures can be created for a single statement. Generally, these procedures are not released until you close the connection or end the application. Ending the application in design mode does not clear these statements. In this case, only ending Visual Basic clears these temporary procedures.
To avoid the creation of these procedures in the first place, specify the rdExecDirect option when using the OpenResultset method. For example,
Set rs = cn.OpenResultset("Select * from Authors", rdOpenStatic, _
rdConcurValues, rdExecDirect)
By using the rdExecDirect option, the ODBC interface does not create a procedure that is used to subsequently run the SQL statement. In some cases, this can be somewhat faster to execute, but only if the statement is used infrequently.
The next step is to set the value of each input parameter and set the Direction property to indicate that the parameter is used for input, output, or both. The default Direction is rdParamInput. The ordinal number of the parameters is a function of the order in which they appear in the SQL statement. In this case, the "0th" parameter is the return value (? = ), the "1st" is the first input parameter, and the "2nd" is the second input parameter. The rdoParameters collection is zero-based. It is not necessary to set the Direction property on the input parameters because the default Direction is rdParamInput.
Cpw.rdoParameters(0).Direction = rdParamReturnValue
Cpw.rdoParameters(2).Direction = rdParamInput
Cpw.rdoParameters(1) = "clyde" ' Set the first input parameter.
Cpw.rdoParameters(2) = "framis" ' Set the second input parameter.
Once the parameter direction and values are set, you can use the Execute method to run the query if it does not return rows, or the OpenResultset method if the procedure contains one or more SELECT statements:
Cpw.Execute()
Once the procedure is executed, you can examine the rdoParameters collection for the returned value:
If Cpw.rdoParameters(0) <> 0 Then
Msgbox "Could not change password"
End If
You can also use the same technique to pass parameters into a SQL statement. For example, if you want to create a query to look up authors by name from the Pubs sample database, you could create an ODBC SQL statement like this:
SELECT * FROM Authors WHERE Au_Lname = ?
You can then set up a query using the following code, which passes the value entered by the user in Text1.Text into the query:
QSQL$ = "SELECT * FROM Authors WHERE Au_Lname = ?"}
Set PSAuthors = cn.CreatePreparedStatement("",QSQL$)
PSAuthors.rdoParameters(0) = Text1.Text
Set MyRs = PSAuthors.OpenResultset()
If the user changes the value in Text1.Text, you can re-execute the query by using the Requery method against the rdoResultset (MyRs).
PSAuthors.rdoParameters(0) = Text1.Text
MyRs.Requery
When RDO executes the Requery method, it refreshes the parameter value(s) in the rdoParameters collection, builds a new SQL statement, flushes the current result set, sends the query to the data source for execution, and creates a new rdoResultset.
For more information, search Visual Basic Books Online or online Help for "rdoPreparedStatement object", "rdoParameter object", "Requery method", and "OpenResultset method".
Using the same technique used with parameter queries in SELECT statements, you can also capture the output parameters from a procedure.
To capture output parameters:
When the query has completed processing, retrieve the output parameter values from the rdoParameters collection.
The final example in the online Help topic "Creating Parameter Queries" is coded incorrectly. A correct example is shown below. Note that you use the variable set to the created rdoPreparedStatement to create the rdoResultset, which is not shown correctly in the Help example. This example executes a stored procedure that expects two input parameters and returns two output parameters along with a return value argument.
Dim SQL As String, MyOutputVal1 As Variant
Dim MyOutputVal2 As Variant, MyRetVal As Variant
Dim cn As rdoConnection, rs As rdoResultset
rdoEnvironments(0).CursorDriver = rdUseOdbc
' To permit execution on SQL Server 6.0 Set
' cn=rdoEnvironments(0).OpenConnection(dsname:="MyDSN",
' Prompt:=rdDriverNoPrompt)
' Use ODBC parameter argument syntax.
SQL = "{ ? = call MyProcName (?, ?,?,?) }"
Dim Ps As rdoPreparedStatement
' Create reusable rdoPreparedStatement.
Set Ps = cn.CreatePreparedStatement("PsTest",SQL)
' Set Parameter "direction" types for each parameter,
' both input and output.
Ps(0).Direction = rdParamReturnValue
Ps(1).Direction = rdParamInput
Ps(2).Direction = rdParamInput
Ps(3).Direction = rdParamOutput
Ps(4).Direction = rdParamOutput
' Set the input argument values.
Ps.rdoParameters(1) = "Test%"
Ps.rdoParameters(2) = 1
' Create the result set and populate the Ps values.
Set rs = Ps.OpenResultset(rdOpenStatic)
MyRetVal = Ps(0) ' Contains the return value argument.
MyOutputVal1 = Ps(3) ' Contains the first output parameter.
MyOutputVal2 = Ps(4) ' Contains the second output parameter.
If you need to examine the tables exposed by a data source or column detail, you can use the rdoTables collection. To improve performance, no table meta-data is requested from the data source until the rdoTables collection is referenced. Once an rdoConnection is open, you can enumerate the tables and place their names in a ListBox control, as in the following example:
Dim tb As rdoTable
For Each tb in cn.rdoTables
List1.AddItem tb.Name
Next
Each rdoTable object contains an rdoColumns collection that contains details about the data type and size of each column. Although it is possible to create an rdoResultset against the rdoTable object, this query can only return all of the rows and is not recommended.
Each rdoEnvironment, rdoConnection, rdoPreparedStatement, and rdoResultset object includes support for an ODBC handle that you can use to manipulate the object using the ODBC API. The hEnv, hDbc, and hStmt properties correspond to the rdoEnvironment, rdoConnection, and rdoResultset objects, respectively.
Table 5 lists the ODBC handles that are created by RDO. You can use these handles in your own ODBC API code.
Table 5. ODBC Handles Created by RDO
| RDO object property | Handle created by the ODBC API |
| rdoEnvironment.hEnv | SQLAllocEnv |
| rdoConnection.hDbc | SQLAllocConnect, SQLDriverConnect |
| rdoResultset.hStmt | SQLAllocStmt |
Warning Although it is possible to use the ODBC API with the RDO ODBC handles, you are cautioned that incorrect use of the ODBC API can cause unpredictable behavior. For example, if you close connections or deallocate any of these handles using ODBC API code, the RemoteData control or RDO can behave unpredictably. The ODBC handles should not be saved for future use, inasmuch as they are subject to change without notice.
Each time the ODBC driver manager is used to carry out an RDO request, an error can be generated. These errors can be of varying severity and, in some cases, may cause the query to be canceled or abandoned. As errors are generated, they are placed in the rdoErrors collection. You can examine the individual members of the rdoErrors collection for details on what caused the error.
Visual Basic also produces a trappable error when an error occurs. In your On Error handler you can examine the rdoErrors collection to determine what action should be taken. To change the severity of error that can trip a fatal error, set the rdoDefaultErrorThreshold or the ErrorThreshold property.
Informational messages returned from the data source do not trigger a trappable error. These messages do appear in the rdoErrors collection, which can be manually cleared with the Clear method.
For more information, search Visual Basic Books Online or online Help for "rdoError object" or "rdoDefaultErrorThreshold property".
The RemoteData control is similar to the Visual Basic Data control. Both the Data control and the RemoteData control are designed to connect data-aware bound controls to a data source. The primary difference is that the RemoteData control uses RDO to connect to the ODBC driver manager, while the Data control uses the Microsoft Jet database engine and DAO to connect to the ODBC driver manager. Even though both controls use the same ODBC driver manager and data source entries, the RDO and Jet engines cannot share data source connections, data objects, or other resources.
Without a RemoteData control, a Data control, or its equivalent, data-aware (bound) controls on a form can't automatically access data. You can perform most remote data access operations using the RemoteData control without writing any code at all. Data-aware controls bound to a RemoteData control automatically display data from one or more columns for the current row or, in some cases, for a set of rows on either side of the current row. The RemoteData control performs all operations on the current row.
If the RemoteData control is instructed to move to a different row, all bound controls automatically pass any changes to the RemoteData control to be saved by the ODBC data source. The RemoteData control then moves to the requested row and passes back data from the current row to the bound controls, where it is displayed.
The RemoteData control automatically handles a number of contingencies, including empty result sets, adding new rows, editing and updating existing rows, and handling some types of errors. However, in more sophisticated applications, you need to trap some error conditions that the RemoteData control can't handle. For example, if the remote server has a problem accessing the data source, doesn't have permission, or can't execute the query as coded, a trappable error results. If the error occurs before your application procedures start, or because of some internal errors, the Error event is triggered.
When accessing an empty, updatable result set with the RemoteData control (RDC), you cannot depend on the EOFAction property to force the control to switch to AddNew mode. To add the first record to an empty result set, you must use the AddNew method against the RemoteData Resultset property. This will be corrected in a later release of the control.
The RemoteData control uses and creates the same RDO objects discussed in this chapter. When the appropriate RemoteData control properties are set, the RemoteData control can create an rdoResultset that can be manipulated in code like any other rdoResultset. You can also create an rdoResultset in code and set the RemoteData control's Resultset property to point to this object.
Note To use the RemoteData control, you need to add it to the Visual Basic Toolbox. To add the RemoteData control to your Toolbox, use the Custom Controls dialog box (available from the Tools menu), and select "Microsoft Remote Data Control" from the list of custom controls.
RDO is only supported on 32-bit operating systems (Windows 95 and Windows NT). To use the Remote Data Objects, you must set a reference to the Microsoft Remote Data Object 1.0 object library in the Visual Basic References dialog box.
Once your application begins, if sufficient properties have been set at design time, Visual Basic uses the RemoteData control to establish a connection to the ODBC data source. This creates an rdoConnection object by using the DataSourceName, UserName, Password, Options, Type, and Connect properties.
If insufficient information is provided in the RemoteData control properties, the ODBC driver manager exposes a dialog box to gather missing parameters. If the connection is established, the RemoteData control sets or resets the Environment, Connection, DataSourceName, Transactions, and Connect properties, based on the values used to establish the connection.
Once the connection is established, the RemoteData control runs a query against the data source using the SQL, CursorDriver, Options, LockType, ErrorThreshold, and ResultsetType properties. This creates an rdoResultset object and sets the Resultset, ResultsetType, and Updatable properties. By default, a read-only, forward-only type of rdoResultset is created. The StillExecuting property is set to True while the rdoResultset is created. If you choose to cancel the query, and the rdAsyncEnable option is set, you can use the Cancel method against the rdoResultset to terminate processing of the query.
Note: A known bug prevents complete use of Image or PictureBox controls with the RemoteData control. Although it is possible to read TEXT and IMAGE datatypes using the RemoteData control, it is not possible to update this type of column. It is possible to update these columns using RDO code or with the Data control.
Note also that graphics columns created by Microsoft Access must be accessed with OLE controls, not Picture or Image controls.
Once the first row of the rdoResultset is available, the StillExecuting property is set to False and the RemoteData control passes column data to each bound control requesting data. The rdoResultset.RowCount property is set to a non-zero value if any rows resulted from the query. If no data is returned by the rdoResultset, the RemoteData control's behavior is determined by the EOFAction property.
Note: Make sure that each bound control's DataField property corresponds to the Name property of the rdoColumn object returned by the rdoResultset. If the DataField property does not match the Name property of one of the rdoColumn objects in the rdoResultset, a trappable error occurs.
Users can manipulate the RemoteData control with the mouse. They can move from row to row, or to the beginning or end of the rdoResultset by clicking the control. As the user manipulates the RemoteData control buttons, the current row pointer is repositioned in the rdoResultset. The RemoteData control doesn't permit the user to move off either end of the rdoResultset using the mouse. You can't set focus to the RemoteData control.
Keep the following guidelines in mind when writing code that will reposition to the current row pointer:
To create an rdoResultset programmatically with the RemoteData control:
You can manipulate all of the RemoteData control properties and the new rdoResultset object independently of the RemoteData control—with or without bound controls. The rdoConnection and rdoResultset objects each have properties and methods of their own that you can use with your procedures.
For example, the MoveNext method of an rdoResultset object moves the current row to the next row in the rdoResultset. To invoke this method with an rdoResultset created by a RemoteData control, you could use this code:
RemoteData1.rdoResultset.MoveNext
If you need to execute a parameter query using the RemoteData control, first create an rdoResultset based on an rdoPreparedStatement, and then set the RemoteData control Resultset property to the newly created rdoResultset. You can also use this technique to access another rdoEnvironment.
If you set the Options property to rdAsyncEnable before the RemoteData control creates the rdoResultset, control returns to your application before the rdoResultset contains rows. Check the StillExecuting property of the rdoResultset object to determine when the first data row is available. To cancel the query, use the rdoResultset.Cancel method. Once the query is complete, the QueryCompleted event is invoked, which indicates that new data is now available from the rdoResultset.
If you do not specify asynchronous operation with rdAsyncEnable, no other Visual Basic operations or events can occur until the first data row of the rdoResultset is fetched. However, other Windows-based applications can continue executing while the rdoResultset is being created.
When you use a RemoteData control to create an rdoResultset object, the remote database engine automatically populates the rdoResultset object as a background task. As a result, any existing bookmarks are saved, the user doesn't need to manipulate the RemoteData control, and you don't need to invoke the MoveLast method in code. In addition, page locks used to create the rdoResultset are released more quickly, making it possible for other rdoResultset objects to access the same data.
You can also create an rdoResultset object using the OpenResultset method and set the Resultset property to the new rdoResultset. If this is done, all other appropriate RemoteData control properties are reset to reflect the new rdoResultset and rdoConnection.
Use the Validate event and the DataChanged property to perform last-minute checks on the rows being written to the database. The Validate event is invoked before each repositioning of the current row pointer. If the data changes, the Validate event is invoked, and, if not canceled by the action argument, the data source is updated.
For more information, search Visual Basic Books Online or online Help for "Validate event".