PostgreSQL: Capture RAISE NOTICE from a client connection via ZeosLib/Lazarus

Question

I have developed a client application that use PostgreSQL 8.4 RDBMS.

My Application is written in Lazarus and ZeosLib 7.2 for database access.

I use a lot of stored procedures and in specific point I use raise notice to get info of the procedure status, Es:

RAISE NOTICE 'Step 1: Import Items from CSV file';
....
....
RAISE NOTICE 'Step 2: Check Items data';

When I execute procedures in PgAdmin3 it show notice in "Messages" Tab. There is a way to capture raised notices in my client application?

TZIBEventAlerter might be the component which receives these notices. See my related example about Firebird events in my blog [article](https://mikejustin.wordpress.com/2012/11/06/firebird-database-events-and-message-oriented-middleware/) — mjn, Nov 04 '15 at 16:38
FPC contains API headers for Postgres: `fpc/packages/postgres/src/postgres3.pp` and `fpc/packages/postgres/src/postgres3dyn.pp` and here you can find function [PQsetNoticeReceiver](http://www.postgresql.org/docs/8.4/static/libpq-notice-processing.html) So only what you need is to get from Zeos connection `PGconn` parameters (try to start at the `function TZPostgreSQLBaseDriver.ConnectDatabase` method in the `ZPlainPostgreSqlDriver` unit) PS: I am using latest trunk of FPC & Zeos — Abelisto, Nov 04 '15 at 18:33

score 2 · Accepted Answer · answered Nov 04 '15 at 20:41

Ok, while I interesting in this topic too, here is some working example crated after the quick investigation:

uses
    Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
    ZConnection, ZDbcPostgreSql;

type

    { TForm1 }

    TForm1 = class(TForm)
        Button1: TButton;
        Memo1: TMemo;
        pgConn: TZConnection;
        procedure Button1Click(Sender: TObject);
        procedure pgConnAfterConnect(Sender: TObject);
    private
        { private declarations }
    public
        { public declarations }
    end;

var
    Form1: TForm1;

implementation

{$R *.lfm}

procedure PGNotifyProcessor(arg: Pointer; message: PAnsiChar); cdecl;
begin
    Form1.Memo1.Lines.Add(message);
end;

{ TForm1 }

procedure TForm1.pgConnAfterConnect(Sender: TObject);
var
    pg: IZPostgreSQLConnection;
    args: Pointer;
begin
    pg := pgConn.DbcConnection as IZPostgreSQLConnection;
    pg.GetPlainDriver.SetNoticeProcessor(pg.GetConnectionHandle, @PGNotifyProcessor, args);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
    pgConn.ExecuteDirect('select foo(''bar'')');
end;

end.

It works for me.

I guess this example is not accurate and contains some issues. For example usage LCL calls in the procedure called from external source. But I hope it is enough to start.

Test environment is: FPC 2.6.4, Lazarus 1.5, Postgres 9.3, Linux Mint

works well! Of course, for functions that take a long time, to have the messages in real time stored procedures must be launched in external thread... Thank you! — AndreaBoc, Nov 06 '15 at 07:46

PostgreSQL: Capture RAISE NOTICE from a client connection via ZeosLib/Lazarus

1 Answers1