Choosing an RDO Cursor Library

RDO supports several different cursor libraries — each with a specific purpose. While not all cursor libraries support all types of cursors, all libraries support forward-only result set management. For example, the ODBC client-side driver can only support rdOpenStatic and rdOpenForwardOnly cursor types, while the SQL Server server-side driver supports all four types.

Selecting the right cursor library is important when it comes to using certain RDO functionality. For example, you must use the client batch library to create dissociate rdoResultset objects or use the BatchUpdate method. To disable creation of cursors and use forward-only, read-only, or single-row result sets, use the rdUseNone option.

When creating a stand-alone rdoQuery object whose query is to be used as a method, you should set the CursorType before the query is executed because there is no option to do so when the query is executed.

The value of the CursorType property is used as the type argument of the OpenResultset method. To choose the type of cursor driver to use, set the rdoDefaultCursorDriver property of the rdoEngine or the CursorDriver property of the rdoEnvironment or rdoConnection object. The following table describes the options available with these properties.

Option Description
rdUseODBC RDO will use the ODBC cursor library. This gives better performance for small result sets, but degrades quickly for larger result sets.
rdUseServer RDO uses server-side cursors if they're available.
rdUseClientBatch RDO uses the client batch cursor library.
rdUseIfNeeded (Default) The ODBC driver chooses the appropriate style of cursor and uses server-side cursors if available.
rdUseNone RDO creates a cursorless result set.

Using the ODBC Cursor Library

The ODBC cursor library is the simplest of all of the libraries. It only supports static and forward-only cursors, so it is of limited appeal to many developers. It is also not particularly efficient for situations where you must create cursors of more than a few hundred rows. It is the most flexible in that it can access virtually all ODBC drivers — even those that are not fully ODBC Level II compliant.

The static cursor implemented by the ODBC cursor library is simply a data dump of the selected rows copied to the workstation.

Note   When using the ODBC cursor library, you cannot execute a stored procedure that returns solely a Binary Large Object (BLOB) data column. You can, however, execute a query that returns another column and a BLOB column using the ODBC cursor driver. This limitation does not apply to the Microsoft SQL Server server-side cursor library.

Using the rdUseIfNeeded Cursor Library Option

This CursorDriver property option tells RDO to choose server-side cursors if available. Because it is the default driver option, this means that if you connect to Microsoft SQL Server, RDO chooses server-side cursors. If you connect to Oracle or other ODBC data sources, the ODBC cursor library is used.

Using the Server-Side Cursor Library

If your data source supports server-side cursors and you choose the rdUseServer CursorDriver option, 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 versions 6.0 and higher support server-side cursors, but not all data sources do.

Server-side cursors reduce the amount of memory and disk space required on the client machines, but shift that load to the server. For SQL Server, 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.

Server-Side Transact SQL Cursors

Another option you might wish to consider leverages SQL Server's ability to implement server-side cursors using its own Transact SQL cursor functions. You can create a server-side cursor, position to specific rows, and perform positioned updates using these functions.

Server-Side Cursors and Multiple Result Set Queries

If you submit a query that contains more than one SELECT statement to SQL Server using the server-side cursor library, the ODBC driver will reject the query because it is unable to handle this situation. If you must execute multiple result set queries or stored procedures that contain multiple SELECT statements, you have several alternatives:

Using the Client Batch Cursor Library

RDO 2.0 introduced the Client Batch cursor library. This library is capable of creating all four types of cursors in addition to the forward-only result set. In addition, this library is designed with Microsoft's proprietary Rushmore technology, so it is far more efficient than the ODBC cursor library. This option also enables you to create dissociate rdoResultset objects, perform offline changes to the data, and re-associate the rdoResultset with a live connection to post updates back to the database using optimistic batch updates. Even if you do not create a dissociate rdoResultset, you can still use the BatchUpdate method to submit blocks of database changes.

This library also creates all cursors on the client. That is, either raw row data row images or keysets are built in on the client system.

Using the rdUseNone Cursor Library Option

This CursorDriver property tells RDO to bypass creation of a cursor and create a single-row, forward-only, read-only result set.

Not all queries you create need to be returned in the form of a cursor. As a matter of fact, cursors are a particularly expensive way to fetch data and are rarely used in production-class applications. If you set the rdoDefaultCursorDriver property to rdUseNone, all result sets created by your application will be created as if you used the OpenResultset method with the rdOpenForwardOnly and rdConcurReadOnly options set, and with RowsetSize set to 1. This is the most efficient result set available for many applications, as it is the simplest to implement. However, it has the least number of features — some of which your application might require. The efficiency of this technique can permit your application to forgo the luxury of a full cursor.