Difference between TYPE_SCROLL_SENSITIVE and TYPE_FORWARD_ONLY

Question

I have some trouble with running a SELECT query on an AS400/DB2 database.

When I run the following code, I get an Exception that states, that the cursor is not valid.

String jdbcURL = "jdbc:as400://10.1.2.200";
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver()); 
Properties props = new Properties();
props.setProperty("user", "tracktool"); 
props.setProperty("password", "tooltrack1"); 
con = DriverManager.getConnection(jdbcURL, props);
stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 

int count = 0;
try {
    String sql = "select * from MVXJDTALR.CSYNBR where CNNBTY='ZZ'";
    assertTrue("Select lieferte kein ResultSet.", stmt.execute(sql));
    assertTrue("Keine Results im Resultset", stmt.getResultSet().first());
    count = stmt.getResultSet().getInt("CNNBNR");
    assertTrue("ResultSet hatte falsche Anzahl Spalten", count > 0);
} catch (Exception ex) {
    ex.printStackTrace();
    fail("Konnte den aktuellen Stand von CSYNBR nicht auslesen!");
}

After Changing just this line

stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 

To

stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 

It works perfect. I found in the Internet, that it has something to do with updates made to the result set while working with it. But I did not do any updates nor do I want to know them. I just try to read a single line and want to have one single value from this line. I could read the single value directly without reading the whole line, but this did not work either.

Here is a call trace when the exception occurs.

Logging before exception:

Toolbox for Java - Open Source Software, JTOpen 6.0, codebase 5722-JC1 V5R4M0.6
Properties  (7481705) : access = "all".
Properties  (7481705) : block size = "32".
Properties  (7481705) : block criteria = "2".
Properties  (7481705) : date format = "".
Properties  (7481705) : date separator = "".
Properties  (7481705) : decimal separator = "".
Properties  (7481705) : errors = "basic".
Properties  (7481705) : extended dynamic = "false".
Properties  (7481705) : libraries = "".
Properties  (7481705) : naming = "sql".
Properties  (7481705) : package = "".
Properties  (7481705) : package add = "true".
Properties  (7481705) : package cache = "false".
Properties  (7481705) : package clear = "false".
Properties  (7481705) : package error = "warning".
Properties  (7481705) : package library = "".
Properties  (7481705) : password = "".
Properties  (7481705) : prefetch = "true".
Properties  (7481705) : prompt = "".
Properties  (7481705) : remarks = "system".
Properties  (7481705) : sort = "hex".
Properties  (7481705) : sort language = "ENU".
Properties  (7481705) : sort table = "".
Properties  (7481705) : sort weight = "shared".
Properties  (7481705) : time format = "".
Properties  (7481705) : time separator = "".
Properties  (7481705) : trace = "true".
Properties  (7481705) : transaction isolation = "read uncommitted".
Properties  (7481705) : translate binary = "false".
Properties  (7481705) : user = "tracktool".
Properties  (7481705) : package criteria = "default".
Properties  (7481705) : lob threshold = "32768".
Properties  (7481705) : secure = "false".
Properties  (7481705) : data truncation = "true".
Properties  (7481705) : proxy server = "".
Properties  (7481705) : secondary URL = "".
Properties  (7481705) : data compression = "true".
Properties  (7481705) : big decimal = "true".
Properties  (7481705) : thread used = "true".
Properties  (7481705) : cursor hold = "true".
Properties  (7481705) : lazy close = "false".
Properties  (7481705) : driver = "toolbox".
Properties  (7481705) : bidi string type = "".
Properties  (7481705) : key ring name = "".
Properties  (7481705) : key ring password = "".
Properties  (7481705) : full open = "false".
Properties  (7481705) : server trace = "0".
Properties  (7481705) : database name = "".
Properties  (7481705) : extended metadata = "false".
Properties  (7481705) : cursor sensitivity = "asensitive".
Properties  (7481705) : behavior override = "0".
Properties  (7481705) : package ccsid = "13488".
Properties  (7481705) : minimum divide scale = "0".
Properties  (7481705) : maximum precision = "31".
Properties  (7481705) : maximum scale = "31".
Properties  (7481705) : translate hex = "character".
Properties  (7481705) : toolbox trace = "".
Properties  (7481705) : qaqqinilib = "".
Properties  (7481705) : login timeout = "".
Properties  (7481705) : true autocommit = "false".
Properties  (7481705) : bidi implicit reordering = "true".
Properties  (7481705) : bidi numeric ordering = "false".
Properties  (7481705) : hold input locators = "true".
Properties  (7481705) : hold statements = "false".
Properties  (7481705) : rollback cursor hold = "false".
Properties  (7481705) : variable field compression = "true".
Properties  (7481705) : query optimize goal = "0".
Properties  (7481705) : keep alive = "".
Properties  (7481705) : receive buffer size = "".
Properties  (7481705) : send buffer size = "".
Properties  (7481705) : XA loosely coupled support = "0".
Properties  (7481705) : translate boolean = "true".
Properties  (7481705) : metadata source = "1".
Properties  (7481705) : query storage limit = "-1".
Properties  (7481705) : decfloat rounding mode = "half even".
Properties  (7481705) : autocommit exception = "false".
Driver AS/400 Toolbox for Java JDBC Driver (21790187) : Using IBM Toolbox for Java JDBC driver implementation.
Toolbox for Java - Open Source Software, JTOpen 6.0, codebase 5722-JC1 V5R4M0.6
JDBC Level: 30
Connection 10.1.2.200 (21576085) : Client CCSID = 13488.
Connection 10.1.2.200 (21576085) : Setting server NLV = 2929.
Connection 10.1.2.200 (21576085) : Client functional level = V5R4M01   .
Connection 10.1.2.200 (21576085) : Data compression = RLE.
Connection 10.1.2.200 (21576085) : ROWID supported = true.
Connection 10.1.2.200 (21576085) : True auto-commit supported = true.
Connection 10.1.2.200 (21576085) : 128 byte column names supported = true.
Connection 10.1.2.200 (21576085) : Maximum decimal precision = 31.
Connection 10.1.2.200 (21576085) : Maximum decimal scale = 31.
Connection 10.1.2.200 (21576085) : Minimum divide scale = 0.
Connection 10.1.2.200 (21576085) : Translate hex = character.
Connection 10.1.2.200 (21576085) : query optimize goal = 0.
Connection 10.1.2.200 (21576085) : query storage limit = -1.
Connection 10.1.2.200 (21576085) : Using extended datastreams.
Connection 10.1.2.200 (21576085) : JDBC driver major version = 7.
Connection 10.1.2.200 (21576085) : i5/OS VRM = V6R1M0.
Connection 10.1.2.200 (21576085) : Server CCSID = 37.
Connection 10.1.2.200 (21576085) : Server functional level = V6R1M00014 (14).
Connection 10.1.2.200 (21576085) : Server job identifier = 546098/QUSER/QZDASOINIT.
Properties  (7481705) : decimal separator = ".".
Properties  (7481705) : date format = "dmy".
Properties  (7481705) : date separator = ".".
Properties  (7481705) : time format = "hms".
Properties  (7481705) : time separator = ":".
Connection LR59227P (21576085)  open.
Connection LR59227P (21576085) : Auto commit = "true".
Connection LR59227P (21576085) : Read only = "false".
Connection LR59227P (21576085) : Transaction isolation = "1".
Statement STMT0001 (16678784)  open. Parent: Connection LR59227P (21576085) .
Statement STMT0001 (16678784) : Escape processing = "true".
Statement STMT0001 (16678784) : Fetch direction = "1000".
Statement STMT0001 (16678784) : Fetch size = "0".
Statement STMT0001 (16678784) : Max field size = "0".
Statement STMT0001 (16678784) : Max rows = "0".
Statement STMT0001 (16678784) : Query timeout = "0".
Statement STMT0001 (16678784) : Result set concurrency = "1007".
Statement STMT0001 (16678784) : Result set holdability = "-9999".
Statement STMT0001 (16678784) : Result set type = "1003".
Statement STMT0001 (16678784) : Behavior Override = "0".
Statement STMT0001 (16678784) : Data to correlate statement with cursor Cursor CRSR0001 (6598415) .
Statement STMT0001 (16678784) : Executing SQL Statement -->[select * from MVXJDTALR.CSYNBR where CNNBTY='ZZ'].
Statement STMT0001 (16678784) : Prepared STMT0001*, SQL Statement -->[select * from MVXJDTALR.CSYNBR where CNNBTY='ZZ'].
Cursor CRSR0001 (6598415)  open.
Cursor CRSR0001 (6598415)  closed.
ResultSet CRSR0001 (20035600)  open. Parent: Statement STMT0001 (16678784) .
ResultSet CRSR0001 (20035600) : Conncurrency = "1007".
ResultSet CRSR0001 (20035600) : Fetch direction = "1000".
ResultSet CRSR0001 (20035600) : Fetch size = "0".
ResultSet CRSR0001 (20035600) : Max rows = "0".
ResultSet CRSR0001 (20035600) : Type = "1003".
Statement STMT0001 (16678784) : Executed STMT0001*, SQL Statement --> [select * from MVXJDTALR.CSYNBR where CNNBTY='ZZ'].
Statement STMT0001 (16678784) : Update count = -1.
Statement STMT0001 (16678784) : Result set = true.
Statement STMT0001 (16678784) : Number of result sets = 0.
Statement STMT0001 (16678784) : Row count estimate = 1.

Actual exception:

static method: Throwing exception, sqlState: 24000 reason: Cursor state not valid. vendor code -99999.java.sql.SQLException: Cursor state not valid.
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:389)
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:366)
        at com.ibm.as400.access.AS400JDBCResultSet.beforePositioning(AS400JDBCResultSet.java:1234)
        at com.ibm.as400.access.AS400JDBCResultSet.first(AS400JDBCResultSet.java:1343)
        at com.lr.tracktool.barcode.tests.ConnectionTest.testSQLSelectAndUpdateNew(ConnectionTest.java:389)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
        at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
        at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
        at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
        at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
        at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
        at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
        at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
        at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
        at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
        at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
        at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
        at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
        at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
        at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
        at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

    java.sql.SQLException: Cursor state not valid.
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:389)
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:366)
        at com.ibm.as400.access.AS400JDBCResultSet.beforePositioning(AS400JDBCResultSet.java:1234)
        at com.ibm.as400.access.AS400JDBCResultSet.first(AS400JDBCResultSet.java:1343)
        at com.lr.tracktool.barcode.tests.ConnectionTest.testSQLSelectAndUpdateNew(ConnectionTest.java:389)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
        at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
        at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
        at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
        at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
        at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
        at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
        at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
        at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
        at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
        at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
        at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
        at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
        at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
        at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
        at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
ResultSet CRSR0001 (20035600)  closed.
Statement STMT0001 (16678784)  closed.
Connection LR59227P (21576085)  closed.

Answer 1

Calling first() on a TYPE_FORWARD_ONLY ResultSet is required to throw an SQLException by the JDBC specification: (emphasis mine)

Throws:
SQLException - if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY

And this is exactly what happens in your code. If you want to check if a ResultSet has a row, use next(). next() is guaranteed to work with all result set types.

Answer 2

You are correct in your findings. However, let me clear the explanation as why this is happening.

According to Oracle documentation:

• TYPE_SCROLL_SENSITIVE The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set reflects changes made to the underlying data source while the result set remains open.
• TYPE_FORWARD_ONLY The result set cannot be scrolled; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database generates the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.

Here is the answer as why when you changed it then it started working. It's because the code is not using executeQuery() instead its using execute(). The main reason for this is because execute method should be used only when it is possible that a statement may return more than one ResultSet object. I have provided two links below for you one is the source for the first two bullet points above and the second source is about the explanation for executeQuery() and execute().

Retrieving and Modifying Values from Result Sets

JDBC Guide: Getting Started - Statement

Answer 3

Actually I am not understanding your problem from your long post. But from your Heading I want to tell you difference between TYPE_FORWARD_ONLY and TYPE_SCROLL_SENSITIVE.

If you choose TYPE_FORWARD_ONLY then your cursor will move in forward direction only. You can't move reverse back, i.e. you can read any row once only in one track.

And If you choose TYPE_SCROLL_SENSITIVE then you can move in reverse direction also multiple times.

If you want to to ask any more please summarise your query.