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
- Len
Holgate has written a series of articles on writing an OLE DB
Provider. You can find these at www.codeproject.com/database.
- 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.
