3.8.3. 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

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.