pgExpress Driver v1.50
A PostgreSQL dbExpress Driver
 

 

The pgExpress Driver and all it's components are Copyright © 2001-2002 Vita Voom Software.

This is the "readme" file for the pgExpress Driver.

Table of Contents:

  1. Introduction
  2. History
  3. Features and Capabilities
  4. Installation
  5. Special params
  6. Known issues
  7. Types mappping
  8. Multibyte Support
  9. Stored Procedures Support
  10. License
  11. Orders
  12. Notes
  13. Credits
  14. About the pgExpress Suite
  15. Contact, Support, Bugs

Introduction

The pgExpress Driver is a dbExpress Driver for Borland Delphi 6+/Kylix which can access PostgreSQL 7.1+ databases, use almost all its field types, and is compliant to the Borland specifications of dbExpress drivers. It might access earlier PostgreSQL Server versions, but this was not tested. Since version 0.95, pgExpress includes support for stored procedures (emulated through PostgreSQL Functions).
The pgExpress Driver is the core of the pgExpress Suite of components, which provides the widest range of support to acessing PostgreSQL databases.
This driver is the basis for a future pgExpress Suite of components, which is in late development, and should be released in the next months.
You can see the ChangeLog for dbExpress Driver.


History

The Vita Voom pgExpress Driver was born out of the lack of good PostgreSQL support for the Delphi community. I, Steve Howe, have also worked on the Zeos Library project for a couple months, but I have quit from it since I couldn't agree with many of the development directives.
The pgExpress Driver was developed months ago Borland released it's PostgreSQL Driver for Kylix, at the time it was written, it was the first driver in the world written using Borland Delphi.


Features and Capabilities

The pgExpress PostgreSQL dbExPress Driver version can access PostgreSQL 7.1+ databases, use almost all field types, and is complient to the Borland specifications of dbExpress drivers. It might access earlier versions of the server, but this was not tested. Since version 0.95, pgExpress includes support for stored procedures (emulated through PostgreSQL Functions).
The pgExpress Driver is the core of the pgExpress Suite of components, which provides the widest range of support to acessing PostgreSQL databases.
Asynchronous connections can't be supported by current Borland's dbExpress implementation; arrays are mapped as strings (see Known Issues) and ADT (custom fields) support will be fully suported only by the full pgExpress Suite. It has support for all the most commonly used PostgreSQL field types (notably: int2, in4, int8, serial, char, varchar, text, date, datetime, time, timestamp, timestamp with time zone, BLOB (large objects; read only), float, real, numeric and others).

These are some of the features ofthe driver:


Installation

For registration and deployment details, please refer to the Registration file (registered users only).

The pgExpress PostgreSQL dbExpress Driver installation procedure is much like any other dbExpress driver's installation.
If you want to try the driver for Delphi, the files are under the pgExpress installation's 'Delphi' directory; for the Kylix files, the directory is 'Kylix'.
If you're using the installer version (*.exe), it can perform the operations below for you, except configuring your connections parameters (what you can do manually or just double-clickcing a TSQLConnection). You can edit the default [PGEConnection] entry it creates.
The steps for manual installation are:

  1. Copy the driver 'dbexppge.dll' ('libdbexppge.so' on Kylix) to a folder in your path. If you prefer using the packages version of the driver, which is smaller, use the 'dbexppge_pkg.dll' file (libdbexppge_pkg.so on Kylix), and rename it back to 'dbexppge.dll' ('libdbexppge.so' on Kylix). The usual folder is $Delphi\bin on Windows (where $Delphi is the Delphi 6's installed folder), or $kylix/bin under Kylix/Kylix2, but as long as it's on your path, you can choose any other folder.

  2. Copy the 'libpq.dll' ('libpq.so' on Kylix) file to a dir on your PATH. This library in our is custom version of the PostgreSQL access library and is needed in order to connect to the database server. The pgExpress Driver won't work properly without this library, under Windows, because some functions needed by the pgExpress aren't exported by default (again - under Windows only). If you're under Kylix, the standard libpq.so from the PostgreSQL original installation should work.

    Note for libpq incompatibilities:
    there could be some incompatilbility problems between the libpq.dll/libpq.so library provided in the distribution and other clients such as psql - specialy under Kylix. To solve this, you can either:
    1) If under Kylix - use the standard 'libpq.so' file from the original PostgreSQL distribution, as it is, on your system;
    2) Rename the 'libpq.so'/'libpq.dll' file from the pgExpress Driver distribution to something else, and update the entry on the 'dbxdrivers'/'dbxdrivers.ini' file (which is by default 'VendorLib=libpq.so' under Kylix or 'VendorLib=libpq.dll' under Windows). If you rename the library to 'libpq_pge.so', for instance, your entry should become 'VendorLib=libpq_pge.so' accordingly. More instructions on the 'dbxdrivers'/'dbxdrivers.ini' file are below.

  3. Setup the entries on the dbxdrivers.ini and dbxconnections.ini dbExpress configuration files. These files are usually at the "$ProgramFiles\Common Files\Borland Shared\DBExpress" folder. The changes to be applied are the following:


  4. The following are valid Database parameters:

  5. That's it. The driver should be working now.

Please note that inside the distribution there are example dbxdrivers(.ini) and dbxconnections(.ini) for both Delphi and Kylix.


Special Params

The pgExpress driver has a few special parameters that can be used to customize its behaviors.
These values are (red values mean default values):

Key
Values
Meaning
TextAsBlob True/False If True, the PostgreSQL 'Text' field will be mapped as Blob (Memo); if False (deault), it will be mapped as string.
Obs: If mapped as string, it will be padded at the maximum string length (~32Kb).
MapUnknownAsString True/False If True, unknown/unsupported field types (ex: 'point' type) will be mapped as strings.
ArrayAsString True/False If True, arrays will be mapped as strings. If false, they will be ignored (hidden).
Int8Mode AsBcd/AsString/AsInt4/Ignore AsBcd: Int8 fields will be mapped as Bcd fields. This is needed, again, because there is no Int8 support in dbExpress.
AsString: the field will be mapped as string.
AsInt4: the field will be mapped as Int4 (value calculated as Int8 mod High(Int4)).
Ignore: the field will be ignored (hidden)
QuoteObjects True/False If True, quote objects names. To this setting to take effect, the connection must be reopened. An example of unquoted and quoted query:

select typname from pg_type; -- unquoted version
select "typname" from "pg_type"; -- quoted version

Please note that quoted objects names are case sensitive for PostgreSQL. The query above written like this will fail:

db =# select "typname" from "Pg_type"; -- quoted version
ERROR: Relation "Pg_type" does not exist
TypesCachingMode Default/Once/Connection/ Cursor This setting will control how often the types from PostgreSQL will be loaded and mapped to VCL types. Of course the less times they are loaded, the faster the application will be. The default setting should be ok for most applications (unless you do types manipulation).
Default - Currently, the same as "Driver"
Once - will be loaded only once for the driver.
Connection - types will be remapped for each connection open.
Cursor - types will be remapped for each new Dataset open.

Obs: the PostgreSQL database has a flexible types support; that means we must retrieve the types information for the pg_type catalog to map them to actual dbExpress/VCL types.
GetExtendedFieldsInformation True/False If True, will retrieve extra information about the fields in a table (for single table queries) that will allow the driver to determine if a field can be null (IsNullable) or are automatically generated by a sequence the server (IsAutoIncrement). Few applications should need this setting and they can be manually added to the TField component representing each physical field. Since this will add a new query overhead for each cursor (Dataset) retrieved, slowing down the operation, it's adviced not to use it unless you need the information.

These parameters go into the dbxdrivers.ini (dbxdrivers on Kylix) file, but since the 1.X series, they go after the Database parameter in the dbxconnections.ini (dbxconnections on Kylix) parameter, separated by colons. An example:

[OfficeServer]
Database = sales,TextAsBlob = False, Int8Mode = AsString

[PGEConnection]
Database = sales, ArrayAsString = False, QuoteObjects = True, GetExtendedFieldsInformation = False

If you don't want to use any special parameters (as most users will), specify the Database parameter alone, normally:

[OfficeServer]
Database = sales

Of course these parameters can go in the TSQLConnections.Params property as any other dbxconnections parameter:

SQLConnection1.Params.Add('Database = sales,TextAsBlob = False,Int8Mode = AsString');

Unhappyly, due to the way dbExpress was designed, there can't be real custom parameters from VCL to the driver, so misusing the Database parameter was a 'hack' that we had to do to allow custom params.


Known issues


Types Mappping


This is a quick guide to show how the PostgreSQL types are mapped into dbExpress types by the pgExpress driver:

PostgreSQL
pgExpress
Obs
int2 TSmallIntField  
int4 TIntegerField  
int8 TBCDField The dbExpress technology does *not* have support for 64 bit integers. The best solution found was map them to BCD fields. There is also options for mapping them as other types; see above in the Special Params section.
char   Fixed length.
varchar TStringField TStringField.Size will be set to the maximum length. It's not adviced to set a maximum value too long in the table definition to keep the memory requirements low in the dbExpress row buffers.
Reads up to 32kb (dbExpress limitation).
text TBLOBField/Memo Check our FAQ for more info on this. Text fields mapped as Memo will low the memory requirements and make a much faster Dataset. You can map them as strings (padded at 32Kb due to dbExpress limitation); check above in the Special Params section.
numeric TBCDField VCL TBCDFields support up to 32 digits. If you have a field with more then that, it will get mapped into strings if you use the proper setting as stated above in the Special Params section.
time TTimeField See note below. The [time] fields support fractions.
timetz TTimeField Timezones and second fractions aren't supported by VCL and they can give your trouble when used in a WHERE clause on update/delete/insert queries. Please refer to our FAQ for more info on this. It's adviced to either don't use that info or don't use those fields on update queries ('Record not found or changed by another user' messages).
date TDateField  
timestamp TTimeStampField See note from fractions on the timetz field.
timestamptz TSQLTimeStampField See notes from timezones and fractions on the timetz field.
bool TBooleanField  
name TStringField Fixed length. [name] is a field type used internally by PostgreSQL.
bpchar TStringField Fixed length.
_bpchar TStringField Fixed length.
oid TIntegerField Integer.
float4 TFloatField Actually TFloatField can support more precision and scale then [float4] fields and are recommended. You can teorically have a overflow or underflow ifsomeone tries to insert a bad value.
float8 TFloatField  
abstime|reltime TTimeField Same as for [time] fields.
interval|tinterval TTimeStampField Same as for [timestamp] fields.
bytea TVarBytesField Reads up to 32kb (dbExpress limitation).
money TFloatField/Money The [money] field is obsolete. The PostgreSQL documentation advices to use [numeric] or [float8] fields instead.
lo TBlobField/Binary Following the PostgreSQL ODBC driver convention, the 'lo' field represents Large Objects. Due to the way the dbExpress architecture was designed, they are read-only and can't be updated.
Other TStringField Unknown fields are mapped by default as Strings. You can turn this off (and thus don't use the fields at all) turning the proper option according to our Special Params section.


Multibyte Support

The pgExpress driver can handle locale/Multibyte issues in two ways:

  1. Using the built-in dbExpress Locale support.
    Basicly, you just would have to set in your dbxconnections file:

    LocaleCode = XXXX

    'XXXX' is the TLocaleCode type value for your locale. For instance:

    LocaleCode = 1041

    would set the current locale to 1041 = Japanese.

    Check the Delphi help for 'TLocaleCode type', 'TSQLConnection.LocaleCode', and 'Driver parameters' (you can use the help's 'Find' feature if you don't locate these easily).

  2. Using automatic PostgreSQL server-client conversion. The pgExpress driver implements this by using the ServerCharset parameter in the dbxconnections file (could also be a Paramater in the TSQLConnection component that wraps the connection).
    Since dbExpress do not provide custom parameters support, nor does it support a 'ClientCharset' parameter, we have to use the following 'hack': providing both the ServerEncoding and ClientEncoding in the ServerCharset parameter. The format is:

    ServerCharset = ServerEncoding/ClientEncoding

    Both parameters are optional.
    The allowed values are (from PostgreSQL documentation):

    Table 5-1. Character Set Encodings

    Encoding Description
    SQL_ASCII ASCII
    EUC_JP Japanese EUC
    EUC_CN Chinese EUC
    EUC_KR Korean EUC
    EUC_TW Taiwan EUC
    UNICODE Unicode (UTF-8)
    MULE_INTERNAL Mule internal code
    LATIN1 ISO 8859-1 ECMA-94 Latin Alphabet No.1
    LATIN2 ISO 8859-2 ECMA-94 Latin Alphabet No.2
    LATIN3 ISO 8859-3 ECMA-94 Latin Alphabet No.3
    LATIN4 ISO 8859-4 ECMA-94 Latin Alphabet No.4
    LATIN5 ISO 8859-9 ECMA-128 Latin Alphabet No.5
    LATIN6 ISO 8859-10 ECMA-144 Latin Alphabet No.6
    LATIN7 ISO 8859-13 Latin Alphabet No.7
    LATIN8 ISO 8859-14 Latin Alphabet No.8
    LATIN9 ISO 8859-15 Latin Alphabet No.9
    LATIN10 ISO 8859-16 ASRO SR 14111 Latin Alphabet No.10
    ISO-8859-5 ECMA-113 Latin/Cyrillic
    ISO-8859-6 ECMA-114 Latin/Arabic
    ISO-8859-7 ECMA-118 Latin/Greek
    ISO-8859-8 ECMA-121 Latin/Hebrew
    KOI8 KOI8-R(U)
    WIN Windows CP1251
    ALT Windows CP866


    The values for server encoding and client encoding are in the PostgreSQL's documentation Multibyte section (http://www.postgresql.org/idocs/index.php?multibyte.html). Internally, pgExpress will interpret the values inthe following way:

    1. If you provide only a ServerEncoding, the pgExpress Driver will try to setup a default client encoding for it. The default ClientEncoding will be the same as the ServerEncoding, except for the UNICODE and MULE_INTERNAL ServerEncodings, whose have no default value.
    2. If you provide a ClientEncoding value, it will set the ClientEncoding to that value, regardless of what is defined in the ServerEncoding param. If you want to set only a ClientEncoding value, just omit the ServerEncoding value (but include the '/' separator), like this:

      ServerCharset = /latin2

      This will set the ClientEncoding to latin2 regardless of the Server encoding.

      Other examples:

      ServerCharset = latin2


      This will set the ClientEncoding to latin2 because it's the default encoding for the latin2 ServerEncoding.

      ServerCharset = latin2
      /latin3

      This will set the ClientEncoding to latin3.

Stored Procedures Support

As most PostgreSQL users know, it does not have stored procedures support. Instead, it has a more flexible functions support (created by create function statements). The pgExpress Driver allows using of such functions as stored procedures.

An usage example:

with SQLDataset1 do
begin
  Close;
  CommandType := ctQuery;
  CommandText := 'create or replace function Test1(int2, int2) returns int2 as ''select $1 + $2;'' language ''SQL'';';
  ExecSQL;

  CommandType := ctStoredProc;
  CommandText := 'Test1';  // This MUST be before setting paramaters. VCL clears Params on setting CommandText.
  with fDataset.Params do  // Params got automatically filled
begin Params[0] := 10; Params[1] := 20; ExecSQL; MsgBox(Params[2]); // Will display 30 end; end;

This could also be written as :

with SQLDataset1 do
begin
  Close;
  CommandType := ctQuery;
  CommandText := 'create or replace function Test1(int2, int2) returns int2 as ''select $1 + $2;'' language ''SQL'';';
  ExecSQL;

  CommandType := ctStoredProc;
  CommandText := 'Test1';  // This MUST be before setting paramaters. VCL clears Params on setting CommandText.
  with fDataset.Params do  // Params got automatically filled
  begin
    Params[0] := 10;
    Params[1] := 20;
    ExecSQL;
    MsgBox(Params[2]); // Will display 30
  end;
end;

 


License

This is the license agreement for the pgExpress Driver ("Software"). BY INSTALLING, COPYING, OR OTHERWISE USING THE SOFTWARE, YOU AGREE TO BE BOUND BY
ALL OF THE TERMS AND CONDITIONS OF THE LICENSE AGREEMENT.

Althought we are we know it works fine, and Vita Voom puts the maximum of its efforts to develop, support and enhance it, the Software provided under this License Agreement, including but not limited to libraries, source code, documentation, redistributables and other files are provided "as is", without warranties of any kind.

The Software is owned by Vita Voom Software and is protected by copyright law and international copyright treaty. Therefore, you must treat this Software like any other copyrighted material (e.g., a book), except that you may either make one copy of the Software solely for backup or archival purposes or transfer the Software to a single hard disk provided you keep the original solely for backup or archival purposes.

You may not alter any of the programs or accompanying files without Vita Voom Software's written permission. Any resale or commercial distribution of the Software is strictly prohibited, unless Vita Voom Software has given explicit written permission. You can, however, distribute the software as part of a program your company have produced.

You have the right to use the Software as set forth in this licensing agreement. You are not obtaining title to the Software or any copyrights. You may not sublicense, rent, lease, convey, modify, translate, convert to another programming language, decompile, or disassemble the Software for any purpose.

Finally, the software is provided on a PER DEVELOPER basis. You must buy a license for each developer who's using the pgExpress Driver. You license could be cancelled if you do not comply to the above statements.

DEMO VERSION RESTRICTIONS

(The following applies only to the demostration version of the driver.)

This version of the Software is a demonstration version. This means that you may use the Software for evaluation purposes only. You may use the Software to test whether it meets you demands. You must not distribute materials produced by the Software to computer systems different from the computer on which the Software is installed, regardless of how such a distribution takes place. You must not modify any material produced by the Software, including but not limited to computer files which where created as a result of the usage of the Software. You are not allowed to sell, rent, lease or otherwise use commercially files or parts of files which where created by the Software. The
Software is equipped with a mechanism that prevents the usage of the Software after a certain period of time has elapsed. You agree that you will delete the Software from all computer systems to which you have installed it when this date has been reached.

DISCLAIMER

VITA VOOM SOFTWARE MAKES NO REPRESENTATIONS OR WARRANTIES AS TO THE TRUTH, ACCURACY OR COMPLETENESS OF ANY STATEMENTS, INFORMATION OR MATERIALS CONCERNING THE SOFTWARE THAT IS CONTAINED ON AND WITHIN ANY OF THE WEBSITES OWNED AND OPERATED BY VITA VOOM SOFTWARE.

THIS SOFTWARE IS PROVIDED TO YOU "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED INCLUDING BUT NOT LIMITED TO THE APPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. YOU ASSUME THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THE SOFTWARE AND ALL OTHER RISK ARISING OUT OF THE USE OR PERFORMANCE OF THIS SOFTWARE AND DOCUMENTATION. VITA VOOM SOFTWARE SHALL NOT BE LIABLE FOR ANY DAMAGES WHATSOEVER ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF VITA VOOM SOFTWARE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, IN NO EVENT SHALL VITA VOOM SOFTWARE BE LIABLE FOR ANY CONSEQUENTIAL, INCIDENTAL, DIRECT, INDIRECT, SPECIAL, PUNITIVE, OR OTHER DAMAGES WHATSOEVER, INCLUDING BUT NOT LIMITED TO DAMAGES OR LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION, LOSS OF BUSINESS INFORMATION, OR OTHER PECUNIARY LOSS, EVEN IF VITA VOOM SOFTWARE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. BECAUSE SOME STATES/JURISDICTIONS DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES, THE ABOVE LIMITATION MAY NOT APPLY.

The entire contents of this this package is protected by Brazilian and International Copyright Laws. Unauthorized reproduction, reverse-engineering, "hacking" and redistribution of all or any portion of the code contained in this file is strictly prohibited and may result in severe civil and criminal penalties and will be prosecuted to the maximum extent possible under the law.
This package can be redistributed only under it's original form; no changes may be applied to it or it's contents.
If you disagree about any of these terms, you're not allowed to use any Vita Voom Software. Please remove the all copies of our software from your system.

It is forbidden to use the Vita Voom pgExpress Driver in products that in a way or other reproduce extend the functionality of any Vita Voom Product (namely but not restricted to the pgExpress Suite), unless they're for internal use of your own company. Again, distribution of such products based in pgExpress Driver is FORBIDDEN.

For more details, please contact Vita Voom Software.

The pgExpress PostgreSQL dbExpress Driver and all it's related files are Copyright © 2001-2002 by Vita Voom Software.


Orders

You can purchase the Vita Voom pgExpress Driver and the other Vita Voom products through our reseller, SWREG, using your credit card or one among many other payment forms they support (Mastercard, Eurocard, VISA, Delta, JCB, Switch, Solo, Discover, American Express, Diner's Club, UK cheque, US check, Postcheque, International Money Order, Bank wire and PayPal). The purchase process is done through a secure web server; your personal details such as name, address and credit card number or bank account number won't be reveiled to anyone. We shall send you your product through download link and/or email (as you choose), as soon as SWREG both notifies us about your purchase and consider the transaction done.

To order the pgExpress Express Driver or any of our other products, please go to our Orders Page.

If you have any comments, problems or doubts about purchasing from us, we would like to hear from you at orders@vitavoom.com.


Notes


Credits

This driver is 100% coded by Vita Voom Software. We would like, however, to express our gratitude to the following people:


About the pgExpress Suite

The pgExpress Suite is a set of components to develop client-server applications using Borland Kylix/Delphi 6+ and the PostgreSQL Database Server.
Along with an improved driver version, the following components will be available:


Contact, Support, Bugs

You can find more info about Vita Voom and it's products on our Web site: http://www.vitavoom.com.
If you want to become a beta tester, please email to betatest@vitavoom.com.
If want to contact us, have questions, report bugs, introduce comments, etc. - please use the following email: support@vitavoom.com
For comments about the Vita Voom website, please email to webmaster@vitavoom.com
Support for the Demo version is not official; you might write us, but there is no warranty that all questions are answered. We are also might be going to provide a newsgroups forum and/or mailing lists.

Steve Howe
howe@vitavoom.com