Win a copy of Terraform in Action this week in the Cloud forum!
  • 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:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Resultset updateRow() throws exception for row with auto generated key

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I am having problem in calling the updateRow() when I try to update a row. The table I was trying to update has an auto generated key field (BUDGET_ID) and this causes the updateRow() to throw an exception.


Here's a snippet of my code:

=================================
s = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

String query = "SELECT * FROM BUDGET";
rs = s.executeQuery(query);
int ctr = 0;
while(rs.next()){
rs.absolute(ctr+1);
rs.updateInt("NMONTH", ctr);
rs.updateRow();
ctr++;
}
=================================



On line rs.updateRow(), it throws the following error.

com.ibm.db2.jcc.b.SqlException: A value cannot be specified for column "BUDGET_ID" which is defined as GENERATED ALWAYS.


Are there any ways on how I can update this row? Thanks for your help.


-Jeunne









 
Bartender
Posts: 2659
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What if you replace "SELECT * FROM BUDGET" with a query that does not include the BUDGET_ID?

e.g.: select NMONTH from BUDGET
 
Jeunne Seunne
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried and it was stucked on that line... could you tell me what's happening? I just need to update the row. Could you suggest of other way? Thanks again!
 
author
Posts: 4278
34
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Not all database drivers support scrollable/update SELECT statements. It's considered a non-standard feature at best and a broken one (as you might be seeing) at worst. Why not replace the SELECT with an UPDATE statement?

As for your specific code, though, the rs.absolute(ctr+1) seems unnecessary since your iterating through your results in order anyway. In other words, rs.absolute() in your code shouldn't move anything at all, so best to remove it. If you meant to iterate through the results in a particular order, you need to make that part of your select query such as "SELECT * FROM BUDGET ORDER BY ...". Lastly, usually for update SELECT queries you need the syntax "SELECT ... FOR UPDATE" or something similar.

Ultimately, though, I'd look into writing your queries as a simple UPDATE statement that returns nothing in JDBC, since that would likely be the fastest and safest.
 
Jeunne Seunne
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Scott. You're right I can delete the rs.absolute() from the code. Also For the select query, so I can use SELECT... FOR UPDATE? and the rest of my code will be the same? I haven't used the SELECT... FOR UPDATE actually.

Thanks for your help!
 
reply
    Bookmark Topic Watch Topic
  • New Topic