Page 1 of 1
Access recent value of an AUTOINC field
Posted: Wed 03 Dec 2008 20:59
by Roaster2
Hi,
just wondering how to access the recent assigned value of an autoinc field after inserting a new record into a table which primary key uses an generator to increment the primary key id.
cu,
Michael
Posted: Thu 04 Dec 2008 08:39
by Plash
If you are using Firebird 2.0 or higher, and field value is assigned by a trigger, set the DMLRefresh property of TIBCTable to True. In this case an INSERT statement with the RETURNING clause is generated to insert a record.
Posted: Thu 04 Dec 2008 11:59
by Roaster2
Hi Plash,
how does this work when using a query with a dataset instead?
cu,
Michael
Posted: Fri 05 Dec 2008 08:17
by Plash
You should set the DMLRefresh property of TIBCQuery to True. It works in the same way as for TIBCTable.
Posted: Mon 15 Dec 2008 15:06
by Roaster2
Plash,
I've now tried this in Firebird 2.1 with a query but don't get this running.
I've got a table 'activities' with a generator:
Code: Select all
CREATE GENERATOR ACTIVITIES_ACTIVNO_GEN;
SET GENERATOR ACTIVITIES_ACTIVNO_GEN TO 3404;
And there is an OnBefore trigger on insert:
Code: Select all
BEGIN
IF ((NEW.ACTIVNO IS NULL) OR (NEW.ACTIVNO <= 0)) THEN
NEW.ACTIVNO = GEN_ID(ACTIVITIES_ACTIVNO_GEN, 1);
END
I've set qyActivity.DMLRefresh := True and qyActivity.RefreshOptions := [].
Now I'm stuck. I don't know how I could retrieve the number in ACTIVNO after inserting a new record into table 'activities'. The new record is inserted into the table of course and gets a new ID assigned. I don't know which event(?) to use to get the ID of this newly inserted row.
I'm using a client dataset, btw. This means the TIBCQuery is linked to a TDataSetProvider, the provider linke to a TClientDataset.
Posted: Tue 16 Dec 2008 09:05
by Plash
There was a bug with RETURNING in Firebird 2.1. You should install the latest build of the Firebird 2.1 server to make DMLRefresh work.
Posted: Tue 16 Dec 2008 11:15
by Roaster2
Plash,
but how can I access the returning values?
Is there an event which gets called after the the query returns or can I use just plain coding like TIBCQuery.FieldByName('ACTIVENO').AsInteger?
Posted: Wed 17 Dec 2008 10:01
by Plash
The current IBDAC version does not generate INSERT SQL statement automatically if you apply updates using TClientDataSet and TDataSetProvider. So you should manually set the SQLInsert property of TIBCQuery to a statement with RETURNING. For example:
Code: Select all
INSERT INTO TABLE1(C2, C3) VALUES (:C2, :C3)
RETURNING ACTIVENO
You can use IBCQuery.FieldByName('ACTIVENO').AsInteger to get a new value.
Posted: Wed 17 Dec 2008 11:36
by Roaster2
Plash wrote:The current IBDAC version does not generate INSERT SQL statement automatically if you apply updates using TClientDataSet and TDataSetProvider.
Thanks Plash. Will a future update cover this?
Posted: Thu 18 Dec 2008 08:21
by Plash
We will investigate possibility to add such functionality, but that is hardly to happen in the nearest future.