ResultSet Question 2

[msloan]

I keep getting an error when I attempt to update a ResultSet - Fetch type out of range

Here is the code:

String strSQL = "Select * From tblwEquipSched Where fldwEquipSchedKey = " + intSheduleKey;

try
{
Statement stmt = conUserCurrentConnection.createStatement();
ResultSet rResult = stmt.executeQuery(strSQL);
if (rResult.next())
{
rResult.moveToCurrentRow();
rResult.updateDate("fldwEquipSchedPMDate", new
java.sql.Date(dteLastPMDate.getTime()));

rResult.updateRow();
}

rResult.close();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}


From doing a few System.out.printlns - I know it is hanging up when I call updateRow().

Any ideas?

Thanks

Matt

 

[godcomplex]

You would be better off writing the update statement yourself in sql and sending the newly created update statement, instead of trying to update the result set directly. Result sets seem to be a little unstable and unpredictable.

-gc

 

[daniel135]

that looks far too complex for what you're trying to do... selecting all the fields for a row, then updating them one at a time, for each row in the result set... so there are 11 db calls if there are 10 rows returned...

you could try this instead:

update <tableName>
set <columnName> = <newVal> [, set <columnName> = <newVal> ]
[where <columnName> = <someValue>]

which in your case would translate to:

String sql = &quot; update tblwEquipSched&quot;
+&quot; set fldwEquipSchedPMDate = sysdate&quot;
+&quot; where fldwEquipSchedKey = :intSheduleKey&quot;;

(You can also use a '?' instead of the :intSheduleKey... I use the later because it transfers well to TOAD, and it's easier to tell what it is.)

get your PreparedStatement, set the value for intSheduleKey using ps.setInt(1, value); where ps is your PreparedStatement.

That then only requires one call to the db, regardless of how many rows are updated, and fewer lines of code. Faster, cleaner, lazier... good programming practice!

 

[xutopia]

&quot;Faster, cleaner, lazier....&quot; I like that... Gary

http://www.xutopia.com

Haran

 

[msloan]

I agree with you all, however, the architecture (and those who developed the architecture) used here frowns upon the &quot;update&quot; method of executing queries, as you have all suggested. So, if anyone knows why I am getting that fetch error, I would greatly appreciate some insight as to why.

Thanks

Matt

 

[daniel135]

weird... really, really weird. Anyways, in this situation you are not getting a fetching error. (as per your first post.) It sounds like a problem inserting the row.

One thing that might be worth checking is the spelling of the field. Sounds silly, but so are most of my mistakes...
and did you do a println of 'dteLastPMDate.getTime()'?

Also, a couple style comments. While small variables are generally a bad idea, I find using ps for PreparedStatement and rs for ResultSet good for readability. I wouldn't select all the fields for the row, but only the field that needs to be changed.

If those things don't do it, please print out the exact error message... and get the error code too if you're using Oracle.

Oh, one last thing. Feel free to tell your architects that I think they're exceedingly weird.

 

[msloan]

daniel135,

All valid points - I have limited my select statement to only the columns I need - in this case two. I have double checked column names (spelling and otherwise) and making sure the values I am attempting to update with are valid. When I call this select statement, I only expect one row to be returned - and that is the case. Did a System.out.print on dteLastPMDate.getTime() - looks good.

Please see the exact print out of the error message I am receiving:
ServerException:[ODBC Error], SQLFetchScroll(UPDATE) DBVendorCode:0:, DBState:S1106:. [Microsoft][ODBC SQL Server Driver]Fetch type out of range

As you can probably tell, I am using SQL Server - 7.0.

Also, you will be happy to know that several developers have expressed dissatisfaction with parts of the arcitecture, however, it hasn't done much good...

Regards,

Matt

 

[daniel135]

Ugh. Microsoft. That pretty much sums up a lot of our troubles here too.

So... Two things in your code could be done to try to see if you can get it to work. 1- use the index of the field rather than the name of the column. 2- try eliminating the 'moveToCurrentRow();' statement. I've never actually needed to use it. Just in case.

If those two last ditch tries/tricks don't work, you'll need to look at the long (VERY LONG) list of bugs for SQL Server 7.0. I found a french page which seemed rather unappealing to me:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q254369

And then, if that doesn't work, you could try convincing the goofball architects at your company to use a more robust DB product or just plain allow you to execute simple update statements. Good luck!