Overriding Posting Updates (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Editing Data (FireDAC)

Although FireDAC is able to automatically generate updating SQL commands, in some cases this can not be done correctly:

Original SQL command Generated SQL command possible issues Possible actions
SELECT with JOINs Includes columns from the joined tables.
SELECT with grouping operations Updates more than one record.
SELECT with common table expressions Fails to get updating table name.
SELECT with aliased columns and expressions Includes incorrect column names.
Stored procedure call All above SELECT issues.
Cursor expressions and variable All above SELECT issues.
Other non-SELECT commands All above SELECT issues.

Note: The above is not a must, instead check first what really happens.

FireDAC can detect a base table and column names for an aliased or joined columns:

DBMS Setup
Advantage Database Not supported.
Firebird Required ExtendedMetadata = True connection definition parameter.
Informix Not supported.
InterBase Required ExtendedMetadata = True connection definition parameter.
IBM DB2 Automatic.
MS Access Automatic.
MS SQL Server Required ExtendedMetadata = True connection definition parameter.
MySQL Automatic.
Oracle Not supported.
PostgreSQL Required ExtendedMetadata = True connection definition parameter.
SQL Anywhere Automatic.
SQLite Automatic.
Teradata Database Required ExtendedMetadata = True connection definition parameter.

Specifying Update Table Name

In some cases, the application needs to specify an alternative DB table name, to which the updates will be posted. To do that, set UpdateOptions.UpdateTableName to a required table name.

Specifying Update Column Names and Modes

In some cases, the application needs to exclude columns from an updating SQL command. To do that, exclude pfInUpdate from TField.ProviderFlags. And set TField.ReadOnly to prohibit the field value modification.

To specify alternative an DB column name, set TField.Origin to a required value.

Using TFDUpdateSQL

The TFDUpdateSQL component allows you to:

  • selectively override the update SQL commands generated by FireDAC;
  • enable updates posting, when FireDAC cannot generate the update commands, like a complex SELECT or stored procedure calls.

In general, TFDUpdateSQL is a collection of SQL update commands, each of them handling specific tasks, like inserting a new record into the database. TFDUpdateSQL may be setup at design time and / or at run time.

Setting at Design Time

To setup at design time, drop TFDUpdateSQL on a form. Set the dataset UpdateObject property to point to this component. Then double click TFDUpdateSQL to invoke the FireDAC Update SQL Editor.


The editor will automatically get the updating table name from the associated dataset and setup columns according to the dataset columns TField.ProviderFlags and TField.AutoGenerateValue.

To change the setup, use the following:

  • the "Table Name" combo to specify the updating table name;
  • the "Key Fields" list box to specify unique identifying columns (corresponding to pfInKey in ProviderFlags);
  • the "Updating Fields" list box to specify columns to include in the update (corresponds to pfInUpdate in ProviderFlags);
  • the "Refreshing Fields" list box to specify columns whose values must be refreshed after posting updates (corresponds to AutoGenerateValue <> arNone).

The "Describe From DB" button provides setup for the specified table, by retrieving its metadata from the database. The "Revert To Defaults" button provides setup using the dataset field properties. The additional options may be specified on the "Options" page.

When the setup is finished, press the "Generate SQL" button to generate full set of the updating SQLs. Also, the editor may be used to manually edit update SQL commands on the "SQL Commands" page.

Press OK to store changes in the TFDUpdateSQL.

Settings at Run Time

To specify the TFDUpdateSQL SQL commands at run time, the application should use XxxxSQL properties. To introduce references in an SQL to a specific column value, use the parameter markers:

  • new column value - :NEW_<column name>;
  • old column value - :OLD_<column name>;
  • current column value - :<column name>.

These parameter values will be assigned automatically by FireDAC. Do not assign a value to them, because their values will be overridden. FireDAC will ignore parameters with other names.

The command parameters and command macros may be setup only at run time. To this purpose, you have to use the TFDUpdateSQL.Commands property, which returns references to the TFDCommand objects. For example:

FDUpdateSQL1.InsertSQL.Text := 'insert into &tab (id, name) values (:new_id, :new_name)';
FDUpdateSQL1.Commands[arInsert].Macros[0].AsRaw := 'Orders';

Using OnUpdateRecord

This event allows to completely override posting updates from a dataset. The event handler code can read the dataset fields properties:

  • OldValue - returns the original field value, as it was fetched or as it is after the last CommitUpdates / CancelUpdates call;
  • CurValue / Value - returns the current field value.

Also, you can combine TFDUpdateSQL and OnUpdateRecord approaches to enable semi-automatic updates posting to different tables or databases.

For details, see the OnUpdateRecord event description and the FireDAC demos:

  • FireDAC\Samples\Comp Layer\TFDQuery\CachedUpdates\OnUpdateRecord
  • FireDAC\Samples\Comp Layer\TFDUpdateSQL\Main

See Also