This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

About getGeneratedKeys()

 
Timothy Sam
Ranch Hand
Posts: 751
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
Travis Hein
Ranch Hand
Posts: 161
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

 
Scott Selikoff
author
Saloon Keeper
Posts: 3901
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic