For some reason, the Oracle JDBC driver may move the ResultSet cursor somewhere else (not to the same row, and not to the next row) when updateRow is called (I am also inserting and deleting rows). How can I avoid this problem?
Note: The results are ordered by the primary key of the table (I've specified this in the SQL). But I'm increasingly suspecting that the "order by" clause is not working properly.
No where in the documentation says that after an updateRow operation the cursor is moved to the next row...
Sources:
Forward only updatable result sets maintains a cursor which can only move in one direction (forward), and also update rows. This has to be created with concurrency mode ResultSet.CONCUR_UPDATABLE and type ResultSet.TYPE_FORWARD_ONLY.
Note: The default type is ResultSet.TYPE_FORWARD_ONLY.
Visibility of changes
After an update or delete is made on a forward only result set, the result set's cursor is no longer on the row just updated or deleted, but immediately before the next row in the result set (it is necessary to move to the next row before any further row operations are allowed). This means that changes made by ResultSet.updateRow() and ResultSet.deleteRow() are never visible.
If a row has been inserted, i.e using ResultSet.insertRow() it may be visible in a forward only result set.
Conflicting operations The current row of the result set cannot be changed by other transactions, since it will be locked with an update lock. Result sets held open after a commit have to move to the next row before allowing any operations on it.
Some conflicts may prevent the result set from doing updates/deletes:
If the current row is deleted by a statement in the same transaction, calls to ResultSet.updateRow() will cause an exception, since the cursor is no longer positioned on a valid row.
This problem was due to user error. I had another copy of the application running on a different machine, which I had forgotten about, and that was changing the database at the same time.
