by Ross Mack - GUI Computing
Client-Side Optimistic Batch Update Cursors are a cool part of ODBCDirect and are certainly given a lot of enthusiastic support by the VB5 documentation. But what are they, how can you use them and how can you get the darned things to work?
What is a Batch Optimistic Update Cursor?
VB5's new ODBCDirect interface (see my introductory article elsewhere in this issue) is a great way to efficiently get to ODBC data sources and control the nature of the connection and the work you do with it. You are now able to do many things previously unavailable to mere VB programmers (see Lisa Hooper's article on Multiple RecordSets, for example). One of the cool things you can do is to control how your RecordSet cursors are created. In the past we have barely even thought about the fact that we are using cursors when we MoveNext and MoveLast around our RecordSets. In simple terms a cursor is a pointer to the current record or row of a RecordSet. Whenever we use a RecordSet (even in the days of snapshots and Dynasets) object in VB, we are really using a cursor to navigate and manipulate a set of data records.
Anyway, the point is that ODBCDirect now allows you to specify what sort of cursors you want to use for those ODBC drivers and data sources that support it. This functionality is, of course, also available in RDO as it is the underlying engine of ODBCDirect but I will talk here in terms of ODBCDirect as the DAO object hierarchy is more familiar to more people (including myself). For those of you who use RDO you will be able to readily find the equivalent details covered in Books Online. In general you can specify whether you want to use a Server side cursor, a Client side cursor (managed by ODBC), no cursor at all or a Client Side Batch Update Cursor, which is what we are concerned with here.
But what is it that a Batch Update Cursor lets you do? Well, being a client side cursor the data of the RecordSet that the cursor controls is held and maintained by the client application and ODBC, in comparison to server side cursors where the data of the RecordSet is maintained on the server and fetched from there as required. What makes Batch update cursors really different is that when you update the RecordSet in any way those updates initially occur only on the client side. This means you can open a RecordSet and then update the data, add new records and even delete records, then, you can have all those changes submitted as a batch to the server to be processed on the data there. This increases performance and reduces network usage.
When should you use them?
Optimistic Batch Updates are useful when you want to make a number of changes to a RecordSet that can not easily be expressed in a single SQL statement. If you need to decide on a row by row basis what changes need to be performed or if a new row needs to be added, or a row needs to be deleted this is when you would likely use a Batch Update Cursor. You might also use a batch update cursor if you want to present the user with a number of records in a grid, for example, that they can edit all at the same time, and then submit all those changes at once. in fact any time you want to change a number of records in one go a Batch Update Cursor is a good candidate as a method to do that.
In a number of cases Stored Procedures are used to efficiently process a number of records in complicated ways on the database server. With a batch update cursor you can now do some of that sort of processing on the client side if it suits your application or environment.
How do you get them to work?
Getting batch Update Cursors to work is a little tricky, certainly more complicated than opening up a simple JET dynaset and updating that. However, there are a small number of things you must do to get it to work and I will attempt to explain each of them.
For a Batch Update Cursor to work the cursor must be able to determine what fields in your data are key fields. In Microsoft SQL Server this is achieved simply by marking appropriate columns as keys in the table properties. The method will be somewhat different for each different database server, so check your documentation.
It is important to note at this time that the sample code for Batch Update cursors in the VB5 Books Online shows code that updates a table in the Pubs database, the sample database that installs as part of MS SQL Server. This code does not work. Why? Because there are no fields defined as keys on this table. There are indexes, certainly but no key fields. Don't believe everything you read.
Connection Cursor Drivers.
When you open the connection that you want to use Batch Update Cursors on you must specify Client batch Cursors as the cursor driver for that connection. This is done simply by specifying the DefaultCursorDriver property to be dbUseClientBatchCursor. Normally the driver used by default is determined by the ODBC driver and this may be either a Client Side or a Server Side cursor. And in most cases you probably don't care. In this case, however, we need to specify the driver.
Once you set the DefaultCursorDriver property of the workspace any connections you create after that will use that default driver, until you change the property of the workspace. The code looks like this:
DBEngine.DefaultType = dbUseODBC Set wks = DBEngine.Workspaces(0) wks.DefaultCursorDriver = dbUseClientBatchCursor sConnect = "ODBC;DSN=Pubs;UID=sa;PWD=;" Set cn = wks.OpenConnection("Pubs", dbDriverNoPrompt, False, sConnect)
RecordSet Locking Mode.
Once you have the connection open and you want to open a RecordSet that you will update in Batch mode you need to specify batch mode in the LockEdits argument of the OpenRecordset method. This essentially tells the RecordSet not to lock the rows fetched but to be prepared to lock them appropriately during the batch update. The code for the OpenRecordset Method might look like this:
Set rst = cn.OpenRecordset("SELECT * FROM titles", dbOpenDynamic, dbExecDirect, dbOptimisticBatch)
The UpdateOptions property of the RecordSet object tells it how to perform the Batch updates when they are sent to the cursor. This property is a combination of two constants, one specifying how to identify the rows to be updated and one specifying how updates are to be processed. In code it might look like this:
rst.UpdateOptions = dbCriteriaAllCols + dbCriteriaUpdate
In the above case the RecordSet is instructed to reference all columns when selecting records to update and to use an Update statement to actually perform any updates included in the batch. there are a number of other constants available for customising this property. They are briefly listed and summarised below. As always more information can be gained by checking your VB5 Books Online.
|dbCriteriaKeys||Specifies that the rows to be modified should be identified by their key fields only|
|dbCriteriaModValue||Specifies that the rows to be modified should be identified by the values of the modified columns as well as their key fields.|
|dbCriteriaAllCols||Specifies that the rows to be modified should be identified by the values of all available columns.|
|dbCriteriaTimeStamp||Specifies that the rows to be updated should be identified by the unique ID allocated for every row in the database by the database server. This is not really a traditional Date/Time stamp but an identifier that is unique to each row in the entire database. This option is available only on databases that support it.|
|dbCriteriaDeleteInsert||This option specifies that updates to rows should be performed as SQL DELETE statements followed by SQL INSERT statements of the new data.|
|dbCriteriaUpdate||This option specifies that updates should be performed as typical SQL UPDATE statements. New rows are always processed as INSERT statements and deletions are always processed as DELETE statements.|
The choice between using dbCriteriaDeleteInsert and dbCriteriaUpdate is essentially how efficiently your database server can perform DELETE, INSERT and UPDATE statements. It will also depend upon what triggers are associated with those events on your server.
The Update Statement.
Once you have made all the changes to the rows in your RecordSet and you want to submit all the changes to the server you need to call the Update method of the RecordSet with a special parameter, dbUpdateBatch. This tells the RecordSet to submit all Edits, AddNews and Deletes that have been processed on the local RecordSet to the server.
In code it looks like this:
Simple isn't it ?
If you want the update to be forced on the server regardless of whether or not your updates collide with any other changes that have occurred then you can call the update method with a second parameter. Passign True as this second parameter tells the RecordSet to force the updates. It looks like this:
rst.Update dbUpdateBatch, True
This will make sure your modifications take place, although the BatchCollisions and BatchCollisionCount properties will still indicate where any collisions occurred.
You can also update individual records on the server directly without using Batch mode by passing dbUpdateCurrentRecord as the first parameter to the Update method. This is essentially the same as an Update method on a normal RecordSet, that is the record you are updating is updated immediately on the server and errors can occur as normal. Of course you can force this sort of update as well using the second parameter to the Update method. The code looks like this:
Or, if you are forcing the update:
rst.Update dbUpdateBatch, True
Before Update : the dbUpdateBatch statement has been executed you can examine the current status of each record in the RecordSet using the RcordStatus property. this will return a value indicating if the row has been updated in the Client Side cursor and in what way it has been updated. Different values indicate that the row has been Edited, Deleted or Added.
Beware - this property is read only. VB5's Books Online indicates that you can use this property in a methodology called 'Data Pipelining' to move data in batches from one Connection to another. It's very cool but doesn't work basically due to the fact that this property is read only. If there is anyone who has managed to get this to work please let me know.
When you have performed your Batch Update you need some way of determining the success of your updates. VB provides a variety of information for you to zero in on any updates that failed.. Initially, checking the BatchCollisionCount property of the RecordSet will tell you how many updates were unsuccessful. You can then use the BatchCollisions property to locate those records. The BatchCollisions property is an array of Bookmarks that identify the records that could not be modified for some reason. Setting the Bookmark property of the RecordSet to a member of the BatchCollisions collection will take you directly to that record.
Once you have located a record that failed you can check the RecordStatus to determine what sort of modification was attempted. When an update was attempted you can then examine properties of each field to find out what has happened at a field level if you like. Each field object exposes two additional attributes to allow you to do this. The OriginalValue property tells you what the data in that field was when you first opened the RecordSet (and therefore what the value was that you attempted to update from). The VisibleValue property indicates what the current value stored for that field on the server is. Let's look at an example. If you opened a RecordSet containing two fields and one row. The first field is the key and we will ignore that. The second field is PhoneNumber and contains the value '5551111' in the row we have. We decide that this needs to be changed and update it to '5559999'. Unfortunately when we execute the Batch Update there is a collision at the server and our record has not been successfully updated. When we examine the properties for this field we observe the following values.
this tells us that when we opened the RecordSet the value of the field was '5551111' and when we tried to update it to '5559999' it failed because someone had updated it to '5555555' between the time we open the RecordSet and submitted the update. At this point in time we can decide to live with the value on the server and forget about our modification, attempt to execute our modification again or do whatever.
Essentially, from this point you can decide to go one of three ways, with the original value, with the value you attempted to update to or with the current value on the server. To set the value to the original value you simply set the Value property of the Field to the OriginalValue property of the field. You can then issue an Update in the following fashion to force that value to be written to the server regardless of collisions:
rs.Update dbUpdateCurrentRecord, True
The second parameter to the Update method specifies that the update should be forced regardless of collisions, as detailed above. If you want to force the value to be updated to the value you originally updated to then you can simply leave the properties as they are and force the update as above. Or, to make your RecordSet contain the values currently stored on the server simply refresh the RecordSet.