Visual Basic Concepts

Creating and Changing an RDO Client Batch

If you set the CursorDriver to rdUseClientBatch, you can use the Client Batch cursor library with or without deferred batch updates. To choose the desired type of update operation, set the OpenResultset method's LockType argument as follows:

• rdConcurBatch to select deferred updates. In this case, the Update method simply posts changes to the current rdoResultset, but no changes are made to the underlying database tables until the BatchUpdate method is executed.
  
  
• Any of the other concurrency options. In this case the Update method immediately posts changes to the database on a row-by-row basis.

Dissociating the rdoResultset from its Connection

Once the rdoResultset is opened, you can begin making changes immediately. Or once you have fully populated a rdConcurBatch-style result set, you can dissociate the rdoResultset object from the current connection by setting the ActiveConnection property to Nothing. At this point you can make whatever additions, deletions, or updates on the individual rows are needed.

Making Changes to the Result Set

To make these changes, simply use the Edit, Delete, and AddNew methods followed by the Update method to commit the changes. Once all changes have been made, use the BatchUpdate method to post the batch of changes to the database. The BatchUpdate method scans the rdoResultset, checking the Status property for each row — which is set to rdRowUnmodified for all unchanged rows. If the row has changed, as indicated by a Status not set to rdRowUnmodified, the Client Batch cursor library generates an SQL query to perform the changes. This query is concatenated together with all other SQL operations needed to synchronize the local result set with the data on the remote server. Once all operations are included, the batch is executed (passed to the remote server for execution).

Setting the UpdateOperation Property

The UpdateOperation property determines how RDO and the client batch cursor library perform update operations on your data. Basically, you have two choices: either attempt to perform the update operation in a single step, or intentionally break up the operation into two separate operations — a delete followed by an insert.

Note   Depending on your database triggers or other referential integrity checks, you might find it impossible to perform a separate delete operation.

In many cases, update operations are handled internally by the database engine as a delete operation followed immediately by a corresponding insert — coupled as an atomic transaction. Unless your database engine is capable of "update in place" and your update operation conforms to the requirements of this type of update, the update will be executed as a delete followed by an insert.

By choosing one of the following constants for the UpdateOperation property, you can determine how RDO and the Client Batch cursor library manage the update.

• rdUpdate (Default): Uses an UPDATE SQL statement for each modified row.
  
  
• rdInsertDelete. Uses a pair of SQL DELETE and INSERT statements for each modified row.

Even if you choose the rdInsertDelete option, the Client Batch cursor library encases the individual operations in a single transaction to ensure that if one operation fails, the entire operation is rolled back.

Setting the UpdateCriteria Property

The UpdateCriteria property is used to control how the Client Batch cursor library determines if the row about to be updated has changed in the base tables. If some other user has changed the data while your code was editing the row, this property selects the technique that RDO uses to ensure that you don't inadvertently overlay other users' changes.

By setting the UpdateCriteria property, you can determine how RDO builds the WHERE clause used to synchronize the local result set with the remote database tables. The BatchUpdate method creates an SQL statement for each row whose Status property is not rdRowUnmodified. Each of these statements includes a WHERE clause that uniquely identifies each row affected. By adjusting the UpdateCriteria property, you can adapt your application to deal with the update mechanism of your remote server. For example, in some servers, triggers might be fired unnecessarily if columns not affected by the changes you make are included in the SQL update.

Just exactly how individual rows are identified is determined by the UpdateCriteria property as follows:

• rdKey (Default): Uses just the key column(s) in the WHERE clause. That is, RDO uses those columns you have designated as the primary keys — the columns that uniquely identify specific rows in the table(s).
  
  
• rdKeyAndUpdatable. Uses the key column(s) and all updated columns in the WHERE clause. In this case RDO also adds the columns that were changed to the WHERE clause to ensure that the rows have indeed changed.
  
  
• rdKeyAndModified. Uses the key column(s) and all the columns in the WHERE clause. In this case RDO includes the key and all other columns to the WHERE clause to ensure that the rows have indeed changed.
  
  
• rdKeyAndTimestamp. Uses just the TIMESTAMP column if available (will generate a run-time error if no TIMESTAMP column is in the result set). This option compares the existing TIMESTAMP column with the column in the database to determine if the value has changed.

Tip   If you are using SQL Server 6.5, you can use the SQLTrace utility to observe how RDO builds the WHERE clause when making a batch update.