At 06:01 PM 3/09/2004 +0000, you wrote:

>I am modifying a Delphi program in which I currently use Table
>Objects to insert and edit tables. To make the program run faster, I
>am choosing to instead use a DSQL object to do all of my inserts and
>updates. However, in some cases I need to retrieve a field which is
>auto-incrimented by a trigger and generator. Using the table object,
>I simply grab the value from this field by typing
>Number := FieldbyName('Number') after the Post command. Being as the
>table object just inserted this record, the table object's active
>pointer is still pointing at that record, and the number I am asking
>for is right there. My question is, how do I retrieve the auto-
>incrimented field, from the record I just inserted using the DSQL
>component?

Get the generator value *before* you post the DML. IBO has GeneratorLinks
and also surfaces the underlying Gen_ID() method for this. (Both
TIB_Connection and TIB_Statement have this method).

On the server side, make sure that your Before Insert auto-inc trigger
checks for null and only fires if it gets null:

if (MyPk is null) then
new.MyPK = GEN_ID(MyGen, 1);

This ensures that the auto-inc trigger is there for a backstop but your IBO
apps will not trigger it.

On the client side, include the generated column in the insert set set and
set the GeneratorLinks property of the DSQL statement to
MyPK=MyGen

If your app actually wants to know what the generated value is, don't set
GeneratorLinks but call the underlying method yourself. Again, include the
generated column in the insert set and, in the Before Execute event of the
IB_DSQL, do:

with MyStatement do
ParamByName('MyPk').AsInteger := Gen_ID(MyGen, 1);

>Is it necessary to call a select statement right after the
>execution of the DSQL insert statement to grab the field I need

No, this won't be reliable in a multi-user, transactional environment.

Doing what you do with a table component isn't reliable either. Relegate
that to "something I used to do with Paradox". The set that the table
component looks at with Fb/IB isn't the table, but a highly undesirable
query that returns an image of all of the rows in the table as they were
when the physical transaction started.

Tip: If you're using GeneratorLinks rather than Gen_ID(), you can save
yourself even having to think about it, by listing a generatorlink for each
of your active tables in the GeneratorLinks property of the connection
object. It's a stringlist, so the entries would look like this:

MyTable.MyPK=MyGen
AnotherTable.ID=AnotherTablePKGen
and so on...

Any queries on those tables will then automatically get the appropriate
generator value at the appropriate time. Omit any links for which you want
to use Gen_ID() directly.

Helen