Re: How to retrieve the generated serial id from a batch of inserts

Gabriel Belingueres wrote: > Hi, > >>From Java (using JDBC) I can execute an INSERT statement on a table > which its PK is of a SERIAL datatype, and to retrieve the just > generated pk, I execute the following select: > > select dbinfo('sqlca.sqlerrd1') from systables where tabid=1 > > This works OK. > > Now I want to make several INSERTs into that table, and I want to use > batch processing for that, using addBatch() and executeBatch on a > PreparedStatement. > > However, the previous query does not return a ResultSet with the > generated PKs, but with only one of them (the last generated SERIAL, > but not the others). > > Is it any way to solve this? The only thing you may be able to do is to have an INSERT trigger populate a table with the ids and some key. If you are using a table with a serial or serial8 type column you cannot perform bulk inserts if you have no natural key to use to retrieve the serial numbers for the rows in the batch afterwards. Art S. Kagel