RETURNING-Support in INSERT-Statements

[wkupfer]

Is IBDAC's TIBCQuery supporting the keyword RETURNING in INSERT-Statements?

Example:
The table T_EXAMPLE has the fields ID, LASTNAME, FIRSTNAME. ID is the primary key field. It is filled with a value from a generator G_EXAMPLE by a trigger (BEFORE INSERT).

The Statement

Code: Select all

INSERT INTO T_EXAMPLE (LASTNAME, FIRSTNAME)
VALUES (:LASTNAME, :FIRSTNAME)
RETURNING ID

returns the new ID value.

Is this proceeding possible with TIBCQuery?

[Alex]

We have implemented such functionality in the new version of IBDAC (IBDAC 2.00). It will be released in the near future.

[wkupfer]

Is there an alternative to get the new ID after an insert operation? I need this ID for additional inserts in detail tables.

[xstef]

There are 2 ways to do that

1) Use the KeyGenerator and KeyFields properties of IBCQuery. In that way depending on the GeneratorMode property you will have your ID filled with a value from the Generator either during Insert or Post.

2) Post the dataset so that your trigger fills the ID field and then call RefreshRecord to get all the values from the server.

Good luck,

Stefanos

TwinSoft

[Alex]

1) Use the KeyGenerator and KeyFields properties of IBCQuery. In that way depending on the GeneratorMode property you will have >your ID filled with a value from the Generator either during Insert or Post.

Also it is usefull to know that IBDAC internally uses RETURNING clause with generators in case of Firebird2 server connection and IBCQuery.GeneratorMode = gmPost. This allows to avoid unnecessary server roundtrip.

2) Post the dataset so that your trigger fills the ID field and then call RefreshRecord to get all the values from the server.

IBCQuery can do this refresh automatically, to enable this feature just include roAfterInsert in IBCQuery.RefreshOptions.