Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Statement.getGeneratedKeys() with Sybase not working

 
vinuraj
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please see the following code :

objStatement = objConnection.createStatement();
int i = objStatement.executeUpdate("INSERT INTO employee(address) VALUES('street1') ", Statement.RETURN_GENERATED_KEYS);
objResultSet = objStatement.getGeneratedKeys();
if (objResultSet.next()) {
int key = objResultSet.getInt(1);
}


[The employee tables first field is an auto increment]

The above code causes an error :
Exception in thread "main" com.sybase.jdbc3.utils.UnimplementedOperationException: The method com.sybase.jdbc3.jdbc.SybStatement.executeUpdate(String, int) has not been completed and should not be called.
.........

Why getGeneratedKeys() in Sybase is not Working with jdk1.5 and jdbc3 ? Please help ?

Thanks in advance
Vinuraj
 
Kaydell Leavitt
Ranch Hand
Posts: 690
Eclipse IDE Firefox Browser Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Java Ranch,

Try this:

Kaydell


[ July 26, 2007: Message edited by: Kaydell Leavitt ]
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just as a side note, getGeneratedKeys() is *not* supported by all JDBC drivers, I'd check the documentation. I try to avoid using this function if I can because of this limitation.

One common solution in large enough systems (where concurrency is paramount) is to not use keys generated by the database. Often times a singleton-like service provides all new keys for the system so that all new keys are known prior to creation.
 
vinuraj
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for all your suggestions.

I got the solution. It is working when I used jconn3.jar with jdbc3 and the following code

objStatement = objConnection.createStatement();
int i = objStatement.executeUpdate("INSERT INTO employee(address) VALUES('street1') ", Statement.RETURN_GENERATED_KEYS);
objResultSet = objStatement.getGeneratedKeys();
if (objResultSet.next()) {
int key = objResultSet.getInt(1);
}


thanks
Vinuraj
 
Kaydell Leavitt
Ranch Hand
Posts: 690
Eclipse IDE Firefox Browser Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just as a side note, getGeneratedKeys() is *not* supported by all JDBC drivers...

...One common solution in large enough systems (where concurrency is paramount) is to not use keys generated by the database.


I'm thinking of doing away with auto-generated keys and using a trigger instead. For example, my inventory item record could have a field called ITEM_CODE instead of an auto-key field called ID. Then a preference would control whether the field is automatically populated with a value calculated in a trigger or the user could enter a value such as a UPC code or an ISBN number.

Then, when I export data, the ITEM_CODE field has meaning and when I import my own export, I have control can set the ITEM_CODE field from the value in the import file, instead of having an auto-key and not being in control of the value.

What do you think of this approach?

Kaydell
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"global infonet",
Welcome to the JavaRanch.

We're a friendly group, but we do require members to have valid display names.

Display names must be two words: your first name, a space, then your last name. Fictitious names are not allowed.

Please edit your profile and correct your display name since accounts with invalid display names get deleted, often without warning

thanks,
Dave
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Kaydell Leavitt:
I'm thinking of doing away with auto-generated keys and using a trigger instead.


My short answer on this is to avoid triggers all together (except for audit trails/reporting) since they are extremely non-standard between databases and you'll lose portability in your application. Plus, I've never been a fan of event-based actions not initiated/controlled by the application server.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic