• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Jeunne Seunne
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • 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









 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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
  • 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!
 
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
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
  • 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!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic