dbExpress driver for PostgreSQL

dbExpress is database-independent layer that defines common interface to provide fast access to SQL database servers. For each supported server dbExpress provides a driver as an independent library that implements 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.

dbExpress for PostgreSQL driver provides access to PostgreSQL database server and is based on Borland dbExpress data access technology.

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

Key features

The following list describes the main features of the dbExpress for PostgreSQL driver:

Full support of the latest versions of PostgreSQL database server
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 Subscription with Priority Support
Licensed royalty-free per developer, per team, or per site

Compatibility

The driver 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 7, Delphi 6, and C++Builder 6.

It supports PostgreSQL server versions since 7.1 up to 8.3 and does not require PostgreSQL client to be installed on the client side.

You should also pay attention to the following notes:

It is strongly recommended to use the corresponding library with your IDE. Please take a look at this table for more details.
Update Pack 2 is required for Delphi 2006 .NET Personality.
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 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 driver folder to Project Options|Library and Project Options|Include.

Using

The use of dbExpress for PostgreSQL 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 the Devart driver, just set at design-time ConnectionName property to "Devart PostgreSQL" connection configurations.

Set connection parameters such as User_Name, Password, HostName and Database to provide login information.

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.

Example

Connection with :

  SQLConnection.LibraryName := <library name>; // <library name> depends on your IDE version. See the table below.
  SQLConnection.VendorLib := 'not used';
  SQLConnection.GetDriverFunc := 'getSQLDriverPostgreSQL';
  SQLConnection.Params.Clear;
  SQLConnection.Params.Add('User_Name=MyName');
  SQLConnection.Params.Add('Password=MyPassword');
  SQLConnection.Params.Add('HostName=MyServer');
  SQLConnection.Params.Add('Database=MyDatabase');
  SQLConnection.Open;

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

Library name	IDE version
dbexppgsql40.dll	CodeGear RAD Studio 2007, 2009
dbexppgsql30.dll	Delphi 2006 and Turbo products
dbexppgsql.dll	other IDE versions

Type map

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

PostgreSQL type	Field type	Field class
SMALLINT	ftSmallint	TSmallintField
INTEGER	ftInteger	TIntegerField
BIGINT	ftFMTBcd	TFMTBCDField
NUMERIC	ftFMTBcd	TFMTBCDField
REAL	ftFloat	TFloatField
DOUBLE PRECISION	ftFloat	TFloatField
MONEY	ftCurrency	TCurrencyField
BOOLEAN	ftBoolean	TBooleanField
OID	ftInteger	TIntegerField
CHAR	ftString	TStringField
VARCHAR	ftString	TStringField
NAME	ftString	TStringField
TEXT	ftMemo	TMemoField
BYTEA	ftBlob	TBlobField
DATE	ftDate	TDateField
TIME	ftTime	TTimeField
TIMESTAMP	ftTimeStamp	TSQLTimeStampField

When EnableBCD option is False dataset maps BIGINT and NUMERIC fields as following:

PostgreSQL type	Field type	Field class
BIGINT	ftFloat	TFloatField
NUMERIC	ftFloat	TFloatField

Extended driver options

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

EnableBCD

This boolean option with the True value allows driver to map NUMERIC, DECIMAL, BIGINT datatypes on ftFMTBcd field type.

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

ExtendedFieldsInfo

If True, the driver performs additional queries to the database when opening a dataset. These queries return information about which fields of the dataset are required or autoincrement. Set this option to True, if you need the Required property of fields be set automatically.

const
  coExtendedFieldsInfo = TSQLConnectionOption(502); //boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coExtendedFieldsInfo, Integer(True));

FetchAll

When FetchAll is set to True, all records of the query are fetched from database 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.

Note: You should start a transaction before opening a query when FetchAll=False.

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

OIDAsLargeObject

If True, fields with OID data type are treated as fields with large objects IDs. The driver will map such field on TBlobField, and read data from the corresponding large object automatically. If OIDAsLargeObject is set to False (default), OID data type is mapped on TIntegerField.

const
  coOIDAsLargeObject = TSQLConnectionOption(501); //boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coOIDAsLargeObject, Integer(True));

Note 1: you should start a transaction before opening a query with large objects.

Note 2: when you update TBlobField that corresponds a large object, the driver creates new large object and writes its OID to the field. An old large object that was referenced by this field becomes an orphan large object (an object that exist but is not referenced by any rows, wasting disk space). So do not update such fields, or create a trigger that unlinks old large object on update.

For example:

CREATE TABLE lo_test(id integer, image oid);

CREATE OR REPLACE FUNCTION test_lo_clean() RETURNS trigger AS $$
declare
  lo_oid oid;
begin
  -- If it is an update action but the BLOB (lo) field was not changed, dont do anything
  if (TG_OP = ''UPDATE'') then
    if (old.image = new.image) or (old.image is null) then
      return new;
    end if;
  end if;
  select into lo_oid loid from pg_largeobject where lo_oid = oid(old.image);
  if found then
    perform lo_unlink(lo_oid);
  end if;
  return new;
end$$
LANGUAGE 'plpgsql';

CREATE TRIGGER lo_cleanup
  AFTER DELETE OR UPDATE ON lo_test
  FOR EACH ROW EXECUTE PROCEDURE test_lo_clean();

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

Schema

Set the Schema option to change the search path of the connection to the specified schema. The TSQLTable and TSQLStoredProc components will search a table or stored procedure correspondingly in the specified schema if its Schema property is empty.

If you do not set the Schema option, the driver considers that the default schema is 'public'.

const
  coSchema = TSQLConnectionOption(28); //string
. . .
  SQLConnection1.SQLConnection.SetOption(coSchema, Integer(PChar('test')));

SSLMode

This option determines whether or with what priority an SSL connection will be negotiated with the server. There are four modes:

Value	Meaning
DISABLE	Only an unencrypted SSL connection will be attempted.
ALLOW	Negotiates trying first a non-SSL connection, then if that fails, tries an SSL connection.
PREFER	Negotiates trying first an SSL connection, then if that fails, tries a regular non-SSL connection.
REQUIRE	Tries only an SSL connection.

If PostgreSQL is compiled without SSL support, using option REQUIRE will cause an error, while options ALLOW and PREFER will be accepted but PgDAC will not in fact attempt an SSL connection.

It is available for Delphi 2007 and higher IDE versions.

SQLConnection1.Params.Values['SSLMode'] := 'REQUIRE';

SSLCACert

CACert is the pathname to the certificate authority file. This option is available for Delphi 2007 and higher IDE versions.

SQLConnection1.Params.Values['SSLCACert'] := 'd:\Documents and Settings\User\Application Data\postgresql\root.crt';

SSLCert

Cert is the pathname to the certificate file. This option is available for Delphi 2007 and higher IDE versions.

SQLConnection1.Params.Values['SSLCert'] := 'd:\Documents and Settings\User\Application Data\postgresql\postgresql.crt';

SSLKey

Key is the pathname to the key file. This option is available for Delphi 2007 and higher IDE versions.

SQLConnection1.Params.Values['SSLKey'] := 'd:\Documents and Settings\User\Application Data\postgresql\postgresql.key';

SSLCipherList

ChipherList is the list of allowed ciphers to use for SSL encryption. This option is available for Delphi 2007 and higher IDE versions.

SQLConnection1.Params.Values['SSLCipherList'] := 'DES';

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

UnknownAsString

If False, fields of unknown data types (including geometric, composite and array types) are mapped to TMemoField or TWideMemoField depending on the value of the UseUnicode option. Memo is used because maximum length of values from such fields is unknown.

If True, fields of unknown data types are mapped to TStringField or TWideStringField depending on the value of the UseUnicode option. Size of fields is set to 8192. Values larger than this size are truncated.

The default value of this option is False.

const
  coUnknownAsString = TSQLConnectionOption(503); //boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coUnknownAsString, 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));

TCRSQLConnection

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

For example

var
  SQLConnection1: TCRSQLConnection;
...
  SQLConnection1.Params.Values['UseUnicode'] := 'True';
  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 the dbExpress for PostgreSQL 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

dbexppgsql.dll (dbexppgsql30.dll for Delphi 2006 or Turbo products users, dbexppgsql40.dll for CodeGear RAD Studio 2007, 2009 users) Required for any application dbExpress for PostgreSQL installation directory

dbxintf.dll Required for any CLR application IDE installation directory

Devart.DbxPgSql.DriverLoader.dll (should be placed in GAC) Required for CLR applications built with CodeGear RAD Studio 2007 dbExpress for PostgreSQL installation directory

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

Users of dbExpress driver for PostgreSQL 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 dbExpress for PostgreSQL trial version can be used within 30 days starting from the moment of installation.

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 Devart dbExpress driver and provide a good bootstrap to start working with it. They are located inside Dbx\PostgreSQL\Demos folder.

Sample	Description
BlobPictures	This sample project demonstrates how to work with binary BLOB type to store images and display them in TDBImage
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

dbExpress driver for PostgreSQL	History
Readme	Support