As most PostgreSQL
users know, it does not have stored procedures support. Instead, it
has a more flexible mechanism called Functions (created by CREATE
FUNCTION statements).
The pgExpress Driver allows using of functions as stored procedures.
An usage example:
Example 2.4. Stored Procedures usage
procedure TForm1.Button1Click(Sender: TObject);
begin
with SQLDataset1 do
begin
Close;
CommandType := ctQuery;
CommandText := 'create or replace function Test1(int2, int2) returns'
+ ' int2 as ''select $1 + $2;'' language ''SQL'';';
ExecSQL;
CommandType := ctStoredProc;
// This MUST be before setting paramaters.
// VCL clears Params on setting CommandText.
CommandText := 'Test1';
Params[0].Value := 10;
Params[1].Value := 20;
ExecSQL;
ShowMessage(Params[2].AsString); // Will display 30
end;
end;
PostgreSQL supports a feature named "Fetch Cursors". These special cursors allow the records to be retrieved from the server in smaller sets then the defaulbehaviort, which is retrieving the full recordset. To achieve this, the the PostgreSQL DECLARE and FETCH commands are used. This can accelerate the queries response in some situations, specially when the connection to the server is fast and the recordset is large. Also, Fetch Cursors do not need the whole query to be processed: they can start retrieving data as soon as the requested number of rows is available. Finally, Fetch Cursors will consume less memory then regular cursors because fewer rows stays in memory each time.
As of version 1.60, the
pgExpress Driver features
automatic FETCH
cursors. The only thing needed is setting the special param
BlockRead; however, you
probably will want to change also the RowsetSize param (please check your
Kylix/Delphi documentation) if you want (as of
Delphi 6/7 and
Kylix 1/2/3,
default VCL/CLX value is 20).
Internally, the pgExpress Driver will DECLARE a cursor with a unique name (per transaction) for each dataset and use FETCH commands as their rows are requested. When the dataset is freed, it's cursor will be closed.
The number of rows retrieved on each internal fetch operation is
given by the RowsetSize
param. A negative RowsetSize param will issue a
FETCH
ALL command, what actually does not differ much
then a regular query without use of a FETCH
command: all rows will be retrieved at once.
Usually larger values, like 50 or 100 (or even more, depending on
your environment), would give a better performance. Please try and
see what value is best for your application, but have in mind that
each FETCH
command means a separated query and that could slow down dataset
scrolling over slower connections.
A typical dbxconnections(.ini)
setup would be:
[PGEConnection] BlobSize=32 HostName=myhost Database=mydb,BlockRead=True DriverName=PostgreSQL Password=temp123 User_Name=foo RowsetSize=200
Internally, the pgExpress
Driver will automatically execute all SQL commands
needed to use Fetch
Cursors (DECLARE
and FETCH);
all operations are transparent to the user.
An additional, good side effect of using Fetch Cursors is that the overall memory used is much less that if you retrieve all rows at once, due to libpq caching. For large datasets, using this setting is a smart option. Please check Q: 7 for more details.
As of PostgreSQL 7.2, a transaction is needed in order to user Fetch Cursors; the pgExpress Driver will start a transaction automatically.
If you don't set TClientDataset.FetchOnDemand to True, Fetch Cursors will be
worthless because the TClientDataset
will be caching all rows in memory.
The RetainCursor Special
Parameter will be ignored if Fetch Cursors are in use.
For more details on the inners of Fetch Cursors, please
refer to the follow PostgreSQL
documentation links:
As of PostgreSQL
7.3, three authentication
methods are available: password,
crypt and md5.
Since the pgExpress Driver is
libpq based, use these three
methods are automatic, and will follow what you have defined on the
pg_hba.conf file (please check
here for details).
We advice using the MD5 method (PostgreSQL 7.2
and above), unless you are using SSL or other
encryption wrapper in your connection to the server.
The pgExpress Driver has the ability to automatically detect the numeric format used on you server; namely, the Decimal and the thousands separators. There should be no need to set numeric formats manually, but in case you want or need, try the ServerDecimalSeparator special parameter.
In case the automatic detection do not work for you, please mail us at support@vitavoom.com
As of pgExpress Driver
v2.0, Int8 fields can be mapped directly to TLargeintField fields using a hack.
Since dbExpress™ does not support Int8 natively, the 1.X series of the pgExpress Driver used to map them as TBcdField fields by default (AsBcd). That behavior is now DEPRECATED, (except in this situation.
If you want or need to keep compatibility with pgExpress Driver 1.X, use AsBcd. However, users are encouraged to upgrade their field definitions to use native Int8 support. Doing this is easy: just delete the field definition from the Dataset's field list and add it again.
Note that Int8 fields support is not
officially in dbExpress™ as
of D7/K3/BCB6.
While they will work most of time, the TIndexDefs create on these fields will not be
correctly handled by VCL. You can get "Key
violation" messages when trying to insert new values in this
field, even if the values differ. To prevent this, you can try any
of the following workarounds:
False on your TSQLDataset . In older versions then
D7, it's the same as enabling
NoMetadata.IndexDef generated
automatically by dbExpress™.
After opening your ClientDataset,
call ClientDataset1.IndexDefs.Delete(0) for removing
that IndexDef. However, this will
make the field unindexed; the exception will not be raised even
when you insert duplicate values in the field.The lack of a ISQCursor.First() interface for dbExpress™ cursors make it necessary to
run any query twice if you want to access a previous record again;
the dbExpress™ technology is
currently forward-only. Of
course, Client Datasets (TCustomCachedDataset descendants) will cache
records so random access is possible; but non-cached SQL Datasets
(TCustomSQLDataset descendants) will
make the query run again.
The pgExpress Driver implements
an experimental setting,
RetainCursor,
that will make non-cached datatasets access MUCH faster to access
from the second time, since the records will be retrieved from the
internal libpq cache instead of
being retrieved once again from the server. Basically, if RetainCursor is
True, the query is a select query
and the query is the same as the last one executed, the results
displayed will be the same from the cache.
This behavior is controlled by the RetainCursor Special
Parameter. If you are using RetainCursor =
True and want a particular query to
be trully executed again, instead of retrieved from the cache, add
a '!' char to the beginning of the
query (it will be automatically stripped by the pgExpress Driver):
!select * from pg_type;
Large Objects are primarily read only on the pgExpress Driver, but starting on v2.0, we have added writing support.
As of pgExpress v2.31, we had to make some changes to the way pgExpress handles Large Objects and also changes to this documentation. Due to a bug in PostgreSQL up to v7.4 (PQftype() won't return the current oid for a domain, but for it's defined type instead) the way to create the 'lo' type has changed as shown below; it used to be:
create domain lo oid
but that will not work as it should.
If you have a production database using the 'lo' type/domain declared in a different way then below, we suggest to pg_dump the database structure and data separately, then change the struct dump script to create the 'lo' type as shown below, and finally recreate the database structure with the new 'lo' type definition and restore the data.
The Large Object 'lo' field type isn't declared by the default
in the PostgreSQL
distribution, so we must do it manually. However, as of
PostgreSQL 8.0, Windows
users used to install the contrib modules from the installer might
already have installed the 'lo' contrib module, which adds a
compatible lo type, and these steps will
not be necessary. Of course, users from other platforms that
installed that contrib module will also have that type
automatically created.
So, before creating the lo type, check if it isn't already created:
Example 2.5.
howe=# select oid, typname from pg_type where typname='lo'; oid | typname -------+--------- 17245 | lo (1 row)
The Large Object fields declaration must follow the method introduced by the PostgreSQL ODBC driver: a 'lo' type. This type can be created easyly using the following query (PostgreSQL 7.3 and above):
CREATE FUNCTION lo_in(cstring) RETURNS lo AS 'int4in' LANGUAGE 'internal' WITH (ISCACHABLE, ISSTRICT); CREATE FUNCTION lo_out(lo) RETURNS cstring AS 'int4out' LANGUAGE 'internal' WITH (ISCACHABLE, ISSTRICT); CREATE TYPE lo( internallength = 4, externallength=10, input = lo_in, output = lo_out, alignment = int4, default = '', passedbyvalue ); CREATE CAST (lo AS oid) WITHOUT FUNCTION;
...or (PostgreSQL 7.2X and below):
create type lo( internallength=4, externallength=10, input=oidin, output=oidout, default='', passedbyvalue );
You can then use this type normally on your tables:
Example 2.6. Creating a table with a Large Object
create table employee(id integer, name varchar(30), picture lo);
The pgExpress Driver can read Large Object (BLOB) fields without problems; however, due to the way the dbExpress™ technology was designed and the particular implementation of Large Objects on PostgreSQL, the pgExpress Driver has problems on updating Large Object fields.
PostgreSQL refers to Large Objects using a
OID that
points to the the real data.The libpq library needs this OID to do all sort of
operations on the Large Object fields. The problem is that the
dbExpress™ API will be expecting only the BLOB field's data;
the OID information has no way to be stored. After the BLOB field is processed internally by the
VCL/CLX and is sent back to the pgExpress Driver for being stored in the
database, the original OID of the Large Object can't be retrieved,
so pgExpress won't know which BLOB
is refers to. This means that we can't alter the original Large
Object, and if we create a new Large Object, the original Large
Object will end up with as a orphan Large Object (a LO that exist
but is not referenced by any rows, wasting disk space), unless of
course another row refers to the same LO, what is not a common
situation.
At Vita Voom Software™, we understand that Large Objects support it is crucial for some users and thus we have the following suggestion as a workaround to this problem, which affects also the JDBC and ODBC drivers:
lo_clean() function above could easily be
modified to reuse the old OID value in the case of a UPDATE query,
by adding an statment such as:
update pg_largeobject set loid=old.oid where loid=new.oid
contrib/lo
directory on the PostgreSQL
distributions contains code that helps avoiding orphan Large
Objects; you might be insterested in using it, or even reading the
docs for more background on the subject.Create your table normally, including the BLOB field (please read this section about BLOB fields declaration):
create table lo_test(id serial, image lo);
Create a trigger and function that will delete the Large Object if their values are changed (replace the b fieldname for your real field name):
Function:
create or replace function test_lo_clean() returns trigger as '
declare
lo_oid oid;
begin
-- If it is an update action but the BLOB (lo) field was not changed, dont do anything
if (TG_OP = ''UPDATE'') then
if (old.image = new.image) or (old.image is null) then
return new;
end if;
end if;
select into lo_oid loid from pg_largeobject where lo_oid = oid(old.image);
if found then
perform lo_unlink(lo_oid);
end if;
return new;
end'
language 'plpgsql';
Trigger:
create trigger lo_cleanup after delete or update on lo_test -- must be after to avoid deleting the Large Object if record is not deleted for each row execute procedure test_lo_clean();
The function above uses the pl/PgSql language, which must be created in the datbase if it wasn't already. This can be accomplished easily through the following command (at the shell prompt):
$ createlang -d dbname plpgsql
For more details on creating languages, please check here.
This trigger will avoid that orphan Large Objects are left on the table. Note that triggers are fast; in fact, referential integrity is implemented internally in PostgreSQL using them. If you want to try another code to do it, feel free; all you need to do is ensure no orphan OIDs are left on the pg_largeobject table.
You might have to make some small changes to the test_lo_clean() function to reflect your actual
table and field names, if you have large objects reused in more
then one field/record, etc.
Hopefully the PostgreSQL developers will introduce a new API for handling Large Objects that does not suffer from these limitations.
select lo_unlink(lo_column) from mytable;
PostgreSQL has built-in support for
SSL. However, on the backend (client)
side, we depend on libpq support
for actually encrypting the connection. The problem is,
libpq won't compile properly with
SSL enabled under Windows (under Linux it compiles without any problems). This
means that even if we can enable SSL on the server side, it's hard
to set it on the client side.
Happifully, there are other ways to do it. The simplest is making a tunnel using either SSH or Stunnel. Please go to its site for more details.
PostgreSQL by default will print NOTICE
messages (generated by the server and inside functions by
statements such as RAISE
NOTICE) in the server's standard output. The
pgExpress Driver implements a hook
which allows those messages to be logged along other regular
messages so that a TSQLMonitor
component will be able to log them all automatically.
CBInfo
to traceVENDOR on the OnTrace/OnLogTrace events.Whenever you send a query to the PostgreSQL server, it can delay a long time to
return a result to your application. Typical cases include a very
long resultset, lots of computations/calculations, or even a
function hangup/infinite loop. It is nice to let you user cancel a
query if they want to, thus returning control to your program, and
quitting things nicely in the server side. The way to accomplish
this is by calling PQrequestCancel
function.
The pgExpress Driver wraps that API nicely in the pgeDriverUtils.CancelQuery function. Calling it will make the server try its best to cancel the currently query and return control to the client application. This has the same effect as pressing Ctrl+C in the psql console.