• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Query Dillema

 
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 ]
 
author & internet detective
Posts: 40036
809
Eclipse IDE VI Editor Java
  • 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.
 
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 ]
 
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 .
 
Marshal
Posts: 25682
69
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
 
A feeble attempt to tell you about our stuff that makes us money
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic