My java app makes an insertion in an Informix DB. One of the columns in the database is an autogenerated integer value, which is the primary key to the table. After the insert, I need to obtain that value so I can update another table. How can I do it?
In java, the PreparedStatement class can be set with the RETURN_GENERATED_KEYS parameter, allowing getGeneratedKeys() to be called for this purpose. However the Informix version that I'm using (9.2 or 9.3) doesn't support this method. Is there another way to do this?
I'm not familiar with Java, but there is a built-in function within Informix that will get the last serial value inserted into the table. I'd think you should be able to use this. Take a look at the DBINFO function. Basically, you'd call the function as follows:
SELECT DBINFO('sqlca.sqlerrd1') FROM systables
WHERE tabname = <tablename>
Make sure to call this function immediately following a singleton INSERT into the table. DBINFO is documented in the IBM Informix Guide to SQL: Syntax manual.