Creating a Scroll_Insensitive ResultSet using the SAP Hana JDBC Driver

Question

I'm trying to create a Scroll_Insensitive ResultSet using/in the SAP Hana JDBC Driver. When I run the below code:

Class.forName("com.sap.db.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:sap://10.32.86.10:30115/autocommit=false",username,password);                  
java.sql.Statement stmt = connection.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
java.sql.ResultSet resultSet = stmt.executeQuery("SELECT * FROM SAMPLE");
resultSet.next();
String hello = resultSet.getString(1);

I get the following exception:

com.sap.db.jdbc.exceptions.jdbc40.SQLDataException: Invalid argument resultSetType, use TYPE_FORWARD_ONLY.

If I replace the third line with:

java.sql.Statement stmt = connection.createStatement();

It works without a hitch. I need the ResultSet to be Scroll_Insensitive to be able to use methods such as

rs.previous(), rs.last(), rs.getRow(), etc.

The same code works perfectly for MySQL, Microsoft SQL, TeraData & Oracle. What might the problem with SAP Hana? Is there a workaround?

Answer 1

The answer simply is what the error message implies: SAP HANA's JDBC driver (currently) only provides you with a FORWARD ONLY cursor type.

Question here would be what specific characteristic of the TYPE_SCROLL_INSENSITIVE cursor type do you actually need here?

Answer 2

I also faced the same error where I wanted to check if the processed row was the last row of the result set and tried rs.last() method on resultset object. Here is what I found on SAP SCN:

Due to performance improvement when using forward only cursor we changed the default for the resultset type from TYPE_SCROLL_SENSITIVE to TYPE_FORWARD_ONLY starting with JDBC driver version 7.6. So I guess the exception comes from a statement where you didn't set the result set type while creating it. Please check if all of the statements that you want to be scrollable have set the correct resultset type.

here is the link to the thread: The operation is not allowed for result set type FORWARD_ONLY