Informix Error -526: Updates are not allowed on a scroll cursor.

Cause and resolution

Updates are not allowed on a scroll cursor.

For a DECLARE statement, the clause FOR UPDATE is not allowed in conjunction with the SCROLL keyword. For an UPDATE statement in an ANSI-compliant database (in which the FOR UPDATE clause is not required when declaring a cursor for update), the cursor named in this statement was declared with the SCROLL keyword and may not be used for updates. The way a scroll cursor is implemented makes it unsafe for updating a table, since it will sometimes not reflect the current state of the selected rows. If you want to use a scroll cursor to examine rows and then update them, you may redesign your application in the following way (among many). Use the scroll cursor to select also the ROWID of each row. Declare a second, nonscrolling cursor that selects one row for update based on its ROWID. When it is time to update a selected row:

* Open the update cursor using the ROWID value found by the scrolling cursor.

* Fetch the row, and check the error code (the row might have been deleted).

* If the fetch succeeded, verify that the row contents are unchanged from those selected by the scrolling cursor (the row is now locked, so it cannot change further, but it might have changed between the two fetches).

* If the row is unchanged, update it using the nonscrolling cursor.

* Close the nonscrolling cursor.

* This procedure ensures that the update reflects the current state of the table but also retains the convenience of the scrolling cursor. A fetch by ROWID of a recently fetched row will usually entail no disk activity and so will not cost much time.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-526 fires when a cursor declared with SCROLL is also declared FOR UPDATE — the two aren't allowed together, because a scroll cursor's ability to move backward and re-fetch rows makes it unsafe to guarantee the row still reflects the state it had when positioned for update.

DECLARE ... SCROLL CURSOR ... FOR UPDATE combined in a single declaration — the direct, only cause.
Application code assuming scrollable and updatable cursor semantics compose, carried over from a data-access layer or ORM pattern that doesn't distinguish the two.

Solutions / Resolution

Don't combine SCROLL and FOR UPDATE on the same cursor. Use a scroll cursor to browse and select rows (including their ROWID), then declare a separate, non-scrolling cursor to perform the actual update, positioned via WHERE ROWID = ? rather than WHERE CURRENT OF.
Verify the row's state hasn't changed between the scroll cursor's read and the separate update, since the two are no longer the same cursor operation — check the relevant columns (or a version/timestamp column) still match what the scroll cursor last saw before applying the update.

Examples

The disallowed combination

DECLARE order_cursor SCROLL CURSOR FOR
  SELECT order_id, status FROM orders FOR UPDATE;
-- -526: SCROLL and FOR UPDATE cannot be combined

The two-cursor workaround

DECLARE browse_cursor SCROLL CURSOR FOR
  SELECT order_id, status, ROWID rid FROM orders;

-- Browse and select the target row with browse_cursor, capturing rid.

DECLARE update_cursor CURSOR FOR
  SELECT order_id, status FROM orders WHERE ROWID = ? FOR UPDATE;
OPEN update_cursor USING captured_rid;
FETCH update_cursor INTO ...;
-- confirm the fetched row still matches what browse_cursor last saw, then:
UPDATE orders SET status = 'shipped' WHERE CURRENT OF update_cursor;

Diagnostic Checks

Check every DECLARE ... CURSOR for SCROLL combined with FOR UPDATE in the same declaration — that combination is always rejected, regardless of the query behind it.

Related Errors / Related Topics

No closely related error codes are cross-referenced for -526 in this set yet.

Split scrollable browsing and updating into two cursors, linked by ROWID, with an explicit re-check that the row hasn't changed before the update fires.