Capturing PostgreSql's Raise Notice from cammand line app
lazarus / 16-Databases

----------------------------------------------------------------------
martinrame · 2019-03-30 16:07

Hi, I posted this on the Lazarus Mailing list, but it seems to be no
longer working since march 26.

I'm executing an PostgreSql stored procedure using TSqlQuery.ExecSql
and found all RAISE NOTICEs raised from the stored procedure are
written to stdout (I'm on Linux), is there a way to redirect that
output?.

My problem is this application's stdout is captured from a caller app
that stops working if doesn't receive an expected result. In this case
the input received from my program is mixed with the RAISE NOTICEs, so
I want to avoid this.

Regards,
Leonardo

----------------------------------------------------------------------
martinrame · 2019-03-31 10:47

Well, after reading the unit postgres3, and asking a couple of
questions in the PostgreSql IRC channel I've got this solution:

First, libpq has a function for capturing RAISE NOTICE:
PQsetNoticeReceiver, already declared in the postgres3 unit, so, we
just need to use it...but, as it needs access to the connection
handler, and the handler is not accessible by TPQConnection we need to
inherit it by just declaring a class inherited from TPQConnection.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
TMyPQConnection = class(TPQConnection)end;
Then, in both, the .lfm and .pas we must use TMyPQConnection instead
of TPQConnection:

This is the .lfm:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i object
DataModule1: TDataModule1  OnCreate = DataModuleCreate  OldCreateOrder
= False  Height = 159  HorizontalOffset = 1975  VerticalOffset = 257 
Width = 281  object PQConnection1: TMyPQConnection    Connected =
False    LoginPrompt = False    KeepConnection = False    Options =
[]    left = 48    top = 24  end  object SQLTransaction1:
TSQLTransaction    Active = False    Options = []    left = 168    top
= 24  endend 
Then, use it this way:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
dm; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
pqconnection,   postgres3, // PQsetNoticeReceiver lives here!  sqldb,
inifiles; type  // we inherit TPQConnection to be able to access
GetHandle's protected method.  TMyPQConnection = class(TPQConnection) 
end;   { TDataModule1 }   TDataModule1 = class(TDataModule)   
PQConnection1: TMyPQConnection; // <-- don't use TPQConnection, use
TMyPQConnection!.    SQLTransaction1: TSQLTransaction;    procedure
DataModuleCreate(Sender: TObject);  private  end; var  DataModule1:
TDataModule1; implementation {$R *.lfm} { TDataModule1 } procedure
MyNoticeReceiver(arg:pointer; res:PPGresult);cdecl;const
PG_DIAG_MESSAGE_PRIMARY = 77; // 77 is ascii code for 'M', please see
https://docs.huihoo.com/doxygen/postgresql/postgres__ext_8h_source.htm
lbegin  writeln('----------->YEA<-------'); 
writeln(PQresultErrorField(res,
PG_DIAG_MESSAGE_PRIMARY));end; procedure
TDataModule1.DataModuleCreate(Sender: TObject);begin   
PQConnection1.HostName:= '127.0.0.1';    PQConnection1.DatabaseName:=
'dicomscp';    PQConnection1.UserName:= 'postgres';   
PQConnection1.Password:= 'postgres';    PQConnection1.Transaction:=
SQLTransaction1;    PQConnection1.Connected:= True;    // now we set
the notice receiver callback   
PQsetNoticeReceiver(PPGConn(PQConnection1.GetHandle),
@MyNoticeReceiver, nil);end; end. 
And that's it.

----------------------------------------------------------------------
Cyrax · 2019-03-31 17:43

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type  // we
inherit TPQConnection to be able to access GetHandle's protected
method.  TPQConnection = class(pqconnection.TPQConnection)  end; 
You don't need to edit .lfm file for that. Just use interposer class
inheritance.

Handbook Note 67/113: Interposer Classes :
http://blog.marcocantu.com/blog/handbook_note_67.html
http://caryjensen.blogspot.com/2014/01/two-approaches-to-sub-
classing.html
https://stackoverflow.com/questions/14783400/delphi-subclass-visual-
component-and-use-it/14783549#14783549
http://hallvards.blogspot.com/2004/05/hack-4-access-to-protected-
methods.html