Reprints - OLEDB; no pain, no gain

Reprints

I’ve just finished posting several OLE DB provider articles from back in 1999 and 2000 when the favourite method of data access that Microsoft recommended was OLE DB. This was relatively easy to use as a data consumer, especially from VB. Writing a data provider was another matter entirely. The OLE DB documentation was mostly written in a style that assumed that you were only using it for reference, this made it hard to get to grips with when you first started working with it.

The ATL OLE DB provider templates offered some help, but, to be honest, as soon as you started to stray from the simple examples provided I found that the supplied templates were very limited.

When I started to write an updateable (read/write) OLE DB provider I found that there was relatively little information available on the web, even questions to newsgroups failed to provide answers for what appeared to be simple questions. This was before the time of the amazingly good OMNI provider sample; in fact the Prash Shirolkar at Microsoft was dealing with my support calls when this sample was produced.

The design of OLE DB and ADO makes it difficult to debug a provider, they don’t use the standard COM functionality discovery mechanism - QueryInterface() - to determine levels of functionality, they ask you for the properties you support. If you answer the question incorrectly you’ll never be asked for certain pieces of functionality. At least with QI calls you can see what’s being asked of you and keep adding interfaces until everything works… Deciding from the documentation which properties and which interfaces are required is not always easy.

These articles show how to do some things that, I expect, many people wanted to do with OLE DB and ADO at the time:

  • Provide access to an existing data object via an ADO recordset interface. Sometimes you have a perfectly useful COM object that happens to store data that is tabular in nature. It would be nice to expose this data via ADO.

  • Keep all of the OLE DB access hidden inside your object, you just expose a completely constructed ADO recordset.

  • Provide read/write access to your data via a provider written with the ATL templates.

Articles:

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

Having read back through these articles I’m pretty sure that my work with OLE DB in 1999 and early 2000 is one of the causes of my cynicism towards Microsoft’s technology hype process… At the time OLE DB was the hot topic and there were lots of articles about how easy it was to provide access to your data via a custom provider. Unfortunately as soon as you stepped away from the simple path that the samples took you were in for a world of pain. The hype gave management a warm feeling about taking the OLE DB route for providing data access yet, in reality, if your core business wasn’t writing database engines, it really wasn’t a cost effective route to pursue…

So, because of OLE DB, when someone brings me a “cool” new technology and claims that because simple things are so easy to do in a sample my complex problem will also be easy to solve using the technology I ask to see the code that solves my problem…