aspose file tools*
The moose likes JDBC and the fly likes Manually incremented keys Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Manually incremented keys" Watch "Manually incremented keys" New topic
Author

Manually incremented keys

Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I did a forum search on incrementing keys and found a lot of references to auto-increment columns. But I'm just given a database with an int key; I guess I have to increment it myself. As usual, I need to know the new key to update the next table. Surely there's a common way of doing this?


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Hmmm, just heard back from the database designer. They recommend random with dup detection. I did that in the 80s when the only DB2 doc we had was "the Date book" 1st ed.
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14342
    
  22

What brand of database are you using?

Oracle has sequences. You perform an SQL statement like "SELECT MYSEQUENCE.NEXTVAL FROM DUAL" to get the next number in the sequence and increment the sequence in one atomic operation. MySQL has auto-increment columns. Other databases have other ways to do this. As far as I know there is not one common, standard mechanism to do this.

The problem with generating keys is that getting the value and incrementing it has to be done as an atomic operation (if there are multiple threads or processes doing updates at the same time), so that no duplicate keys are generated.


Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I'm stuck with what the DA will give me, which is an int field, period. As noted above he recommended random with dup detection, and sent me some code from another project that tries up to 100 times to generate a new unique key. They needed it for a 2 digit int key once.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
In DB2 6.1 and above you should be able to use the generate_unique function.

INSERT INTO table VALUES (GENERATE_UNIQUE(),'000020','Update entry 1...');
INSERT INTO table VALUES (GENERATE_UNIQUE(),'000020','Update entry 2...');

Also, regarding the Oracle sequence value advice. Your code only is syntactically correct, but it is only valid, if a sequence is available to you in your schema.

A little extra FYI on sequences, all RDBMS systems I have been exposed to (Oracle, PostgreSQL, DB2, MySQL, etc.) all support sequences. I'm not sure why they have chosen not to include one in this database, but the function will resolve the issue for you and will always return a unique key.
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Thanks for Generate_Unique(). I'll read up on it. If I can get the value back after the insert I'll be golden.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
you can find a wealth of information in IBM's online manuals and white papers. here is the link to generate_unique function: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000809.htm
 
 
subject: Manually incremented keys