This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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.
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
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.