3.6. Datasets - 3.6.1. TFDQuery Component - 《Firebird 3.0 Developer's Guide》

3.6.1. TFDQuery Component

3.6.1. TFDQuery Component

Table 3. TFDQuery component main properties
Property	Purpose
`Connection`	Reference to the `FDConnection` object
`MasterSource`	If the dataset is to be used as detail to a master dataset, this property refers to the data source (`TDataSource`) of the master set
`Transaction`	If specified, refers to the transaction within which the query will be executed. If not specified, the default transaction for the connection will be used.
`UpdateObject`	Reference to the `FDUpdateSQL` object providing for the dataset to be editable when the `SELECT` query does not meet the requirements for automatic generation of modification queries with `UpdateOptions.RequestLive=True`.
`UpdateTransaction`	The transaction within which modification queries will be executed. If the property is not specified the transaction from the `Transaction` property of the connection will be used.
`UpdateOptions.CheckRequired`	If set to True (the default) FireDac controls the `Required` property of the corresponding `NOT NULL` fields. If you keep it True and a field with the `Required=True` has no value assigned to it, an exception will be raised when the `Post` method is called. This might not be what you want if a value is going to be assigned to this field later in `BEFORE` triggers.
`UpdateOptions.EnableDelete`	Specifies whether a record can be deleted from the dataset. If `EnableDelete=False`, an exception will be raised when the `Delete` method is called.
`UpdateOptions.EnableInsert`	Specifies whether a record can be inserted into the dataset. If `EnableInsert=False`, an exception will be raised when the `Insert`/`Append` method is called.
`UpdateOptions.EnableUpdate`	Specifies whether a record can be edited in the dataset. If `EnableUpdate=False`, an exception will be raised when the `Edit` method is called.
`UpdateOptions.FetchGeneratorPoint`	Controls the moment when the next value is fetched from the generator specified in the `UpdateOptions.GeneratorName` property or in the `GeneratorName` property of the auto-incremental field `AutoGenerateValue=arAutoInc`. The default is `gpDeferred`, causing the next value to be fetched from the generator before a new record is posted in the database, i.e., during `Post` or `ApplyUpdates`. For the full set of possible values, see note (1) below.
`UpdateOptions.GeneratorName`	The name of the generator from which the next value for an auto-incremental field is to be fetched.
`UpdateOptions.ReadOnly`	Specifies whether it is a read-only dataset. The default value is False. If the value of this property is set to True, the `EnableDelete`, `EnableInsert` and `EnableUpdate` properties will be automatically set to False.
`UpdateOptions.RequestLive`	Setting `RequestLive` to True makes a query editable, if possible. Queries for insert, update and delete will be generated automatically. This setting imposes strict limitations on the `SELECT` query. It is supported for backward compatibility with the ancient BDE and is not recommended.
`UpdateOptions.UpdateMode`	Controls how to check whether a record has been modified. This property allows control over possible overwriting of updates in cases where one user is taking a long time to edit a record while another user has been editing the same record simultaneously and completes the update earlier. The default is `upWhereKeyOnly`. For information about the available modes, see note (2) below.
`CachedUpdates`	Specifies whether the dataset cache defers changes in the dataset buffer. If this property is set to True, any changes (`Insert`/`Post`, `Update`/`Post`, `Delete`) are saved to a special log and the application must apply them explicitly by calling the `ApplyUpdates` method. All changes will be made within a small period of time and within one short transaction. The default value of this property is False.
`SQL`	Contains the text of the SQL query. If this property is a `SELECT` statement, execute it by calling the `Open` method. Use the `Execute` or `ExecSQL` for executing a statement that does not return a dataset.

Note 1: UpdateOptions.FetchGeneratorPoint

The property UpdateOptions.FetchGeneratorPoint can take the following values:

gpNone

no value is fetched from the generator

gpImmediate

the next value is fetched from the generator right after the Insert/Append method is called

gpDeferred

the next value is fetched during Post or ApplyUpdates

Note 2: UpdateOptions.UpdateMode

The user in a lengthy editing session could be unaware that a record has been updated one or more times during his editing session, perhaps causing his own changes to overwrite someone else’s updates. The UpdateOptions.UpdateMode property allows a choice of behaviours to lessen or avoid this risk:

upWhereAll

check whether a record exists by its primary key + check all columns for old values, e.g.,

update table set …
where pkfield = :old_ pkfield and
          client_name =  :old_client_name and
         info = :old_info …

With upWhereAll set, the update query will change content in a record only if the record has not been edited by anyone else since our transaction started. It is especially important if there are dependencies between values in columns, such as minimum and maximum wages, etc.

upWhereChanged

check whether a record exists by its primary key + check for old values only in the columns being edited.

update table set …
where pkfield = :old_pkfield and
    client_name = :old_client

upWhereKeyOnly

check whether a record exists by its primary key. This check corresponds to the automatically generated UpdateSQL query.

To avoid (or handle) update conflicts in a multi-user environment, typically you need to add WHERE conditions manually. You would need a similar tactic, of course, to implement a process that emulates upWhereChanged, removing the unused column modifications from the update table set, leaving in the update list only the columns that are actually modified. The update query could otherwise overwrite someone else’s updates of this record.

Obviously, the UpdateSQL needs to be created dynamically.

If you want to specify the settings for detecting update conflicts individually for each field, you can use the ProviderFlags property for each field.