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.