[TOMCAT-JDBC] rs.absolute() throws "Invalid operation for forward only resultset: absolute"

[Mohamad Abbas]

Hello,

I am working on a legacy web application running on a tomcat 8.0.37, and i
am getting an error "Invalid operation for forward only resultset:
absolute" when trying to use an InsensitiveScrollableResultSet (to paginate
results).

To make it simple and reproduce the problem i've created a test project who
contains the "Main" class below. The test project is executed on on a
windows 7, using jdk1.8.0_92. The only jars are present in the classpath of
the project are: tomcat-jdbc(8.0.37), tomcat-juli(8.8.37),
and ojdbc7-12.1.0.2.0.

Those are the steps to reproduce the problem:

   - Create a Statement using the method .prepareStatement(String)=> OK
   - Execute the query => OK (simple query: SELECT 1 FROM dual)
   - Create a new Statement (with a SCROLL_INSENSITIVE type) for the same
   query (SELECT 1 FROM dual) => KO (or OK?), tomcat-jdbc is returning the
   cached Statement created in Step 1 (a FORWARD_ONLY Statement).
   - Execute the query => OK
   - Call absolute method => Exception: Invalid operation for forward only
   resultset: absolute

I don't know if it is really a bug in tomcat-jdbc or if my problem is due
to a misconfiguration.

Here is the code:

package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class Main {

    private static DataSource datasource;

    static {
        PoolProperties p = new PoolProperties();
        p.setUrl("jdbc:oracle:thin:@localhost:1531:test");
        p.setDriverClassName("oracle.jdbc.OracleDriver");
        p.setUsername("test");
        p.setPassword("test");
        p.setTestWhileIdle(true);
        p.setTestOnBorrow(false);
        p.setValidationQuery("SELECT 1 FROM dual");
        p.setValidationInterval(30000);
        p.setMaxActive(1); // to simplify the test
        p.setInitialSize(1); // to simplify the test
        p.setMaxWait(1); // to simplify the test
        p.setMinEvictableIdleTimeMillis(30000);
        p.setMinIdle(1); // to simplify the test
        p.setMaxIdle(1); // to simplify the test
        p.setJdbcInterceptors("StatementCache(max=10)"); // If i
replace max=10 by max = 0, the problem disappears
        datasource = new DataSource();
        datasource.setPoolProperties(p);
    }

    public static void main(String[] args) throws Exception {
        testWithForwardOnlyResultSet(datasource);
        testWithInsensitiveResultSet(datasource);
    }

    private static void testWithForwardOnlyResultSet(DataSource datasource)
throws Exception {
        Connection con = datasource.getConnection();
        PreparedStatement st = con.prepareStatement("select 1 from dual");
        ResultSet rs = st.executeQuery();
        while (rs.next()) {
            System.out.println("Result = " + rs.getString(1));
        }
        rs.close();
        st.close();
        con.close();
    }

    private static void testWithInsensitiveResultSet(DataSource datasource)
throws Exception {
        Connection con = datasource.getConnection();
        PreparedStatement st = con.prepareStatement("select 1 from dual",
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery();
        rs.absolute(1);
        while (rs.next()) {
            System.out.println("Result = " + rs.getString(1));
        }
        rs.close();
        st.close();
        con.close();
    }

}

Please tell me, if you need more informations.

Thanks for your help!