Link to home
Create AccountLog in
Avatar of thready
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)
}
Avatar of ghayasurrehman
ghayasurrehman
Flag of Pakistan image

insert into table () value ()
select @@scopeidenty


it will return the current id
Avatar of thready
thready

ASKER

isn't this database-specific?
Avatar of jkr
>>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.
yes, this is for MS sql server
Avatar of thready

ASKER

and wouldn't it depend on knowing how columns were created (like auto-incrementing) ?
Avatar of thready

ASKER

If I must use database-specific, would you know how to do it with Postgres?
ASKER CERTIFIED SOLUTION
Avatar of jkr
jkr
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of thready

ASKER

gracius seignior