At 10:45 AM 30/06/2005 +0300, you wrote:

> > 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);
>var
> TempId: Int64;
>begin
> with EventQuery do
> begin
> SQL.Clear;
> SQL.Add('insert into event(id, date, somevalue) ');
> SQL.Add('values(:id, :date, :somevalue)');
>
> TempId := Gen_ID('gen_eventid', 1);
>
> Prepare;
> ParamByName('id').AsInt64 := TempId;
> ParamByName('date').AsDate := Now;
> ParamByName('somevalue').AsInteger := 123;
>
> Execute;
> end;
>end;
>
>Is that a good approach?

It's not a "bad" approach, given that you are separating your insert
operation from any dataset. It's not a tidy approach otherwise. By the
way, avoid AsInt64. Use AsInteger.

>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?

No chance. A generator returns a unique number each time GEN_ID() is
invoked on the server and it happens outside all transaction contexts
except its own. (It's the only operation on the server that behaves this
way.)

>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? ;-)

Sample code not needed! All you have to do is read it in any Delphi event
*after* IBO's AfterInsert has fired. BeforePost is a good place. Pull
FieldByName('id').AsInteger into a variable or, better still, set a property.

Helen