Recent

Author Topic: SQLdb | Postgres >=10 \ cacth RAiSE NOTICE  (Read 1012 times)

cpalx

  • Hero Member
  • *****
  • Posts: 638
SQLdb | Postgres >=10 \ cacth RAiSE NOTICE
« on: February 28, 2020, 04:56:21 pm »
Hello,
I am using SQLdb (PQConnection) and postgres 10 with lazarus 2.0.6 (Linux)

I need to catch a RAISE NOTICE.

Example

Code: SQL  [Select][+][-]
  1. DO $$
  2. BEGIN  
  3.    RAISE NOTICE '{"id": % }', 4;
  4. END$$;
  5.  

i need to get {"id"; 4}

any idea?



dsiders

  • Sr. Member
  • ****
  • Posts: 315
Re: SQLdb | Postgres >=10 \ cacth RAiSE NOTICE
« Reply #1 on: February 28, 2020, 05:23:14 pm »
Hello,
I am using SQLdb (PQConnection) and postgres 10 with lazarus 2.0.6 (Linux)

I need to catch a RAISE NOTICE.

Example

Code: SQL  [Select][+][-]
  1. DO $$
  2. BEGIN  
  3.    RAISE NOTICE '{"id": % }', 4;
  4. END$$;
  5.  

i need to get {"id"; 4}

any idea?

Not when using PQConnection. The function library in packages/postgres/ has PQSetNoticeReceiver and PQSetNoticeProcessor though.
Lazarus 2.1 (SVN) / FPC 3.0.4 / FPC 3.2.0 / x86-win64 / Windows 8.1

cpalx

  • Hero Member
  • *****
  • Posts: 638
Re: SQLdb | Postgres >=10 \ cacth RAiSE NOTICE
« Reply #2 on: February 28, 2020, 06:08:17 pm »
i can do it now,  (https://forum.lazarus.freepascal.org/index.php?topic=44854.0)
but i have a new problem, I can public with writeln, but can not get to another variable using in my Class


Code: Pascal  [Select][+][-]
  1. ..
  2. TMyPQConnection = class(pqconnection.TPQConnection)
  3. ..
  4. public
  5. Answer Pchar;
  6. ..
  7.  
  8. procedure TForm1.Connect();
  9. begin
  10.   Postg:= TMyPQConnection.Create(nil);
  11.   Postg.DatabaseName:= 'zzzzz';
  12.   Postg.HostName:= '127.0.0.1';
  13.   Postg.UserName:= 'yyyy';
  14.   Postg.Password:= 'xxxxxxx';
  15.   Postg.Connected:= True;
  16.   SQLTransaction1.DataBase:= Postg;
  17.   SQLQuery1.DataBase:= Postg;
  18. end;
  19.  
  20. procedure MyNoticeReceiver(arg: pointer; res: PPGresult);cdecl;
  21. const PG_DIAG_MESSAGE_PRIMARY = 77;
  22. begin
  23.   //WriteLn( PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY) ); // This Work Fine
  24.    Form1.Answer:= PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY); // CAn assign but lost later
  25.  
  26.  
  27. end;
  28.  
  29. procedure TForm1.Button1Click(Sender: TObject);
  30. begin
  31.   Connect;
  32.   SQLQuery1.Close;
  33.   SQLQuery1.SQL.Text:= memo1.Text;
  34.   try
  35.      SQLQuery1.Prepare;
  36.      SQLQuery1.ExecSQL;
  37.        PQsetNoticeReceiver(PPGConn(Postg.GetHandle), @MyNoticeReceiver, nil);
  38.        ShowMessage( Answer ); //<-- Always empty
  39.   except on e: Exception do begin
  40.     Memo2.Text:= e.Message;
  41.   end;
  42.   end;
  43.   Postg.Free;
  44. end;
  45.                                                              
« Last Edit: February 28, 2020, 06:10:28 pm by cpalx »

sstvmaster

  • Full Member
  • ***
  • Posts: 162
Re: SQLdb | Postgres >=10 \ cacth RAiSE NOTICE
« Reply #3 on: February 28, 2020, 09:43:23 pm »
Have you tried this?
Code: Pascal  [Select][+][-]
  1. Form1.Answer:= PQresultErrorField(res, ord(PG_DIAG_MESSAGE_PRIMARY));

Found here: https://sourceforge.net/p/zeoslib/code-0/17/tree//trunk/src/plain/ZPlainPostgreSqlDriver.pas?diff=500986a671b75b2b8b001f0f:16 at the end.

Or you can try get the error direct, example code:
Code: Pascal  [Select][+][-]
  1. Answer := xxx.GetResultErrorField(Res, PG_DIAG_MESSAGE_PRIMARY);
« Last Edit: February 28, 2020, 09:58:12 pm by sstvmaster »
Windows 10 (64 bit)
Lazarus: 2.0.8 / Trunk x32

cpalx

  • Hero Member
  • *****
  • Posts: 638
Re: SQLdb | Postgres >=10 \ cacth RAiSE NOTICE
« Reply #4 on: February 28, 2020, 11:44:47 pm »
i tried

Quote
Code: Pascal  [Select][+][-]
  1.     Form1.Answer:= PQresultErrorField(res, ord(PG_DIAG_MESSAGE_PRIMARY));

but the same

What "xxx" means?

Quote
Code: Pascal  [Select][+][-]
  1.     Answer := xxx.GetResultErrorField(Res, PG_DIAG_MESSAGE_PRIMARY);

i dont use Zcomponents (i will like to, but it is imposible that works in cgi project)

sstvmaster

  • Full Member
  • ***
  • Posts: 162
Re: SQLdb | Postgres >=10 \ cacth RAiSE NOTICE
« Reply #5 on: February 29, 2020, 12:14:16 am »
So i don't know, how i can help you, sorry  :-\
« Last Edit: February 29, 2020, 12:19:38 am by sstvmaster »
Windows 10 (64 bit)
Lazarus: 2.0.8 / Trunk x32

 

TinyPortal © 2005-2018