OLEDB - Client Cursor Engine updates

2000-01-02

Making the ADO Client Cursor Engine believe that your rowset is updateable involves jumping through a few extra hoops…

Client Cursor Engine Updates

It turns out that supporting updates through the client cursor engine is relatively easy. Discovering that it’s relatively easy was extremely difficult. An article that was recently added to the MSDN gives complete and full information on what a rowset needs to support for updates via the CCE to be possible. Before that article was published you had to rely on guess work to find out how to do it… By the way, if anyone from Microsoft reads this, the client cursor engine article really rocks, we need more articles with that level of technical information.

A9645971-91EE-11D1-9251-00C04FBBBFB3

I must have spent around 6 months trying to get client cursor updates to work. I tried adding IRowsetUpdate, an obvious choice, but completely unnecessary. I tried adding all manner of other interfaces, guessing at random and failing completely. The main problem with client cursor updates is that at the point when the dreaded “Insufficient base table information for updating or refreshing” message is issued your OLE DB provider’s session object is queried for an undocumented interface. The IID is {A9645971-91EE-11D1-9251-00C04FBBBFB3} and according to a Microsoft support person this interface is the subject of a documentation bug which relates to updating providers that do not support SQL.

So, what’s the secret

It turns out that all you need to do to get rid of the “Insufficient…” error message is to implement IColumnsRowset which isn’t too hard as most of the required information can be obtained from a call to IColumnsInfo::GetColumnInfo() which is supported by the ATL templates. The main issues with the implementation of IColumnsRowset are that GetAvailableColumns() must return at least the following three optional meta data columns:

DBCOLUMN_BASETABLENAME
DBCOLUMN_BASECOLUMNNAME
DBCOLUMN_KEYCOLUMN

and that GetColumnsRowset() must return a rowset where these optional meta data columns are present and contain valid information.

In our current situation the table name is not really relevant, so we can fill in any old name and the base column names can be the same as the column names that IColumnsInfo reports.

Once this is done we can add support to our updateable proxy and add the appropriate rowset property and the “Insufficient…” error is no more. Unfortunately we have yet to actually fix the problem.

Once a rowset supports IColumnsRowset the client cursor engine has enough information to issue insert, update and delete requests to the rowset in SQL that is written in terms of the ‘base table’ information. That is it knows the real names of the columns and tables involved in an update and can write an update statement that can deal with rowsets that are the result of joins or data shape commands on an underlying data source. Unfortunately for us this means we are required to support SQL for updates, inserts and deletes.

The resulting SQL arrives at our provider as a command. If you create a table, obtain an ADO recordset from it with client side cursors and optimistic locking then a a breakpoint set in CConversionProviderCommand::Execute() will be hit as soon as you change data in the Data Grid and tab away from the cell. The value of m_strCommandText will be the SQL, something along the lines of:

   update table set Col1 = ? where Col1 = ? and

   Col2 = ? and Col3 = ? and Col4 = ?

The question marks are place holders for values that have been passed to us in the DBPARAMs accessor. This is because we support ICommandWithParameters (which we need to for our conversion to an ADO recordset). If we didn’t support ICommandWithParameters then the SQL would hold the values in the text string itself.

   update table set Col1 = 'a' where Col1 = '1'
   and Col2 = '2' and Col3 = '3' and Col4 = '4'

The reason for the painful where clause is that our data object doesn’t have any key columns. The CCE wont use a bookmark column as a key column so unless your data source has a valid unique key column and the column flags are set to include DBCOLUMNFLAGS_ISROWID inside of your data object’s GetColumnInformation method then the where clause will be written so that all columns are used to identify the row that needs to be changed.

An exercise for the reader…

I’m afraid that’s where I’m going to leave this particular problem. Parsing the SQL is relatively straight forward as the statements will only ever be generated by the CCE and should remain in a consistent form. Identifying the rowset that your command is to manipulate can be done by retaining a mapping table within the provider and using the key into the mapping table as the base table name returned from a call to GetColumnsRowset for a particular rowset object. The row to be modified is easily located if your data source has a unique key and more laboriously located if not (it’s a pity that the CCE can’t be made to use the bookmark column if no other unique key is present…). Once you have your row handles you need to create a copy of the accessor that you’re passed into your command in the context of the rowset and then call the methods in IRowsetChange to do the actual work.

I may be tempted to write another article which covers this final piece of work…

Download

The following source built using Visual Studio 6.0 SP3. Using the July 2000 edition of the Platform SDK. If you don’t have the Platform SDK installed then you may find that the compile will fail looking for “msado15.h”. You can fix this problem by creating a file of that name that includes “adoint.h”.

If your system drive isn’t D:\ then you’ll have to change the #import statements in IGetAsADORecordsetImpl.h and IGetAsADORecordset.cpp.

Revision history

2nd January 2000 - Initial revision at www.jetbyte.com.
4th March 2000 - Updated - Server side cursor updates now work with the DataGrid. Thanks to Francois Leclercq for his helpful information about IRowsetLocate::Hash() and to Prash Shirolkar at Microsoft for his support and the OMNI Provider article.
2nd October 2000 - Fixed some build configuration errors. Thanks to Charles Finley for reporting these.
19th October 2005 - reprinted at www.lenholgate.com.

Other articles in the series

Objects via ADO - ADO seems to be the ideal way to expose tabular data from your own COM objects and the ATL OLE DB Provider templates can help!
Custom Rowsets - The ATL OLE DB Provider templates appear to rely on the fact that your data is kept in a simple array, but that’s not really the case at all!
IRowsetLocate and Bookmarks - Adding bookmark functionality is relatively easy and it enables our ADO recordset to be used with a greater number of data bound controls.
Updating data through an ADO recordset - The ATL OLE DB Provider templates only seem to support read-only rowsets, and making them support updating of data isn’t as easy as you’d expect!
Client Cursor Engine updates - Making the ADO Client Cursor Engine believe that your rowset is updateable involves jumping through a few extra hoops…
Disconnected Recordsets - If you are going to use the client cursor engine then often it’s a good idea to disconnect your recordset…