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

Updating data through an ADO recordset

First it's worth clearing up some confusion about client and server side cursors and our rowset. Normally selecting either client or server side cursors is a simple choice between network traffic and local storage. Server side cursors are physically located with the data and in the case of most OLE DB providers that's probably on the far end of a network connection to your database server. With our rowset that's used to access our data object our server side is inside our data object... If we could get all of the data bound controls to work with this server side cursor implementation then we wouldn't need to bother with client side cursors at all, but for some reason most Microsoft written data bound controls will fail to fetch any data from these rowsets if the cursor setting is set to server side. Selecting client side cursors causes OLE DB to insert the Client Cursor Engine between your rowset and consumers, this is an OLE DB service component that adds functionality (client side data caching) that you don't supply yourself. The problem with using the CCE with our rowset is that the data is already all on the client side, so the caching just duplicates data and takes up twice the storage that we'd usually require... However, at present, if you need to allow consumers to update your data via Microsoft written data bound controls then you have to use the CCE...

Supporting updating from our rowset is relatively easy if if we're using server side cursors, however if we select client side cursors then things are more complex. We'll address the server side update issue in this article and cover the changes that are required for using client side cursors in the following article.

Implementing IRowsetChange

The OLE DB provider documentation seems to imply that if you want your provider to be updateable then you need to implement either IRowsetChange or IRowsetUpdate. IRowsetChange has all you need for adding new rows, deleting rows and changing data. IRowsetUpdate adds the ability to batch together a series of changes and apply them to the data source in one go. Interestingly the Janus Grid will use IRowsetChange for all updates if IRowsetUpdate is not available, but will use the later if it is available. As  IRowsetUpdate is more complex to implement and doesn't add any value to our examples we won't bother with it. When attempting to get the client cursor engine updates working I added support for IRowsetUpdate but it neither helps nor hinders in getting CCE updates to work...

IRowsetChange is a relatively simple interface to implement, consisting of three fairly straight-forward methods:

   HRESULT DeleteRows(
      HCHAPTER hChapter, 
      ULONG cRows, 
      const HROW rghRows[], 
      DBROWSTATUS rgRowStatus[]);

   HRESULT InsertRow(
      HCHAPTER hChapter, 
      HACCESSOR hAccessor, 
      void *pData, 
      HROW *phRow);

   HRESULT SetData(
      HROW hRow, 
      HACCESSOR hAccessor, 
      void *pSrcData);

A provider can choose to implement any or all of of the three methods above, and return DB_E_NOTSUPPORTED for any functionality that it does not support. It reports its level of support via the DBPROP_UPDATABILITY property. See the interface documentation for more detail. This makes implementing the methods slightly more complex as they must first check to see that the rowset that they're being used on supports the operation required. The other main complication with the IRowsetChange methods is handling the consumer notification stages correctly. Each method can cause multiple notifications to be fired into consumers via the IRowsetNotify connection point interface. What's more, consumers can veto some actions by responding appropriately to the notification call.

Using our implementation of  IRowsetChange allows us to set the following properties in our rowset's property map.

PROPERTY_INFO_ENTRY_VALUE(IRowsetChange, VARIANT_TRUE)

PROPERTY_INFO_ENTRY_EX(
   UPDATABILITY, 
   VT_I4, 
   DBPROPFLAGS_ROWSET | DBPROPFLAGS_READ | DBPROPFLAGS_WRITE, 
   DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_DELETE | DBPROPVAL_UP_INSERT, 
   0)

PROPERTY_INFO_ENTRY_EX(
   OWNINSERT, 
   VT_BOOL, 
   DBPROPFLAGS_ROWSET | DBPROPFLAGS_READ | DBPROPFLAGS_WRITE, 
   VARIANT_TRUE, 
   0)

PROPERTY_INFO_ENTRY_EX(
   OWNUPDATEDELETE, 
   VT_BOOL, 
   DBPROPFLAGS_ROWSET | DBPROPFLAGS_READ | DBPROPFLAGS_WRITE, 
   VARIANT_TRUE, 
   0)
 
PROPERTY_INFO_ENTRY_EX(
   REMOVEDELETED, 
   VT_BOOL, 
   DBPROPFLAGS_ROWSET | DBPROPFLAGS_READ | DBPROPFLAGS_WRITE, 
   VARIANT_TRUE, 
   0)
 
PROPERTY_INFO_ENTRY_EX(
   IConnectionPointContainer, 
   VT_BOOL, 
   DBPROPFLAGS_ROWSET | DBPROPFLAGS_READ | DBPROPFLAGS_WRITE, 
   VARIANT_TRUE, 
   0)

Supporting notifications

To support consumer callbacks via IRowsetNotify our rowset needs to be a connection point container and we need to support the connection of  IRowsetNotify connection points. This is achieved relatively easily using the ATL connection point support. 

The actual notification firing code is slightly more complex than the event firing code that you can get ATL to generate for you as the consumer is allowed to respond to some notifications and veto the change occurring in the rowset. This means that we need to pay special attention to the return values of the notification calls and react appropriately to requests to veto changes.

The IRowsetNotify event source is actually quite simple, but using the events is complex due to the nature of the event types and event phases that are possible. For example, before an update we might send an "column set" "ok to do" event followed by a "column set" "about to do" event, if a consumer vetoes either of these events then all consumers are sent a "column set" "failed to do" event. Because of this complexity we wrap the groups of events in helper methods which can be called from within our IRowsetChange methods.

The ordering and details of the notification events required by the OLE DB specification is quite difficult to determine from the documentation. The events that this code sends appear to be adequate but your mileage may vary... One method of investigating these events is to watch the sequence of events fired by the client cursor engine when client side cursor updates are applied to a recordset. 

An updateable proxy rowset

Now that we have the IRowsetChange and IRowsetNotify event source interfaces we can implement a proxy rowset which uses these to provide update capability to our data object's rowset. CUpdatableProxyRowsetImpl derives from the proxy rowset that we developed over the previous articles and also from our implementations of IRowsetChange and IConnectionPointContainer. 

template <
   class DataClass,
   class T, 
   class CreatorClass, 
   class Storage = CRowsetStorageProxy<T>, 
   class ArrayType = CRowsetArrayTypeProxy<T, Storage>,
   class RowClass = CSimpleRow,
   class RowsetInterface = IRowsetImpl < T, IRowset, RowClass> >
class CUpdatableProxyRowsetImpl:
   public CProxyRowsetImpl< 
      DataClass, 
      T, 
      CreatorClass, 
      Storage, 
      ArrayType,
      RowClass,
      RowsetInterface >,
   public IRowsetChangeImpl<T, Storage>,
   public IConnectionPointContainerImpl<CUpdatableProxyRowsetImpl>

Within our rowset class we handle the connection point container that's required for our rowset notifications. We also provide code that calls our data object's rowset to perform updates. Inserts and deletes are done using the operations already available on our rowset's proxy storage object. 

Please note that the SetDataHelper() method in CUpdatableProxyRowsetImpl has a horrible hard coded limit of 256 bytes of data per column. This could easily be removed but is left as an exercise for the reader ;)

Changes to our data object's rowset

We need to change our data object's rowset object to take advantage of the updateable functionality we have provided. It now inherits from our new updateable proxy rowset and it needs to implement the UpdateColumn() method. Once this is done our object can be updated via ADO as long as have selected server side cursors. If we run the VB test harness program and create a table, then obtain a rowset from the data object with a server side cursor and batch optimistic locking we can click the button to display the rowset in the Janus Grid and set a break point inside the data object rowset's UpdateColumn() method. When we change the data in the grid we end up inside the rowset's UpdateColumn() method and the data is updated.

Interestingly for the example above to work we don't need to set the data source property DBPROP_DATASOURCEREADONLY to VARIANT_TRUE, but we probably should do... Also, inside our proxy rowset we don't indicate that that the columns are writable by adding DBCOLUMNFLAGS_WRITE to the column flags by default, again we should do. Failure to mark the column data as writable by adding this flag prevents client side cursor updates from working at all... (and yes, it took me ages to find out why they were failing in code that had previously worked fine

Insufficient base table information...

Our rowset now supports updates but it still doesn't work with many of the common Microsoft data bound controls. The Data Grid doesn't display data if we use server side cursors (I've no idea why and I have an open support call with Microsoft over it), so to update data from the Microsoft data bound controls we need to support updating via the client cursor engine. However if we select client side cursors and optimistic locking in our test program and try to change data in the Data Grid we get an error message "Insufficient base table information for updating or refreshing". Switching to batch optimistic locking simply causes the error to occur when we issue an UpdateBatch command on the recordset rather than as soon as the data is changed... We address the cause of this error and its solution in the next article

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.