Handle transactions

By default, all new Connection objects are in autocommit mode. When autocommit mode is on, a COMMIT statement is automatically executed after each statement that is sent to the database server. To turn off autocommit mode , explicitly call Connection.setAutoCommit(false).

When autocommit mode is off, Informix® JDBC Driver implicitly starts a new transaction when the next statement is sent to the database server. This transaction lasts until the user issues a COMMIT or ROLLBACK statement. If the user has already started a transaction by executing setAutoCommit(false) and then calls setAutoCommit(false) again, the existing transaction continues unchanged. The Java™ program must explicitly terminate the transaction by issuing either a COMMIT or a ROLLBACK statement before it drops the connection to the database or the database server.

In a database that has been created with logging, if a COMMIT statement is sent to the database server and autocommit mode is on, the error -255: Not in transaction is returned by the database server because there is currently no user transaction started. This occurs whether the COMMIT statement was sent with the Connection.commit() method or directly with an SQL statement.

In a database created in ANSI mode, explicitly sending a COMMIT statement to the database server commits an empty transaction. No error is returned because the database server automatically starts a transaction before it executes the statement if there is no user transaction currently open.

For an XAConnection object, autocommit mode is off by default and must remain off while a distributed transaction is occurring. The transaction manager performs commit and rollback operations; therefore, you avoid performing these operations directly.

For HCL Informix® releases later than 11.50.xC2, two JDBC classes support SQL transactions that can be rolled back to a savepoint (rather than canceled in its entirety) after an adverse event is encountered:
  • IfmxSavepoint (Interface )
  • IfxSavepoint (Savepoint class)
JDBC applications can create, destroy, or rollback to savepoint objects through the following standard JDBC methods:
Table 1. JDBC savepoint classes and methods
Class Method
IfxConnection

setSavepoint()
releaseSavepoint()
rollback(savepoint)

IfxSavepoint

getSavepointId()
getSavepointName()

These two methods are not interchangeable. A call to getSavepointName() fails with an error unless the savepoint object is declared with a string argument to the setSavepoint() method or to the setSavepointUnique() method. Similarly, an error is returned if you call getSavepointId() for a named savepoint object.

In addition, the setSavepointUnique() method can set a named savepoint whose identifier is unique. While the unique savepoint is active,Informix® issues an exception if the application attempts to reuse its name within the same connection.

The following restrictions apply to savepoint objects in JDBC:
  • Savepoints are not valid within XA transactions.
  • Savepoints cannot be used unless the current connection sets autocommit mode off.
  • Savepoints are not valid in connections to unlogged databases.
  • Savepoints cannot be referenced in a triggered action.
  • In cross-server distributed queries in which any participating subordinate server does not support savepoint objects, a warning is issued if you set a savepoint after connecting to a server that does not support savepoints, and any call to rollbacksavepoint fails with an error.
See the descriptions of the SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK WORK TO SAVEPOINT statements in Informix® Guide to SQL: Syntax for more information about using savepoint objects in SQL transactions.