How to receive notifications from PostgreSQL?

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
FCS
Posts: 166
Joined: Sat 23 Feb 2013 18:46

How to receive notifications from PostgreSQL?

Post by FCS » Wed 29 May 2019 18:49

Hello,

How to receive notifications from PostgreSQL using UniDac ?
I want to do it from code.

Regards
Michal

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: How to receive notifications from PostgreSQL?

Post by ertank » Wed 29 May 2019 21:28

Hello,

You can use TUniAlerter component.

Briefly;
-You write your notify channel name in TUniAlerter.Event property.
-Write your code to process paylod in TUniAlerter.OnEvent event.
-Preferably you can set AutoRegister to True for automatic listening of component. Or, You must set Active to True for manual start of listening.

For more details: https://www.devart.com/unidac/docs/deva ... lerter.htm

FCS
Posts: 166
Joined: Sat 23 Feb 2013 18:46

Re: How to receive notifications from PostgreSQL?

Post by FCS » Thu 30 May 2019 06:13

Hello,

Ertank thank you.

I need get notification from PostgreSQL in only one procedure, not in all application.

I try use this code, but I get errors:

Code: Select all

USES
  Windows, Classes, Forms, Controls, StdCtrls, Graphics, Dialogs, Messages, Variants,  SysUtils,
  AdvStyleif, TMS, ShellApi, jpeg,  CheckLst,
  Uni, DB,  DASQLMonitor, UniSQLMonitor, 
  DAAlerter,


procedure UniAlert(Sender:TDAAlerter; E:Exception);
begin
end;

procedure Analiza;
var
  UQ           : TUniQuery;
  UQ_Alert     : TDAAlerter;

begin
  UQ:=TUniQuery.Create(nil);
  UQ.Connection:=DM_01.UniConnection1;

  UQ_Alert := TDAAlerter.Create(nil);
  UQ_Alert.Connection := DM_01.UniConnection1;
  UQ_Alert.OnError    := [b]@UniAlert[/b];                       -> compilation error 
  UQ_Alert.Active     := true;
  UQ_Alert.Start;

  ... code of analysis 
 
  UQ_Alert.Active     := false;
  UQ_Alert.Stop;
  UQ_Alert.Free;
end;
I still use version 6.1.6, and I have not onEvent method in UniAlerter.
Should I add some more in uses declaration to get this method ?

Regards
Michal

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: How to receive notifications from PostgreSQL?

Post by ertank » Thu 30 May 2019 12:01

Your first problem is not related with UniDAC operation. It is a Delphi thing "procedure assignment at run-time".

You can do it as in below example. I have remarked some lines and removed some units in order to be able to compile it on my system. I am using Delphi 10.3.1.

Code: Select all

program Project2;

{$APPTYPE CONSOLE}

{$R *.res}

USES
  Windows, Classes, Messages, Variants,  SysUtils,
  ShellApi, Uni, DB,  DASQLMonitor, UniSQLMonitor,
  DAAlerter;

type
  TMyType = class
  public
    class procedure UniAlert(Sender:TDAAlerter; E:Exception);
  end;

class procedure TMyType.UniAlert(Sender:TDAAlerter; E:Exception);
begin
end;

procedure Analiza;
var
  UQ           : TUniQuery;
  UQ_Alert     : TDAAlerter;

begin
  UQ:=TUniQuery.Create(nil);
//  UQ.Connection:=DM_01.UniConnection1;

  UQ_Alert := TDAAlerter.Create(nil);
//  UQ_Alert.Connection := DM_01.UniConnection1;
  UQ_Alert.OnError    := TMyType.UniAlert;
  UQ_Alert.Active     := true;
  UQ_Alert.Start;

  UQ_Alert.Active     := false;
  UQ_Alert.Stop;
  UQ_Alert.Free;
end;

begin
end.
About not existing TUniAlert.OnEvent() event in UniDAC 6.1.6. I did not use that old UniDAC version and I cannot comment on that. Possibly Devart people address that question of yours.

Thanks & regards,
Ertan

FCS
Posts: 166
Joined: Sat 23 Feb 2013 18:46

Re: How to receive notifications from PostgreSQL?

Post by FCS » Thu 30 May 2019 18:51

Hello,

Thank you Ertank for the example, but it does not work.

At this moment the code is:

Code: Select all

Unit Test;
{$H-}

USES
  Windows, Classes, Forms, Controls, StdCtrls, Graphics, Dialogs,
  Messages, Variants,  SysUtils,
  Uni, DB,
  DASQLMonitor, UniSQLMonitor,
  DAAlerter, UniAlerter,

INTERFACE

USES
  Windows, Classes, Forms, Controls, StdCtrls, Graphics, Dialogs,
  Messages, Variants,  SysUtils,
  AdvStyleif, TMS, ShellApi, jpeg,
  CheckLst,
  Uni, DB,
  DASQLMonitor, UniSQLMonitor,
  DAAlerter, UniAlerter;

IMPLEMENTATION

type
  TMyClass = class
    {$H+}
    procedure UniAlert(Sender:TDAAlerter; const EventName:string; const Message:string);
    {$H-}
  end;

{$H+}
procedure TMyClass.UniAlert(Sender:TDAAlerter; const EventName:string; const Message:string);
begin
  Memo.Add(EventName+', '+Message);
  // Memo.MemoEnd;
end;
{$H-}

//---------------------------------------------------------------------------
procedure Analiza_Gemoetrii;
var
  UQ               : TUniQuery;
  UQ_Alert     : TUniAlerter;
  MyClass      : TMyClass;

begin

  UQ:=TUniQuery.Create(nil);
  UQ.Connection:=DM_01.UniConnection1;

  MyClass := TMyClass.Create;

  UQ_Alert := TUniAlerter.Create(nil);
  UQ_Alert.Connection := DM_01.UniConnection1;
  UQ_Alert.Events     := 'LISTEN';
  UQ_Alert.OnEvent    :=  MyClass.UniAlert;
  UQ_Alert.AutoRegister:= true;
  UQ_Alert.Active     := true;
  UQ_Alert.Start;
 
    ....
  
  UQ_Alert.Active     := false;
  UQ_Alert.Stop;
  UQ_Alert.Free;

The code compiles, but procedure MyClass.UniAlert is never called.

The notifications should by like this:

UWAGA: Self-intersection at or near point 6429969.9642239548 5950917.9311281741
UWAGA: Self-intersection at or near point 6452077.6754628168 5964164.2918521771
UWAGA: Self-intersection at or near point 6461595.7107596546 5941361.9610327957
UWAGA: Self-intersection at or near point 6437919.798975829 5974191.5072886562

as a result of geometry validation by st_isvalid() PostGis function.

The query I use give these results ie in PgAdmin.

Regards
Michal

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: How to receive notifications from PostgreSQL?

Post by ertank » Fri 31 May 2019 18:58

You can try below. I have added remarks in it.

Code: Select all

Unit Test;
{$H-}

USES
  Windows, Classes, Forms, Controls, StdCtrls, Graphics, Dialogs,
  Messages, Variants,  SysUtils,
  Uni, DB,
  DASQLMonitor, UniSQLMonitor,
  DAAlerter, UniAlerter,

INTERFACE

USES
  Windows, Classes, Forms, Controls, StdCtrls, Graphics, Dialogs,
  Messages, Variants,  SysUtils,
  AdvStyleif, TMS, ShellApi, jpeg,
  CheckLst,
  Uni, DB,
  DASQLMonitor, UniSQLMonitor,
  DAAlerter, UniAlerter;

IMPLEMENTATION

type
  TMyClass = class
  public
    {$H+}
    procedure UniAlert(Sender:TDAAlerter; const EventName:string; const Message:string);
    {$H-}
  end;

{$H+}
procedure TMyClass.UniAlert(Sender:TDAAlerter; const EventName:string; const Message:string);
begin
  Memo.Add(EventName+', '+Message);
  // Memo.MemoEnd;
end;
{$H-}

//---------------------------------------------------------------------------
procedure Analiza_Gemoetrii;
var
  UQ               : TUniQuery;
  UQ_Alert     : TUniAlerter;
  MyClass      : TMyClass;
begin
  UQ:=TUniQuery.Create(nil);
  UQ.Connection:=DM_01.UniConnection1;

//  MyClass := TMyClass.Create;  // You do not need to create that dummy class. It is only to help you assign a procedure to your event

  UQ_Alert := TUniAlerter.Create(nil);
  UQ_Alert.Connection := DM_01.UniConnection1;
  UQ_Alert.Events     := 'LISTEN'; // This must be your notification name. I am not sure if LISTEN is correct here.
  UQ_Alert.OnEvent    :=  TMyClass.UniAlert; // Here you can directly assign TMyClass.UniAlert procedure to your OnEvent event.
  UQ_Alert.AutoRegister:= true;
  UQ_Alert.Active     := true;
  UQ_Alert.Start;
 
// you will only receive notifications here.
// and only those notification names match to your above UQ_Alert.Events property.
    ....
  
  UQ_Alert.Active     := false;  // At this point you cannot receive notifications
  UQ_Alert.Stop;
  UQ_Alert.Free;
end;
I would suggest to;
- assign "my_test_name" to your UQ_Alert.Events property.
- After that, Run your program and keep it in listen mode. (Execute "UQ_Alert.Start" and keep it that way)
- Open up a PgAdmin SQL window.
- execute below command

Code: Select all

NOTIFY my_test_name, 'my payload';
You should read "my payload" in your application memo.

FCS
Posts: 166
Joined: Sat 23 Feb 2013 18:46

Re: How to receive notifications from PostgreSQL?

Post by FCS » Sat 01 Jun 2019 08:11

Hello,

Ertank, thanks for your help. One step forward.

When I set Events property for my_test_name and then execute from PgAdmin

NOTIFY my_test_name, 'my payload';

the my payload text appear in the memo as you described.

But I still can't catch console warning generated by st_isvalid function. It must be something more to set.

At this the named notifications work. The Event property seams to be like a filter.

Regards
Michal

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: How to receive notifications from PostgreSQL?

Post by ertank » Sat 01 Jun 2019 14:36

FCS wrote:
Sat 01 Jun 2019 08:11
But I still can't catch console warning generated by st_isvalid function. It must be something more to set.
You need to know more about st_isvalid() function internals. What event name (channel) it uses, etc. After that use same event name in your TUniAlert.Events property to catch these notifications.

FCS
Posts: 166
Joined: Sat 23 Feb 2013 18:46

Re: How to receive notifications from PostgreSQL?

Post by FCS » Sat 01 Jun 2019 19:33

Hello,

I begin to think, that UniAlerter is not proper component for this job.

In Zeos components there is the ZSQLMonitor which has methods called onTrace and onLogTrace. Both of them returns the necessary information. They catch the PostgreSQL console output.
The UniDac UniSQLMonitor has only onSQL event which does not catch console output (Or I don't know how to do this).

The notification is designed to communication between clients and servers to send or receive messages using named channels. For example updating a dictionary table can send notification to rest clients to reread this table contents.
It could be made in the trigger after update statement.

Regards
Michal

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: How to receive notifications from PostgreSQL?

Post by ertank » Sun 02 Jun 2019 00:46

Hello,

UniDAC has built in support for PostgreSQL, SQLite, SQL Server, Oracle. Your application do not need a client driver installed for these.

I myself would like to have such a support in a single EXE file.

Thanks & regards,
Ertan

FCS
Posts: 166
Joined: Sat 23 Feb 2013 18:46

Re: How to receive notifications from PostgreSQL?

Post by FCS » Sun 02 Jun 2019 08:19

Hello,

I know that UniDac does not need any external libraries to connect to PosgreSQL. This is the point I prefer UniDac then FireDac or any other components which required libpq,dll external library to connect to database.

I told about Zeos, because I tested these components for problem described in this topic.

The main problem is how to receive notification from PostgreSQL database console using UniDac ?

Meanwhile I installed the current version of UniDac on Lazarus and tested it. Results are the same.

Regards
Michal

MaximG
Devart Team
Posts: 1438
Joined: Mon 06 Jul 2015 11:34

Re: How to receive notifications from PostgreSQL?

Post by MaximG » Fri 07 Jun 2019 13:19

The functionality you described is available in our PgDAC product: https://www.devart.com/pgdac/docs/devar ... notice.htm
However, you can only get such messages in the scope of the same connection.

FCS
Posts: 166
Joined: Sat 23 Feb 2013 18:46

Re: How to receive notifications from PostgreSQL?

Post by FCS » Sat 08 Jun 2019 16:05

Hello,

Thank you for the information.

Regards
Michal

Post Reply