rajsubramani wrote:

> I have not looked at Generators before.

Please, do so. They are what you're looking for.

> I have a server side faced to
> separate the SQL from other Java code (so that DB migration is easier
> to achieve). I guess if I can use a Generator via JDBC/SQL then I
> guess thats the only way for Firebird. Select for Update was what I
> was used to and well, I quite like it.
> :-(

GENERATORS won't involve your client side. One you have a generator in
place (in the db) and a trigger to assigns its next value to your column
on inserts, all the client has to do is to insert a new row. The column
will take its unique incremented new ID automatically.

Should you need to know the id on the client side for anything, you can
also proceed this way : first grab the next value from the generator by
some SELECT GEN_ID(my_generator, 1) FROM RDB$DATABASE (or any other
table for this purpose - that's just a placeholder). This is the
high-performance equivalent to the "select max(foo) from bar for update
of foo" you intended to use. It just imply incrementing a generator
value on the server side and returning it to you. This value will be
unique accross the thousands requests you might get at the same time.
Yet, it won't imply accessing a table for getting it's MAX value from a
column. Much quicker.

You can see the GENERATOR / TRIGGER approach like an auto-increment
column but with more (whatever) control you might like.

--
Olivier Mascia