Oninit
Talk to an expert →

Informix Error -398

-398 Cursor manipulation must be within a transaction.

An attempt to use an UPDATE or a DELETE WHERE CURRENT OF statement produces this error message. This statement would modify a table through a cursor. However, this database uses a transaction log. When that is the case, modifications made through a cursor must be placed within the bounds of a transaction.

Review the program logic, and check that it executes a BEGIN WORK statement prior to this statement and that it ends the transaction at some reasonable point. If the program has to work with both databases that use transactions and those that do not, you can have it check the second element of the sqlwarn array of the SQL Communications Area. This area will contain the letter W after a DATABASE statement if the database has a transaction log, and a space if it does not.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-398 fires when UPDATE/DELETE ... WHERE CURRENT OF is attempted through a cursor against a logged database without an open transaction — logged databases require explicit transaction boundaries around cursor-based modifications.

  1. A cursor-based UPDATE/DELETE ... WHERE CURRENT OF issued without a preceding BEGIN WORK on a database with transaction logging enabled.
  2. Application code written against (or tested on) an unlogged database, later run against a logged one without adding the necessary transaction boundaries.
  3. A generic data-access layer that doesn't check whether the target database uses transaction logging before performing cursor-based modifications.
Solutions / Resolution
  1. Wrap the cursor-based modification in BEGIN WORK / COMMIT WORK, per the official guidance, on a logged database.
  2. For applications supporting both transactional and non-transactional databases, check the second element of the sqlwarn array in the SQL Communications Area to determine at runtime whether the connected database uses transaction logging, and branch the transaction-boundary logic accordingly.
Examples
Wrapping a cursor-based update in a transaction
BEGIN WORK;
DECLARE curs1 CURSOR FOR SELECT status FROM orders FOR UPDATE;
OPEN curs1;
FETCH curs1;
UPDATE orders SET status = 'shipped' WHERE CURRENT OF curs1;
COMMIT WORK;
Checking sqlwarn for transaction-logging status (embedded SQL/ESQL example)
/* after connecting to the database */
if (sqlca.sqlwarn.sqlwarn2 == 'W') {
    /* database is logged; wrap cursor modifications in
       BEGIN WORK / COMMIT WORK */
}
Diagnostic Checks
  1. Confirm whether the target database has transaction logging enabled.
  2. Check whether the cursor-based modification is wrapped in BEGIN WORK/COMMIT WORK (or ROLLBACK WORK).
  3. For portable application code, check sqlwarn2 rather than assuming a fixed database configuration.
Related Errors / Related Topics
  • -377 — "Must terminate transaction before closing database." A related transaction-state restriction, on the closing side rather than requiring one to exist.
  • -290 — "Cursor not declared with FOR UPDATE clause." A related cursor-update restriction, about the FOR UPDATE clause rather than transaction boundaries.

On a logged database, cursor-based UPDATE/DELETE ... WHERE CURRENT OF always needs an explicit transaction wrapped around it — check sqlwarn2 for portable code that might run against either logged or unlogged databases.