![]() |
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:
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.
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.
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:
- 100% VCL/CLX implementation - unlike other drivers which are coded in C language, our driver is 100% coded using Delphi itself. This automatically makes it portable to any platform where Delphi/Kylix are available.
- Portability - both Delphi and Kylix are supported.
- FAST! - The pgExpress Driver is highly optimized, and is designed to minimize the traffic to the server as much as possible.
- Low Cost - pgExpress has NO RUNTIME FEES and does not need to be licensed in a per machine basis. PostgreSQL has the most unobstrusive license in the market - BSD (totally free). Thereby, we believe that PostgreSQL + Delphi/Kylix + pgExpress is the dream of all database developers.
- Exclusive features - the Vita Voom driver has much more features then the Borland's Kylix PostgreSQL driver, is available also for Delphi and has features not found on any other driver.
- Reability - Since the driver is based on dbExpress implementation, it minimizes the possibility of bugs, because many of the code that actually access the databases from Delphi is written by Borland itself.
- Full types support - All regular types from PostgreSQL are supported (see Known Issues)
- Database independence - you can switch for another database, just by replacing the driver for another driver. This is a main advantage of the dbExpress technology itself;
- Small - the packages version of the driver is 30Kb only.
- Support - Vita Voom primes for its custumers. We have a real great support system for ensuring our custumers have the best and fastest possible support.
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:
- 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.
- 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.
- 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:
- Edit the 'dbxdrivers.ini' file. On Kylix, this file is called 'dbxdrivers'.
On the [Installed Drivers] section, add the PostgreSQL=1 key and value:
[Installed Drivers]
DB2=1
Interbase=1
MYSQL=1
Oracle=1
PostgreSQL=1
Still on this file, add the [PostgreSQL] section:
[Oracle]
GetDriverFunc=getSQLDriverORACLE
LibraryName=dbexpora.dll
VendorLib=OCI.DLL
BlobSize=-1
DataBase=Database Name
ErrorResourceFile=
LocaleCode=0000
Password=password
Oracle TransIsolation=ReadCommited
User_Name=user[PostgreSQL]
GetDriverFunc=getSQLDriverPOSTGRESQL
LibraryName=dbexppge.dll
VendorLib=LIBPQ.DLL
NOTE: On Kylix, the PostgreSQL section is slightly different to reflect the linux library file names:
[PostgreSQL]
GetDriverFunc=getSQLDriverPOSTGRESQL
LibraryName=libdbexppge.so
VendorLib=libpq.so
Still on Kylix, you must add your driver's path to the LD_LIBRARY_PATH environment variable (or copy the driver to your Kylix's 'bin' subdirectory):
LD_LIBRARY_PATH=/home/steve/lib/
export LD_LIBRARY_PATH
Now, there is still the dbxconnections file to be edited.
Edit the 'dbxconnections.ini' file. On Kylix, this file is called 'dbxconnections'. This file can contain as many entries for the pgExpress driver as you might want. We supply here an example connection, but you should modify it to meet your own settings:
[PGEConnection]
BlobSize=32
HostName=host
Database=database_name
DriverName=PostgreSQL
Password=temp123
TransIsolation=ReadCommited
User_Name=steve
The only required fields are Database, DriverName, User_Name (and Password if you don't want a login prompt). You can use a more reduced form like the one below (you're encouraged to use this one):
[PGEConnection]
DriverName=PostgreSQL
Database=hostname/database_name
User_Name=steve
Password=temp123
The other parameters are optional (and only those listed above are supported by pgExpress) and their meaning is that on dbExpress documentation.
Pay special attention to the Database parameter. dbExpress only will load the HostName parameter if you set the TSQLConnection.LoadParamsOnConnect property to True. We at Vita Voom Software have used the following rules for the format of the database specifier that avoid that confusion and also adds support for a Port parameter. It seems like an URL:
host:port/database
Where:
- host is the Hostname (DNS or IP) of the machine. If you don't supply this parameter, it defaults to localhost.
- port is the port to connect to, on the database server host. This do not need to be supplied, if you use the default, 5432, port. But if you do supply this parameter, you need also specify the host name, and separate the host and port with a colon (':').
- database is the database name to connect to. This parameter must be supplied.
The following are valid Database parameters:
- Database = myserver.mydomain.com/database
- Database = myserver.mydomain.com:5432/database
- Database = 111.111.111.111:5432/database
- Database = database
Note that if you supply both the HostName and Database parameters with embedded hostname (as show above), the Database parameter will prevail.
Don't forget you can add as many sections to the dbxconnections file as you want, like this:
[PGEConnection]
DriverName=PostgreSQL
Database=hostname1/database_name
User_Name=steve
Password=temp123
[pgserver]
DriverName=PostgreSQL
Database=pgserver/database_name
User_Name=john
- 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.
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 MeaningTextAsBlob 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 existTypesCachingMode 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.
This is a quick guide to show how the PostgreSQL types are mapped into dbExpress
types by the pgExpress driver:
|
The pgExpress driver can handle locale/Multibyte issues in two ways:
- 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 = 1041would 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).- 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:
- 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.
- 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.
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;
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.
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.
This driver is 100% coded by Vita Voom Software. We would like, however, to express our gratitude to the following people:
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:
... and more advanced features that will be added in final version.
Do you have suggestions ? Please mail us at support@vitavoom.com.
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