Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications in Delphi → Datasets
Firebird Home Firebird Home Prev: Working with TransactionsFirebird Documentation IndexUp: Developing Firebird Applications in DelphiNext: TFDCommand component

Datasets

Table of Contents

TFDQuery Component
TFDUpdateSQL component

The components TFDQuery, TFDTable, TFDStoredProc and TFDCommand are the components for working with data in FireDac. TFDCommand does not deliver a dataset and, when TFDStoredProc is used with an executable stored procedure, rather than a selectable one, it does not deliver a dataset, either.

TFDQuery, TFDTable and TFDStoredProc are inherited from TFDRdbmsDataSet.

Apart from datasets for working with the database directly, FireDac also has the TFDMemTable component for working with in-memory datasets. It is functionally equivalent to TClientDataSet.

The main component for working with datasets, TFDQuery, can be used for practically any purpose. The TFDTable and TFDStoredProc components are just variants, expanded or reduced to meet differences in functionality. No more will be said about them and we will not be using them in our application. If you wish, you can learn about them in the FireDac documentation.

The purpose of a dataset component is to buffer records retrieved by the SELECT statement, commonly for displaying in a grid and providing for the current record in the buffer (grid) to be editable. Unlike the IBX TIBDataSet component, TFDQuery component does not have the properties RefreshSQL, InsertSQL, UpdateSQL and DeleteSQL. Instead, a separate TFDUpdateSQL object specifies the statement for dataset modifications and the dataset component carries a reference to that component in its UpdateObject property.

RequestLive Property

Sometimes it is possible to make an FDQuery object editable without referring, through the UpdateObject property, to an FDUpdateSQL object that specifies queries for insert, update and delete. The property UpdateOptions.RequestLive can be set to True for sets that are naturally updatable and the object will generate the modification queries for you. However, because this approach puts strict limitations on the SELECT query, it is not always useful to rely on it.

TFDQuery Component

Table 3.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.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 methold. 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.

TFDUpdateSQL component

Table of Contents

TFDUpdateSQL Properties

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.

Important

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 a read-only transaction as well.

Figure 3.2. TFDUpdateSQL property editor

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.

Figure 3.3. TFDUpdateSQL SQL command editor

TFDUpdateSQL SQL command editor


Note

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 3.4. TFDUpdateSQL component main properties

Property Purpose
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.

Prev: Working with TransactionsFirebird Documentation IndexUp: Developing Firebird Applications in DelphiNext: TFDCommand component
Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications in Delphi → Datasets