Mini Tutorial about How to use PostgreSQL LISTEN/NOTIFY

[nilsonrio]

ATTENTION

There is a component TZPGEventAlerter !!!
You can found it at http://zeos.firmos.at/viewtopic.php?t=2 ... highlight=

The rest of this document shows the details of How use LISTEN/NOTIFY with Zeos without a component.

First: Using a ZQuery q with q.sql.Text = 'LISTEN <notify-name>' perform a "query.Open" and a "query.Close".

Warning: This forces postgreSQL driver (libpq.dll) to ALLOCATE MEMORY for each NOTIFY received. So you MUST retrieve the NOTIFIES.

If you want to stop to LISTEN, repeat the first step changing the LISTEN to UNLISTEN.

Second: Use the code below to check notifies at regular intervals.

Note that the PlainDriver defines a procedure called SetNoticeProcessor.
It´s a trap. This procedure is used to receive warnings from server, not notifies.

PASCAL CODE:

uses
ZDbcPostgreSql, ZPlainPostgreSql8; // or ZPlainPostgreSql7

type

// This is the FIXED definition of PGNotify for PostGres 8.4 and below
PGNotifyOk = packed record
relname : PCHAR; // <===is string[32] in Zeos Plain Driver;
be_pid : integer;
dummy1 : pointer;
dummy2 : pointer;
end;

PPGNotifyOk = ^PGNotifyOk;

//
//
//
//
procedure checkNotify(db: TZConnection);
var
hNotify : PPGnotify;
handle : pointer;
np : TPGNotifyProc;
begin

if db = nil then exit;
if not db.Connected then exit;

handle :=(db.DbcConnection as IZPostgreSQLConnection).GetConnectionHandle;

// The procedure below detects NOTIFY messages (A) in messages
// received from server without any damage to other messages received
// So you can run it at any moment, even after a query.Open
PQconsumeInput(handle);

hNotify := PQnotifies(handle); // get the first notify
while hNotify <> nil do begin //

// PPgNotifyOk(hNotify).relname has the notify name
Memo1.lines.Add(PPgNotifyOk(hNotify).relname);

// You MUST free the Notify
PQfreeNotify(hNotify);

// try to get the next received NOTIFY
hNotify := PQnotifies(handle);
end;

end;

//// That's all, Folks.

[mrLion]

so sorry... do you see that???:

http://zeos.firmos.at/viewtopic.php?t=2 ... highlight=

[nilsonrio]

Not really....

I spent a half day debugging the libpq code to find out how to use LISTEN/NOTIFY with Zeos. When I finished, I decided to share it.

Well, I´m new here - ZEOSLib. In fact, I created this account only to send that message.

So, I think that I should alter my original post to inform people, at first place, that exists the component PGEventAlert that handles PG notifies and a link to the topic.

But I think that it´s good to keep this topic because my message shows the minimal code to use Listen/Notify and also shows BUG on the PGNotify type.

[mrLion]

nilsonrio, Friend, I'm not going to indicate to you what to write. I would on the contrary proposed to read the above topic and to refine the finished component based on identified errors.