Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

retrieve autonumber value of last insert

 
Jon Dornback
Ranch Hand
Posts: 137
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 567
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic