aspose file tools*
The moose likes JDBC and the fly likes How to return Primary Key (auto increment) after an Insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to return Primary Key (auto increment) after an Insert" Watch "How to return Primary Key (auto increment) after an Insert" New topic
Author

How to return Primary Key (auto increment) after an Insert

Tony Huynh
Greenhorn

Joined: Aug 21, 2001
Posts: 7
Hi all,
I think I posted my first message in the wrong forum oops!
I am developing an Intranet system. Anyway I'm purely using JSPs to do it. And I have no problem doing the usual SQL commands. However, I have a problem now. I need to return the automatically incremented Primary Key after I insert a new record. I have also developed web apps using ASP before and used the cursor bookmarks. JSP is a tad different so if someone could help me, I'd be forever in their debt!!
Other Information:
I'm using DB2 as my database.
Using Websphere Studio as my development environment.
Using Websphere Test Environment (in VAJ) to run the JSPs.
Thanks in Advance!
-Tony
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

There is a way in oracle(not sure if DB2 has the same capabilities). You can select the next sequence number from the database which will cause the counter to increase as if it was used by an insert. From there you can use the number to insert its value into the database as the primary key in the row...sorry...record for you DB2 people
If this is not possible, you could write your own auto increment class. Synchronize it so that only one person could access the class at a time. All it would do is increase the number by 1, update the old value, and return the new value.
Haven't tried the above, but hopefully it gives you some alternative ideas if you can't find an easy way.
Jamie
[This message has been edited by Jamie Robertson (edited August 22, 2001).]
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Yeah , Jamie is right ..
The command for getting the primary key goes something like this
SELECT SQ_M_0001.NEXTVAL FROM DUAL where SQ_M_0001 is the name of the sequence used for getting the primary key ..
Tony Huynh
Greenhorn

Joined: Aug 21, 2001
Posts: 7
Yeah thanks for that but I'm using DB2, I've talked to a DB2 Admin about my problem and he told me to use something called 'IDENTITY_VAL_LOCAL()' I haven't tried it yet but I'll see how it goes. Thanks again for your replies!
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to return Primary Key (auto increment) after an Insert