FireDAC.TFDQuery.OnUpdateRecord Sample

This sample demonstrates how to use the the OnUpdateRecord event of TFDQuery to apply updates on behalf of queries that cannot post updates directly.

Location

You can find the CentralizedCachedUpdates sample project at:

• Start | Programs | Embarcadero RAD Studio Alexandria | Samples and then navigate to:
  • Object Pascal\Database\FireDAC\Samples\Comp Layer\TFDQuery\CachedUpdates\OnUpdateRecord
• Subversion Repositories:
  • You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.

Description

The OnUpdateRecord sample shows you how to cache the user changes when using read-only dataset and then, how to apply updates on behalf of queries that cannot post updates directly. To this end, the sample uses the OnUpdateRecord event of TFDQuery and the TFDUpdateSQL object.

How to Use the Sample

1. Navigate to the location given above and open Qry_CachedUpdates.dproj.
2. Press F9 or choose Run > Run.

Files

File in Delphi	Contains
Qry_CachedUpdates.dproj Qry_CachedUpdates.dpr	The project itself.
fCachedUpdates.pas fCachedUpdates.fmx	The main form.

Implementation

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

• A TFDQuery object named qryProducts. This component is used to implement a dataset capable of executing SQL queries. The following setup is needed:

• The Connection property is configured to specify the FireDAC connection object that is used to connect to a DBMS.
• The SQL property of qryProducts is set to:

select p.*, c.CategoryName
from {id Products} p left outer join {id Categories} c on p.CategoryID = c.CategoryID
order by ProductID

Note: In the RDBMS it has one-to-many relation between Categories and Products tables.

• The CachedUpdates property is set to True. It means that the dataset will log data changes without immediately applying them to the database.

• Two TFDUpdateSQL objects named usProducts and usCategories. Both objects are used to provide SQL statements for posting updates from TFDQuery. To this end, the following setup is needed:

• The ModifySQL property of usProducts is set to:

UPDATE {id Products} SET
  ProductName = :NEW_ProductName,
  SupplierID = :NEW_SupplierID, CategoryID = :NEW_CategoryID,
  QuantityPerUnit = :NEW_QuantityPerUnit, UnitPrice = :NEW_UnitPrice,
  UnitsInStock = :NEW_UnitsInStock, UnitsOnOrder = :NEW_UnitsOnOrder,
  ReorderLevel = :NEW_ReorderLevel, Discontinued = :NEW_Discontinued
WHERE
  ProductID = :OLD_ProductID

• The ModifySQL property of usCategories is set to:

UPDATE {id Categories} SET
  CategoryName = :NEW_CategoryName
WHERE
  CategoryID = :OLD_CategoryID

• A TDataSource objects named dsProducts. This component provides an interface between a dataset component and data-aware controls on a form. In this sample, it is used to provide communication between the dataset and the grid where the dataset is displayed. To this end, the following properties are set:

• The DataSet property of dsProducts is set to qryProducts.
• The DataSource property of DBGrid1 is set to dsProducts.

When you run the application, click on the Use Connection Definition combo box and select an option in order to define a connection. When you select an item of the combo box, the sample uses the Open method of qryProducts in order to execute the SQL statement that retrieves the datasets from the database. Then, the sample displays the data from the datasets using a TDBGrid component. Once the DBGrid1 is filled, you can interact with the sample by clicking on the different buttons. Each button implements a on click event to do the following:

• The Apply Updates button.

This button uses the ApplyUpdates method of qryProducts in order to applly changes for all records in the dataset change log to the database. After calling the ApplyUpdates method, the sample calls the OnUpdateRecord event of qryProducts. This event applies the changes using the Apply method of the TFDUpdateSQL objects in the following order:

1. First, usProducts applies changes into Products (detail) table.
2. Secondly, usCategories applies changes into Categories (master) table.

• The Cancel Updates button.

This button uses the CancelUpdates method of qryProducts to remove all records from the change log. Then, the dataset records are restored to the state they were at the moment when the dataset was opened, cached updates were last enabled or updates were last successfully applied to the database.

• The Commit Updates button.

This button uses the CommitUpdates method of qryProducts to clear the changes log and mark all records as not modified.

• The Revert Updates button.

This button uses the RevertRecord method of qryProducts to undo changes to the current record and remove it from the dataset changes log.

• The Undo Last Change button.

This button use the UndoLastChange method of qryProducts to undo the last modification to the dataset records and remove the change from the dataset change log.

Note: For proper use of the methods that are implemented by the buttons mentioned above, the CachedUpdates property has to set to True.

Uses

• TFDQuery
  • OnUpdateRecord
  • Open
  • ApplyUpdates
  • CancelUpdates
  • CommitUpdates
  • RevertRecord
  • UndoLastChange
• TFDUpdateSQL
  • Apply
• TDataSource

See Also

• FireDAC
• Editing Data (FireDAC)
  • Changing DataSet Data (FireDAC)
  • Caching Updates (FireDAC)
  • Update Command Generation (FireDAC)
  • Overriding Posting Updates (FireDAC)

Samples

• FireDAC TFDQuery Centralized Cached Updates sample
• FireDAC TFDMemTable Cached Updates sample