How can I determine the value of a DBMS auto-generated key after record insertion?
Created May 4, 2012
Hans Gerwitz I had to research this issue for both MySQL and MS SQL and was able to answer my own question.
With JDBC 2.0 or greater, this can be accomplished using scroll-sensitive ResultSets. See sections 5.8 and 5.9.
Unfortunately, I was limited to JDBC 1.2.
- In the case of MySQL, the popular tjFM driver supports a getLastInsertID method on twz1.jdbc.mysql.jdbcMysqlBase.
- In the case of MS SQL 7, no 1.2.2 or lesser JDBC driver appears to provide a similar method, but the magic variable @@IDENTITY can be used on the same connection, using a trick to execute both statements sequentially. A good example of this is documented in the FAQ for the i-net UNA driver.