Tutorial :How can I treat a sequence value like a generated key?



Question:

Here is my situation and my constraints:

  1. I am using Java 5, JDBC, and DB2 9.5

  2. My database table contains a BIGINT value which represents the primary key. For various reasons that are too complicated to go into here, the way I insert records into the table is by executing an insert against a VIEW; an INSTEAD OF trigger retrieves the NEXT_VAL from a SEQUENCE and performs the INSERT into the target table.

  3. I can change the triggers, but I cannot change the underlying table or the general approach of inserting through the view.

  4. I want to retrieve the sequence value from JDBC as if it were a generated key.

Question: How can I get access to the value pulled from the SEQUENCE. Is there some message I can fire within DB2 to float this sequence value back to the JDBC driver?

Resolution: I resorted to retrieving the PREVIOUS_VAL from the sequence in a separate JDBC call.


Solution:1

Have you looked at java.sql.Statement.getGeneratedKeys()? I wouldn't hold out much hope since you're doing something so unusual but you never know.


Solution:2

You should be able to do this using the FINAL TABLE syntax:

select * from final table (insert into yourview values (...) );  

This will return the data after all triggers have been fired.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »