dbExpress driver for InterBase	History
Readme	Support

dbExpress is database-independent layer that defines a common interface to provide fast access to SQL database servers. For each supported server dbExpress provides a driver as an independent library that implements the common dbExpress interfaces for processing queries and stored procedures. As data-access layer is thin and simple, dbExpress provides high performance database connectivity and is easy to deploy. dbExpress drivers are available for Windows as dynamic-link libraries.

DbxIda driver provides access to InterBase, Firebird and Yaffil database servers is based on Borland dbExpress data access technology. It uses directly InterBase client software to connect to server.

• Key features
• Compatibility
• Windows installation notes
• Linux installation notes
• Using
• Type map
• Custom transaction isolation
• Extended driver options
• TCRSQLConnection
• Using DBMonitor
• Deployment
• Trial limitations
• Notes
• Known problems
• Demo projects

Key features

The following list describes the main features of DbxIda driver

• Full support of the latest versions of InterBase, Firebird and Yaffil database servers
• High performance
• Ability of monitoring query execution with DBMonitor
• Extended options for advanced behaviour
• Complete support for dbExpress version 4
• Available source code
• Included annual DbxIda Subscription with Priority Support
• Licensed royalty-free per developer, per team, or per site

Compatibility

DbxIda works with RAD Studio 2010, RAD Studio 2009, RAD Studio 2007, Turbo Delphi, Turbo Delphi for .NET, Turbo C++, Delphi 2006, Delphi 2005, Delphi 8, Delphi 7, Delphi 6, C++Builder 6, Kylix 3 and Kylix 2.

DbxIda supports InterBase starting with the version 5.x, Firebird 2.x, 1.x versions and Yaffil. The driver requires InterBase client to be installed on the client side.

You should also pay attention to the following notes:

1. It is strongly recommended to use the corresponding library with your IDE. Please take a look at this table for more details.
2. Update Pack 2 is required for Delphi 2006 .NET Personality.
3. Update Pack 3 is required for C++Builder 2007 and Delphi 2007 for Win32.

Windows installation notes

RAD Studio 2007 and higher

The standard TSQLConnection component in RAD Studio 2007 and higher provides exactly the same functionality like TCRSQLConnection does. Therefore TCRSQLConnection component is not registered automatically. But source code of the package containing this component is installed for providing backward compatibility. If you need to use TCRSQLConnection, you should compile and install its packages manually. They are located in the <Driver_Installation_Directory>\Source\DelphiXX\ directory. The dclcrdbxXXX.dproj package is used with Delphi for Win32 and C++Builder. And the Devart.Dbx.dproj package is used with Delphi for .Net.

If you have only C++Builder installed, you can compile the package from the command line using a command like the following one:

C:\Program Files\CodeGear\RAD Studio\5.0\bin\dcc32.exe" dclcrdbx105.dpk

To compile an application that uses TCRSQLConnection components, it is necessary to add the path to TCRSQLConnection sources to the Search path in Project Options or to the Library path in Environment Options:

• in Delphi for Win32 add <Driver_Installation_Directory>\Source;
• in Delphi for .Net add <Driver_Installation_Directory>\Source and <Driver_Installation_Directory>\Source\Net;
• in C++Builder add <Driver_Installation_Directory>\Source to the Library path and to the Include path in Project Options.

Delphi 2006

If you use TCRSQLConnection in applications developed with C++Builder Personality of Delphi 2006 or Turbo C++, you should add the path to the DbxIda folder to Project Options|Library and Project Options|Include.

Linux installation notes

Make libsqlida.so.X.X library available for applications by correcting LD_LIBRARY_PATH environment variable or by copying it to directory already contained in LD_LIBRARY_PATH.

To work with driver from IDE, you need to register driver in dbExpress configuration files.

1. Find dbxdrivers file (path is similar to /home/<user>/.borland)
2. Add or replace the following sections:

     [DevartInterBase]
     GetDriverFunc=getSQLDriverInterBase
     LibraryName=libsqlida.so.X.X
     VendorLib=libgds.so.0
     BlobSize=-1
     DataBase=
     User_Name=
     Password=
   
     

3. In section [Installed Drivers] add line

     DevartInterBase=1
     

4. Find the dbxconnections file (path is similar to /home/<user>/.borland)
5. Add or replace the following lines:

     [Devart InterBase]
     BlobSize=-1
     DataBase=
     DriverName=DevartInterBase
     User_Name=
     Password=
     

6. Restart Kylix IDE

Using

The use of DbxIda is the very same as one of standard dbExpress drivers supplied by Borland. For additional information refer to help on TSQLConnection component.

To setup SQLConnection for using DbxIda, just set at design-time ConnectionName property to "Devart InterBase" connection configurations.

Set connection parameters such as User_Name, Password and Database to provide login information. You need to set string to Database parameter in the following format:

Protocol	Host name	Database path	Database parameter
TCP	Server	DBPath	Server:DBPath
NetBEUI	Server	DBPath	\\Server\DBPath
SPX	Server	DBPath	Server@DBPath

Borland Turbo products and Delphi 2006 introduce dbExpress 3.0 interfaces, CodeGear RAD Studio 2007 and higher introduce dbExpress 4.0. They are implemented in dbexpoda30.dll and dbexpoda40.dll correspondingly, and we highly recommend using the correspondent driver.

Example

Connection with :

  SQLConnection.LibraryName := <library name>; // <library name> depends on your IDE version. See the table below.
  SQLConnection.VendorLib := 'gds32.dll';
  SQLConnection.GetDriverFunc := 'getSQLDriverInterBase';
  SQLConnection.Params.Clear;
  SQLConnection.Params.Add('User_Name=SYSDBA');
  SQLConnection.Params.Add('Password=masterkey');
  SQLConnection.Params.Add('Database=Server:c:\InterBase\test.gdb')
  SQLConnection.Open;

The following table determines how library name depends on the used IDE version:

Library name	IDE version
dbexpida40.dll	CodeGear RAD Studio 2007 and higher
dbexpida30.dll	Delphi 2006 and Turbo products
dbexpida.dll	other IDE versions

Type map

InterBase data types are mapped to TFieldType in the following way:

InterBase type	Field type	Field class
SMALLINT	ftSmallInt	TSmallIntField
INTEGER	ftInteger	TIntegerFiled
FLOAT	ftFloat	TFloatField
DOUBLE PRECISION	ftFloat	TFloatField
DATE	ftData	TDateField
TIME	ftTime	TTimeField
TIMESTAMP	ftTimeStamp	TSQLTimeStampField
CHAR	ftString	TStringField
VARCHAR	ftString	TStringField
BLOB(BINARY)	ftBlob	TBlobField
BLOB(TEXT)	ftMemo	TMemoField

NUMERIC, DECIMAL and BIGINT datatypes mapping depends on the values of OptimizedNumerics, EnableBCD options and IDE version. This datatypes mapping is described in the table below.

InterBase type	Value of OptimizedNumerics, EnableBCD
	True, True	True, False	False, True	False, False
NUMERIC, DECIMAL
Precision <= 9, Scale = 0	ftInteger (TIntegerFiled)	ftInteger (TIntegerFiled)	ftFMTBcd (TFMTBCDField)	ftFMTBcd (TFMTBCDField)
Precision <= 9, Scale > 0	ftFloat (TFloatField)	ftFloat (TFloatField)	ftFMTBcd (TFMTBCDField)	ftFMTBcd (TFMTBCDField)
Precision <= 18	ftFMTBcd (TFMTBCDField)	ftFloat (TFloatField)	ftFMTBcd (TFMTBCDField)	ftFMTBcd (TFMTBCDField)
BIGINT	ftFMTBcd (TFMTBCDField)	ftFloat (TFloatField)	ftFMTBcd (TFMTBCDField)	ftFMTBcd (TFMTBCDField)

Note: To make DbxIda type map correspond Borland dbExpress driver for InterBase you should set OptimizedNumerics option to False.To simplify setting driver options it's recommended to use an additional component - TCRSQLConnection.

Custom transaction isolation

You can use custom isolation level (xilCUSTOM) to start read-only transaction. There is special constant ciREADONLY. You should assign it to the CustomIsolation field of TTransactionDesc:

const
  ciREADONLY = 1;
var
  td: TTransactionDesc;
begin
  td.TransactionID := 1;
  td.IsolationLevel := xilCUSTOM;
  td.CustomIsolation := ciREADONLY;
  SQLConnection.StartTransaction(td);
end;

Extended driver options

DbxIda provides several extended options that expand functionality of the driver. In order to overcome restrictions of dbExpress on several IDEs, DbxIda provides the TCRSQLConnection component. It simplifies working with extended driver options.

The dbExpress technology version 4 in CodeGear RAD Studio 2007 and higher does not have such restrictions, so the TCRSQLConnection component has no sense for these IDEs.

Starting with CodeGear RAD Studio 2007 you should assign parameter values to TSQLConnection and TCRSQLConnection at run time in this way:

SQLConnection.Params.Values['Option Name'] := 'Option Value';

In other IDE versions it is better to use TCRSQLConnection instead of standard TSQLConnection. Extended options are available with TSQLConnection only at run time.

To setup these options at design time, you should use the Params property of TCRSQLConnection.

To use an option with TSQLConnection in Delphi 2006 and lower IDE versions, you should define constants and set appropriate option values using event handlers like:

TSQLConnection.AfterConnect
TSQLQuery.BeforeOpen

Below is the example that demonstrates setting extended driver options in Delphi 2006 and lower IDE versions:

const
  coFetchAll = TSQLConnectionOption(301);

procedure TForm1.SQLQuery1BeforeOpen(DataSet: TDataSet);
begin
  SQLConnection1.SQLConnection.SetOption(coFetchAll, Integer(True));
end;

If you compile the application for CLR, you should use

  ISQLConnection_SetOption(SQLConnection1, ...)

instead of

  SQLConnection1.SQLConnection.SetOption(...)

Here is the description of the options and how to use them.

BooleanDomainFields

If the BooleanDomainFields option is set to True, TBooleanField objects are created for fields that have domain of the integer data type, and the domain name contains 'BOOLEAN'. Default value is False.

const
  coBooleanDomainFields = TSQLConnectionOption(402); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coBooleanDomainFields, Integer(True));

CharLength

Specifies the size in bytes of a single character. Set this option with the number in range [0..6] to reflect InterBase support for the national languages. Setting CharLength to zero will instruct DbxIda to interrogate InterBase server for the actual character length. Default value is 1.

const
  coCharLength = TSQLConnectionOption(203); // integer
. . .
  with SQLConnection1.SQLConnection do begin
    SetOption(coCharLength, 3);
  end;

Charset

Sets character set that driver uses to read and write character data.

const
  coCharset = TSQLConnectionOption(204); //string
. . .
  SQLConnection1.SQLConnection.SetOption(coCharset, Integer(PChar('ASCII')));

DBMonitorHost

Set this option to the host name where dbMonitor runs to enable remote monitoring.

const
  coDBMonitorHost = TSQLConnectionOption(106); //string
. . .
  SQLConnection1.SQLConnection.SetOption(coDBMonitorHost, Integer(PChar('MyHost')));

DBMonitorPort

Set this option to the port number of dbMonitor.

const
  coDBMonitorHost = TSQLConnectionOption(107); //integer
. . .
  SQLConnection1.SQLConnection.SetOption(coDBMonitorPort, 1001);

EnableBCD

This boolean option with the True value allows driver to map NUMERIC, DECIMAL, BIGINT datatypes on ftFMTBcd field type. If the OptimizedNumerics option is set to False, EnableBCD takes no effect.

const
  coEnableBCD = TSQLConnectionOption(102); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coEnableBCD, Integer(False));

DevartInterBase TransIsolation

This options is used to set TransIsolation level for the default transaction.

Note: Use "DevartInterBase TransIsolation" option name in Delphi 2006.

  SQLConnection1.Params.Values['DevartInterBase TransIsolation'] := 'ReadCommited';
  SQLConnection1.Connected := True;

FetchAll

Set FetchAll to True to request all records of the query from database server when the dataset is opening. When set to False, records are retrieved when a data-aware component or a program requests it. If a query can return a lot of records, set this property to False if initial response time is important.

const
  coFetchAll = TSQLConnectionOption(301); //boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coFetchAll, Integer(True));

LongStrings

When this boolean option is set to True, this allows dataset to represent long strings (more than 255 symbols) as memos. Default value of this option is True.

const
  coLongStrings = TSQLConnectionOption(101); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coLongStrings, Integer(False));

OptimizedNumerics

When this boolean option is True, the driver performs "smart" mapping of NUMERIC, DECIMAL, BIGINT datatypes on TFieldType according to the precision and scale parameters. This can cause performance and memory consumption gains when working with these InterBase datatypes. To make DbxIda datatypes mappping correspond to stadard driver for InteBase set this option to False.

Default value is True.

const
  coOptimizedNumerics = TSQLConnectionOption(401); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coOptimizedNumerics, Integer(False));

Prepared

This boolean option allows all dataset to be prepared. If you execute a query or a stored procedure more than once, you can improve performance by setting Prepared to True before opening the dataset. Setting Prepared to True prepares the dataset at once, rather than waiting until the dataset is opened.

const
  coPrepared = TSQLConnectionOption(302); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coPrepared, Integer(True));

RoleName

Use RoleName to specify InterBase connection role.

To use this option with TCRSQLConnection you may set it in Params property of TCRSQLConnection. You must set this option before establishing connection. Sample code looks like this.

  SQLConnection1.Params.Values['RoleName'] := 'USER';
  SQLConnection1.Connected := True;

TrimFixedChar

Specifies whether to discard all trailing spaces in string fields of the dataset.

const
  coTrimFixedChar = TSQLConnectionOption(103); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coTrimFixedChar, Integer(True));

UseQuoteChar

This boolean option when turned on makes the driver quote all names of objects. Setting it to True makes sense only for using reserved words as object names. Default value is False;

const
  coUseQuoteChar = TSQLConnectionOption(202); // boolean
. . .
  with SQLConnection1.SQLConnection do begin
    SetOption(coUseQuoteChar, Integer(True));
  end;

UseUnicode

Enables or disables Unicode support. Affects character data fetched from the server. When set to True all character data is stored as WideStrings and TStringField is replaced with TWideStringFiled.

This option is available for Delphi 2006 and higher IDE versions.

Default value of this option is True for Delphi 2009 and higher IDE versions, and False for Delphi 2006 and 2007.

const
  coUseUnicode = TSQLConnectionOption(209); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coUseUnicode, Integer(True));

WaitOnLocks

Use this option to set WaitOnLocks paramater for default transaction.

  SQLConnection1.Params.Values['WaitOnLocks'] := 'True';
  SQLConnection1.Connected := True;

TCRSQLConnection

TCRSQLConnection component serves to support additional options of dbExpress drivers for InterBase (DbxIda), Oracle (DbxOda), SQL Server (DbxSda) and MySQL (DbxMda). Option configuration can be made in dbExpress configuration files (dbxconnections.ini) or in Params list of TCRSQLConnection.

You can use following options:

• EnableBCD
• CharLength
• FetchAll
• LongStrings
• OptimizedNumerics
• Prepared
• RoleName
• ServerCharset
• DevartInterBase TransIsolation
• TrimFixedChar
• UseQuoteChar
• UseUnicode
• WaitOnLocks

For example

var
  SQLConnection1: TCRSQLConnection;
...
  SQLConnection1.Params.Values['OptimizedNumerics'] := 'False';
  SQLConnection1.Connected := True;

Note: for quick replacement of TSQLConnection to TCRSQLConnection at design-time it's enough to use context menu of the component.

Using DBMonitor

To extend monitoring capabilities of dbExpress applications with DbxIda driver, you can use an additional freeware tool called DBMonitor. It is provided as an addition to a monitoring procedure with using SQLMonitor component.

DBMonitor is easy-to-use tool to provide visual monitoring of your database applications.

DBMonitor has the following features:

• Multiple client processes tracing;
• SQL event filtering (by sender objects);
• SQL parameter and error tracing.

DBMonitor is intended to hamper application being monitored as less as possible.

To trace your application with DBMonitor you should follow these steps:

• drop TSQLMonitor component onto the form;
• start DBMonitor before running your program.

Note: if you are user of Delphi Pro version, you do not have the TSQLMonitor component installed on the component palette. It is included in the SQLExpr.pas unit and you need to install it on the component palette manually.

Deployment

The following additional files are required on the target computer:

File	When required	Where to find
dbexpida.dll (dbexpida30.dll for Delphi 2006 or Turbo products users, dbexpida40.dll for CodeGear RAD Studio 2007 and higher users)	Required for any application	DbxIda installation directory
dbxintf.dll	Required for any CLR application	IDE installation directory
Devart.DbxIda.DriverLoader.dll (should be placed in GAC)	Required for CLR applications built with CodeGear RAD Studio 2007	DbxIda installation directory

Linux applications require the libsqlida.so.X.X library.

Users of dbExpress driver for Interbase/Firebird with Source Code can embed the driver into the application directly. For information on how to do this refer to Borland documentation.

Trial limitations

Windows DbxIda trial version can be used within 30 days starting from the moment of installation.

Linux Trial version of the driver is limited by number of the returned table columns. DbxIda trial version returns cursors with maximum 6 columns. Extra select-list columns are truncated.

Notes

Database object names that contain spaces or national language characters should be always quoted. For example,

  SQLStoredProc.StoredProcName := '"My stored proc"';

Known Problems

• When executing stored procedure using TSQLStoredProc component or TSQLDataSet with CommandType property set to ctStoredProc under Delphi 2006 CLR, dbExpress passes invalid length of BLOB parameters to the driver. That is why values of the BLOB parameters are truncated. To avoid this problem execute your stored procedure as a query (set ctQuery for CommandType property of TSQLDataset or use TSQLQuery).

Demo projects

The following demo projects accompany DbxIda and provide a good bootstrap to start working with it. They are located inside DbxIda\Demos folder.

Sample	Description
BlobPictures	This sample project demonstrates how to work with binary BLOB type to store images and display them in TDBImage
BlobText	This sample project demonstrates how to work with text BLOB to store text information and display in TDBText
ClientDataSet	This sample project demonstrates working with TSQLClientDataSet in Delphi 6
Query	This sample project allows getting result record set using TSQLQuery component
SimpleDataSet	This sample project demonstrates working with TSimpleDataSet in Delphi 7
StoredProc	This sample project demonstrates working with input/output parameters and executing of stored procedures

 

 

---

© 2001-2009 Devart. All rights reserved.