Solved

best way to get next ID in database table

Posted on 2010-09-13
8
1,029 Views
Last Modified: 2012-05-10
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
Comment
Question by:thready
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 3

Expert Comment

by:ghayasurrehman
ID: 33663455
insert into table () value ()
select @@scopeidenty


it will return the current id
0
 
LVL 1

Author Comment

by:thready
ID: 33663489
isn't this database-specific?
0
 
LVL 86

Expert Comment

by:jkr
ID: 33663492
>>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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 3

Expert Comment

by:ghayasurrehman
ID: 33663502
yes, this is for MS sql server
0
 
LVL 1

Author Comment

by:thready
ID: 33663539
and wouldn't it depend on knowing how columns were created (like auto-incrementing) ?
0
 
LVL 1

Author Comment

by:thready
ID: 33664258
If I must use database-specific, would you know how to do it with Postgres?
0
 
LVL 86

Accepted Solution

by:
jkr earned 500 total points
ID: 33664327
0
 
LVL 1

Author Closing Comment

by:thready
ID: 33664420
gracius seignior
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question