|Subject||RE: [IBO] How to get the record id that was just stored with a query|
> > Actually, the way it was written was a bit misleading. However, at theit's not "reserved" - it's actually incremented, never to be given out again
> > 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
> 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
to anybody, as soon as you ask for it. These requests are serialised at the
server, and are outside the transaction context.
Just don't play with resetting generator values.
If you want the value returned afte generator links is activated, just query
the value of the PK field.