Data Change Notifications (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with Commands (FireDAC)


The Data Change Notifications feature can be used to notify database clients on data changes for some databases. This feature tracks changes to data in a database (such as INSERT, UPDATE or DELETE operations) and notifies the database client about these changes.

FireDAC supports the Change Notifications feature for different databases (see Change Notification Support). However, FireDAC uses different methods to implement this feature depending on the kind of database. This topic describes the general information of the FireDAC Data Change Notifications feature and then give some considerations for specific DBMS implementations.

For more information about database alert mechanisms, see Database Alerts.

Set up a FireDAC Data Change Notifications feature

To set up the Data Change Notifications feature on a FireDAC application, you should include and configure the following main components:

  • A TFDConnection object - This is the FireDAC connection object that is used to connect to a DBMS.
    Note: This section assumes that you have read and understood First Steps to Use FireDAC, which provides instructions on how to develop a simple VCL application to connect to demo databases.
  • A TFDQuery object - This is the FireDAC object used to implement datasets capable of executing SQL queries. You should configure the following basic properties of the SQL query that is being tracked:
    • The Connection property has to be set in order to specify the FireDAC connection object.
    • The SQL property has to be set with the desired SQL statement.
    • The ChangeAlerter property has to be set in order to specify the FireDAC alerter object associated with your dataset.
    • The ChangeAlertName property can be set in order to specify the name of the base table whose changes are being tracked.
  • A TFDEventAlerter object - This is the objects used to handle the database change notifications. You should configure the following basic properties of the event alerter:
    • The Options property can be used to specify a set of properties to control the event alerter behavior.
    • The SubscriptionName property has to be set in order to specify the subscription name of the change notification. It depends on the type of database that you are using.
      Note: Activate TFDEventAlerter before executing the tracked TFDQuery. This is because the statement must be registered with query notification API before its execution. This is a requirement of SQL Server and Oracle.

Responses to Change Notifications

Upon receiving a data change notification, FireDAC automatically fetches the changes and updates the dataset. This process is controlled by the following properties of the TFDEventAlerter component:

Any of the demo projects listed on Data Change Notifications for Specific DBMS illustrates how to use the above mentioned scenario.

Moreover, you can use the TFDCustomEventAlerter.OnAlert event handler, which is fired after detecting any changes made to the tracked dataset. This event handler allows you to implement your custom scenario to respond to a data change notification. The following code implements an OnAlert event handler that shows a message when any data on the database is changed.

Delphi:

procedure TForm1.FDEventAlerter1Alert(ASender: TFDCustomEventAlerter;
  const AEventName: string; const AArgument: Variant);
begin
ShowMessage('Detected changes to the demo dataset.');
end;

C++Builder: 

void __fastcall TForm1::FDEventAlerter1Alert(TFDCustomEventAlerter *ASender, const UnicodeString AEventName,
          const Variant &AArgument)
{
  ShowMessage("Detected changes to the demo dataset. ");
}

Change Notification Support

The following table summarizes information about the Change Notification mechanisms used for each DBMS.

DBMS Change notification type Delta type TFDEventAlerter.SubscriptionName
InterBase Event Alerter (manual notification) Change Views (automatic delta) InterBase Change View subscription name
MS SQL Server Query Update Notification (automatic notification) None (full dataset refreshing) Notification message text
Oracle DBMS_ALERT / DBMS_PIPE (manual notification) None (full dataset refreshing) Notification message text
Continuous Query Notification (CQN) Automatic Delta Any value
Advantage Database Event Alerter (manual notification) None (full dataset refreshing) Notification message text
Sybase SQL Anywhere Messages (manual notification) None (full dataset refreshing) Notification message text
DataSnap server Callbacks (manual notification) None (full dataset refreshing) Notification message text
DB2 DBMS_ALERT / DBMS_PIPE (manual notification) None (full dataset refreshing) Notification message text
Firebird Event Alerter (manual notification) None (full dataset refreshing) Notification message text
Informix DBMS_ALERT (manual notification) None (full dataset refreshing) Notification message text
PostgreSQL Event notification (manual notification) None (full dataset refreshing) Notification message text
SQLite Event Alerter (manual notification) None (full dataset refreshing) Notification message text


Data Change Notifications for Specific DBMS

This section gives some specific considerations for implementing a Data Change Notifications feature for different databases.

InterBase Change Views

The Change Views feature is the Interbase method for implementing Data Change Notification. For more information, see Change Views.

Before developing an InterBase application that uses the Change Views feature you should create a subscription to Change Views. The following piece of code is an example of how to create the subscription: 

/* Create the xxx subscription to track changes to the 'yyy' table */
CREATE SUBSCRIPTION xxx ON yyy FOR ROW (INSERT, UPDATE, DELETE);

For more information of how to configure the the Change Views feature, you can find a demo project at:

  • Start | Programs | Embarcadero RAD Studio 10.2 Tokyo | Samples and navigate to Object Pascal\Database\FireDAC\Samples\DBMS Specific\InterBase\ChangeView\Generic

Oracle CQN

The Continuous Query Notification (CQN) feature is the Oracle implementation of The Data Change Notification feature. For more information, see Continuous Query Notification.

To configure the Oracle CQN feature you should take into account the following considerations:

  • The Oracle client must have the change notification privilege. The following piece of code is an example of how to give the user this privilege:
/* Give change notification privilege to the xxx Oracle user */
GRANT DBMS_CHANGE_NOTIFICATION TO xxx;
  • The SQL property should include the ROWID columns into the SELECT list for the base table. For example: SELECT t.ROWID, t.* FROM table t.
    Note: The ROWID identifier is a Unique Identifying Fields (FireDAC) for a row in a database. It is created at the time the row is inserted into the table, and destroyed when it is removed. ROWID values have several important uses, for example, they are the fastest way to access a single row.
  • The following properties of the TFDEventAlerter object should be configured as follows:
    • Set the Options.Kind property to QueryNotifies.
    • Set the SubscriptionName property to any value. This property cannot be empty. Otherwise the query will be fully refreshed instead of incrementally.

For more information of how to configure the Oracle CQN feature, see the FireDAC Oracle CQN sample.

Oracle Known Issues

Some versions of 11.2 client software are unable to receive change notifications, OCI Callbacks or Advanced Queuing (AQ) notifications against some versions of 11.2 database. This is an Oracle known issue. You can find more information in the following Oracle page.

Microsoft SQL

For more information of how to configure the the Data Change notifications feature for a MSSQL database, you can find a demo project at:

  • Start | Programs | Embarcadero RAD Studio 10.2 Tokyo | Samples and navigate to Object Pascal\Database\FireDAC\Samples\DBMS Specific\MSSQL\QueryNotify

See Also

Retrieved from "https://docwiki.embarcadero.com/RADStudio/Tokyo/e/index.php?title=Data_Change_Notifications_(FireDAC)&oldid=258744"