How can I determine the value of a DBMS auto-generated key after record insertion?

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.