CODEWITHSUNDEEP
javamavenjdbcinformix
Manuel Pap
Manuel Pap

Reputation: 1399

JDBC Informix Transaction Rollback

On a maven project I am connecting to an informix database and I want to make a rollback when an exceptions occurs. It seems that informix doesn't support rollback and I receive the following exception.

java.sql.SQLException: Not in transaction.
    at com.informix.util.IfxErrMsg.buildExceptionWithMessage(IfxErrMsg.java:416)
    at com.informix.util.IfxErrMsg.buildIsamException(IfxErrMsg.java:401)
    at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3096)
    at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3368)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2292)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2217)
    at com.informix.jdbc.IfxSqli.executeRollback(IfxSqli.java:646)
    at com.informix.jdbc.IfxSqliConnect.rollback(IfxSqliConnect.java:2124)
    at com.company.helpers.DBDriver.makeConnection(DBDriver.java:72)
    at com.company.Main.main(Main.java:40)

And here is the snippet in question. The exception occurs when calling connection.rollback(). For testing purposes, I called rollback on try clause.

     try{
        connection = DriverManager.getConnection(this.url,this.username,this.password);
        LOGGER.info("Database connection successful.");
        statement = connection.createStatement();
        resultSet = statement.executeQuery("select FIRST 10 * from clients");
        while (resultSet.next()) {
            System.out.println(resultSet.getString("id") + ", " + resultSet.getString("name"));
        }

        String sql = "UPDATE clients\n" +
                "\tSET idhost=5\n" +
                "\tWHERE id=9058;\n";
        statement.executeUpdate(sql);
        connection.rollback(); //temporary to test rollback() 
        //connection.commit();

      }
      catch (Exception e) {
        //connection.rollback();
        LOGGER.error("Errors occurred in database.");
        LOGGER.error(e.getMessage(), e);

      }

Upvotes: 2

Views: 1005

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 754490

Informix has 4 types of database:

  • unlogged
  • buffered logging
  • unbuffered logging
  • (unbuffered logging and) MODE ANSI

An unlogged database does not support transactions. It may be that you've connected to an unlogged database. The buffered and unbuffered logging databases do not start a transaction until you execute BEGIN WORK (or just BEGIN). You might be failing because you didn't explicitly start a transaction. A MODE ANSI database automatically starts transactions and keeps them open until explicitly committed or rolled back, or when the session terminates, in which case they're rolled back. You can always rollback (or commit) in a MODE ANSI database.

It's possible that the JDBC standard requires the 'MODE ANSI' semantics. They're not hard to emulate — the driver simply has to send BEGIN WORK at appropriate points.

…Hmmm...

At the top of the exception stack trace, it says "Not in transaction". That's error -255; there's also error -256 "Transaction not available". If you were connected to an unlogged database, you'd get -256. Since you get -255, you must be connected to a logged database, but you didn't explicitly start a transaction, so you can't roll it back. Each statement is a self-contained transaction in the absence of BEGIN followed by COMMIT or ROLLBACK.

To fix, you'll need to start a transaction. That might be as simple as: 
Statement st = connection.createStatement;
int count = st.executeUpdate("BEGIN WORK");
st.close();

Or there may be an 'execute immediate' option that can be used instead. The code is untested; I'm not a JDBC programmer and don't have a setup to test JDBC code.

The analysis is basically correct. The suggested fix is not. See the answer by Brian Hughes for a discussion of autocommit which provides the correct way to resolve the problem.

Upvotes: 3

Brian Hughes
Brian Hughes

Reputation: 683

This is actually expected behavior when using JDBC autocommit. The Informix JDBC driver has auto-commit enabled by default. See https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html for a brief introduction to this.

So each statement you execute is automatically committed. The JDBC essentially executes a BEGIN WORK, EXECUTE, COMMIT WORK for each query and/or statement.

Thus when you explicitly execute a connection.rollback(), it will fail because all of your statements have been committed and you are technically not in a transaction.

You can turn this off with connection.setAutoCommit(false) then you can issue SQL statement "BEGIN WORK" to start a transaction and connection.rollback() to roll it back.

As Jonathan notes another quirk is if you use ANSI compatible database versus a normal logged database. If it is ANSI, then you don't need the "BEGIN WORK" because ANSI databases are always in a transaction and a new one is started for you.

In general if you want to work with transactions you have to turn off auto-commit.

Upvotes: 2

Related Questions