Delphi 2009: Using DBX4 Framework

Delphi 2009 DBX4 has some behavioral changes compare to Delphi 2007 DBX4.  I blog all my study and research against DBX4 here so that I may continue working next time when there are new DBX updates in later official updates from CodeGear.

My first experience on Delphi 2009 DBX4 is not good and full of glitches while I working on it.  After spending few days study the DBX4 source (documentation doesn't help much here).  I finally come out with my own solution for Delphi 2009 DBX4 in order to make my Delphi 2007 application able to migrate over.

Case 1: dbxdrivers.ini and dbxconnections.ini

Note: This problem has resolved in Delphi build 12.0.3250.18309.

Deploy DBX4 application on a new machine will always fail at runtime if dbxdrivers.ini and dbxconnections.ini do not exist in folder "%public%\Documents\RAD Studio\dbExpress".

I have post this issue to CodeGear discussion forum and receive pretty good response here: https://forums.codegear.com/message.jspa?messageID=20783#20783

Bob Swart who help me confirm the problem has file a QC report: http://qc.codegear.com/wc/qcmain.aspx?d=67210 

Case 1: Solution

The cause of this problem to related to the design of static method TDBXConnectionFactory.GetConnectionFactory in unit DBXCommon.pas. This method is design using singleton pattern that return a TDBXConnectionFactory instance.  However, the default instance returned is instantiated from class TDBXIniFileConnectionFactory.  This class always return loaded drivers and connections from the 2 *.ini files.

TDBXMemoryConnectionFactory is only used by TSQLConnection.DoConnect in a try...except...end block.  I think it's purpose is to create TDBXIniFileConnectionFactory factory first, and if fail due to missing two *.ini files or whatever reason, it will use a TDBXMemoryConnectionFactory instance.  However, there are many other DBX operations invoke TDBXMemoryConnectionFactory.GetConnectionFactory too (e.g.: TSQLConnection.SetDriverName).  This is not a good design to cover all the holes by try...except...end as in TSQLConnection.DoConnect.

To provide a workaround solution for this problem, I create a TDBXMemoryConnectionFactory instance explicitly and inject it into TDBXConnectionFactory before TDBXConnectionFactory.GetConnectionFactory was invoked.  Since it behave as singleton, it won't instantiate the default TDBXIniFileConnectionFactory class any more for it's life time process:

var C: TDBXConnectionFactory;
begin
  C := TDBXMemoryConnectionFactory.Create;
  C.Open;
  TDBXConnectionFactory.SetConnectionFactory(C);
end;

Case 2: TSQLConnection.DriverName must have value

In my DBX application, I create a TSQLConnection instance on the fly as my application may work with many type of database server (e.g.: Firebird, MSSQL or MySQL).  I couldn't decide the DriverName at design time.  It is up to the end user to state which database server to connect.

As GetDriverFunc, LibraryName and VendorLib property in TSQLConnection seems to provide enough parameters for a database connection, I tend to left TSQLConnection.DriverName empty as it serve no purpose for the connection except filling the 3 properties value mention retrieve from TDBXConnectionFactory.FDrivers collection.

However, an empty DriverName will fail in procedure TSQLConnection.CheckLoginParams invoked by TSQLConnection.DoConnect:

procedure TSQLConnection.CheckLoginParams;
var
  I: Integer;
  DriverProps: TDBXProperties;
begin
  ...
  if FDriverName = '' then DataBaseError(SMissingDriverName);
  ...
end;

I personally feel this is an unnecessary checking if I don't rely on two *.ini files to initiate a connection.  In order to avoid spending time doing source code patch for this issue, I rather specify a non-empty DriverName value for TSQLConnection:

begin
  ...
  sqlconnection1.DriverName := 'Firebird';
  ...
end;

Another issue float on now.  As I use a workaround solution mentioned in Case 1 to avoid deploying 2 *.ini files, I have no driver instance store in TDBXConnectionFactory.FDrivers collection.  When I try to set a value for DriverName, a "TDBXErrorCodes.DriverInitFailed" exception will occurs in SQLConnection.SetDriverName method.

Case 2: Solution

I should register a Firebird dynalink driver to DBX4 framework in order to make DriverName setting work.  The code is copy from DBXInterbase.pas and some changes has made allow it work as expected:

unit DBXFirebird;

...

const
  sDriverName = 'Firebird';

...
initialization
  TDBXDriverRegistry.RegisterDriverClass(sDriverName, TDBXFirebirdDriver);
finalization
  TDBXDriverRegistry.UnregisterDriverClass(sDriverName);
end.

You should able to to connect to firebird database with DBX4 now.

Case 3: Custom value for GetDriverFunc, LibraryName and VendorLib in property TSQLConnection is not functioning

No matter what value you set for GetDriverFunc, LibraryName and VendorLib, TSQLConnection will not respect these values.  It will only follow the initial values specify in dynalink driver (DBXInterbase.pas).

After tracing the dynalink driver source, I found out the problem occurs in the following method:

constructor TDBXInterBaseDriver.Create(DBXDriverDef: TDBXDriverDef);
begin
  inherited Create(DBXDriverDef, TDBXDynalinkDriverLoader);
  rpr;
  InitDriverProperties(TDBXInterBaseProperties.Create(DBXDriverDef.FDBXContext));
end;

Invoke InitDriverProperties will set a default properties instance into TDBXInterbaseDriver.  When the following method is invoke:

procedure TDBXDynalinkDriver.LoadDriver(DBXContext: TDBXContext);
var
  Loader: TDBXDynalinkDriverCommonLoader;
begin
  if not Assigned(FMethodTable) then
  begin
  ...
      Loader.LoadDriverLibraryAndMethodTable(DBXContext, GetDriverProperties);
  ...
  end;
end;

The Loader.LoadDriverLibraryAndMethodTable will always get the default TDBXProperties instance from InitDriverProperties.

I have reported this problem to QC: http://qc.codegear.com/wc/qcmain.aspx?d=67139

Case 3: Solution

After study the source, I found out the problem happens in the following method:

function TDBXDynalinkDriverNative.CreateConnection(ConnectionBuilder: TDBXConnectionBuilder): TDBXConnection;
var
  ConnectionHandle: TDBXConnectionHandle;
  ErrorResult:  TDBXErrorCode;
begin
  LoadDriver(ConnectionBuilder.DbxContext);
  ErrorResult := FMethodTable.FDBXDriver_CreateConnection(FDriverHandle, ConnectionHandle);
  CheckResult(ErrorResult);
  Result := TDBXDynalinkConnection.Create(ConnectionBuilder, ConnectionHandle, FMethodTable);
end;

The ConnectionBuilder.ConnectionProperties contain the all the custom setting from TSQLConnection.  Unfortunately, it never be used in CreateConnection.  Instead, it use the initial properties stored in TDBXDriver.FDriverProperties.

My solution is override the CreateConnection method to make it consume the custom value in ConnectionBuilder.ConnectionProperties:

function TDBXFirebirdDriver.CreateConnection(ConnectionBuilder:
    TDBXConnectionBuilder): TDBXConnection;
var
  ConnectionHandle: TDBXConnectionHandle;
  ErrorResult:  TDBXErrorCode;
begin
  LoadDriverWithConnectionBuilder(ConnectionBuilder);
  ErrorResult := FMethodTable.FDBXDriver_CreateConnection(FDriverHandle, ConnectionHandle);
  CheckResult(ErrorResult);
  Result := TDBXDynalinkConnection.Create(ConnectionBuilder, ConnectionHandle, FMethodTable);
end;

procedure TDBXFirebirdDriver.LoadDriverWithConnectionBuilder(ConnectionBuilder:
    TDBXConnectionBuilder);
var
  Loader: TDBXDynalinkDriverLoader_Enhance;
begin
  if not Assigned(FMethodTable) then
  begin
    Loader := FDriverLoaderClass.Create as TDBXDynalinkDriverLoader_Enhance;
    try
      Loader.LoadDriverLibraryAndMethodTable(ConnectionBuilder.DbxContext, ConnectionBuilder.ConnectionProperties);
      FMethodTable := Loader.FMethodTable;
      Loader.FMethodTable := nil;
      FDriverHandle := Loader.FDriverHandle;
      Loader.FreeOldLibrary;
    finally
      FreeAndNil(Loader.FMethodTable);
      Loader.Free;
    end;
  end;
end;

The loader class TDBXDynalinkDriverLoader_Enhance is duplicated and inherited from TDBXDynalinkDriverLoader as I can't access 2 private methods: FreeOldLibrary and LoadDriverLibraryAndMethodTable:

TDBXDynalinkDriverLoader_Enhance = class(TDBXDynalinkDriverLoader)
private
  procedure FreeOldLibrary;
  procedure LoadDriverLibraryAndMethodTable(DBXContext: TDBXContext; Properties:
      TDBXProperties);
end;

Case 4: AutoUnloadDriver doesn't work as is

AutoUnloadDriver is a new connection parameter property for Delphi 2009.  As stated in DBXCommon.pas for AutoUnloadDriver:

If set to true, dynalink drivers will automatically unload their dll, when there are no longer any open connections that use the driver.

I love this feature as it will release the related DLL if no connection is active.

However, none of the Delphi DBX driver provide this as default setting and there is no way to make AutoUnloadDriver activate.

I have report this problem: QC#67233.

Case 4: Solution

The Dynalink Driver class has the following constructor:

constructor TDBXDynalinkDriver.Create(DBXDriverDef: TDBXDriverDef; DBXDriverLoader: TDBXDynalinkDriverCommonLoaderClass);
begin
  inherited Create(DBXDriverDef);
  FDriverLoaderClass := DBXDriverLoader;
  // '' makes this the default command factory.
  //
  AddCommandFactory('', CreateDynalinkCommand);
  if (DriverProperties = nil) or not DriverProperties.GetBoolean(TDBXPropertyNames.AutoUnloadDriver) then
    CacheUntilFinalization;
end;

To make AutoUnloadDriver work as it should, the clue is to avoid invoke CacheUntilFinalization in the constructor.  In order to do that, we must make sure DriverProperties is not nil and AutoUnloadDriver property has value "True":

constructor TDBXFirebirdDriver.Create(DBXDriverDef: TDBXDriverDef);
var P: TDBXProperties;
begin
  P := TDBXProperties.Create(DBXDriverDef.FDBXContext);
  P.Values[TDBXpropertyNames.AutoUnloadDriver] := 'True';
  InitDriverProperties(P);
  inherited Create(DBXDriverDef, TDBXDynalinkDriverLoader_Enhance);
end;