Doug Chamberlin wrote:

> Maintaining generator values which do not conflict with existing field
> values is better done in the client. What I do while my client application
> is starting up is to query the max existing value and if the generator is
> currently set below that then I reset it to max+1. This is done for every
> table which uses a generated field value. I have a short table in the
> database which lists tablename, fieldname, generatorname so the application
> just loops through these and checks each one. Simple, easy, efficient, and
> well worth it to avoid duplicate generated values.

It can still lead to problems.

But, let's not forget the original problem posted by Lee. He is just
bulk-insering some records, so he could be a single connected user at
the time, so I guess any of this options will work for him.

There's another idea: use two generators. One is for "regular" users
that fill data from application, other is for bulk-inserts.

Now you have two choices:

1. make a composite PK of two fields. You can use something like:

insert into table values (1, GEN_ID(first_generator)...
for "regular" users

and

insert into table values (2, GEN_ID(second_generator)...
for bulk-inserts

2. use minus with second generator

insert into table values (GEN_ID(first_generator)...
for "regular" users

and

insert into table values (-GEN_ID(second_generator)...
for bulk-inserts

etc.

This gives me even more ideas, but IMHO first we need some feedback from
Lee to see what he really needs.

Regards,

--
Milan Babuskov
http://fbexport.sourceforge.net