• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

Query Dillema

 
Ranch Hand
Posts: 180
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 40791
828
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 26909
82
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your valuable information
 
You showed up just in time for the waffles! And this tiny ad:
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
reply
    Bookmark Topic Watch Topic
  • New Topic