Data Change Notifications (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with Commands (FireDAC)


This topic describes basic steps to use notifications on data changes for some databases.


General

The Data Change Notifications feature tracks changes to data in a database (such as insert, update, and delete operations) and notifies the database client about these changes.

The Change Notification method depends on a particular DBMS. This topic describes this feature for InterBase. For more information about other databases, see Database Alerts.

InterBase Change Views

The Change Views™ feature uses InterBase multigenerational architecture to track changes to data. This feature allows you to quickly answer the question, "What data has changed since I last viewed it?".

Previously it involved triggers, logging, and/or transaction write-ahead log scraping. This was time-consuming for the developer and affected the database performance for a certain transaction load or change volume. Now with Change Views, there is no performance overhead on existing transactions because it maintains a consistent view of changed data observable by other transactions.

For more information, see Change Views

Using the Change Views Feature

For InterBase XE7, the notification system can be implemented using the TFDEventAlerter component (database event alerters).

This section illustrates basic steps to create a demo VCL application ( Delphi and C++Builder) that tracks changes to InterBase tables. The following subjects are covered:

  • Creation of a demo InterBase database and a subscription to Change Views
  • Connecting to an InterBase database
  • Displaying and editing the records from the database
  • Subscribing to data change notifications
  • Handling the data change events

Setup Change Views Database

Before developing the demo application, you should create a demo database and a subscription to Change Views.

RAD Studio Setup provides the create.sql script that helps you perform the above mentioned operations. To clarify, consider the following code snippets of this script that create the InterBase demo database and a subscription to Change Views: 

/* Create a demo database */
CREATE DATABASE 'C:\SUB.IB'
USER 'SYSDBA' PASSWORD 'masterkey'
DEFAULT CHARACTER SET UTF8;

/* Create a trigger that fires after updating the 'tab' table */
CREATE TRIGGER tr_tab_after_upd FOR tab
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
  POST_EVENT 'TAB';
END


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

/* Insert data to the 'tab' table */
INSERT INTO tab (name) VALUES ('Alex Simpson');
INSERT INTO tab (name) VALUES ('Nicole Burns');
INSERT INTO tab (name) VALUES ('Peter Pauls');

Location of the create.sql script: C:\Users\Public\Documents\Embarcadero\Studio\17.0\Samples\Object Pascal\Database\FireDAC\Samples\DBMS Specific\InterBase\ChangeView\Generic\connect.sql

To run the create.sql script

  1. Switch to the C:\Users\Public\Documents\Embarcadero\Studio\17.0\Samples\Object Pascal\Database\FireDAC\Samples\DBMS Specific\InterBase\ChangeView\Generic folder.
  2. At the command prompt, type the following syntax: 
    isql.exe -i create.sql

Note By default, RAD Studio Setup installs the isql.exe to the C:\Program Files (x86)\Embarcadero\Studio\16.0\InterBaseXE7\bin\

This script creates the C:\sub.ib InterBase database, the 'sub' subscription to view changes, and triggers that fire after changes made to the 'tab' table of this database.

Setup the Change View Demo Application

You can now create and configure a demo application that tracks changes to the C:\sub.ib database. The application form is similar to the following screen:

IB Connect VCL form.png

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. Follow these instructions to develop a user interface similar to the above screen.

To configure the FireDAC components settings, complete the following steps:

  1. On the Form Designer, right-click FDConnection1, select Connection Editor on the shortcut menu, and in FireDAC Connection Editor, do the following:
    • Go to the Definition tab, and from the Driver ID list, select IB.
    • Specify the following parameters of connection, and then click OK:
      • Database = C:\SUB.IB
      • User_Name = sysdba
      • Password = masterkey
      • Protocol = TCPIP
      • Server = 127.0.0.1
  2. Select FDQuery1, and specify the following properties in the Object Inspector:
    • ChangeAlerter = FDEventAlerte1
    • Connection = FDConnection1
    • ChangeAlertName =tab - it is always the name of table where to track changes.
    • SQL = select * from tab
  3. Select DataSource1 and set its property DataSet to FDQuery1
  4. Select FDEventAlerter1, and set its property SubscriptionName to sub.
  5. Select DBGrid1, and set its property DataSource to DataSource1

Now you should configure the OnClick event handler for the Open DB button that opens the demo dataset and enables tracking changes to the database tables.

To implement the OnClick event handler for the Open DB button

  1. On the Form Designer, double-click the Open DB button.
  2. In the Code Editor, specify the following code:

    Delphi:

    procedure TForm1.Button1Click(Sender: TObject);
begin
      FDQuery1.Active := True;
      FDQuery1.ChangeAlerter.Active :=True;
end;

    C++Builder: 

    void __fastcall TForm1::Button1Click(TObject *Sender)
{
FDQuery1->Active = true;
FDQuery1->ChangeAlerter->Active = true;
}

Responding 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:

  • TFDEventAlerter.Options.AutoRefresh
  • TFDEventAlerter.Options.Timeout
  • TFDEventAlerter.Options.MergeData

The demo project IBChangeView illustrates how to use the above mentioned scenario. To access this project, open Start | Programs | Embarcadero RAD Studio 10 Seattle | Samples and navigate to Object Pascal\Database\FireDAC\Samples\DBMS Specific\InterBase\ChangeView\Generic

In our scenario, we use the TFDCustomEventAlerter.OnAlert event handler that fires after detecting any changes made to the demo dataset. In this event handler, you can implement your custom scenario to respond to a data change notification.


To implement the OnAlert event handler

  1. On the Form Designer, select FDEventAlerter1.
  2. In the Object Inspector, open the Events tab, and double-click next to OnAlert.
  3. In the Code Editor, specify the following code:

    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. ");
}

Running the Sample Application

To run this application, do the following:

  1. In the Project Manager, select the target platform (supported platforms: Win32 or Win64).
  2. Press Shift+Ctrl+F9 to run the application without debugging.

To test your application, you can use the following scenario:

  1. Click the Open DB button.
  2. Make any changes to any row in the Name column.
  3. To commit your changes, click other row.

After detecting your changes, the application displays the information message box:

InterBase test.png

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
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

Demo Projects

For more information, see the following demo projects:

  • Start | Programs | Embarcadero RAD Studio 10 Seattle | Samples and navigate to Object Pascal\Database\FireDAC\Samples\DBMS Specific\InterBase\ChangeView\Generic
  • Start | Programs | Embarcadero RAD Studio 10 Seattle | Samples and navigate to Object Pascal\Database\FireDAC\Samples\DBMS Specific\MSSQL\QueryNotify

See Also

Retrieved from "http://docwiki.embarcadero.com/RADStudio/Seattle/e/index.php?title=Data_Change_Notifications_(FireDAC)&oldid=245284"