Access recent value of an AUTOINC field

Roaster2 · Post by **Roaster2** » Wed 03 Dec 2008 20:59

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

Plash · Post by **Plash** » Thu 04 Dec 2008 08:39

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.

Roaster2 · Post by **Roaster2** » Thu 04 Dec 2008 11:59

Hi Plash,

how does this work when using a query with a dataset instead?
cu,
Michael

Plash · Post by **Plash** » Fri 05 Dec 2008 08:17

You should set the DMLRefresh property of TIBCQuery to True. It works in the same way as for TIBCTable.

Roaster2 · Post by **Roaster2** » Mon 15 Dec 2008 15:06

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.

Plash · Post by **Plash** » Tue 16 Dec 2008 09:05

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.

Roaster2 · Post by **Roaster2** » Tue 16 Dec 2008 11:15

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?

Plash · Post by **Plash** » Wed 17 Dec 2008 10:01

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.

Roaster2 · Post by **Roaster2** » Wed 17 Dec 2008 11:36

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?

Plash · Post by **Plash** » Thu 18 Dec 2008 08:21

We will investigate possibility to add such functionality, but that is hardly to happen in the nearest future.