CODEWITHSUNDEEP
informix
Delwin Andrews
Delwin Andrews

Reputation: 145

IBM Informix database error 'Not in transaction'

Informix Version 11.7.

When we try execute the query getting following exception as:

ERROR [HY000] [Informix .NET provider][Informix]Not in transaction..

Before execution we checked the connection status is shown as 'OPEN' but still we got this error.

Please share if anyone faced the similar exception?

Upvotes: 2

Views: 4824

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753455

Informix can support databases with different 'modes':

  • Unlogged — no transactions available.
  • Logged (buffered) — transactions available, but require an explicit BEGIN WORK statement to start the transaction, and once a transaction is started, a COMMIT is required to make the changes permanent (or a ROLLBACK to cancel the changes).
  • Logged (unbuffered) — very similar to a database with buffered logging.
  • MODE ANSI — most SQL statements start a transaction; a COMMIT is required to make changes permanent (or a ROLLBACK to cancel the changes).

From the error you got ("-255: not in a transaction"), you appear to be using a logged database, and the statement you're executing requires a transaction, but you aren't in a transaction. The primary context in which you get this is if you execute COMMIT or ROLLBACK before you execute a BEGIN WORK statement. If you were using an unlogged database, you'd get "-256: transaction not available" as the message; if you were using a MODE ANSI database, you'd be able to COMMIT or ROLLBACK, but once you've executed a statement other than those (for example, a SELECT), you'd not be able to explicitly BEGIN WORK — you'd get error "-535: already in transaction".

So, it appears that yu have a logged database, your code executed either COMMIT or ROLLBACK (possibly behind the scenes) before it executed a BEGIN WORK.

To resolve the problem, you can consider switching to a MODE ANSI database (not a change to be made lightly — there are other ramifications to the change that typically have a big impact on applications, which mean it probably isn't the correct answer), but you'll probably need to ensure that your package is aware of the rules of the Informix database you're connected to and either explicitly starts transactions with BEGIN WORK or doesn't send COMMIT or ROLLBACK operations before executing BEGIN WORK.

(In a database when there is no transaction in progress, each separate SQL statement forms its own transaction. If a session terminates with an uncommitted transaction in progress, that transaction is rolled back. The changes are committed only if an explicit COMMIT is given.)

Upvotes: 3

Related Questions