SynOleDB: OpenSource Unit for direct access to any database via OleDB

Providers

Just a small list of available OleDB providers:
- Active Directory Service
- Advantage
- AS/400 (from IBM)
- AS/400 and VSAM (from Microsoft)
- Commerce Server
- DB2
- DTS Packages
- Exchange
- Excel
- Internet Publishing
- Index Server
- Microsoft Jet
- Microsoft Project
- MySQL
- ODBC Databases
- OLAP Services
- Oracle (from Microsoft)
- Oracle (from Oracle)
- Pervasive
- Simple Provider
- SQLBase
- SQL Server
- SQL Server via SQLXMLOLEDB
- Sybase Adaptive Server Anywhere
- Sybase Adaptive Server Enterprise
- Text Files
- UniData and UniVerse
- Visual FoxPro

Sample code

Here is some working sample program, using our SynOleDB unit to connect to a local Microsoft SQL Server 2008 R2 Express edition, which will write a file with the JSON representation of the Person.Address table of the sample database AdventureWorks2008R2:

program TestOleDB;
{$APPTYPE CONSOLE}
uses
  SysUtils,
  Classes,
  SynCommons,
  SynOleDB;

var Props: TOleDBConnectionProperties;
    Conn: TSQLDBConnection;
    Query: TSQLDBStatement;
    F: TFileStream;
begin
  with OleDBSynLogClass.Family do begin
    Level := LOG_VERBOSE;
    AutoFlushTimeOut := 10;
  end;
  Props := TOleDBMSSQLConnectionProperties.Create('.\\SQLEXPRESS','AdventureWorks2008R2','','');
  try
    //Props.ConnectionStringDialogExecute;
    Conn := Props.NewConnection;
    try
      Conn.Connect;
      Query := Conn.NewStatement;
      try
        Query.Execute('select * from Person.Address',true,[]);
        F := TFileStream.Create(ChangeFileExt(paramstr(0),'.json'),fmCreate);
        try
          Query.FetchAllToJSON(F,false);
        finally
          F.Free;
        end;
      finally
        Query.Free;
      end;
    finally
      Conn.Free;
    end;
  finally
    Props.Free;
  end;
end.

Note that you can call Props.ConnectionStringDialogExecute to display the ADO/OleDB connection dialog box, to let the user set the appropriate database settings.

You can specify parameters, bound to the request, as such:

Query.Execute('select * from Person.Customer where Name like ?',true,['B%']);

Or using direct Bind*() methods.

TQuery-like wrapper

There is also a TQuery class defined, which is able to mimic basic TQuery VCL methods:

 Q := TQuery.Create(aSQLDBConnection);
 try
   Q.SQL.Clear; // optional
   Q.SQL.Add('select * from DOMAIN.TABLE');
   Q.SQL.Add('  WHERE ID_DETAIL=:detail;');
   Q.ParamByName('DETAIL').AsString := '123420020100000430015';
   Q.Open;
   Q.First;    // optional
   while not Q.Eof do begin
     assert(Q.FieldByName('id_detail').AsString='123420020100000430015');
     Q.Next;
   end;
   Q.Close;    // optional
 finally
   Q.Free;
 end;

This class can mimic basic TQuery VCL methods, but won't need any BDE installed, and will be faster for field and parameters access than the standard TDataSet based implementation; in fact, OleDB replaces the BDE or the DBExpress layer!
- Since there is no underlying TDataSet, you can't have read and write access, or use the visual DB components of the VCL: it's limited to direct emulation of low-level SQL as in the above code, with one-direction retrieval (e.g. the Edit, Post, Append, Cancel, Prior, Locate, Lookup methods do not exist within this class);
- This class is Unicode-ready even before Delphi 2009 (via the TQueryValue AsWideString method), will natively handle Int64/TBytes field or parameter data, and will have less overhead than the standard DB components of the VCL;
- You should better use TSQLDBStatement instead of this wrapper, but having such code-compatible TQuery replacement could make easier some existing code upgrade (e.g. to avoid deploying the deprecated BDE, generate smaller executable, access any OleDB database without paying a big fee, avoid rewriting a lot of existing code lines of a big application...).

The magic of late binding

We implemented late binding access of column values, via a custom variant time. It uses the internal mechanism used for Ole Automation, here to access column content as if column names where native object properties.

The resulting Delphi code to write is just clear and obvious:

Props := TOleDBMSSQLConnectionProperties.Create('.\\SQLEXPRESS','AdventureWorks2008R2','','');
procedure TestISQLDBRowsVariant;
var Customer: Variant;
begin
  with Props.Execute('select * from Sales.Customer where AccountNumber like ?',
    ['AW000001%'],@Customer) do
    while Step do
      assert(Copy(Customer.AccountNumber,1,8)='AW000001');
end;

Note that Props.Execute returns an ISQLDBRows interface, so the code above will initialize (or reuse an existing) thread-safe connection (OleDB uses a per-thread model), initialize a statement, execute it, access the rows via the Step method and the Customer variant, retrieving the column value via a direct Customer.AccountNumber statement.
The above code is perfectly safe, and all memory will be release with the reference count garbage-collector feature of the ISQLDBRows interface. You don't have to add any try..finally Free end statements in your code.
This is the magic of late-binding and interfaces in Delphi.

The low-level Variants unit is (optionally) hacked to use a dedicated direct call of our properties. In practice, this late binding is as fast using a standard property like Column['ColumnName'] than using the variant variation Customer.ColumnName. This speed increase has also been shared with the late-binding access of values for our Big Table unit.

Works from Delphi 6 up to XE.
Licensed under a MPL/GPL/LGPL license, as part of our framework.

Feedback and comments are welcome on our forum.

Synopse