Friday, September 26, 2008

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;

20 comments:

欣然 said...

I am a newbie, I just want no user and password in dbxconnections.ini,but I cann't find how to do,Can you help me?

Esteban Pacheco said...

Thank you for the post.

It help a lot during my D2007 to D2009 conversions.

Keep it up!

Chau Chee Yang said...

欣然, I don't understand your question. Please re-phrase.

jones said...
This comment has been removed by the author.
jones said...

accroding to you solution, I write the code like this, but it does not effect, must use dbx*.ini, can you give me some suggestion? thanks a lot.

procedure initial;
var C: TDBXConnectionFactory;
begin

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

end;

initialization
initial;
end;

Chau Chee Yang said...

Jones. Please send me a prototype of your apps. I try to have a look.

Weverton Gomes de Morais said...

Greate post. It helps me a lot.

Thanks

Djarn said...

Thank you for this blog. It is/was very helpful. In C++ Builder 2009 to rid the need of the ini files and load the drivers correctly, I added the following:

#pragma link "DbxMySQL"
...
TDBXConnectionFactory *connectionFactory = new TDBXConnectionFactory();
connectionFactory->Open();
connectionFactory->SetConnectionFactory(connectionFactory);

Anonymous said...

Delphi 2010 still has most of these problems. The solutions do work for Delphi 2010 as well. Thanks!

george said...

I have a problem with dbexpress driver to. Could you give me an advice. I can't write a blob file into firebird 2.1. My delphi wersion is 12.0.3420.21218 (Delphi 2009). I have all updates available for this time. My application was writen with Delphi 7, and it's wrote blob fields correctly. After recompile on a new delphi I have an error "Incorrect values within SQLDATA structure". I'm using your dbxfb4d12.dll library but the error occures to. I'm using Param[x].LoadFromStream function. Any suggestions ?

Chau Chee Yang said...

George, what error you encounter? Perhaps you need to send me a prototype of your project for replay.

george said...

An error which I see is only "Incorrect values within SQLDATA structure". Can I get more information about an error ?

This is a part of my code:
"
// A "sU" is a blob file, others are integer,timestamp or varchar

vStream:= TMemoryStream.Create;
vStream.Write(vZmienna.sU, SizeOf(vZmienna.sU));
vStream.Seek(0, soFromBeginning);

SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add('INSERT INTO Sk(fData,iP,iL,iS,iD,sU,sT) ');
SQLQuery1.SQL.Add('VALUES (:p0,:p1,:p2,:p3,:p4,:p5,:p6)');

SQLQuery1.Params[0].AsString := vZmienna.s;
SQLQuery1.Params[1].AsInteger := vZmienna.iID;
SQLQuery1.Params[2].AsInteger := vZmienna.iL;
SQLQuery1.Params[3].AsInteger := vZmienna.iS;
SQLQuery1.Params[4].AsInteger := vZmienna.iD;

// at the Delphi 7 was this line, but it dosen't work with Delphi 2009
SQLQuery1.Params[5].AsBlob := vZmienna.sUwagi;

// with Delphi 2009 I tryed a line like this
SQLQuery1.Params[5].SetBlobData(vStream.Memory,vStream.Size);

// and this one
SQLQuery1.Params[5].LoadFromStream(vStream);

SQLQuery1.Params[6].AsString := sTesty;
SQLQuery1.ExecSQL;
"

Chau Chee Yang said...

George, please send the DDL of table Sk to me as well. And which version of Firebird server you are using?

george said...

DDL is a structure of a table ? If yes here you are:
CREATE TABLE "SK"
(
"INR" INTEGER NOT NULL,
"FD" TIMESTAMP NOT NULL,
"IP" INTEGER NOT NULL,
"IL" INTEGER NOT NULL,
"IS" INTEGER NOT NULL,
"ID" INTEGER,
"SU" BLOB SUB_TYPE 0 SEGMENT SIZE 80,
"ST" VARCHAR(150),
PRIMARY KEY ("INR", "FD", "IP", "IL", "IS")
);

Currently I'm using Firebird 2.1 version 2.1.3.18185.

Chau Chee Yang said...

George,

I have test your code and it works fine in my PC using Delphi 2010 and DBX Firebird 4 driver.

There may be some DBX issue that you need to fix in Delphi 2009 VCL source.

This is my code:

var vStream: TMemoryStream;
A: array[0..999] of byte;
begin
vStream := TMemoryStream.Create;
try
vStream.Write(A[0], Length(A));
vStream.Seek(0, soFromBeginning);

SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add('INSERT INTO Sk(INR,iP,iL,"IS",iD,sU,sT,FD) ');
SQLQuery1.SQL.Add('VALUES (:p0,:p1,:p2,:p3,:p4,:p5,:p6,:p7)');

SQLQuery1.Params[0].AsString := '1';
SQLQuery1.Params[1].AsInteger := 1;
SQLQuery1.Params[2].AsInteger := 2;
SQLQuery1.Params[3].AsInteger := 3;
SQLQuery1.Params[4].AsInteger := 4;

// at the Delphi 7 was this line, but it dosen't work with Delphi 2009
// SQLQuery1.Params[5].AsBlob := vZmienna.sUwagi;

// with Delphi 2009 I tryed a line like this
// SQLQuery1.Params[5].LoadFromStream(vStream.Memory, vStream.Size);

// and this one
SQLQuery1.Params[5].LoadFromStream(vStream, ftBlob);

SQLQuery1.Params[6].AsString := 'b';
SQLQuery1.Params[7].AsDateTime := Now;
SQLQuery1.ExecSQL;
finally
vStream.Free;
end;
end;

Anonymous said...

Thanks for sharing the link, but unfortunately it seems to be offline... Does anybody have a mirror or another source? Please reply to my post if you do!

I would appreciate if a staff member here at chee-yang.blogspot.com could post it.

Thanks,
William

Anonymous said...

Hi there,

This is a message for the webmaster/admin here at chee-yang.blogspot.com.

May I use part of the information from this blog post right above if I provide a backlink back to your site?

Thanks,
Alex

Chau Chee Yang said...

Yes. You may do so.

alexeyshestakov said...

Thank you for the post.

Is it possible to get sources for dbExpress Driver for Firebird?

Chau Chee Yang said...

alexeyshestakov: I am trying to tidy up the project. Stay tune at https://sites.google.com/site/dbxfirebird/