Originally posted by Tay Thotheolh: Hi. I have an 'ID' column for my database (MySQL) which is set to auto_increment. Everytime I add a row, the 'id' auto_increments. How do I get the auto_incremented 'ID' value ?
I doubt if I get the most recently added row would work because what happen if there maybe a chance someone did an insert after you very quickly ?
What are you trying to get the latest ID for and which language are you using?
Read the JDBC API on getGeneratedKeys() command. Also, most large systems do not use built-in database auto-increment features since they are difficult to code around (as you've discovered). [ September 02, 2008: Message edited by: Scott Selikoff ]
Originally posted by Tay Thotheolh: I am trying to get the latest id to do another insert into another table.
This is part of why its better to avoid using generated keys and create your own. For example, lets say you have a service that create 2 records, the second using the id of the first. By relying on the database to generate the keys, you have to stop in the middle of the service, connect to the database, and process the results. If you generate your own keys you can perform both inserts at the same time, one right after the other.
Originally posted by Tay Thotheolh: Maybe I should try the getGeneratedKeys().
Keep in mind getGeneratedKeys() is a driver dependent feature, some support it well, some do not.
First off, rs from getGeneratedKeys() will never be null, so no need to check for it (it will be en empty result set object if none present). I tend to use rs.getInt(1) to read the generated key, since its usually a result set of 1 column. Also, I'm not sure what the call to getRow() does, seems unnecessary to me. [ September 02, 2008: Message edited by: Scott Selikoff ]
Oh and lastly, you can use "if(rs.next())" instead of "while(rs.next())" since you would never expect your INSERT SQL statement to return more than 1 row of keys. In situations like this, I prefer to use if(rs.next()) to read the data, then provide a second if(rs.next()) that throws an error if a second row is found.
Tay, Oracle database has an INSERT..RETURNING sql statement just for this purpose. Oracle also provides a free to use database that can also be shipped with your commercial software applications for no charge. Do an Internet search for "Oracle database express edition (XE)".
Cool .. I didn't know my topic could spark off some blog. Yup.. I think the getInt(1) is working. I don't know the deep ends of JDBC except for the normal database access stuff. I might want to explore deeper next time.
About the Oracle, my environment is MySQL since most hosting uses Apache / Tomcat / MySQL , but thanks for the information on Oracle's XE and stuff.
I wonder why not much things have been done to address this issue of key generation since I noticed that this topic is rather common in Java forums as I was looking for a solution and most database makers prefer to just leave this auto generate key thing there and ignore it. Hopefully someone could come up with a clever way to tackle this issue and make it easier.
Not unless you have START TRANSACTION; before it and COMMIT; afterwards.
Um, I'm going to disagree on this one, sorry Campbell. This may be a driver dependent issue (as I mentioned in my post getGeneratedKeys() is very driver dependent) but I would think as long as you did not reuse the PreparedStatement object, the generated keys would be available regardless of transaction status since they are tied to that specific command. I could be wrong though ;)