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);

      }

Solution

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.