Subject Re: [IBO] How to get the record id that was just stored with a query
Author Antti Kurenniemi
> Actually, the way it was written was a bit misleading. However, at the
> After Insert event, the new record has not yet been posted.
> What should be made clear is that, with Firebird and IB, you don't have to
> wait until after the record has been posted to assign the value from a
> generator. Both IBO's Gen_ID() function and GeneratorLinks get the
> generator value *before* anything is written to the database. Don't use
> both - use one or the other!! And simply DO NOT use any retrospective
> means
> to try to determine the value. It is totally unreliable to do so in a
> multi-transaction environment.
> Alan's "third method" is not recommended for IBO. It is just too darned
> easy to run the thing multiple times by accident.
> If using GeneratorLinks or Gen_ID(), make sure the Before Insert trigger
> for your table is safe, viz.
> if (new.ID is null) then
> new.ID = GEN_ID(YourGenerator, 1);
> If you don't fix this, you will be getting two numbers generated every
> time
> you do an insert, and will mess up the keys of any dependent records.

A lot of information, thanks all. This is what I came up with and it seems
to work (with GeneratorLinks not defined):

procedure TForm1.InsertEvent(Sender: TObject);
TempId: Int64;
with EventQuery do
SQL.Add('insert into event(id, date, somevalue) ');
SQL.Add('values(:id, :date, :somevalue)');

TempId := Gen_ID('gen_eventid', 1);

ParamByName('id').AsInt64 := TempId;
ParamByName('date').AsDate := Now;
ParamByName('somevalue').AsInteger := 123;


Is that a good approach? What I'm still not clear with is, is the generated
id somehow "reserved" when Gen_ID is called, because (especially when
working over a slow network connection) there still is a small time gap
between that and Execute, and if this was, say, and app that generates a lot
of inserts automatically very quickly, there could still be a possibility of
two clients getting same id's, no?

I tried using GeneratorLinks, and it works otherwise except that I couldn't
figure out how to read the id back. Anyone up for a wee little piece of
sample code? ;-)

Antti Kurenniemi