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

[Terence M. Bandoian]

On 9/8/2016 3:20 AM, Mohamad Abbas wrote:
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!


If a JDBC driver does not support TYPE_SCROLL_INSENSITIVE, it may fall 
back silently to TYPE_FORWARD_ONLY.

-Terence Bandoian
 http://www.tmbsw.com/


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org