Appendix A. FAQ and Changelog

1. F.A.Q.
2. Changelog

1. F.A.Q.

1. General Questions


What is the pgExpress Driver ?

The pgExpress Driver is a PostgreSQL v7.1+ dbExpress™ Driver for Codegear Delphi6+/Codegear Kylix™/Codegear C++ Builder6+.

It can use all PostgreSQL field types, and is compliant to the Codegear specifications of dbExpress™ drivers.

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


What are the advantages of the dbExpress™ architeture ?

Please refer to,1410,28688,00.html.


What platforms does the pgExpress Driver run on?

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


Is there a PostgreSQL for Windows ?

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 You can also buy a commercially supported Cygwin™ based port from

2. Programming Questions


My queries that use colons are not properly executed

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.


I can't run parametrized select TSQLClientDataset queries, or my queries only work for the first parameter and don't get refreshed.

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

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


The pg_type table is an internal PostgreSQL catalog table that is avaiable on all PostgreSQL databases, so it's a good table to work as example.

Now explaining it:

  1. Before calling a parametrized query, you must create a TParam for each parameter you set in the CommandText property, and assign them to the TSQLClientDataset.Params property:

    with Params.CreateParam(ftString, 'type', ptInput) do
                    AsString := Edit1.Text;

    Good values to use as test Paramaters are '%a%' (for matching any type that has an 'a' in its name) or 't%' (for any type that starts with 't'). Remember that '%' is PostgreSQL's wildcard char.

  2. The first time you open the query, everything hould be ok: the parameter is correctly interpreted and the query comes filtered:

  3. As quoted from the Delphi's help's [TCustomClientDataSet.Params] entry, we have to set the new Param value and then call Execute at the second time:

    Warning: The parameters are only applied to the provider when a query or stored procedure is run the first time the client dataset fetches records. To change parameter values and force a query or stored procedure to be rerun with new values, use the Execute method."

    ... what lead us to...

    Params[0].AsString := Edit1.Text;
  4. After calling Execute, we have to refresh (or close/reopen) the TSQLClientDataset so it willl match the new rows retrieved:



Using text fields as strings is very slow and uses a lot of memory.

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 TextAsMemo special param in your configuration (please follow the steps in the pgExpress Driver distribution's documentation):

TextAsMemo = 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.


TextAsMemo=True is the default behavior for the pgExpress Driver.


My queries using backslashes '\' fail.

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:


I'm having problems with queries with timestamp/datetime fields.

The problem is caused by a dbExpressVCL/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:

Record not found or changed by another user

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 DB2dbExpress™ driver.


I want to do updates using OIDs but pgExpress does not supports it.

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;
  1. In your TSQLDataset component, construct your query so that it will retrieve the OID field. If you don't explicitly declare you don't want this field, psql will not return it. An example:

  2. In your TSQLDataset component, for all the TField field definitions, unset all the ProviderFlags = pfInKey, except for the OID field definition; this field has to have the pfInKey ProfiderFlag. This is because it will be our primary key.

  3. You might also want to set for the OID field definition the pfHidden flag. This means it will be used on updates for can't be seen by the client (what makes total sense with OID fields).

  4. Set TSQLDataset.UpdateMode = upWhereKeyOnly . This will make OID field the only one used to generate the WHERE statements.

  5. That's it. An alternative way would be using TSQLDataset.UpdateMode = upWhereAll, and deactivate the pfInWhere flag for all other fields.


  • As of PostgreSQL7.2, the OID field is optional. Certify that the table you want to use for OID updates has that field before trying to use it.
  • Contrary to what most users think, OID fields are not indexed. This means the updates whose WHERE clause are based on those fields will be slow, since a sequential scan will have to be used. Thus, we don't advice using OID fields for doing the updates, unless you make an explicity index for it (yes, that is perfectly legal). Probably a standard primary key field would be much better for doing the updates.


Why does pgExpress consumes so much memory ?

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


Can you recommend me a good book about dbExpress ?

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

Get it at:
Publisher's site


My tables float/numeric fields can't be opened; a "Catastrophic failure" message is raised.

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:

  • Use the "C" locale (or any other locale that uses '.' as decimal separator; (an easy way to do it is editing your postgresql.conf file)

  • ... or use the ServerDecimalSeparator special param to switch the decimal separator char to ',' (or '.' depending on your setup).

  • On PostgreSQL 7.4+, you might update the locale settings on the fly:

    update pg_settings set setting='C' where name='lc_numeric';
    update pg_settings set setting='C' where name='lc_monetary';


If the pgExpress Driver can't automatically detect the decimal separator on your system, and you want to help us debugging it, please create an empty database and send us host/user/password/database information so that we can connect on your database and test it. No data will be harmed of course.


Under Win95, I can't connect to some machines. I get messages like:

Connection to the database 192.x.x.x unsuccessful. Could not connect to DB Server. Reason [unknown hostname 192.x.x.x]

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.


Why does my query/function return a TMemoField field instead of a TStringField on VARCHAR fields?

The query/function is returning a VARCHAR VARCHAR field without length specifier; that could be up to 4gb in size. Since the VCL/CLX does not support arbitrary length string fields, they must be mapped as Memo fields.

Fixing this situation is easy: just specify a length to those string fields:

select cast(f1, varchar(30)) from mytable;

The same behavior can occur when using functions that return variable length VARCHAR fields:

              select substr(name, 1, 10) from mytable; 

To get what you expect from this query, use:

              select cast(substr(name, 1, 10), varchar(10)) from mytable; 


I'm getting "Record changed or not found" when trying to update a dataset.

This is happening because the VCL/CLX is not being able to find - or is finding more then one - record which you're trying to change. This is not a bug at all; it's instead a lack of configuration parameters. Probably you need to set a primary key in your dataset, and the generated update statement will have a WHERE clause valid for that record. The SQL generated by dbExpress™ is directly a related to the database primary key and the parameters set on the VCL/CLX database components.

The default TDatasetProvider Provider update setting is upWhereAll; that is rather inneficient since it will use all the fields in the dataset to compose the primary key used in the WHERE clase of the SQL statement - and it probably was not enough since the error message above is being raised. Instead, try to use upWhereKey on the TDatasetProvider and set the appropriate values on the TSQLDataset's field's ProviderFlags property to the dataset's primary key. Please read the documentation of these properties for more details.

To debug this situation and understand the exact communication between the pgExpress Driver and the PostgreSQL server, try attaching a TSQLMonitor into your TSQLCOnnection and inspect the produced log, or instead activating debug logging into the PostgreSQL server and checking it directly. The WHERE clause produced should be reflecting the properties described above.


My application works under Windows XP (or others), but not under Windows 98 (or 95); it says it cant' load the libpq library.

Depending on the compiler which compiled the libpq, it could be the msvcr80.dll file instead. Try installing the msvcr71.dll library into your system, or using the which comes with the official for Windows distribution.


When I try to connect to a server, I get a message like "Could not load the LIBPQ library [LIBPQ.DLL]; reason: ["%1 is not a valid Win32 application"].

You're probably trying to load a 64 bits libpq library from a 32 bits application. Please use a 32 bits libpq library. Perhaps you'll have to uninstall the 64 bits PostgreSQL and install the 32 bits version.


On Delphi 2007, my boolean fields show wrong values; for instance, they are all loaded as True.

Please update your Delphi 2007 IDE using the updates.


Is there any way to force the pgExpress Driver to use case-sensitive object names ?

Normally this is caused to bad detabase design or to force compatibility with other database systems. PostgreSQL distinguishes between upper and lower cases when the object names are quote, e.g.:

select "typname" from pg_type;
select TYPNAME from pg_type;

That query will suceed while the following ones will not:

select "Typname" from pg_type;
select typname from "PG_TYPE";          

Since the VCL/CLX quotes the objects names by default, PostgreSQL will be distinguishing between them.

To avoid that, up to Delphi 7, please use the QuoteObjects (beware that uses an internal pgExpress Driver hack. On later Delphi versions, disable TDatasetProvider.Options poUseQuoteChar flag.

For a better understanding of what is running under the scenes between the pgExpress Driver and the PostgreSQL server, and check the quotes added to statements, please attach a TSQLMonitor into your TSQLConnection component and log the communication between them into a file.


I think I have a bug, or I have this doubt; I need support. Could you help me ?

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

  • The Delphi/Kylix/Codegear C++ Builder™, pgExpress Driver server (PostgreSQL's) and client (pgExpress's) OSs, PostgreSQL and libpq versions you are using.
  • If you are a registered customer, quote your registered user's name somewhere into the top of the email, such as: "Registered User Name: xxxxxxxxxxxx". Registered users always have priority support.
  • A small (as bare as possible) project that allows us to reproduce your problem. Try to set it's TSQLConnection.ConnectionName to "PGEConnection" (this is the the default test entry for the pgExpress Driver). If your project has a problem just by loading a table, this could be skipped; otherwise, please do it.
  • A pg_dump of your data/table with the '-d' option. This option will generate INSERT instead of COPY commands and will be more portable and easier to restore. All tables accessed by your project should be in the dump. You can skip this if you have a doubt/problem that is not related to some specific data, or if it can be reproduced using PostgreSQL system catalog tables.
  • Last but not less, a good description of your problem and the things you tried to solve it, if any. Please describe how we can reproduce your situation in order to allow us to fix/help you.

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.