How to receive notifications from PostgreSQL?

[FCS]

Hello,

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

Regards
Michal

[ertank]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Hello,

Thank you for the information.

Regards
Michal