Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

JDBC Return Value

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
lomidien
Asked:
lomidien
  • 9
  • 8
  • 7
  • +1
1 Solution
 
Giant2Commented:
After the insert.
Make a SELECT statement taking this incremented field.

Giant.
0
 
objectsCommented:
try:

ResultSet rs = stmt.getGeneratedKeys();
0
 
objectsCommented:
what database?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Giant2Commented:
maybe:
public int executeUpdate(String sql, int autoGeneratedKeys)

and then:
public ResultSet getGeneratedKeys()
0
 
lomidienAuthor Commented:
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
 
Giant2Commented:
Do you have changed your executeUpdate into the following?
executeUpdate(String sql, int autoGeneratedKeys)
0
 
Giant2Commented:
In your case:

int successful = stmt.executeUpdate(sqlStmt,Statement.RETURN_GENERATED_KEYS);
0
 
sompol_kiatkamolchaiCommented:
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
 
objectsCommented:
another option would be to use an sp that does the insert and returns the id to you.
0
 
sompol_kiatkamolchaiCommented:
Oh! ignore my comment. follow objects,Giants
0
 
Giant2Commented:
But my first solution?
>After the insert.
>Make a SELECT statement taking this incremented field.
0
 
sompol_kiatkamolchaiCommented:
Keep my solution as the last way for you.

The getGeneratedKeys would be better solution.
0
 
lomidienAuthor Commented:
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
 
lomidienAuthor Commented:
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
 
objectsCommented:
you could also try:

select @@identity

0
 
lomidienAuthor Commented:
What is "select @@identity"?
0
 
Giant2Commented:
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
 
sompol_kiatkamolchaiCommented:
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
 
objectsCommented:
> 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
 
Giant2Commented:
>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
 
lomidienAuthor Commented:
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
 
lomidienAuthor Commented:
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
 
lomidienAuthor Commented:
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
 
Giant2Commented:
>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
 
objectsCommented:
Did you try the query I posted above?
0
 
lomidienAuthor Commented:
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
 
objectsCommented:
Glad we could all help, good luck with your presentation :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 8
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now