Appendix A. FAQ and Changelog

1. F.A.Q.
2. Changelog

1. F.A.Q.

1. General Questions

1.

What is the pgExpress Driver ?

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.

2.

What are the advantages of the dbExpress™ architeture ?

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

3.

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, Delphi 6/ 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.

3.1.

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 http://www.cygwin.com. You can also buy a commercially supported Cygwin™ based port from http://www.dbexperts.net.

2. Programming Questions

1.

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.

2.

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

Obs:

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 mathcing 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 corretly interpreted and the query comes filtered:

    Open;
    
  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;
                    Execute;
    
  4. After calling Execute, we have to refresh (or close/reopen) the TSQLClientDataset so it willl match the new rows retrieved:

    Refresh;
    
3.

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 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 circumvents this issue automatically for the string fields.

Note

TextAsBlob=True is the default behavior for the pgExpress Driver.
4.

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

5.

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.

6.

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.

Notes:

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