Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications in Delphi → Creating the Primary Modules
Firebird Home Firebird Home Prev: TFDCommand componentFirebird Documentation IndexUp: Developing Firebird Applications in DelphiNext: Creating a Secondary Module

Creating the Primary Modules

Table of Contents

The Read-only Transaction
The Read/Write Transaction
Configuring the Customer Module for Editing
Implementing the Customer Module
Using a RETURNING Clause to Acquire an Autoinc Value

We will create two primary modules in our application: a product module and a customer module. Each primary dataset is displayed on a form by means of a TDBGrid grid and a toolbar with buttons. The business logic of working with the dataset will be located in a separate DataModule that contains a TDataSource data source, a TFDQuery dataset, and two TFDTransaction transactions, one read-only and one read/write.

As our model for creating datasets, we will create the Customer dataset on the dCustomers datamodule:

Figure 3.4. dCustomers datamodule

dCustomers datamodule


On tabbing to the Customers form, this is the initial view. The DataSource component is not visible on the form because it is located in the dCustomers datamodule.

Figure 3.5. Customers form, initial view

Customers form, initial view


We have placed the TFDQuery component in the dCustomers datamodule and named it qryCustomers. This dataset will be referred to in the DataSet property of the DataSource data source in DCustomers. We specify the read-only transaction trRead in the Transaction property, the trWritetransaction in the UpdateTransaction property and, for the Connection property, the connection located in the main data module. We populate the SQL property with the following query:

SELECT
  customer_id,
  name,
  address,
  zipcode,
  phone
FROM
  customer
ORDER BY name
      

The Read-only Transaction

The trRead read transaction is started when the dataset form is displayed (the OnActivate event) and is ended when the form is closed. READ COMMITTED isolation level (Options.Isolation = xiReadCommitted) is usually used to show data in grids because it allows the transaction to see changes committed in the database by other users by just repeating queries (rereading data) without the transaction being restarted.

Since this transaction is used only to read data, we set the Options.ReadOnly property to True. Thus, our transaction will have the following parameters: read read_committed rec_version.

Why?

A transaction with exactly these parameters can remain open in Firebird as long as necessary (days, weeks, months) without locking other transactions or affecting the accumulation of garbage in the database because, with these parameters, a transaction is started on the server as committed.

We set the property Options.DisconnectAction to xdCommit, which perfectly fits a read-only transaction. Finally, the read transaction will have the following properties:

  Options.AutoStart = False
  Options.AutoCommit = False
  Options.AutoStop = False
  Options.DisconnectAction = xdCommit
  Options.Isolations = xiReadCommitted
  Options.ReadOnly = True
        

Important

Although we do not discuss reporting in this manual, be aware that you should not use such a transaction for reports, especially if they use several queries in sequence. A transaction with READ COMMITTED isolation will see all new committed changes when rereading data. The recommended configuration for reports is a short read-only transaction with SNAPSHOT isolation (Options.Isolation = xiSnapshot and Options.ReadOnly= True).

The Read/Write Transaction

The write transaction trWrite that we use for our FDUpdateSQL object must be as short as possible to prevent the oldest active transaction from getting “stuck” and inhibiting garbage collection. High levels of uncollected garbage will lead to lower performance. Since the write transaction is very short, we can use the SNAPSHOT isolation level. The default value of the Options.DisconnectAction property, xdCommit, is not appropriate for write transactions, so it should be set to xdRollback. We will not rely on starting and ending transactions automatically. Instead, we will start and end a transaction explicitly. Thus, our transaction should have the following properties:

  Options.AutoStart = False
  Options.AutoCommit = False
  Options.AutoStop = False
  Options.DisconnectAction = xdRollback
  Options.Isolations = xiSnapshot
  Options.ReadOnly = False
        

SNAPSHOT vs READ COMMITTED Isolation

It is not absolutely necessary to specify SNAPSHOT isolation for simple INSERT/UPDATE/DELETE operations. However, if a table has complex triggers or a stored procedure is executed instead of a simple INSERT/UPDATE/DELETE query, it is advisable to use SNAPSHOT. The reason is that READ COMMITTED isolation does not ensure the read consistency of the statement within one transaction, since the SELECT statement in this isolation can return data that were committed to the database after the transaction began. In principle, SNAPSHOT isolation is recommended for short-running transactions.

Configuring the Customer Module for Editing

In this section, we will configure some properties in the qryCustomer and FDUpdateCustomer objects to make the Customer dataset editable.

The TFDUpdateSQL Settings

To make the dataset editable, the InsertSQL, ModifySQL, DeleteSQL and FetchRowSQL properties should be specified in the FDUpdateSQL object that is linked to the dataset. The wizard can generate these statements but it may be necessary to correct some things afterwards. For example, you can add a RETURNING clause, remove some columns from the update list or cancel an automatically generated stored procedure call entirely.

InsertSQL
INSERT INTO customer (
  customer_id,
  name,
  address,
  zipcode,
  phone)
VALUES (:new_customer_id,
  :new_name,
  :new_address,
  :new_zipcode,
  :new_phone)
          
ModifySQL
UPDATE customer
SET name = :new_name,
    address = :new_address,
    zipcode = :new_zipcode,
    phone = :new_phone
WHERE (customer_id = :old_customer_id)
          
DeleteSQL
DELETE FROM customer
WHERE (customer_id = :old_customer_id)
          
FetchRowSQL
SELECT
  customer_id,
  name,
  address,
  zipcode,
  phone
FROM
  customer
WHERE customer_id = :old_customer_id
          

Getting a Generator Value

In this project, we will get the value from the generator before making an insert into the table. To enable that, specify the following values for the properties of the TFDQuery component:

  UpdateOptions.GeneratorName = GEN_CUSTOMER_ID
     and
  UpdateOptions.AutoIncFields = CUSTOMER_ID

          

Note

This method works only for autoinc fields that are populated by explicit generators (sequences). It is not applicable to the IDENTITY type of autoinc key introduced in Firebird 3.0.

Another way to get the value from the generator is to return it after the INSERT is executed by means of a RETURNING clause. This method, which works for IDENTITY fields as well, will be shown later, in the topic Using a RETURNING Clause to Acquire an Autoinc Value.

Implementing the Customer Module

Modal forms are often used to add a new record or to edit an existing one. Once the modal form is closed by the mrOK result, the changes are posted to the database. Database-aware visual components are usually used to create this kind of form. These components enable you to display the values of some fields from the current record and immediately accept the user's changes in the corresponding fields if the dataset is in the Insert/Edit mode, i.e. before Post.

The only way to switch the dataset to Insert/Edit mode is by starting a write transaction. So, if somebody opens a form for adding a new record and leaves for a lunch break, we will have an active transaction hanging until the user comes back from lunch and closes the form. This uncommitted edit can inhibit garbage collection, which will reduce performance. There are two ways to solve this problem:

  1. Use the CachedUpdates mode, which enables the transaction to be active just for a very short period (to be exact, just for the time it takes for the changes to be applied to the database).
  2. Give up using visual components that are data-aware. This approach requires some additional effort from you to activate the data source and pass user input to it.

We will show how both methods are implemented. The first method is much more convenient to use. Let's examine the code for editing a customer record:

procedure TCustomerForm.actEditRecordExecute(Sender: TObject); 
var 
  xEditorForm: TEditCustomerForm; 
begin 
  xEditorForm := TEditCustomerForm.Create(Self); 
  try 
    xEditorForm.OnClose := CustomerEditorClose; 
    xEditorForm.DataSource := Customers.DataSource;
    xEditorForm.Caption := 'Edit customer'; 
    Customers.Edit; 
    xEditorForm.ShowModal; 
  finally 
    xEditorForm.Free; 
  end; 
end;
The Customers property is initiated in the OnCreate event:
procedure TCustomerForm.FormCreate(Sender: TObject); 
begin 
  FCustomers := TDMCustomers.Create(Self); 
  DBGrid.DataSource := Customers.DataSource; 
end;
        

We set the CachedUpdates mode for the dataset in the Edit method of the dCustomers module before switching it to the edit mode:

procedure TdmCustomers.Edit; 
begin 
  qryCustomer.CachedUpdates := True; 
  qryCustomer.Edit; 
end;
        

The logic of handling the process of editing and adding a record is implemented in the OnClose event handler for the modal edit form:

procedure TCustomerForm.CustomerEditorClose(Sender: TObject; 
  var Action: TCloseAction); 
begin 
  if TEditCustomerForm(Sender).ModalResult <> mrOK then
  begin 
    Customers.Cancel; 
    Action := caFree; 
    Exit; 
  end; 
  try 
    Customers.Post; 
    Customers.Save; 
    Action := caFree; 
  except 
    on E: Exception do 
    begin 
      Application.ShowException(E); 
      // It does not close the window give the user correct the error 
      Action := caNone; 
    end; 
  end;
end;
        

To understand the internal processes, we can study the code for the Cancel, Post and Save methods of the dCustomer data module:

procedure TdmCustomers.Cancel; 
begin 
  qryCustomer.Cancel; 
  qryCustomer.CancelUpdates; 
  qryCustomer.CachedUpdates := False; 
end;

procedure TdmCustomers.Post; 
begin 
  qryCustomer.Post; 
end;
 
procedure TdmCustomers.Save; 
begin 
  // We do everything in a short transaction 
  // In CachedUpdates mode an error does not interrupt the running code. 
  // The ApplyUpdates method returns the number of errors. 
  // The error can be obtained from the property RowError 
  try 
    trWrite.StartTransaction; 
    if (qryCustomer.ApplyUpdates = 0) then 
    begin 
      qryCustomer.CommitUpdates; 
      trWrite.Commit; 
    end 
    else 
      raise Exception.Create(qryCustomer.RowError.Message); 
    qryCustomer.CachedUpdates := False; 
  except 
    on E: Exception do 
    begin 
      if trWrite.Active then 
        trWrite.Rollback; 
      raise; 
    end; 
  end; 
end;
        

Observe that the write transaction is not started at all until the OK button is clicked. Thus, the write transaction is active only while the data are being transferred from the dataset buffer to the database. Since we access not more than one record in the buffer, the transaction will be active for a very short time, which is exactly what we want.

Using a RETURNING Clause to Acquire an Autoinc Value

Creating the product is similar to creating the customer one. We will use it to demonstrate the method of getting an auto-incremented value by means of a RETURNING clause.

The main query:

SELECT
  product_id,
  name,
  price,
  description
FROM product
ORDER BY name
        

The TFDUpdateSQL.InsertSQL property will contain the following statement:

INSERT INTO PRODUCT (NAME, PRICE, DESCRIPTION)
VALUES (:NEW_NAME, :NEW_PRICE, :NEW_DESCRIPTION)
RETURNING PRODUCT_ID
        

The RETURNING clause in this statement will return the value of the PRODUCT_ID field after it has been populated by the BEFORE INSERT trigger. The client side in this case has no need to know the name of the generator, since it all happens on the server. Leave the UpdateOptions.GeneratorName property as nil.

To acquire the autoinc value by this method also requires filling a couple of properties for the PRODUCT_ID field because the value is being entered indirectly:

  Required = False
     and
  ReadOnly = True
        

Everything else is set up similarly to the way it was done for the Customer module.

Prev: TFDCommand componentFirebird Documentation IndexUp: Developing Firebird Applications in DelphiNext: Creating a Secondary Module
Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications in Delphi → Creating the Primary Modules