The pgExpress Driver is a PostgreSQL v7.1+ dbExpress™ Driver for Borland Delphi™ 6+/Borland Kylix™/Borland C++ Builder™ 6+.

It can use all PostgreSQL field types, and is compliant to the Borland specifications of dbExpress™ drivers. 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.

Please refer to the Changelog of the pgExpress Driver for checking the changes history.

Please refer to http://community.borland.com/article/0,1410,28688,00.html.

As of the time this is written, the pgExpress Driver was tested and runs properly under Kylix 1/2/ 3, Delphi6/ 7 and Borland C++ Builder™ 6. The supported PostgreSQL servers are 7.10 to 7.31. Support for the forthcoming PostgreSQL versions will appear as they get released.

PostgreSQL 7.4 is supposed to introduce a native port for Windows. You can try the free cygwin port, available at the Cygwin™ package at http://www.cygwin.com. You can also buy a commercially supported Cygwin™ based port from http://www.dbexperts.net.

Since the VCL/CLX  parses the colons (':') as being parameters delimiters, you need to use double colons in SQL syntax. Colons are used to do typecasts as:

/*Instead of 'select typelem::int4 from pg_type;' */
            select typelem::::int4 from pg_type;

You can also use the cast() function, similary:

select cast(typelem as integer) from pg_type;

The pgExpress Suite includes proper escaping functions to work with such queries.

The problem is Delphi's weirdness :-) You should use a code like this:

procedure TForm1.Button1Click(Sender: TObject);
begin
with SQLClientDataSet1 do
  begin
    CommandText := 'select * from pg_type where typname ~~ :type';
    if Active then
    begin
      Params[0].AsString := Edit1.Text;
      Execute;
      Refresh
    end       
    else
    begin
      with Params.CreateParam(ftString, 'type', ptInput) do
      AsString := Edit1.Text;
      Open;
    end;
  end;
end

Now explaining it:

This happens because the Delphi String field has a limited size and psql text fields have arbitrary length. Thus, pgExpress is obligated to allocate the maximum string length, which is 32k. This leads to datasets that are slow and uses more memory then what's needed most times. Unhappily, this is a dbExpress™ design flaw (or "misfeature"): there is no real support to variable length strings, only for fixed lengthed. Mapping those text fields as (Blob) Memo fields will be more efficient the as strings in terms of memory. If you can do it, however, we advice to change your text fields to varchar or char field types with a smaller field length; they will be more efficient in Delphi due to the more efficient memory allocation. To do this, define the TextAsBlob special param in your configuration (please follow the steps in the pgExpress Driver distribution's documentation):

TextAsBlob = True

Also you might want to define poFetchBlobsOnDemand in your TSQLClientDataset.Options property (or in your TProvider). This will fetch the blob fields only on demand, making the grids scroll much faster and using less memory.

The pgExpress Suite product circunvents this issue automatically for the string fields.

This is not a bug, but a PostgreSQL feature. The '\' is used to escape the SQL strings to allow use of chars like ''' (which is the string delimiter), '\t' (TAB), '\0' (char #0), etc - pretty much like C language or the Format() function, familiar to DelphiKylixC++ Builder/. Without this feature, it wouldn't be possible to uses such chars in SQL queries. Please refer to the link below for more information:

http://www.vitavoom.com/postgresql-docs/sql-syntax.html#SQL-SYNTAX-STRINGS

The problem is caused by a dbExpress™ VCL/CLX  bug. There is an interface called ISQLCursor.isSearchable() making an update query, and that would create a statement that would have a non-existent value, like this:

update test set b = '2002-08-02 18:36:50' where b = '2002-08-02 18:36:52';

Obviously PostgreSQL will not be able to find the b value from the WHERE clause, and a exception message like this will be raised:

To solve this, use a primary key/index and the UpdateMode to upWhereKeyOnly mode with keys on fields that are not timestamp/ datetime/etc (do not make the timestamp values indexed). This will keep VCL/CLX from generating a wrong WHERE statement.

If in your database these fields are indexed, removing the pfInWhere option from the TField.ProviderFlags property for that field should be enough. If the actual value in your table that have no fraction or timezone value values, the generated SQL will be able to find that record (because it will use only the indexed fields in the WHERE clause, and the update will suceed.

Another way of solving this issue is by using a view using the date_trunc function, like this:

create view TestView as select a, date_trunc('second', b) as b from test;

and then create a rule to INSERT/UPDATE/DELETE rows, but this method is more complicated.

The pgExpress Suite does not suffer from this problem since it maps the fields to custom types. Just to document, the Delphi 7 Readme.txt file has a similar "Known Issues" note for the DB2™ dbExpress™ driver.

In fact pgExpress *does* support it, but you have to do it the right way (please read the note at the end of the answer):

select oid, * from pg_type;

The pgExpress Driver itself uses very little memory. The problem is the behavior of libpq and TClientDataset: both cache the rows read from the server. Imagine the following situation: you run a query which reads 100.000 records from the server. The libpq library will cache all of them, as strings, in the memory. Additionally, the TClientDataset (remember that TSQLClientDataset and TSimpleDataset also have an internal TClientDataset component, which inherites from TCustomCachedDataset , which caches all rows read) component will be caching every record internally too. This will end up caching the rows twice. For large datatsets, this could mean lots of megabytes of memory used.

To minimize this effect, we have to reduce the memory used by the libpq library, using the BlockRead special param. The libpq library will only cache RowsetSize records, which usually is a small value (like 100). This will reduce the memory spent by libpq and have an enourmous impact on the overall memory usage.

Also, since TClientDataset caches the records in a binary format, they usually spend less memory then their string versions (as stored internally by the libpq library).

To develop using dbExpress™ can be a bit tricky sometimes, due to the amount of flexibility the technology provides, and to the new paradigm it offers. Hapifully, it's fairly easy to use dbExpress™ when you know how. We recommend this book for dbExpress™ development:

Delphi/Kylix Database Development DataCLX for Windows and Linux by Eric Harmon

The problem here is a incompatilibity between the decimal separator of the client system (the Delphi/Kylix/C++ Builder program) and the PostgreSQL server. Usually, the PostgreSQL uses the "C" locale, which uses '.' as decimal separator. In a few cases, the pgExpress Driver won't detect this change automatically: the pgExpress Driver will be expecting '.' as decimal separator, and not ',' as used by some locales (such as most european locales). When the pgExpress Driver tries to convert the server value (ex: '1,0') expecting a '.', an exception is raised by the VCL/CLX .

To circunvent this, you can either:

The libpq library needs Winsock 2™ in order to sucessfully connecting to the PostgreSQL server. Please install the Winsock 2™ update and everything should work.

Of course: that's what we are here for. But to allow us to help you better, please include in your email (whenever it applies):

Obviously any data sent us remain private and be used solely for testing purposes and will be deleted as soon as your support request is considered done. Also, remember that the easiest to reproduce your problem, the easier for us to help you.