Making ATL OLE DB Provider templates support updating of data - Part 2

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:

<LI><CODE>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...

The source was built using Visual Studio 6.0 SP3. Using the July 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". 

Please send any comments or bug reports to me via email. For any updates to this article, check my site here.