> How can I write a SP or query statement in the FirebirdSQL that will
> set all Genrators to 0, or to get last value from tables and put it in
> to the generator value?

This is possible in FB 1.5 or higher using the Execute Statement
syntax. Read the release notes for that. You will need to obtain a
list of generators. I do not have FB installed just here, but it is
the system table RDB$GENERATORS from memory that would be of interest
to you. A For select statement could retrieve the generator name and
run the set generator to command. However there is no direct link
between a generator and a table (unless you maintained such a link
table in your database). We have a standard naming convention for
generators gen_[tablename]UID. If you had something similar, then you
could use some UDF functios to pull the table name out of the
generator name and run your select max query on the table.


Now you see it is possible, we must look at whether it is a good idea
or not.

In particular, look at isolation. Generator values are transaction
independent. That means that the moment I update the generator value
from transaction 1, transaction 2 can see the new value even if
transaction 1 is uncommitted. This is an important feature as it means
that simultaneous transactions don't step on each others toes.

On the other hand, select max queries select the maximum value that is
visible to your transaction. That means if transaction 1 is using a
value but has not yet committed, and transaction 2 runs a select max,
it will not consider the value that has been inserted by transaction
1. (Firebird does not support dirty reads, nor should it). In your
case, the stored procedure that is resetting the generated values
won't notice someone else in the middle of an insert and would set the
generator to the wrong value. Of course if you did this as the only
user, this point is moot.

I would suggest you change the logic slightly, but whether this is
possible I do not know. Perhaps someone has been toying around in an
admin tool and has inserted records without updating the generator
value which is causing some grief to your software?

If this is the case, I would alter your method.

Select the current generator value as well as the maximum value.

If the maximum value is greater than the generator value, then work
out the difference

max value - current generator value.

Now increment the generator by this value. This does not have the
weakness I showed above.

Adam