Implementing an ADO Server with Delphi
Using the OLE DB Simple Provider
This article explains the concepts and the implementation
of an OLE DB Provider in Delphi. It uses the OLE DB Simple Provider framework
to simplify the task and includes a complete running sample program.
OLE DB Providers
If you want to access your legacy data through ADO,
e.g. with Visual Basic or in an Active Server Page, you will have to write
a OLE DB Provider. ADO is just a simplified access layer for OLE DB and
therefore there is no such thing as an ADO server proper. OLE DB on the
other hand is a hard-core COM technology from the times, where Microsoft
was thinking that everything should be coded in form of CoClasses and Interfaces,
even the operating system itself. Nowadays Microsoft's philosophy has changed
as you can see in .NET, but OLE DB is still an important technology. Being
COM makes it rather unpleasant to implement as there are several dozens
of interfaces and some hundreds of properties to take care of. But is that
effort really necessary? For a lot of applications the answer is a definite
no. If your concern is to expose legacy data to contemporary programming
tools and technologies you don't need e.g. the ability to create new tables,
to use indexes or perhaps even to execute SQL statements. And perhaps even
performance might be not such a big issue for you.
The OLE DB Simpler Provider Framework
In this case take a look at the OLE DB Simple Provider
framework offered by Microsoft. Like ADO being an access layer for OLE DB
at the client-side, the OLE DB Simple Provider as a layer at the server-side,
whose purpose is to facilitate the implementation of a (simple) OLE DB Provider.
The component used by the OLE DB Simple Provider to access the data is called
data object. The data object is a COM object and the custom part
of the OSP framework. That is what you have to implement in order to create
an OLE DB Provider using the OSP framework.
The OLE DB Simple Provider simplifies the task of
writing an OLE DB Provider by making some assumptions on the data structure,
concentrating on the absolute minimum of functionality, trading performance
against simplicity and by providing standard implementations for a lot of
methods and properties. The assumptions made by the OSP are:
- All data is accessed in form of tables/rowsets
directly. No SQL commands available.
- Records can be addressed by row indexes.
- All data values are set and retrieved in form
of variants.
- Updates can only be performed for server-sided
cursors.
- There are very few operators for searching records.
Creating an OSP Data Object with Delphi
For many reasons Delphi is the programming tool most
suited for creating COM clients and servers. Object Pascal has a built-in
support for COM programming that can not be equaled by any C++ dialect,
even if COM has been designed with C++ compatibility in mind. On the other
hand scripting languages like Visual Basic do not have the power to be used
for serious COM server programming. Technically VB can create COM components,
but without the possibility to implement true object oriented designs and
without a reasonable error-handling scheme, one should rather head for a
better suited tool.
This said, you will be eager to implement the data
object for your custom data. Ok, so what do have to do? An OSP data object
consists of two parts, the data source and the provider. The provider encapsulates
the actual data access. One such provider will be instantiated for each
rowset created at the ADO client side. The provider has the ability of reading
the data and handing it to the OSP in form of variants. It can modify, insert
and delete rows in the rowset and offers a simple searching functionality.
All this is accessed via a single COM interface called IOleDbSimpleProvider.
OLEDBSimpleProvider = interface(IUnknown) ['{E0E270C0-C0BE-11D0-8FE4-00A0C90A6341}']
function getRowCount(out pcRows: Integer): HResult; stdcall;
// Returns the number of rows available in the table
function getColumnCount(out pcColumns: Integer): HResult; stdcall;
// Returns the number columns in the table.
function getRWStatus(iRow: Integer; iColumn: Integer; out prwStatus: OSPRW): HResult; stdcall;
// Returns a constant to decide, if the field at iRow, iColumn is writable or read-only
function getVariant(iRow: Integer; iColumn: Integer; format: OSPFORMAT;
out pVar: OleVariant): HResult; stdcall;
// Returns the value of field iRow, iColumn as a variant.
function setVariant(iRow: Integer; iColumn: Integer; format: OSPFORMAT;
Var_: OleVariant): HResult; stdcall;
// Sets a new value to the field at (iRow, iColumn).
function getLocale(out pbstrLocale: WideString): HResult; stdcall;
// Returns the locale string for the table
function deleteRows(iRow: Integer; cRows: Integer; out pcRowsDeleted: Integer): HResult; stdcall;
// Deletes cRows rows from the table starting at row iRow
function insertRows(iRow: Integer; cRows: Integer; out pcRowsInserted: Integer): HResult; stdcall;
// Inserts cRows empty rows beginning at row iRow.
function find(iRowStart: Integer; iColumn: Integer; val: OleVariant; findFlags: OSPFIND;
compType: OSPCOMP; out piRowFound: Integer): HResult; stdcall;
// Searches (starting at iRowStart) for a row satisfying the search-condition
// defined by iColumn, val, findFlags and compType.
function addOLEDBSimpleProviderListener(const pospIListener: OLEDBSimpleProviderListener):
HResult; stdcall;
// Registers a listener with the provider to notify of data modifications
function removeOLEDBSimpleProviderListener(const pospIListener: OLEDBSimpleProviderListener):
HResult; stdcall;
// Unregisters the listener.
function isAsync(out pbAsynch: Integer): HResult; stdcall;
// Return true (=1), if the provider is able to populate or update the data asynchronously.
function getEstimatedRows(out piRows: Integer): HResult; stdcall;
// Returns an estimated row count if the exact count is not available.
function stopTransfer: HResult; stdcall;
// Stops the current asynchronous data transfer.
end;
All row and column indexes in this interface are one-based.
Row index 0 in getVariant means the header and not a data cell, i.e. the
column names are expected.
The other part of the OSP data object is the data
source. Its purpose is to manage the various providers, offer a list of
available providers and to create and deliver the provider for a given table
name. To this end, the data source object implements the DataSource
interface, which has only five very simple methods:
DataSource = interface(IUnknown) ['{7C0FFAB3-CD84-11D0-949A-00A0C91110ED}']
function getDataMember(const bstrDM: DataMember; var riid: TGUID; out ppunk: IUnknown):
HResult; stdcall;
// Returns the IOleDbProvider interface reference for the table named bstrDM
function getDataMemberName(lIndex: Integer; out pbstrDM: DataMember): HResult; stdcall;
// Optional: Returns the name of the table available with index lIndex
function getDataMemberCount(out plCount: Integer): HResult; stdcall;
// Returns the number of data members (= tables) availabe. If zero, tables can still
// be created passing any name to getDataMember
function addDataSourceListener(const pDSL: DataSourceListener): HResult; stdcall;
// Registers a listener with the data source to receive events on modifications of
// data members
function removeDataSourceListener(const pDSL: DataSourceListener): HResult; stdcall;
// Unregisters a listener previously registered using addDataSourceListener
end;
Implementation
Before you start to code, you should perhaps download
the current version of the Microsoft Data Access SDK (2.6 at the time of
this writing). MDAC includes the OLE DB Simple Provider as well as the two
type libraries you will need for the COM server (http://www.microsoft.com/data/download.htm).
When MDAC is installed, you can create a new ActiveX library (File/New/Others/ActiveX/ActiveX
Libarary in Delphi 6) and then add a new COM object (File/New/Others/ActiveX/COM
Object). In the COM Object Wizard enter a name for the data source
object, e.g. MyOleDbDataSource, leave Instancing and
Threading Model as it is and click the Implemented Interface
button. You should be able to find the DataSource interface in the list
(click on the interface header to sort after the interface names). Its version
should be 1.0 for MDAC 2.6 and the type library name is msdatsrc.tlb. If
you can't find this interface in the list, click Add Library and browse
for the type library delivered with MDAC and most probably installed in
c:\programs\microsoft data access sdk\tlb\x86.
The result of doing so is visible in the type library
editor that shows up now. On the uses page of the project you will
see the Microsoft Data Source Interfaces included in the list and
on the implements page of the CoClass there is the DataSource
interface. You may just close the type library editor and start implementing
the MyOleDbDataSource object. The type library editor of Delphi
6.163 has a small error in that it does not list the methods of the implemented
interface correctly with the implementing class. At least in my version
only the last method of the interface is included. But you can easily correct
the problem. Open the type library unit MSDATSRC_TLB.pas the TypeLib editor
has just created and copy and paste all methods of the DataSource interface
to the implementing class. Depending on the edition you have, you can now
right-click and choose Complete class at cursor... to create all
the method bodies.
The same process is necessary for the provider class.
This time you have to implement OLEDBSimpleProvider which is to
be found in the SIMPDATA type library also located in the MDAC directory.
Here too, the TypeLib editor of my copy of Delphi does not create all the
method headers and bodies correctly, so they have to be adjusted by hand.
Now you have about 20 methods to implement according
to the logic of your data source. Some of the methods can be left empty
(i.e. just returning S_OK) like add*Listener and remove*Listener, others
are trivial to implement like isAsynch (no), or getDataMemberCount
(zero). Only a few need real programming to be done, most of it concerning
your proprietary data format (setVariant, getVariant, insertRows, deleteRows,
find, etc.). But there is one method we should give some general thoughts,
namely getDataMember.
This DataSource method takes a data member
name (=table name) and returns the interface reference to the OLEDBSimpleProvider
interface of this table. Where does this provider object come from? If there
isn't one for the table required we have to instantiate it. You can do this
either in the regular way, retrieving the ComObjectFactory from
the ComClassManager and calling CreateComObject on it,
or by simply calling the constructor. The second alternative has the advantage
of being able to use a custom constructor, but you must not forget to call
_AddRef on the object to control its life-time. In any case you
might want to keep a list of active providers in the data source, so you
can return the same object, when the table is required a second time. This
enables you to handle caching and other centralized functions. Note that
all the methods in OLEDBSimpleProvider are stateless, so you can
reuse one provider instance for an arbitrary number of queries. When you
are done with the implementation, build it to create the library and to
register the ActiveX server.
Using the OLE DB Simple Provider
The OSP works fine with Visual C++, Visual Basic and
even .NET, but it is more difficult to use it from Delphi. I have tried
a number of Delphi ADO components and it turned out that many of them use
methods or properties that are not included in the specification of a minimal
OLE DB Provider and therefore do work properly with the OSP. Borland's ADOExpress
components for instance can access the OSP with a client-side cursor but
not with a server-sided one. Therefore they are not able to update the data
source, it only works in the read-only mode. One ADO component suite I have
found working properly is Adonis from WinSoft (http://www.cybermagic.co.nz/adonis/).
With all ADO components you will have to set the command type to TableDirect
for ADO to request a rowset directly instead of a command. The connection
string is Provider=MSDAOSP;Data Source=YourLib.YourComponent, where
YourLib.YourComponent refers to the complete ProgId of the data
source component.
Notifications
There are two outgoing interface in the data object,
to notify clients (i.e. MSDAOSP) of changes in the data source or in the
table. One is used for modifications in the number or the names of the available
data members (= tables) in the data source. The client implements the DataSourceListener
interface and registers it using your data source implementation's addDataSourceListener
method. There will be only one client registering so you can do with a single
variable of type DataSourceListener.
The other notification interface is OLEDBSimpleProviderListener
and is the source for the ADO events like WillChangeField and
RecordSetChangeComplete. If you are using the approach mentioned
above to create only one provider instance per table, you will need to implement
a list of listeners here, as the OLE DB Simple Provider registers one listener
for each table. The implementation of both mechanisms is straight-forward
and can be examined in the sample program.
Sample Program
The sample program is
written in Delphi 6 and demonstrates the implementation of all functions
including the event interfaces. The data is merely a in-memory array to
keep the code simple and understandable, it is documented so you can use
it to base your own implementation on it.
Further Reading
The Microsoft OLE DB Simple Provider Toolkit documentation
Peter Pohmann is a trainer, speaker, coach and
author at dataweb specialized on Delphi, COM and database applications.
He likes to hear about your comments to this article: peterDOTpohmannADDdataweb.de
|
