3.6.2. TFDUpdateSQL component

The TFDUpdateSQL component enables you to refine or redefine the SQL command that Delphi generates automatically for updating a dataset. It can be used to update an FDQuery object, an FDTable object or data underlying an FDStoredProc object.

Using TFDUpdateSQL is optional for TFDQuery and TFDTable because these components can generate statements automatically, that can sometimes be used for posting updates from a dataset to the database. For updating a dataset that is delivered into an FDStoredProc object, use of the TFDUpdateSQL is not optional. The developer must figure out a statement that will result in the desired updates. If only one table is updated, a direct DML statement might be sufficient. Where multiple tables are affected, an executable stored procedure will be unavoidable.

We recommend that you always use it, even in the simplest cases, to give yourself full control over the queries that are requested from your application.

TFDUpdateSQL Properties

To specify the SQL DML statements at design time, double-click on the TFDUpdateSQL component in your data module to open the property editor.

Each component has its own design-time property editor. For multiple data-aware editors to run, FireDac needs an active connection to the database (TFDConnection.Connected = True) and a transaction in the autostart mode (TFDTransaction.Options.AutoStart = True) for each one.

Design-time settings could interfere with the way the application is intended to work. For instance, the user is supposed to log in to the program using his username, but the FDConnection object connects to the database as SYSDBA.

It is advisable to check the Connected property of the FDConnection object and reset it each time you use the data-aware editors. AutoStart will have to be enabled and disabled for a read-only transaction as well.

fbdevgd30 delphi 002

Figure 3. TFDUpdateSQL property editor

You can use the Generate tab to make writing Insert/Update/Delete/Refresh queries easier for yourself. Select the table to be updated, its key fields, the fields to be updated and the fields that will be reread after the update and click the Generate SQL button to have Delphi generate the queries automatically. You will be switched to the SQL Commands tab where you can correct each query.

fbdevgd30 delphi 003

Figure 4. TFDUpdateSQL SQL command editor

Since product_id is not included in Updating Fields, it is absent from the generated Insert query. It is assumed that this column is filled automatically by a generator call in a BEFORE INSERT trigger or, from Firebird 3.0 forward, it could be an IDENTITY column. When a value is fetched from the generator for this column at the server side, it is recommended to add the PRODUCT_ID column manually to the RETURNING clause of the INSERT statement.

The Options Tab

The Options tab contains some properties that can affect the process of query generation. These properties are not related to the TFDUpdateSQL component itself. Rather, for convenience, they are references to the UpdateOptions properties of the dataset that has the current TFDUpdateSQL specified in its UpdateObject property.

Table 4. TFDUpdateSQL component main properties
PropertyPurpose

Connection

Reference to the TFDConnection component

DeleteSQL

The SQL query for deleting a record

FetchRowSQL

The SQL query for returning a current record after it has been updated or inserted — “RefreshSQL”

InsertSQL

The SQL query for inserting a record

LockSQL

The SQL query for locking a current record. (FOR UPDATE WITH LOCK)

ModifySQL

The SQL query for modifying a record

UnlockSQL

The SQL query for unlocking a current record. It is not used in Firebird.

Notice that, because the TFDUpdateSQL component does not execute modification queries directly, it has no Transaction property. It acts as a replacement for queries automatically generated in the parent TFDRdbmsDataSet.