Delphi and database events

Home → Knowledgebase → Delphi → Delphi and database events

Delphi and Microsoft SQL Server events

Database events are a very convenient way to let applications know that a certain event has occurred. Instead of having the application constantly polling the database, you get an event at exactly the right moment to let you know from the database that you need to take action. For example, suppose an application makes a change in a specific customer record, which causes another application to perform an action. The other application only has to wait for the specific event and does not have to continuously execute an SQL statement to see if the record has been modified.

FDEventAlerter

Within Delphi (or rather within FireDAC) you can solve this by using the component FDEventAlerter. This component is made to ‘listen’ to events within a database environment. The beauty of the FDEventAlerter component is that it is designed to provide one interface, independent of which database you use. With this interface, you can decide whether you wait for a specific event, or for a change in a specific record in a table.

Example with Microsoft SQL Server

In the example below, we are using a Microsoft SQL Server. In order to send the events with Microsoft SQL Server, you can use Query Notifications.

To create these Query Notifications, we can use the following code:


-- Create the queue and the service
CREATE QUEUE [NotificationsQueue];
CREATE SERVICE [NotificationsService] ON QUEUE [NotificationsQueue] (
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
);
GRANT SEND ON SERVICE::[NotificationsService] TO [];

-- Permissions needed for []
GRANT SELECT to []
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO []
GRANT RECEIVE ON QueryNotificationErrorsQueue TO []
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to []
GRANT RECEIVE ON [NotificationsQueue] TO []
GRANT VIEW DEFINITION TO []


Once these things are set up, we can implement the event system in Delphi. You do this by placing the FDEventAlerter component on a form or data module and connect it to your database with the Connection property. Also, you have to set the queue, service and change events with the Names property:

FDEventAlerter.Connection := ''
FDEventAlerter.Names.Add('QUEUE=NotificationsQueue');
FDEventAlerter.Names.Add('SERVICE=NotificationsService');

Then you can use the component’s OnAlert event handler to catch the events and take action using the code below:

procedure TFrmMain.FDEventAlerterAlert(ASender: TFDCustomEventAlerter; const AEventName: string;
  const AArgument: Variant);
begin
  mmLog.Lines.Add('Event - [' + AEventName + '] - [' + AArgument + ']');
end;

Specifically for Microsoft SQL Server, you must turn on the service-broker (ALTER DATABASE <your db name> SET ENABLE_BROKER), otherwise no events are shared with the queue and thus no alerts are captured by the FDEventAlerter. This is also described in https://docwiki.embarcadero.com/RADStudio/Sydney/en/Database_Alerts_(FireDAC) for Microsoft SQL Server.

For each user of an FDEventAlerter a queue and a service is needed. The FDEventAlerter will create this queue and service on the default schema of the logged-in user. Therefore it is necessary to have rights for the logged-in user to create these. The queue and service will be assigned a unique name when not provided. After closing or unregistering the FDEventAlerter, the component clears the same created unique queue and service.