File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes retrieve autonumber value of last insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "retrieve autonumber value of last insert" Watch "retrieve autonumber value of last insert" New topic
Author

retrieve autonumber value of last insert

Jon Dornback
Ranch Hand

Joined: Apr 24, 2002
Posts: 137
i am making inserts to multiple tables, the first of which generates an autonumbered id which the other tables use as a foreign key. to get the value of the autonumbered id, i am using the SQL server property: "select @@identity" which returns the last autonumber id inserted.
my question is: how do i ensure that the database (or table) is locked so that no other updates/inserts are performed? is this what the Connection.setTransactionIsolation(int level) method is for?
other questions: do autonumber columns *always* increment from the highest value? (eg, if i insert some rows that have numbers 1,2,3,4 then delete a row so that i am left with 1,2,4 then do another insert, will autonumber user 3 or 5 as the next value? so far from my experiments it always uses the highest value, but is this guaranteed?)
is @@identity a microsoft only implementation, or is it a sql standard?
thanks for any answers to these many (not-entirely-java) questions.
Jon


use the [CODE] tags - it makes it much easier for people to help you.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

A slightly different conversation, but some of what you want is dicussed in this thread.
Firstly, the syntax used to get the next number is vendor-specific, so @@identity is a MS thing.
Again I believe its vendor specific, but you can't be guaranteed that the next number returned is one more than the previous one. To make it more efficient, indicies are sometimes handed out in batches. This means person one may be getting numbers in the range 1-10 and person two is getting 11-20. If person one doesn't use all their numbers, they may just be discarded by the database.
Dave
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 565
You also definitely want to setAutoCommit() to off, and then commit the transaction at the end, to make sure simultaneous processes don't collide.
Perhaps someone else can tell you what setTransactionIsolation(level) you need...... I've always used the default


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: retrieve autonumber value of last insert
 
Similar Threads
strange problem...help!!!!!
oracle sequence id
retrieving autonumber field from MSAccess
oracle sequence id and table data insertion
Referential Integrity: How 2 INSERT the professional way ?