Auf deutsch anzeigenDisplay in English

 

:: Home  :: Sitemap  :: Downloads  :: Shop  :: Impressum  :: Newsletter

:: Products  :: Support  :: dataweb

Support

:: NEWS :: With NShape, application programmers can integrate MS Visio-like functionality in their applications. NShape is open source and specifically made for industrial applications. More..

Support Options ::
Frequently Asked Questions ::
Product Documentation ::
Additional Material ::
Discussion Forum ::
NShape Feedback ::
Partners ::
Service Releases ::
Articles ::
Database and SQL Links ::
ISAPI Links ::

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.

OLE DB Layers

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