Hi,

I have a question for database applications programmers. How can i retrive autogenerated fields values using SQLdb, in same transaction as append record?

I am using Firebird database and i have a table with 3 fields:

CREATE TABLE TEST_CU
(
ID INTEGER NOT NULL,
ITEMNAME VARCHAR( 40) NOT NULL COLLATE NONE, MODIFYED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, CONSTRAINT PK_TEST_CU PRIMARY KEY (ID)
);

CREATE TRIGGER TEST_CU_ID FOR TEST_CU ACTIVE BEFORE INSERT POSITION 0 AS
begin
  if ( (new.ID is null) or (new.ID = 0) )
  then new.ID = gen_id(TEST_CU_GEN, 1);
end

At 'append' operation, if the ID field is empty, it is updated by a trigger+generator, by database server.
Also the Modifyed field is updated with current timestamp.

For a test program:
I drop a SQLQuery component, SQLTransaction, DataSource and DBgrid.

In CreateForm event:
SQLTransaction.StartTransaction;
SQLQuery.Open;
In AddRecBtn.onClick event:
SQLQuery.Append;
SQLQuery.FieldByName('ItemName').AsString := ' Item 1';
SQLQuery.Post;

in ApplyBtn.onClick event:
SQLQuery.ApplyUpdates;
SQLTransaction.CommitRetaining.

At this point I am expecting to see the fields values for ID and Modifyed fields, but they are empty.
Seems that SQLQuery.Refresh does nothing.
Someone can give me a clue?