• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query Dillema

 
shaf maff
Ranch Hand
Posts: 180
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys

I have two queries executing, I am using the commit/Rollback method. The problem is for the second query, I need the ID from the first query for one of the foriegn key columns in the second query. Is there anyway of getting the ID for the first INSERT record before committing to the transaction? See my code below:





.
[ December 21, 2008: Message edited by: shaf maff ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shaf,
JDBC 3 supports retrieving autogenerate keys via a JDBC API. You could use that before committing.
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That depends on the DB and the JDBC driver used though. For a long time this wasn't supported by the JDBC drivers of Oracle and PostgreSQL. If I am correct, since one of the latest database and driver versions, Oracle finally supports it. For the older versions and for PostgreSQL you need to fire a specific query to obtain the generated key. Do that on the same Statement as you used for insert. Those of MySQL, DB2 and MSSQL already supported it for a long time.

Use (Prepared)Statement#getGeneratedKeys() on the same statement. The first column of the first row contains the generated key.
[ December 21, 2008: Message edited by: Bauke Scholtz ]
 
RaviNada Kiran
Ranch Hand
Posts: 528
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Use (Prepared)Statement#getGeneratedKeys() on the same statement. The first column of the first row contains the generated key.
[ December 21, 2008: Message edited by: Bauke Scholtz ]


you mean to say that Statement#getGeneratedKeys() will be returning the primary key of that column or a one or zero value (success/failure indication).

Please tell me i don't have any idea on this .
 
Paul Clapham
Sheriff
Posts: 21559
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
RaviNada Kiran wrote:you mean to say that Statement#getGeneratedKeys() will be returning the primary key of that column or a one or zero value (success/failure indication).

Please tell me i don't have any idea on this .
Actually if you read the API documentation for the method, you will find that it doesn't return either of those two things. But you will get an idea if you read that documentation.
 
RaviNada Kiran
Ranch Hand
Posts: 528
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your valuable information
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic