3.8.5. 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:

  1. SELECT
  2.   product_id,
  3.   name,
  4.   price,
  5.   description
  6. FROM product
  7. ORDER BY name

The TFDUpdateSQL.InsertSQL property will contain the following statement:

  1. INSERT INTO PRODUCT (NAME, PRICE, DESCRIPTION)
  2. VALUES (:NEW_NAME, :NEW_PRICE, :NEW_DESCRIPTION)
  3. 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:

  1. Required = False

and

  1. ReadOnly = True

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