Recent

Author Topic: Capturing PostgreSql's Raise Notice from cammand line app  (Read 461 times)

martinrame

  • Jr. Member
  • **
  • Posts: 67
Capturing PostgreSql's Raise Notice from cammand line app
« on: March 30, 2019, 05:07:02 pm »
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

  • Jr. Member
  • **
  • Posts: 67
[SOLVED] Re: Capturing PostgreSql's Raise Notice from cammand line app
« Reply #1 on: March 31, 2019, 12:47:16 pm »
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]
  1. TMyPQConnection = class(TPQConnection)
  2. end;

Then, in both, the .lfm and .pas we must use TMyPQConnection instead of TPQConnection:

This is the .lfm:

Code: Pascal  [Select]
  1. object DataModule1: TDataModule1
  2.   OnCreate = DataModuleCreate
  3.   OldCreateOrder = False
  4.   Height = 159
  5.   HorizontalOffset = 1975
  6.   VerticalOffset = 257
  7.   Width = 281
  8.   object PQConnection1: TMyPQConnection
  9.     Connected = False
  10.     LoginPrompt = False
  11.     KeepConnection = False
  12.     Options = []
  13.     left = 48
  14.     top = 24
  15.   end
  16.   object SQLTransaction1: TSQLTransaction
  17.     Active = False
  18.     Options = []
  19.     left = 168
  20.     top = 24
  21.   end
  22. end
  23.  

Then, use it this way:

Code: Pascal  [Select]
  1. unit dm;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, pqconnection,
  9.   postgres3, // PQsetNoticeReceiver lives here!
  10.   sqldb, inifiles;
  11.  
  12. type
  13.   // we inherit TPQConnection to be able to access GetHandle's protected method.
  14.   TMyPQConnection = class(TPQConnection)
  15.   end;
  16.  
  17.   { TDataModule1 }
  18.  
  19.   TDataModule1 = class(TDataModule)
  20.     PQConnection1: TMyPQConnection; // <-- don't use TPQConnection, use TMyPQConnection!.
  21.     SQLTransaction1: TSQLTransaction;
  22.     procedure DataModuleCreate(Sender: TObject);
  23.   private
  24.   end;
  25.  
  26. var
  27.   DataModule1: TDataModule1;
  28.  
  29. implementation
  30.  
  31. {$R *.lfm}
  32.  
  33. { TDataModule1 }
  34.  
  35. procedure MyNoticeReceiver(arg:pointer; res:PPGresult);cdecl;
  36. const PG_DIAG_MESSAGE_PRIMARY = 77; // 77 is ascii code for 'M', please see https://docs.huihoo.com/doxygen/postgresql/postgres__ext_8h_source.html
  37. begin
  38.   writeln('----------->YEA<-------');
  39.   writeln(PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY));
  40. end;
  41.  
  42. procedure TDataModule1.DataModuleCreate(Sender: TObject);
  43. begin
  44.     PQConnection1.HostName:= '127.0.0.1';
  45.     PQConnection1.DatabaseName:= 'dicomscp';
  46.     PQConnection1.UserName:= 'postgres';
  47.     PQConnection1.Password:= 'postgres';
  48.     PQConnection1.Transaction:= SQLTransaction1;
  49.     PQConnection1.Connected:= True;
  50.     // now we set the notice receiver callback
  51.     PQsetNoticeReceiver(PPGConn(PQConnection1.GetHandle), @MyNoticeReceiver, nil);
  52. end;
  53.  
  54. end.
  55.  

And that's it.
« Last Edit: March 31, 2019, 12:53:27 pm by martinrame »

Cyrax

  • Hero Member
  • *****
  • Posts: 727
Re: Capturing PostgreSql's Raise Notice from cammand line app
« Reply #2 on: March 31, 2019, 07:43:26 pm »
Code: Pascal  [Select]
  1. type
  2.   // we inherit TPQConnection to be able to access GetHandle's protected method.
  3.   TPQConnection = class(pqconnection.TPQConnection)
  4.   end;
  5.  

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
« Last Edit: March 31, 2019, 07:46:24 pm by Cyrax »