What is the pgExpress Driver ?
The pgExpress Driver is a PostgreSQL v7.1+ dbExpress™ Driver for Codegear Delphi™ 6+/Codegear Kylix™/Codegear C++ Builder™ 6+.
Please refer to the Changelog of the pgExpress Driver for the changes history.
What are the advantages of the dbExpress™ architeture ?
Please refer to http://community.borland.com/article/0,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++ Builder™ 6. 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
PostgreSQL 7.4 is supposed to introduce a native port for
My queries that use colons are not properly executed
Since the VCL/CLX parses the colons ('
/*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
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.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:
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 = True
Also you might want to define
The pgExpress Suite™ product circunvents this issue automatically for the string fields.
My queries using backslashes '
This is not a bug, but a PostgreSQL feature. The '
I'm having problems with queries with timestamp/datetime fields.
The problem is caused by a dbExpress™ VCL/CLX bug. There is an interface called
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
To solve this, use a primary key/index and the
If in your database these fields are indexed, removing the
Another way of solving this issue is by using a view using the
create view TestView as select a, date_trunc('second', b) as b from test;
The pgExpress Suite™ does not suffer from this problem since it maps the fields to custom types.
Just to document, the Delphi 7
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;
Why does pgExpress consumes so much memory ?
The pgExpress Driver itself uses very little memory. The problem is the behavior of
To minimize this effect, we have to reduce the memory used by the
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
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:
NoteIf 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]
Why does my query/function return a
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
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
Depending on the compiler which compiled the libpq, it could be the
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
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
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):
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.