Solved

JDBC Return Value

Posted on 2004-08-24
28
363 Views
Last Modified: 2008-01-16
Let's say I'm executing an INSERT statement on a table as such:

int successful = stmt.executeUpdate(sqlStmt);

In this statment, I'm creating a record which has field which increments.  How can I return this value to the application so that I can process adding sub-records which are tied to this id?

Thanks,
David
0
Comment
Question by:lomidien
[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
  • 9
  • 8
  • 7
  • +1
28 Comments
 
LVL 12

Expert Comment

by:Giant2
ID: 11889521
After the insert.
Make a SELECT statement taking this incremented field.

Giant.
0
 
LVL 92

Expert Comment

by:objects
ID: 11889533
try:

ResultSet rs = stmt.getGeneratedKeys();
0
 
LVL 92

Expert Comment

by:objects
ID: 11889534
what database?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:Giant2
ID: 11889535
maybe:
public int executeUpdate(String sql, int autoGeneratedKeys)

and then:
public ResultSet getGeneratedKeys()
0
 

Author Comment

by:lomidien
ID: 11889545
SQL Server 2000 (stupid omission from me)

I tried getGeneratedKeys() and got the following:

java.lang.AbstractMethodError: com.microsoft.jdbc.base.BaseStatement.getGeneratedKeys()Ljava/sql/ResultSet;
        at PreProduction.FMS.Panels.PartsMgmt.CreatePIF.SubmitPIFClicked(CreatePIF.java:422)
        at PreProduction.FMS.Panels.PartsMgmt.CreatePIF$2.mouseClicked(CreatePIF.java:201)
        at java.awt.AWTEventMulticaster.mouseClicked(AWTEventMulticaster.java:212)
0
 
LVL 12

Expert Comment

by:Giant2
ID: 11889560
Do you have changed your executeUpdate into the following?
executeUpdate(String sql, int autoGeneratedKeys)
0
 
LVL 12

Expert Comment

by:Giant2
ID: 11889561
In your case:

int successful = stmt.executeUpdate(sqlStmt,Statement.RETURN_GENERATED_KEYS);
0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11889572
stmt.executeUpdate(sqlStmt);
I think we can not get the new key it inserts directly.

You can do it by

1.nextId = getNextID() by select max(id)+1 from table
2.use nextId insert to table1.
3.use nextId insert to table2.

Although it is easy to implement but this solution has drawback. it's possible that two client can get the same id and the second insert statement may throw SQLException: Key violation.

If your application is not serious and not much transaction at a time, you can do that. But If you need a perfect solution, you need to lock table by using Isolation Level and Transaction.

0
 
LVL 92

Expert Comment

by:objects
ID: 11889575
another option would be to use an sp that does the insert and returns the id to you.
0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11889580
Oh! ignore my comment. follow objects,Giants
0
 
LVL 12

Expert Comment

by:Giant2
ID: 11889583
But my first solution?
>After the insert.
>Make a SELECT statement taking this incremented field.
0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11889590
Keep my solution as the last way for you.

The getGeneratedKeys would be better solution.
0
 

Author Comment

by:lomidien
ID: 11889598
Ok, tried the following:

                int successful = stmt.executeUpdate(sqlStmt,Statement.RETURN_GENERATED_KEYS);
                ResultSet testSet = stmt.getGeneratedKeys();

And got the error above.  Actually, the statement will not execute with the 2nd argument inserted for some reason....same message each time.  The ID in the database table is marked as a primary key and is auto-incrementing, but it still yields an empty resultset.  

Thanks
David

0
 

Author Comment

by:lomidien
ID: 11889605
Sompol,

Yeah, thanks for your help.  I'm hoping not to go to that extent. :)  There would be a problem with concurrent users getting the same key because there is just enough transactions taking place that this would be somewhat likely.  :(

Thanks,
David
0
 
LVL 92

Accepted Solution

by:
objects earned 500 total points
ID: 11889607
you could also try:

select @@identity

0
 

Author Comment

by:lomidien
ID: 11889618
What is "select @@identity"?
0
 
LVL 12

Expert Comment

by:Giant2
ID: 11889619
Is the driver you use to connect to your DB a type-3 ?
(I read that only this implements the fieature)
Try to use a type-3 JDBC Driver.
0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11889620
I think stmt.getGeneratedKeys should be work. You may have to check whether jdbc driver support it or not.
and I think there should be jdbc that support because in EJB I can do that.
0
 
LVL 92

Expert Comment

by:objects
ID: 11889631
> What is "select @@identity"?

A query to return the last generated auto id

>  You may have to check whether jdbc driver support it or not.

appears that it doesn't.
prehaps also check you are running the latest version.
0
 
LVL 12

Expert Comment

by:Giant2
ID: 11889637
>check whether jdbc driver support it or not
As I told before:
Is the driver you use to connect to your DB a type-3 ?
(I read that only this implements the feature)
Try to use a type-3 JDBC Driver.
0
 

Author Comment

by:lomidien
ID: 11889657
jdbc:microsoft:sqlserver

The latest JDBC driver from Microsoft I believe.  I can execute the query no problem, but when I try .returnGeneratedKeys() to a resultset, that's when I'm getting the errors.

:(
0
 

Author Comment

by:lomidien
ID: 11889660
I just read on http://forum.java.sun.com/thread.jsp?forum=48&thread=87438&start=15&range=15&hilite=false&q= that the Microsoft supplied JDBC driver does not support retrieval of autoGeneratedKeys.  How's that for convenience.  :(
0
 

Author Comment

by:lomidien
ID: 11889672
What about after the update quickly grabbing a "top value" for the field.  I understand that potentially this could be problematic, but the transactions are not so heavy that the time period spent seding in 4 columns and quickly running the query will make a difference.

Off the top of their head, does anyone know the syntax for a "top value" SQL Query?


David
0
 
LVL 12

Expert Comment

by:Giant2
ID: 11889675
0
 
LVL 12

Expert Comment

by:Giant2
ID: 11889691
>after the update quickly grabbing a "top value" for the field
Mmmmhm.
In this manner you are not really secure to take the correct value.
Like I told in my first post:
>After the insert.
>Make a SELECT statement taking this incremented field.
the select must be secure (you can try to select using all the other field you use in the insert. (It's only an idea).
0
 
LVL 92

Expert Comment

by:objects
ID: 11889693
Did you try the query I posted above?
0
 

Author Comment

by:lomidien
ID: 11889722
SELECT @@identity works.

Objects, I'll have to admit, I thought it couldn't possibly work....just didn't seem like a valid query to me (in my mind).  Thanks a ton and thanks to every single person helping me with this problem at 2:00 AM.  I have a presentation in about 4 hours and was getting a little worried about this last hurdle.

Thanks again everyone!!!
David
0
 
LVL 92

Expert Comment

by:objects
ID: 11889740
Glad we could all help, good luck with your presentation :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running JavaFX on JDeveloper 12C 1 109
mysql jsp example issue 32 94
Html Table Looping (part 2) 5 39
Java import explained 4 47
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Suggested Courses

739 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