Database Alerts (FireDAC)

Go Up to Working with Commands (FireDAC)

The DBMS alert refers to a database notification or alert sent by a database trigger or stored procedure with the purpose of notifying a database client about some events at database side.

An alert is identified by name and can include additional arguments. The clients are registering with alerts. Multiple clients can register with a single alert, and one client can register with multiple alerts. When an alert is signaled in a database, all registered clients will be notified about that. When an alert is not used anymore, the application unregisters from that alert.

Classic examples of alerts are:

• A table data change. In this case, an application refreshes a dataset returning this table data.
• A notification about some condition in data being met.
• A notification sent to other applications, that a specific application will perform some special task, such as data archiving or backup.

Each DBMS implements DBMS alerts on their own. There is no standard for alerts mechanisms.

Using TFDEventAlerter

FireDAC offers unified alerts API as the TFDEventAlerter component. Some DBMS may implement few DBMS alert mechanisms. Each TFDEventAlerter object listens for several alerts by specifying their names in the TFDEventAlerter.Names property, and using a single mechanism specified by the Options.Kind property.

FireDAC listens for alerts in a background thread using an additional private connection to the database. This additional connection is created automatically by FireDAC for each TFDEventAlerter component. When the application creates multiple TFDEventAlerter objects, consider using pooled connections to improve the performance.

To start receiving the event alerts, fill in the TFDEventAlerter.Names property with the required event names. Set Options.Kind to the event alerter type or leave it empty to use the default alerter. Specify the OnAlert event handler that will be fired when an event occurs, and set Active to True or call the Register method. To stop receiving the event alerts, set Active to False or call Unregister.

The OnAlert event handler can be called in the main or background thread contexts. Use the Options.Synchronize property to control that.

Note: The application should minimize the runtime of the background thread handler.

The application sets timeout for alerts by specifying the Options.Timeout property. When there are no alerts for a specified period of time, the OnTimeout event handler is called.

For example, to register for the "Customers" alert using the "DBMS_ALERT" mechanism on Oracle database and the standard Firebird mechanism, use the following code:

FDEventAlerter1.Names.Clear;
FDEventAlerter1.Names.Add('Customers');
case FDConnection1.RDBMSKind of
  mkOracle:    FDEventAlerter1.Options.Kind := 'DBMS_ALERT';
  mkInterbase: FDEventAlerter1.Options.Kind := 'Events';
end;
FDEventAlerter1.Options.Synchronize := True;
FDEventAlerter1.Options.Timeout := 10000;
FDEventAlerter1.OnAlter := DoAlert;
FDEventAlerter1.OnTimeout := DoTimeout;
FDEventAlerter1.Active := True;

// …

procedure TForm1.DoAlert(ASender: TFDCustomEventAlerter;
  const AEventName: String; const AArgument: Variant);
begin
  if CompareText(AEventName, 'Customers') = 0 then
    qryCustomers.Refresh;
end;

procedure TForm1.DoTimeout(ASender: TObject);
begin
  // …
end;

And the server-side code for Oracle:

CREATE OR REPLACE TRIGGER TR_CUSTOMERS
AFTER INSERT OR UPDATE OR DELETE ON CUSTOMERS
BEGIN
  SYS.DBMS_ALERT.SIGNAL('Customers', '123');
END;

For Firebird, use the following code:

CREATE TRIGGER TR_CUSTOMERS FOR CUSTOMERS
ACTIVE AFTER INSERT OR UPDATE OR DELETE
BEGIN
  POST_EVENT 'Customers';
END;

DBMS Alert Mechanisms

As noted before, every DBMS implements database alerts on its own. The alter mechanism type is identified by the TFDEventAlerterOptions.Kind property value. If this is empty, the default mechanism is used. For most mechanisms, the client-side functionality is the same, only the database-side functionality is different.

The following table lists the DBMS and their alter mechanisms, supported by the FireDAC drivers:

DBMS	Event Alerter Kind	Description
Advantage Database	Events (*)	Is used the standard Events (Notifications) functionality. To initiate an event, use sp_SignalEvent stored procedure. For example: sp_SignalEvent('Customers', true, 0, '123'); Note: sp_SignalEvent can be called only from a stored procedure or a trigger. It cannot be called directly from the SQL command.
Sybase SQL Anywhere	Message (*)	The MESSAGE statement functionality is used. To initiate an event, a specially formatted message of this kind: _FD_$$<event name>[$$<argument>] must be sent. For example: MESSAGE '_FD_$$Customers$$123'
DataSnap server	Callbacks (*)	DataSnap “heavyweight” callbacks are used (for more information, see Delphi Labs: DataSnap XE - Callbacks). The TFDEventAlerter.Names content should have one of the following formats: <channel name>. FireDAC will register a callback for the specified channel. Use TDSAdminClient.BroadcastToChannel to broadcast an event to all TADEventAlerters registered for the specified channel. <channel name>=<callback name>. FireDAC will register a callback with the specified name for the specified channel. If you want to initiate an event, see above.
DB2	DBMS_ALERT (*)	The DBMS_ALERT package is used. Before the usage, a DBA must execute GRANT EXECUTE ON DBMS_ALERT TO <user or group>. To initiate an event, use the DBMS_ALERT.SIGNAL call. For example: CALL DBMS_ALERT.SIGNAL('Customers', '123');
	DBMS_PIPE	The DBMS_PIPE package is used. Before the usage, a DBA must execute GRANT EXECUTE ON DBMS_PIPE TO <user or group>. To initiate an event, use the DBMS_PIPE.SEND_MESSAGE call. For example: BEGIN CALL DBMS_PIPE.PACK_MESSAGE(123); CALL DBMS_PIPE.SEND_MESSAGE('Customers'); END;
Firebird	Events (*)	The standard Firebird mechanism for event notifications is used. To initiate an event, use the POST_EVENT <name> statement. For example: EXECUTE BLOCK AS BEGIN POST_EVENT 'Customers'; END;
Informix	DBMS_ALERT (*)	The DBMS_ALERT package is used from External Compatibility Package. To initiate an event, use the DBMS_ALERT_SIGNAL call. For example: EXECUTE PROCEDURE DBMS_ALERT_SIGNAL('Customers', '123')
Interbase	Events (*)	The standard InterBase mechanism for event notifications is used. To initiate an event, use the POST_EVENT <name> statement from a trigger or a stored procedure. Client-side initiating is not supported.
Microsoft SQL Server	QueryNotifies (*)	The Query Update Notification service is used. The TFDEventAlerter.Names content should have one of the following formats: CHANGE<index>=<message>;<SELECT query>. The event is fired when the data returned by the SELECT query is be updated and the <message> is returned as the event name. To fire an event, an UPDATE statement against the selected data must be executed. <message>. FireDAC creates the _FD_EVENTS table. The event is fired when the VALUE of the NAME=<message> row is updated. To fire an event, an UPDATE statement must be performed. Additionally, these parameters can be specified in the Names: SERVICE=<name>. The name of the service to use. '?' means creating a uniquely-named service and dropping it after usage. QUEUE=<name>. The name of the message queue to use. '?' means creating a uniquely-named queue and dropping it after usage. Note: To enable the query notification, execute the following command: ALTER DATABASE <your db name> SET ENABLE_BROKER
MongoDB	Tail (*)	Uses a tailable cursor on a given capped collection to alert about insertions. The Names property of the alerter must contain a single value using one of the following formats to indicate the target capped collection: <database>.<collection> <database>.<collection>=<size> <collection> <collection>=<size> Where: <database> is the name of the database that contains the target capped collection. The default database name is "test". <collection> is the name of the target capped collection. If the target capped collection does not exist, FireDAC creates a new capped collection with the specified database name, collection name and number of documents (<size>). The default size is 1000 documents. When an insertion occurs, your handler for the OnAlert event receives the inserted document as a JSON string (AArgument). AEventName is the value of the "name" field of the inserted document.
Oracle	DBMS_ALERT (*)	The DBMS_ALERT package is used. Before the usage, a DBA must execute GRANT EXECUTE ON DBMS_ALERT TO <user or group>. To initiate an event, use the DBMS_ALERT.SIGNAL call. For example: BEGIN SYS.DBMS_ALERT.SIGNAL('Customers', '123'); END;
	DBMS_PIPE	The DBMS_PIPE package is used. Before the usage, a DBA must execute GRANT EXECUTE ON DBMS_PIPE TO <user or group>. To initiate an event, use the DBMS_PIPE.SEND_MESSAGE call. For example: BEGIN SYS.DBMS_PIPE.PACK_MESSAGE(123); SYS.DBMS_PIPE.SEND_MESSAGE('Customers'); END;
	QueryNotifies	The Continuous Query Notification (CQN) feature is the Oracle implementation of The Data Change Notification feature. For more information, see Oracle CQN and Continuous Query Notification.
PostgreSQL	Notifies (*)	The standard event notification mechanism is used. To initiate an event, use the NOTIFY <name> statement. PostgreSQL 9.0 supports payload arguments, use NOTIFY <name> [, <paylod>]. For example: NOTIFY Customers
SQLite	Events (*)	The POST_EVENT custom function is used. To initiate an event, use POST_EVENT(<name>, [arg1 [,arg2 [,arg3 [,arg4]]]]). For example: SELECT POST_EVENT('Customers', 123)

Note: The asterisk marks the default event alerter kind.

See Also

• Multithreading
• TFDEventAlerter sample application