Update Command Generation (FireDAC)

Go Up to Editing Data (FireDAC)

Generalities

When the database application calls the following dataset methods, the updates mode will be different:

• Insert / Edit / Post / Delete--in immediate updates mode,
• ApplyUpdates--in cached updates mode,

and updating data SQL commands will be automatically generated by FireDAC. The FireDAC SQL commands generator is aware of the database identity fields, sequences, triggers, special data types (Oracle BLOB / CLOB / BFILE, and so on) and generates an efficient SQL command, depending on the connected DBMS. This reduces the number of cases where a developer must use the manual SQL commands. FireDAC does not force a developer to use TFDUpdateSQL, which may be used to override the update SQL commands.

For example, when posting a new record to an Oracle table, where ID field is filled by a trigger from a sequence, and IMAGE is of BLOB type, FireDAC will generate the following SQL command:

INSERT INTO OracleTab (NAME, DT, IMAGE)
VALUES (:NEW_NAME, :NEW_DT, EMPTY_BLOB())
RETURNING :NEW_ID, :NEW_IMAGE

1. FROM, INTO, and UPDATE Phrases

FireDAC will use the main (first) table in the SELECT ... FROM ... statement, as an updating table name. Also, this table will be used to retrieve mkPrimaryKeyFields metadata. Use UpdateOptions.UpdateTableName to explicitly specify the updating table. This is required, when:

• the dataset is TFDStoredProc;
• TFDQuery contains not a SELECT query;
• FireDAC fails to get the updating table name correctly from the query;
• the application needs to redirect updates to a specific table.

2. WHERE Phrase

The UpdateOptions.UpdateMode controls the WHERE clause generation for posting updates and deletions. The default value upWhereKeyOnly uses in the WHERE phrase only the unique identifying columns and provides an efficient and safe way to locate the updating row. When no unique identifying columns are specified and no row identifying column is found, FireDAC will switch UpdateOptions.UpdateMode to upWhereAll. The following fields included in the WHERE clause may lead to the "no rows found" error:

• DOUBLE, FLOAT, TIME, and DATETIME fields, and other floating point based fields may lead to a precision lost at value comparison;
• textual fields may have an invalid encoding or extra spaces leading to unsuccessful comparisons;
• other similar failures may happen.

In such cases, the application gets an exception:

[FireDAC][DApt]-400. Update command updated [0] instead of [1] record.

Similarly, when columns in WHERE are not unique identifying a row, more than a single record may be updated. Then the exception is the following:

[FireDAC][DApt]-400. Update command updated [4] instead of [1] record.

To resolve these issues, consider:

• providing correct unique identifying columns;
• disabling some fields with WHERE usage by excluding pfInWhere from the corresponding TField.ProviderFlags property;
• suppressing these errors by setting UpdateOptions.CountUpdatedRecords to False.

Note: To avoid the above issues with an SQL Server table, which also has triggers, the triggers should have SET NOCOUNT ON specified at the beginning. If that is not possible, then set UpdateOptions.CountUpdatedRecords to False.

3.SET and VALUES Phrases

UpdateOptions.UpdateChangedFields controls the fields to include in the UPDATE SET ... or INSERT VALUES ... phrases. Setting to True will include only changed fields, which helps:

• to minimize the traffic at updates posting;
• to avoid unnecessary constraints validations;
• to avoid extra trigger firings;
• to minimize redo log generation.

Setting to False will include all fields, wihch helps to reuse the same generated statement for posting all updates and minimize DBMS work to prepare the statements.

To disable updates to columns, exclude pfInUpdate from the corresponding TField.ProviderFlags property.

4. RETURNING and Additional SELECTs

UpdateOptions.RefreshMode = rmOnDemand controls automatic refreshing of the column values, which may be changed by the DBMS after inserting or updating a record. The columns, which may require refreshing after record inserting are as follows:

• auto-incrementing columns;
• database calculated columns;
• columns with default values;
• row-identifying columns;
• timestamp columns;
• columns updated by a trigger.

The columns, which may require refreshing after record updating are as follows:

• database calculated columns;
• timestamp columns;
• columns updated by a trigger.

Depending on the DBMS features, the additional phrases / commands will be generated, returning the refreshing column values:

• Oracle, Firebird, PostgreSQL - the RETURNING phrase;
• DB2 - the SELECT ... FROM FINAL TABLE phrase;
• SQL Server, SQL Anywhere, SQLite - the SQL batch with an additional SELECT command;
• otherwise - an additional SELECT command.

5. Updates Command Caching

Sometimes setting UpdateChangedFields to False may improve the performance. And combined with several other settings, like an UpdateOptions.FastUpdates property, it allows to get even better performance at updates posting by avoiding the additional queries and enabling to cache the generated update commands.

FastUpdates = True is equivalent to:

• UpdateChangedFields = False;
• UpdateMode = upWhereKeyOnly;
• LockMode = lmNone;
• RefreshMode = rmManual;
• CheckRequired = False.

See Also

• Editing Data