Click here to Skip to main content
15,867,308 members
Articles / Desktop Programming / MFC
Article

Creating an OLE DB Data Provider

Rate me:
Please Sign up or sign in to vote.
3.45/5 (13 votes)
12 Jan 20029 min read 128K   3.4K   69   9
This article shows how to create an OLE DB Data Provider that wraps both a C struct and C++ class containing data that is to be made accessible by a SQL query.

Introduction

OLE DB is the Microsoft system-level programming interface to diverse sources of data. OLE DB specifies a set of Microsoft Component Object Model (COM) interfaces that encapsulate various database management system services. The following example shows how to create an OLE DB Data Provider that wraps both a C struct and C++ class containing data that is to be made accessible by a SQL query. The Data Provider supports both reads and writes of the data but not transactions. It contains a simple SQL parser that is sufficient to allow the Visual Basic Data Environment Designer access to the metadata. The example shows how to create a C++ OLE DB Data Consumer using ADO. The Data Consumer demonstrates how to use the Microsoft provided OLE DB Data Service, MsDataShape. It also demonstrates the use of ADO Events to receive a callback when a data table has been updated.

OLE DB

OLE DB specifies a set of COM interfaces that encapsulate various database management system services. Microsoft has provided a template implementation of the minimum needed to get this all to work. In fact, using Visual C++ you can generate a simple Data Provider and Consumer that will share data. The gist of this article will be to show where the simple Data Provider is too simple. You can refer to the msdn library for the article “OLE DB/ADO: Making Universal Data Access a Reality” for an overview of OLE DB.

OLE DB Data Provider

A Data Provider is composed of four components: Data Source, Session, Command and Rowset.

The provider has an instantiation of the Rowsets that contain the hard coded data. This could be easily converted to get the data from a user specified file. The Star Rowset illustrates an example of computing the data from the input parameters. The provider contains a simple SQL parser. The next section will explain what needs to be changed to use this code for a different project.

Data Source – see MySensorDS.h

The Data Source is the COM object that is registered by your DLL as the OLE DB Provider. The only modification done here was to make the dbprop INIT_DATASOURCE writable and to capture it after the database was opened.

Session – see MySensorSess.h and .cpp

The Session is the COM object that controls the transactions against the data ie start, commit and rollback. It also provides the metadata for the database using the SCHEMA_MAP. These were fairly straightforward to implement. However, make sure that the optional fields: DBCOLUMN_BASECOLUMNNAME, DBCOLUMN_BASETABLENAME and DBCOLUMN_KEYCOLUMN (see IColumnsRowsetImpl.h) are provided.

Command – see MySensorCmd.h and .cpp

The Command is the COM object that receives the SQL. It needs to parse the SQL and return a Rowset to the consumer. A number of dbprops were added in order to state that the database supports updates and callback events. The Execute() routine parses the SQL command. It assumes it is for a single table. It finds the name of the table in the hard coded data and tells that table to return a copy of the selected rows. The GetColumnInfo() routine will parse the SQL command for a single table name and return the metadata about that table.

Rowset – see MySensorRS.h and .cpp

The Rowset is the COM object that contains the data. The hard coded data is in an STL map of Rowsets. The Command::Exexcute() will return a copy of one of these Rowsets. The Rowset contains the object that points to the actual data. If it is a C++ class, then you can simply use the PROVIDER_COLUMN_MAP to describe the members of the class. If it is a C struct, then you must wrap it with a class and have the wrapper implement the two versions of GetColumnInfo(). The Rowset must implement the Execute() routine. A template implementation, IMyRowsetUpdateImpl, was added to simplify the coding of the Execute() routine. Note: the Star Rowset doesn’t call MyExecute() but computes the data from the input parameters.

SQL Parser – see SqlParser.cpp, SqlCollections.cpp

The SQL parser understands SELECT, UPDATE, INSERT, DELETE and simple where clauses. It creates a linked list of CStrings for each keyword and value specified. It verifies the table and column names. A helper function was added, FindColumnOperator(), that returns the operator and value for a specified column. This allows the database writer to support queries where the data must be computed before it can be returned. See the Star Rowset for an example. The parser contains a Print function to help debug the provider.

Data – see MyData.h and .cpp

The data that the OLE DB system provides is in the form of Rowsets. MyData is a global object that provides a map keyed on the table name of those Rowsets. This allows easy access from anywhere in the system to the implemented tables. Each table in the map must contain at least one row of data. This will allow the metadata for that table to be accessed even if the “real” values will be computed at runtime. MyData also loads the SqlParser with the table and column names. An improvement would be to change the SqlParser to get this information from GetColumnInfo() instead of storing it separately.

Another OLE DB project

A lot of the above code can be used directly in any OLE DB provider. A new project would need to change the class names from MyXXX to <newname>XXX. The GUID in the MyDataSource needs to be changed. Most of the code in MySensorRowset.h needs to be changed to use the new data classes. The code in MySensorRowset.cpp needs to also support the new classes. Note that if the data is not computed then the Execute() routines can simply find the correct table in the MyData::map and call MyExecute(). The data stored in MyData.cpp needs to be changed. The .rc file must contain a registry entry that points to the .rgs file. The Data Source uses this through the DECLARE_REGISTRY_RESOURCEID. The .rc file and resource.h file should contain the IDS_XXX values. Copy these into your project’s respective files.

OLE DB Data Consumer – see SensorInterfDlg.h and .cpp

Microsoft has done a lot of work to try and make database access easy and universal with ADO (ActiveX Data Objects) . It is particularly useful in Visual Basic where the COM objects of OLE DB are cumbersome to use. However, it also simplifies things in C++. ADO has the concept of connections and declares the smart pointer _ConnectionPtr. When a user opens a connection they specify the name of the database and optionally their name, authorization and location of the database. A connection will open the Data Source and start a Session within the Data Provider. ADO also has the concept of Recordsets and declares the smart pointer _RecordsetPtr. Recordsets are very similar to Rowsets in the Data Provider. The user can open a Recordset by specifying the SQL statement and connection. The SQL statement will be handed to a Command object, which will return the data to the Recordset.

SQL – supported subset

The current Data Provider only supports a subset of the full SQL language: SELECT, UPDATE, INSERT, DELETE and simple where clauses. The values for the where clause should be in the SQL text and not specified as separate Command parameters. The parser does not implement the where clauses. However, certain tables may have implemented specific queries. The consumer needs to check with the data provider for which where clauses are supported. The Star Rowset is an example of such an implementation.

MsDataShape – support for multiple tables

The SQL parser only supports queries on single tables. However, this can be worked around using the OLE DB Data Service, MsDataShape, provided by Microsoft. A Data Service sits between the Data Consumer and Data Provider and acts as a consumer to the provider and a provider to the consumer. MsDataShape can perform the join across tables. Note that it has its own special SQL syntax but will issue just vanilla SQL to the Data Provider. Also it requires the use of client side cursors. This means that MsDataShape extracts a copy of each table and performs the join. If you large amounts of data, this could get expensive.

ADO Events

If the user wants to make a “real time” graph or report, they can request the Data Provider to send them an event when the data is updated in a specific table. The user must find the ConnectionPoint on the table. They must create a class specifying all the possible callbacks, see CrstEvent in SensorInterfDlg.h. (This seems like overkill but is required by COM. COM+ fixes this but OLE DB hasn’t been updated yet.) The ConnectionPoint is then Advise() of the requesting class. Note the use of adStatusUnwantedEvent on most of the callbacks. Also note the setting of “Update Resync”. This will cause the event to be sent to any open queries on the table. Finally the dialog was stored in the callback class to make it easy to update the dialog within the callback.

Using ADO

Microsoft has provided some data bound controls to make it easier to work with a database. This example uses a data bound combo box. The combo box is pointed at the table, SetRefRowSource(), and the field within the table, SetListField(). It will then show all the values in that column in the combo. A similar thing can be done with Rich Text Edit fields but this example just loads the text normally. The ADO code will throw an exception if there are any errors. Remember to use try/catch calls around the ADO code. Also all open Connections and Recordsets should be closed before exiting.

Multiple consumers

The current Data Provider has been used with the Data Consumer described above written in C++. Also, a separate application using Visual Basic was able to access the data. By specifying VB as the application in Dev Studio, the provider could be debugged while running VB. A simple report using Crystal Reportsä has been generated. Note tell Crystal Reports the provider is an ADO database, not just an OLE DB database. Finally, a simple application using ChartFXä was written. ADO was used to fetch a Rowset from the provider, which was handed directly to ChartFX. This shows the power of providing an OLE DB interface to your custom data.

Acknowledgements

  1. Len Holgate has written a series of articles on writing an OLE DB Provider. You can find these at <a href="http://www.codeproject.com/database">www.codeproject.com/database.
  2. Ben Faul has written an article on using SQL in C++. The SQL parser was copied from this article. See www.cuj.com for March 2000.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
David Utz
Senior Software Engineer
Analytical Graphics, Inc.
40 General Warren Blvd.
Malvern, PA 19355
dutz@stk.com

Comments and Discussions

 
QuestionHow to get data in Microsoft Access from this custom OLE DB Provider? Pin
ppk8-Dec-06 11:16
ppk8-Dec-06 11:16 
QuestionCan't Compile Pin
Mike C#9-Oct-06 10:41
Mike C#9-Oct-06 10:41 
GeneralCrystal Report Not fetching all records from my Provider Pin
Ghaffar Khan15-Aug-06 1:55
Ghaffar Khan15-Aug-06 1:55 
GeneralUsing OLE DB within OLE DB Pin
larsibam20-Dec-05 0:49
larsibam20-Dec-05 0:49 
GeneralRe: Using OLE DB within OLE DB Pin
David Utz20-Dec-05 3:16
David Utz20-Dec-05 3:16 
GeneralRe: Using OLE DB within OLE DB Pin
larsibam20-Dec-05 5:45
larsibam20-Dec-05 5:45 
Thanks David for the answer.

Unfortunately I can't use .NET (need to be compatible with 'classic' OLEDB).
I couldn't figure any other way to clean up my internal connection (i.e. call the IDBInitialize::Release()) but in the finalrelease() of the session object, and that's never called ;-(
I've tried to encapsulate the internal connection in a seperate com dll using a surrogate process and LOCAL_SERVER instantiation. That's working, but of course the inter-process overhead almost doubles the time it takes to fetch the records from the underlying database (We are talking about something between a million and two million recordsets). That is kind of critical for our application concerning the performance, but I think we have to swallow that.
GeneralJoins Pin
Paul Wolfensberger30-Oct-03 17:17
Paul Wolfensberger30-Oct-03 17:17 
GeneralUsing indexes Pin
Gil Messerman23-Jul-03 20:44
Gil Messerman23-Jul-03 20:44 
GeneralRe: Using indexes Pin
David Utz25-Jul-03 3:16
David Utz25-Jul-03 3:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.