Unable to get the Delphi EventAlerter sample working with SQL Server 2012

Question

I need to detect database changes and thought the TFDEventAlerter component would be a good way to go. The Sample app provided with Delphi 10.2 Tokyo doesn't work for me. It works for other database types. Clicking on the Fire Events button does not get detected when hooked up to MS SQL. Can anyone suggest any changes to get this working? Alternatively, can you point me to a working example of this component with SQL Server?

Answer 1

FireDAC provides two ways how to consume SQL Server query notifications. Either you let FireDAC create _FD_EVENTS table that is being watched for an UPDATE of the field value on the row that is inserted for each registered event^[1] (section 2.1.), or specify a SELECT query whose resultset will be watched for any UPDATE since event registration^[1] (section 2.2.).

For using SQL Server query notifications implemented by FireDAC in general, you must first enable them for the used database (section 1.).

---

1. Enable notifications

For query notifications FireDAC implements you must have enabled Service Broker on a database that you'll be connected to. To check whether the Service Broker is enabled for your database you can execute query like this (with the possible results 0=Disabled, 1=Enabled):

SELECT is_broker_enabled FROM sys.databases WHERE name = 'MyDatabase'

If it's disabled, execute this query:

ALTER DATABASE MyDatabase SET ENABLE_BROKER;

Or, if you'll be having some pending transactions and the above query takes long time to execute, you can rollback all pending transactions and ALTER your database immediately:

ALTER DATABASE MyDatabase SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Ok, from now on let's assume you have the Service Broker enabled for your database and you have sufficient access rights to subscribe to notifications.

---

2.1. _FD_EVENTS UPDATE notification

When event defined by the <message> format is registered^[1], FireDAC creates the _FD_EVENTS table (if necessary) in the connected database and inserts into this table a row with the Name field value of the <message> (which is meant to be the unique event name) and a Value field value set to 0. An event is then triggered whenever the Value field value of that row is UPDATEd.

So, if you define your event like this:

FDEventAlerter1.Names.Clear;
{ create temporary, uniquely named queue and service }
FDEventAlerter1.Names.Add('QUEUE=?');
FDEventAlerter1.Names.Add('SERVICE=?');
{ define event in format '<message>', where:
  <message> - event name (passed as AEventName param of the OnAlert event) }
FDEventAlerter1.Names.Add('MyEvent');

You can trigger the event by e.g. by executing query like this (in reality, even the BIGINT value can overflow by query like that, so beware of using it in production code):

UPDATE _FD_EVENTS SET Value = Value + 1 WHERE Name = 'MyEvent';

Events defined by the <message> format can also be triggered by the Signal method from code (it internally builds the query like above to trigger the subscribed event).

---

2.2. Query resultset UPDATE notification

Another way is watching any UPDATE of a specific resultset returned by the given SELECT query at the time the event is registered^[1].

Let's have a table like this with two rows inserted:

CREATE TABLE Customers
(
   ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
   FirstName VARCHAR(50) NOT NULL
);

INSERT INTO Customers (FirstName) VALUES ('John');
INSERT INTO Customers (FirstName) VALUES ('Alice');

Now, to get a notification when the FirstName field value of any of those two rows changes, define the event format like this before you register the event^[1]:

FDEventAlerter1.Names.Clear;
{ create temporary, uniquely named queue and service }
FDEventAlerter1.Names.Add('QUEUE=?');
FDEventAlerter1.Names.Add('SERVICE=?');
{ define event in format 'CHANGE<index>=<message>;<SELECT query>', where:
  <index> - event index
  <message> - event name (passed as AEventName param of the OnAlert event)
  <SELECT query> - SELECT query whose resultset will be watched for UPDATE
                   changes; when this resultset changes, the event fires }
FDEventAlerter1.Names.Add('CHANGE1=MyEvent;SELECT FirstName FROM Customers');

To trigger the event you can then UPDATE value of the FirstName field of any row in the watched resultset, e.g.:

UPDATE Customers SET FirstName = 'Johnny' WHERE ID = 1;

Or:

UPDATE Customers SET FirstName = 'Alicia' WHERE ID = 2;

Or:

UPDATE Customers SET FirstName = 'Robert' WHERE ID = 1;
UPDATE Customers SET FirstName = 'Cathie' WHERE ID = 2;

But you won't be notified when you INSERT a new row and UPDATE it subsequently after the event is registered^[1], no matter that the defined resultset would actually include such row. So, this won't get you notified:

INSERT INTO Customers (FirstName) VALUES ('Barry');
UPDATE Customers SET FirstName = 'Barrie' WHERE ID = SCOPE_IDENTITY();

It means that you're actually creating sort of resulset snapshot that is being watched. The same is happening in case of events defined by the <message> format as well, only FireDAC creates table with one row that is then being watched for updates internally for you.

---

3. Minimal example

Here is a minimal example using <message> format. I assume that FDEventAlerter1 has assigned Connection that is using SQL Server Native Client driver and has created event handlers for OnAlert and OnTimeout events shown below:

procedure TForm1.ButtonStartClick(Sender: TObject);
begin
  FDEventAlerter1.Names.Clear;
  { create temporary, uniquely named queue and service }
  FDEventAlerter1.Names.Add('QUEUE=?');
  FDEventAlerter1.Names.Add('SERVICE=?');
  { define event in format '<message>', where:
    <message> - event name (passed as AEventName param of the OnAlert event) }
  FDEventAlerter1.Names.Add('MyEvent');
  { there's only one for SQL Server, <default> should equal to this by now }
  FDEventAlerter1.Options.Kind := 'QueryNotifies';
  { if Timeout > 0 and no event arrives in this number of milliseconds, the
    OnTimeout event is fired, we can use it as "heartbeat" }
  FDEventAlerter1.Options.Timeout := 10000;
  { execute event handlers in the main thread }
  FDEventAlerter1.Options.Synchronize := True;
  { start watching }
  FDEventAlerter1.Active := True;
end;

procedure TForm1.FDEventAlerter1Alert(ASender: TFDCustomEventAlerter;
  const AEventName: string; const AArgument: Variant);
begin
  { if AEventName matches <message> from the event definition }
  if SameText(AEventName, 'MyEvent') then
    ShowMessage('MyEvent fired!');
end;

procedure TForm1.FDEventAlerter1Timeout(Sender: TObject);
begin
  ShowMessage('No event was fired in 10 seconds!');
end;

And now try to trigger the above event. Execute this on SQL Server:

UPDATE _FD_EVENTS SET Value = Value + 1 WHERE Name = 'MyEvent';

Calling the Signal method from code should result in the same as executing above query, signalled event.

---

[1] - by registering event is meant either calling Register method, or enabling Active property.