dbExpress driver for Oracle 4.20	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.

DbxOda driver provides direct access to Oracle database server based on Borland dbExpress data access technology. It can work using Oracle Call Interface (OCI) or SQL*Net directly.

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

Key features

Following is list of DbxOda driver's main features

• Direct SQL*Net protocol support. This feature allows driver to work in Direct mode without Oracle client software
• High performance
• Supports Oracle 11g, 10g, 9i, 8i and Oracle 7
• Ability of monitoring query execution with DBMonitor
• Extended options for advanced behaviour
• Complete support for dbExpress version 4
• Source code available
• Free support for registered users
• Licensed per a developer without royalty fee

Compatibility

DbxOda works with Delphi 2009 for Win32, C++Builder 2009, CodeGear RAD Studio 2007, C++Builder 2007, Delphi 2007 for Win32, 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.

DbxOda supports Oracle servers 11g, 10g, 9i, 8i, 8.0, and 7.3, including Oracle 10g Express and Oracle 8i Personal and Lite editions. DbxOda supports x86 versions of the following Oracle clients: 11g, 10g, 9i, 8i, 8.0, and 7.3. Driver requires Oracle software installed on client side to access through OCI. In case of working through SQL*Net TCP/IP protocol required only.

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 2009

The standard TSQLConnection component in RAD Studio 2007 and 2009 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 one of the following commands:

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

C:\Program Files\CodeGear\RAD Studio\6.0\bin\dcc32.exe" dclcrdbx120.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 libsqloda.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 following sections:

     [DevartOracle]
     GetDriverFunc=getSQLDriverORA
     LibraryName=libsqloda.so.X.X
     VendorLib=libclntsh.so
     BlobSize=-1
     DataBase=
     User_Name=
     Password=
   
     [DevartOracleDirect]
     GetDriverFunc=getSQLDriverORADirect
     LibraryName=libsqloda.so.X.X
     VendorLib=libsqloda.so.X.X
     BlobSize=-1
     DataBase=
     User_Name=
     Password=
     

3. In section [Installed Drivers] add line

     DevartOracle=1
     DevartOracleDirect=1
     

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

     [Devart Oracle]
     BlobSize=-1
     DataBase=
     DriverName=DevartOracle
     User_Name=
     Password=
   
     [Devart Oracle Direct]
     BlobSize=-1
     DataBase=
     DriverName=DevartOracleDirect
     User_Name=
     Password=
     

6. Restart Kylix IDE

Note that in Delphi 2006, Turbo Delphi, Turbo Delphi for .NET and Turbo C++ parentheses are not allowed in driver name. Refer to table below for right names.

Using

The use of DbxOda 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 DbxOda just set at design-time ConnectionName property to "Devart Oracle" connection configurations:

Set connection parameters such as User_Name, Password and Database to provide login information. You need to set name of your TNS alias to Database parameter for standard connection. For Direct connection you need assign string, that specifies database, in such format host:port:sid. Where host is the address of server, port is the number of port that server listens and sid is system identifier that specifies instance name.

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

Examples

Connection with OCI:

  SQLConnection.LibraryName := <library name>; // <library name> depends on your IDE version. See the See the table below.
  SQLConnection.VendorLib := 'oci.dll';
  SQLConnection.GetDriverFunc := 'getSQLDriverORA';
  SQLConnection.Params.Clear;
  SQLConnection.Params.Add('User_Name=scott');
  SQLConnection.Params.Add('Password=tiger');
  SQLConnection.Params.Add('Database=ora')
  SQLConnection.Open;

If User_Name parameter of the SQLConnection is not set (User_Name='') OS Authentication connection mode is used.

For Direct connection:

  SQLConnection.LibraryName := <library name>; // <library name> depends on your IDE version. See the table below.
  SQLConnection.VendorLib := <library name>; // <library name> depends on your IDE version. See the table below.
  SQLConnection.GetDriverFunc := 'getSQLDriverORADirect';
  SQLConnection.Params.Clear;
  SQLConnection.Params.Add('User_Name=scott');
  SQLConnection.Params.Add('Password=tiger');
  SQLConnection.Params.Add('Database=host:1521:ora')
  SQLConnection.Open;

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

Library name	IDE version
dbexpoda40.dll	CodeGear RAD Studio 2007, 2009
dbexpoda30.dll	Delphi 2006 and Turbo products
dbexpoda.dll	other IDE versions

Type map

Oracle data types map to TFieldType in the following way (by default, if EnableBCD property is True):

Oracle type	Field type	Field class
NUMBER(<15,<5)	ftBCD	TBCDField
NUMBER(>14,>4)	ftFMTBCD	TFMTBCDField
FLOAT	ftFMTBCD	TFMTBCDField
VARCHAR2	ftString	TStringField
VARCHAR	ftString	TStringField
RAW	ftVarBytes	TVarBytesField
CHAR	ftString	TStringField
DATE	ftTimeStamp	TSQLTimeStampField
CLOB	ftOraClob	TMemoField
BLOB	ftOraBlob	TBlobField
LONG	ftMemo	TMemoField
LONG RAW	ftBlob	TBlobField

When EnableBCD option is False dataset maps number fields as following:

Oracle type	Field type	Field class
NUMBER(<10)	ftInteger	TIntegerField
NUMBER(>=10), NUMBER	ftFloat	TFloatField

Note: Type map, beginning from version 1.80 to correspond Borland dbExpress driver for Oracle was changed for number types to ftBCD, ftFMTBCD. To recover previous behaviour you should set EnableBCD option to False. To simplify setting driver options it's recommended to use an additional component - TCRSQLConnection.

Extended driver options

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

The dbExpress technology version 4 in CodeGear RAD Studio 2007 and 2009 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.

BCDPrecision

This string option allows dataset to represent fields as TBCDField if NUMBER field precision and scale less or equal than precision and scale specified in BCDPrecision. String value is interpreted like two coma separated digits (BCD precision and scale). The default value of this option is '0,0' (do not use TBCDField). The value of BCDPrecision cannot be greater then '14,4'.

const
  coBCDPrecision = TSQLConnectionOption(208); // string
. . .
  SQLConnection1.SQLConnection.SetOption(coBCDPrecision, Integer(PChar('14,4')));

CharLength

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

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

ServerCharset

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

. . .
  SQLConnection1.SQLConnection.SetOption(eConnServerCharSet, Integer(PChar('WE8ISO8859P1')));

Charset

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

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

Charset property cannot be used for "Devart Oracle Direct" connection. Please use ServerCharSet property instead.

SQLConnection.Params.Add('ServerCharSet=WE8ISO8859P1');

EnableBCD

This boolean option with True value allows dataset to represent number fields as TBCDField and TFMTBCDField. It sets the following precision values values SmallIntPrecision = 0
IntegerPrecision = 0
FloatPrecision = 0
BCDPrecision = '14,1'
(TFMTBCDField's are all NUMBER's greater then NUMBER(14,4) )

The default value of this option is True (beginning from version 1.80). If you assign EnableBCD to False it establishes the following values

SmallIntPrecision = 0
IntegerPrecision = 9
FloatPrecision = 38 (maximum possible precision)
BCDPrecision = '0'

To use this option with TCRSQLConnection you may set it in Params property of TCRSQLConnection. In this case EnableBCD will be applied first before SmallIntPrecision, IntegerPrecision, FloatPrecision, BCDPrecision.

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

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));

FloatPrecision

This integer option allows dataset to represent fields as TFloatField if NUMBER field precision less or equal than FloatPrecision. The default value of this option is 0 (do not use TFloatField).

To use this option with TSQLConnection you should define following constant.

const
  coFloatPrecision = TSQLConnectionOption(207); //integer
. . .
  SQLConnection1.SQLConnection.SetOption(coFloatPrecision, Integer(15));

IntegerPrecision

This integer option allows dataset to represent fields as TIntegerField if NUMBER field precision less or equal than IntegerPrecision. The default value of this option is 9.

const
  coIntegerPrecision = TSQLConnectionOption(205); //integer
. . .
  SQLConnection1.SQLConnection.SetOption(coIntegerPrecision, Integer(10));

InternalName

This options sets internal client database name. Setting this option can improve performace of Execute call, but it has unwanted effect with commit or rollback inside PL/SQL block. Also To use this option you must set property LoginPrompt = true in TSQLConnection object. Default value of this option is ''.

const
  coInternalName = TSQLConnectionOption(201); // string
. . .
  with SQLConnection1.SQLConnection do begin
    SetOption(coInternalName, Integer(PChar('DBXORA')));
  end;

LongStrings

This boolean option when turned on allows dataset to represent long strings (more than 255 symbols) as memo. Default value of this option is True.

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

Prepared

This boolean option allows all dataset to be prepared. If you execute the query or 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);
. . .
  SQLConnection1.SQLConnection.SetOption(coPrepared, Integer(True));

Reconnect

This boolean option controls if automatic reconnect should be performed if the connection is broken. The default value of this option is True. Set the Reconnect option to False to disable reconnect.

const
  coReconnect            = TSQLConnectionOption(105);
. . .
  SQLConnection1.SQLConnection.SetOption(coReconnect, Integer(False));

RoleName

Use RoleName to specify which system priviledges to use when the user connects to the server.

Following values are supported for this property:

NORMAL (default) Connect as an ordinary user.
SYSOPER Connect with SYSOPER role.
SYSDBA Connect with SYSDBA 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'] := 'SYSDBA';
  SQLConnection1.Connected := True;

SmallIntPrecision

This integer option allows dataset to represent fields as TSmallIntField if NUMBER field precision less or equal than SmallIntPrecision. The default value of this option is 0 (do not use TSmallIntField).

const
  coSmallIntPrecision = TSQLConnectionOption(206); //integer
. . .
  SQLConnection1.SQLConnection.SetOption(coSmallIntPrecision, Integer(4));

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 allows use QuoteChar = '"' by SQL express. Default value of this option is False.

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

UnicodeEnvironment

Enables or disables using OCI Unicode Environment. When this option is enabled, Unicode characters can be used in SQL statements. Disable this option if you have encountered some problems with Unicode Environment.

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

Default value of this option is True.

  SQLConnection1.Params.Values['UnicodeEnvironment'] := 'True';

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));

TCRSQLConnection

TCRSQLConnection component serves to support additional options of dbExpress drivers for 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 the following options:

• BCDPrecision
• CharLength
• Charset
• EnableBCD
• FetchAll
• FloatPrecision
• IntegerPrecision
• InternalName
• LongStrings
• SmallIntPrecision
• TrimFixedChar
• UseQuoteChar
• RoleName

For example

var
  SQLConnection1: TCRSQLConnection;
...
  SQLConnection1.Params.Values['EnableBCD'] := '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 DbxOda 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 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 Delphi Pro version user, then you don't have TSQLMonitor component installed on the palette, but it is included in SQLExpr.pas unit and you need to install it on the components palette manually.

Deployment

The following additional files are required on the target computer:

File	When required	Where to find
dbexpoda.dll (dbexpoda30.dll for Delphi 2006 or Turbo products users, dbexpoda40.dll for CodeGear RAD Studio 2007, 2009 users)	Required for any application	DbxOda installation directory
dbxintf.dll	Required for any CLR application	IDE installation directory
Devart.DbxOda.DriverLoader.dll (should be placed in GAC)	Required for CLR applications built with CodeGear RAD Studio 2007	DbxOda installation directory

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

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

Direct mode limitations

• Connect using TCP/IP protocol only.
• Oracle 8 objects, arrays, refs are not supported.
• TOraLoader direct load is not supported.
• Please note that we don't guarantee Direct compatibility with all Oracle servers and in every network. We have tested Direct option for all versions of Oracle servers for Windows and Linux since 7.3 in local network. Other platforms may cause some incompatibility issues.
• BreakExec procedure does not work.
• Certain problems may occur when using firewalls.
• Direct does not support NLS conversion on client side.
• OS authentication feature is not available.
• We don't guarantee stable working in multithread applications.

Trial limitations

Windows DbxOda 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. DbxOda 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 performing describe of stored procedure dbExpress components interpret Oracle BLOB and CLOB types as ftBlob. That is not correct. Driver in its turn interprets ftBlob as LONG RAW type. So to avoid error on execution appropriate parameter data type should be manually set to ftOraClob of ftOraBlob.

• DbxOda supports fields of OBJECT and ARRAY datatypes in tables. You must set TSQLQuery.ObjectView property to True. But TClientDataSet.ObjectView must have any value. Due to the peculiarity of Borland dbExpress we do not support OBJECT types with nested OBJECTs or ARRAYs. If you need to use SDO_GEOMETRY or other compound type you must do the following changes.

  Find procedure in TCustomSQLDataSet.AddFieldDesc in SqlExpr.pas. Find "LoadFieldDef(Word(FieldNo + I), FieldDescs[1]);" lines near the ftArray and ftADT case branches. Replace by the following "LoadFieldDef(FieldID, FieldDescs[1]);"

  Find procedure TSQLResolver.GenUpdateSQL in Provider.pas Find "SQL.Add(Format('%s = %s(',[Field.FullName, TObjectField(Field).ObjectType]));" line Replace by the following "SQL.Add(Format('%s.%s = %s(',[Alias, Field.FullName, TObjectField(Field).ObjectType]));"

• 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's why values of BLOB parameters are truncated. To avoid this problem execute your stored procedure in BEGIN END; PL/SQL block as query (set ctQuery for CommandType property of TSQLDataset or use TSQLQuery).

Demo projects

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

Sample	Description
BlobPictures	This sample project demonstrates how to work with Oracle8 BLOB type to store binary images and display in TDBImage
ClientDataSet	This sample project demonstrates working with TSQLClientDataSet in Delphi 6
Clob	This sample project demonstrates working with CLOB fields using TClientDataSet
Objects	This sample project demonstrates working with OBJECT, ARRAY fields using TClientDataSet
Query	This sample project allows to get result record set using TSQLQuery component
SimpleDataSet	This sample project demonstrates working with TSimpleDataSet in Delphi 7
StoredProc	This sample project demonstrates execution of stored procedure for data insertion and viewing entered data

Note: Before running sample projects make sure that your database has objects of schema Scott.

 

 

---

© 2001-2009 Devart. All rights reserved.