BenoitParis BenoitParis - 1 month ago 8
SQL Question

Generate several IDs at once in Firebird for Prepared Statement batches

I'm using

SELECT GEN_ID(TABLE,1) FROM MON$DATABASE
from a
PreparedStatement
to generate an ID that will be used in several tables.

I'm going to do a great number of
INSERT
s with
PreparedStatement
s batches and I'm looking for a way to fetch a lot of new IDs at once from Firebird.

Doing a trigger seems to be out of the question, since I have to
INSERT
on other tables at another time with this ID in the Java code. Also,
getGeneratedKeys()
for batches seem to not have been implemented yet in (my?) Firebird JDBCdriver.

Answer

As jrodenhi says, you can reserve a range of values using

SELECT GEN_ID(<generator>, <count>) FROM RDB$DATABASE

This will return a value of <count> higher than the previously generated key, so you can use all values from (value - count, value] (where ( signifies exclusive, ] inclusive). Say generator currently has value 10, calling GEN_ID(generator, 10) will return 20, you can then use 11...20 for ids.

This does assume that you normally use generators to generated ids for your table, and that no application makes up its own ids without using the generator.

As you noticed, getGeneratedKeys() has not been implemented for batches in Jaybird 2.2.x. Support for this option will be available in Jaybird 3.0.0, see JDBC-452.

Unless you are also targeting other databases, there is no real performance advantage to use batched updates (in Jaybird). Firebird does not support update batches, so the internal implementation in Jaybird does essentially the same as preparing a statement and executing it yourself repeatedly. This might change in the future as there are plans to add this to Firebird 4.

Disclosure: I am one of the Jaybird developers

Comments