--- In firebird-support@yahoogroups.com, Adriano <fadrianoc@...> wrote:

>
> Radu,
> sorry i'm a bit confused.
> I read on FB FAQ that:
>
> Firebird doesn't currently return values from insert statements and, in
> multi-user, it isn't safe to query the generator afterwards
> (GEN_ID(Generator_name, 0) because you have no way to know whether the
> current "latest" value was yours or someone else's. The trick is to

get the

> generator value into your application as a variable *before* you

post your

> insert. This way, you make it available not just for your insert but

for any

> dependent rows you need to create for it inside the same transaction.
>
>
> So can you explain a bit more about that ?

My advice is to forget using:

GEN_ID(GEN_PRATICHE_IDPRATICA, 0)

Its only purpose is to see what value is currently there at this
second. It is *** NOT SAFE *** to use the value you get for anything
at all. It is out of date by the time you have read the variable. You
can not assume that the number it returns is the result of your last
call to it, it may be the result of *** SOMEONE ELSES *** last call to it.

Always use:

GEN_ID(GEN_PRATICHE_IDPRATICA, 1)

There is *** NO SUCH THING *** as getting the next value of the
generator and incrementing it in two steps. That is not safe. Between
you getting the next value and you then incrementing the value,
someone else may get the same value you have. That is why in Firebird
they are one and the same operation. Firebird guarantees for you that
two calls to a generator value with an increment <> 0 will not return
the same value

Providing everyone obeys the convention, and sources their values from
the generator values with a ,1 as I have above, you will *** NEVER ***
get clashing numbers.

Triggers are not required for generators. All triggers are often used
for is to make sure that if you leave the field null, it automatically
generates a value for you. Firebird 2 introduces a syntax to return
this value, but until then you only have two options if you need to
know this value

1. Select the value before you run the query.

SELECT GEN_ID(GEN_PRATICHE_IDPRATICA, 1) AS MYID FROM RDB$DATABASE

Store the number you get in a variable and use it in your insert
statement. The number is yours, and you are free to do whatever you
like with it.

2. Use a stored procedure

This is pretty much the same conceptually, but it is done on the
database server. You can write a stored procedure to do the insert,
and put the generated value as a return parameter. If you are running
the same insert in many places, this may be preferable.

Adam