Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1040
  • Last Modified:

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)
}
0
thready
Asked:
thready
  • 4
  • 2
  • 2
1 Solution
 
ghayasurrehmanCommented:
insert into table () value ()
select @@scopeidenty


it will return the current id
0
 
threadyAuthor Commented:
isn't this database-specific?
0
 
jkrCommented:
>>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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ghayasurrehmanCommented:
yes, this is for MS sql server
0
 
threadyAuthor Commented:
and wouldn't it depend on knowing how columns were created (like auto-incrementing) ?
0
 
threadyAuthor Commented:
If I must use database-specific, would you know how to do it with Postgres?
0
 
jkrCommented:
0
 
threadyAuthor Commented:
gracius seignior
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now