/* Rambling comments... */: Reprints - OLEDB; no pain, no gain

October 19, 2005

Reprints - OLEDB; no pain, no gain

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

Comments

Here's a link to the Microsoft Omni Provider Sample that's mentioned above.

http://support.microsoft.com/kb/254633

Posted by: Len at July 2, 2008 08:11 AM

"No pain, no gain". Yeah, that's true! I've been writing my own OLEDB Provider since about 4 weeks and I must say: you definitely right! I have gone to the wall with my work and I'm really reflecting on continuing my project. Well, I see you are more advanced than me. Maybe you can help me. The aim of my provider is to consume ADO.NET DataSet supported by some Web Service. Now, I can read and parse Web Service methods, I can flush data into the Excel sheet but when I'm trying to import Web Service data into the Word's Mail Merge with the same way I receive message "Record 1 contained to little data fields". Maybe you know what interface or property I have to implement. I'll be very gratefull if you advice me some solution.

Posted by: Andy at April 1, 2010 10:23 AM

Andy,

All I can suggest is to start with the Omni Provider Sample and expect that it will take a long time to work out what's going wrong...

I haven't done any OLEDB provider development for several years now and I can't say that I miss it!

Good luck and sorry that I can't be of more help.

Posted by: Len at April 1, 2010 10:30 AM

I can't explain this but when I wrote my previous post I experienced a revelation. ;-) The problem is inside (surprise, surprise!) atldb.h inlcude file. MS Word creates SQL string while preparing OLEDB command (i.e "select * from 'mytable'") and if you'll look at the implementation of SetCommandText method you'll see that if the dialect type is DBGUID_SQL, the method will return DB_E_BADCONVERTFLAG. I wrote my own implementation and all works fine.
Anyway, thanks for quick reply!

Posted by: Andy at April 1, 2010 01:19 PM

Cool. Glad to hear you solved your problem. I found that most of the time 'the problem was inside atldb.h' ;)

Posted by: Len at April 1, 2010 01:24 PM