I use unixODBC to perform database actions on an Informix database. The situation is as follows: I have a result set from which I read records using SQLFetch. In between, autocommit must be disabled. Then data from another table is modified and the transaction is terminated using SQLEndTran (commit or rollback).

When another SQLFetch is then attempted, it fails because the cursor is no longer valid or the result set is gone.

According to the Informix documentation, setting CURSORBEHAVIOR=1 in odbc.ini should retain the cursors after the end of the transaction, but this does not work.

The following code is a minimal example:

SQLHENV   henv  = SQL_NULL_HENV;
    SQLHDBC   hdbc  = SQL_NULL_HDBC;
    SQLHSTMT  hstmt = SQL_NULL_HSTMT;
 
    SQLRETURN retcode;
 
 
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                            (SQLPOINTER*)SQL_OV_ODBC3, 0);
    SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
 
    SQLDriverConnect(hdbc, NULL, (SQLCHAR*)connstr,
            SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
 
 
    SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 
    SQLPrepare(hstmt, (SQLCHAR*)"SELECT * from tab1", SQL_NTS);
    SQLExecute(hstmt);
 
    /* fetching ... */
    SQLFetch(hstmt);
    SQLFetch(hstmt);
    SQLFetch(hstmt);
 
    /* autocommit off */
    SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
                        (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0);
 
    /*
     * Do something..
     *
     */
 
    /* commit or rollback */
    SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);

    /* autocommit on */
    SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
                        (SQLPOINTER)SQL_AUTOCOMMIT_ON, 0);
 
    /* try fetching again, fails with  HY010 (function sequence error)  */
    SQLFetch(hstmt);

    

This works under Oracle, but not under Informix. What needs to be set here?