Solved

best way to get next ID in database table

Posted on 2010-09-13
8
1,021 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
  • 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
 
LVL 3

Expert Comment

by:ghayasurrehman
ID: 33663502
yes, this is for MS sql server
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

930 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now