thready
asked on
best way to get next ID in database table
Hi Experts,
I want to save the data of a record to the database (which has many rows). I will need to do this in a transaction. I want to get what the next ID should be (without using auto-incrementing rows). So I want some way of locking the table so that I can do the following:
- Lock the table
- Get the next logical ID I should use
- insert my rows
- unlock the table
Is there a way to do this without using database-specific calls?
I could do something like this:
while(true) {
while (lock table does not exist) {
create lock table
query other table for max ID
insert using MaxID + 1
delete lock table
return
}
sleep (2000)
}
I want to save the data of a record to the database (which has many rows). I will need to do this in a transaction. I want to get what the next ID should be (without using auto-incrementing rows). So I want some way of locking the table so that I can do the following:
- Lock the table
- Get the next logical ID I should use
- insert my rows
- unlock the table
Is there a way to do this without using database-specific calls?
I could do something like this:
while(true) {
while (lock table does not exist) {
create lock table
query other table for max ID
insert using MaxID + 1
delete lock table
return
}
sleep (2000)
}
ASKER
isn't this database-specific?
>>Is there a way to do this without using database-specific calls?
If you can guarantee that no other process will be accessing the DB during such an operation - yes. If not, I cannot imagine any way that is not specific to the DB. However, if you can use ODBC (http://en.wikipedia.org/wiki/Open_Database_Connectivity), you can reduce the 'specificness' of that.
If you can guarantee that no other process will be accessing the DB during such an operation - yes. If not, I cannot imagine any way that is not specific to the DB. However, if you can use ODBC (http://en.wikipedia.org/wiki/Open_Database_Connectivity), you can reduce the 'specificness' of that.
yes, this is for MS sql server
ASKER
and wouldn't it depend on knowing how columns were created (like auto-incrementing) ?
ASKER
If I must use database-specific, would you know how to do it with Postgres?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gracius seignior
select @@scopeidenty
it will return the current id