OLEDB - Updating data through an ADO recordset

OLEDB, Reprints, Source Code, Way back

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!

Cursors everywhere

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… 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…

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 a “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 you 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!)

Server side updates and the DataGrid

The Microsoft DataGrid is a demanding consumer. Whilst the changes detailed above work just fine with the Janus Grid, the DataGrid fails to display any data. The reason for this is that the DataGrid also requires that the following rowset properties are set:

PROPERTY_INFO_ENTRY_VALUE(LITERALIDENTITY,VARIANT_TRUE)
PROPERTY_INFO_ENTRY_VALUE(STRONGIDENTITY,VARIANT_TRUE)

These properties tell the grid that each row in the rowset is represented by a single row handle. That is, if the same row is returned from the rowset the row handle is simply add reffed and returned rather than a new row handle being created. This allows a consumer to easily match rows sent to it in notification calls with rows that it already holds.

Once these two are set the DataGrid expects IRowsetLocate::Hash() to be implemented, though I’m at a loss to understand why.

Insufficient base table information…

Our rowset now supports server side cursor updates and works with some of the common Microsoft data bound controls. However if we select client side cursors and optimistic locking in our test program and try to change data 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.

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.
  • 13th 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…