This section aims to give useful hints/tips for the dbExpress/pgExpress Driver developer.
Do not use TSQLQuery
/TSQLTable
/TSQLStoredProc
.
These classes are only included in dbExpress in order to easy migration from older BDE
applications. Use always a TSQLDataset
instead, or TSimpleDataset
if you prefer.
Use lowercase object names.
Since PostgreSQL distinguishes between upper and lowercase chars, it's much easier to work always with lowercase object names. If you create objects with quotes and non-lowercase chars such as:
create table "Test"(a integer);
, you'll always have to quote them too when working with them:
select * from "Test";
Besides, VCL/CLX might have problems acessing mixed case object names since it tends to quote object names automatically.
Do not use unsized VARCHAR or TEXT values.
Unsized (i.e. having no length specifier) VARCHAR fields can length up to 4Gb in size. Since VCL/CLX do not work with unsized fields, pgExpress maps them as Memo fields, and this can lead to unwated field types, specially when they are used as the return type of functions. So, instead of:
create function getuser(varchar) returns varchar as ' select user ' language sql;
or:
create table test(name varchar);
use constructs such as:
create function getname(varchar) returns varchar(100) as ' select user ' language sql;
or
create table test(name varchar(100));
Do not abuse Blob/Memo fields.
When working with Blob/Memo fields, be careful not to waste memory, network
bandwidth and loading time by loading all records at once. Such large fields could be a real load into the
system and make it much slower. Activating the TDatasetProvider.Options
poFetchBlobsOnDemand
flag can be of help in this situation.
Just to illustrate, let's suppose we have a table with 10.000 records, each having a blob field of around 500Kb. If we load that table at once, we'll be loading 5Gb of blobs in memory - besides the other fields. Even if it's much less then this, let's take care to not load such large datasets because they're very slow to manage (by the VCL/CLX, not by the pgExpress Driver itself).