FireDAC.Oracle CQN Sample

From RAD Studio Code Examples
Jump to: navigation, search

This sample demonstrates the FireDAC support for the Oracle Continuous Query Notification (CQN) feature.

Warning: 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. For more information, click here.

Location

You can find the Oracle_CQN project at:

  • Start | Programs | Embarcadero RAD Studio Athens | Samples and then navigate to:
    • Object Pascal\Database\FireDAC\Samples\DBMS Specific\Oracle\CQN
  • Subversion Repository:
    • You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.

Description

The Oracle_CQN sample shows you how to use Continuous Query Notification with Oracle databases using FireDAC. To this end, this sample connects a client with CHANGE NOTIFICATION privilege to an Oracle database server and uses an event alerter to notify about database table data changes.

The implementation of this sample takes into account the following considerations listed in Oracle CQN section of the Data Change Notifications topic of FireDAC.

How to Use the Sample

  1. Navigate to the location given above and open Oracle_CQN.dproj.
  2. Click the Params property of conOriginal in the Object Inspector and adjust the connection parameters.
  3. Press F9 or choose Run > Run.
  4. Interact with the sample:
    • Click Open DB button.
    • Modify the data in the first grid.
    • Use the comboboxes above the second grid to choose different modes.
    • Use the buttons above the third grid to merge, commit and undo the changes.

Files

File in Delphi Contains

Oracle_CQN.dproj
Oracle_CQN.dpr

The project itself.

ChangeFrm.pas
ChangeFrm.dfm

The main form.

Implementation

Design Time

Before running the sample, the main components are configured at design time using the Object Inspector as follows:

  • Two TFDConnection objects named conOriginal and conChanges. These are the FireDAC connection objects that the sample use to connect to a DBMS. The sample sets the ConnectionDefName property of conOriginal to Oracle_Demo.
Notes:
  • You can change the ConnectionDefName property to connect to a different Oracle server using a valid user name and password.
  • The Params properties of conChanges are copied from conOriginal on run-time.
  • Two TFDQuery objects named qOriginal and qChanges. These component are used to implement datasets capable of executing SQL queries.
    • qOriginal setup:
      • The Connection property is set to conOriginal in order to specify the FireDAC connection object.
      • The SQL property contains the following SQL SELECT statement: SELECT * FROM "FDQA_TransTable".
    • qChanges setup:
      • The Connection property is set to conChanges in order to specify the FireDAC connection object.
      • The SQL property contains the following SQL SELECT statement: SELECT t.ROWID, t.* FROM "FDQA_TransTable" t.
      • The ChangeAlerter property is set to eaChanges in order to specify the event alerter that tracks this query.
  • A TFDEventAlerter object named eaChanges. This component is used to handle the database change notifications. The sample configures the following properties as follows:
    • The Options.Kind property is set to QueryNotifies.
    • The SubscriptionName property is set to sub.
      Warning: The SubscriptionName property has to be set to any value. It cannot be empty or the query will be fully refreshed instead of incrementally.
  • Three TDataSource objects named dsOriginal, dsChanges and dsRemote. These components provide interfaces between a dataset component and data-aware controls on a form. In this sample, these objects are used to provide communication between the datasets and the grids where the datasets are displayed. To this end, the sample sets the following properties:
  • The DataSet property of dsOriginal, dsChanges and dsRemote is set to qOriginal, qChanges and mtRemote, respectively.
  • The DataSource property of grdOriginal, grdChanges and grdremote is set to dsOriginal, dsChanges and dsRemote, respectively.

Run Time

When you run the application, you see the following components:

  • A TButton called Open DB. When you click this button, the sample takes the following steps:
  1. Connects conOriginal and activates qOriginal.
  2. Connects conChanges with the same parameters than conOriginal
  3. Activates the eaChanges event alerter.
  4. Activates qOriginal.
    Note: As commented in the Description, you should activate TFDEventAlerter before executing the tracked TFDQuery.
  • Three TGrids labeled as: Original data, Change View and "Remote" data. When you modify the data in the first grid, which contains the original data, it triggers a change in the second grid. The changes appear automatically on the second grid due to the event alerter. Moreover, the changes appear highligthed in yellow.
  • Two TComboBoxes labeled as Data mode and Refresh mode. You can use these comboboxes to choose between different modes.
  • A TCheckBox labeled as Merge using stream. This check box allows you to emulate a remote data transfer.

Uses

See Also