Page 1 of 1
RETURNING-Support in INSERT-Statements
Posted: Tue 10 Oct 2006 09:49
by 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?
Posted: Tue 10 Oct 2006 10:16
by Alex
We have implemented such functionality in the new version of IBDAC (IBDAC 2.00). It will be released in the near future.
How to get the new ID after insert?
Posted: Wed 11 Oct 2006 08:22
by wkupfer
Is there an alternative to get the new ID after an insert operation? I need this ID for additional inserts in detail tables.
Posted: Wed 11 Oct 2006 10:13
by 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
Posted: Thu 12 Oct 2006 09:11
by 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.