aspose file tools*
The moose likes JDBC and the fly likes About getGeneratedKeys() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "About getGeneratedKeys()" Watch "About getGeneratedKeys()" New topic
Author

About getGeneratedKeys()

Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
Hi, somewhere in my program I try to get the generated keys of my insert statement. However, I set auto commit to false. Will getGeneratedKeys() work even when I set to autocommit false? Here's my sample code...




and then I use it like this...



Parent ID prints blank!!!? Thank you...


SCJP 1.5
http://devpinoy.org/blogs/lamia/ - http://everypesocounts.com/
Travis Hein
Ranch Hand

Joined: Jun 06, 2006
Posts: 161
hmm, the getGeneratedKeys() is new in java 1.4; it could be your JDBC driver does not really implement this completely or properly yet.

It may be that this feature does not work with prepared statements ?

I was going to say to try to use the alternate signature for executeUpdate:
int executeUpdate(String sql,
int autoGeneratedKeys)
throws SQLException

the jdbc API docs say :
autoGeneratedKeys - a flag indicating whether auto-generated keys should be made available for retrieval; one of the following constants: Statement.RETURN_GENERATED_KEYS Statement.NO_GENERATED_KEYS


it could be your JDBC driver implementation uis by default not making the generated keys available when the executeUpdate() is called.

but that you need to pass it a query string, which defeats the good of using prepared statements.

hmmm...

would it be possible to have a mthod that invokes a sequence, or generates the keys, then you would pass that value into your insert statement:


hmm, the getGeneratedKeys() is new in java 1.4; it could be your JDBC driver does not really implement this completely or properly yet.

It may be that this feature does not work with prepared statements ?

I was going to say to try to use the alternate signature for executeUpdate:
int executeUpdate(String sql,
int autoGeneratedKeys)
throws SQLException

the jdbc API docs say :
autoGeneratedKeys - a flag indicating whether auto-generated keys should be made available for retrieval; one of the following constants: Statement.RETURN_GENERATED_KEYS Statement.NO_GENERATED_KEYS


it could be your JDBC driver implementation uis by default not making the generated keys available when the executeUpdate() is called.

but that you need to pass it a query string, which defeats the good of using prepared statements.

hmmm...

would it be possible to have a mthod that invokes a sequence, or generates the keys, then you would pass that value into your insert statement:



Error: Keyboard not attached. Press F1 to continue.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

I would avoid using getGeneratedKeys() altogether (although I can greatly understand your desire to use it), it is barely supported by most drivers/dbs such that you can't really rely on. I think only got it to work once out of a number of tries.

Generating keys and using those values is a non-trivial matter. One solution (most common) is to always query for the max row after you create a record. If done as part of a transaction there are ways to make this somewhat safe.

Another solution I prefer is to not use database generated keys but generate the keys yourself. This probably has the best success since you never have to worry about someone else getting your key... if your insert fails (assuming the column is a key and/or unique) the transaction will roll back. Of course, there are ways of preventing that by using a static key generator.


My Blog: Down Home Country Coding with Scott Selikoff
 
jQuery in Action, 2nd edition
 
subject: About getGeneratedKeys()