Saturday, February 6, 2016

Invalid operation: result set is closed.

 The following exception can occur with the DB2 JCC Driver when calling rs.next() after the cursor has been positioned after the last row of the ResultSet.

  Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
    at com.ibm.db2.jcc.am.bd.a(bd.java:663)
    at com.ibm.db2.jcc.am.bd.a(bd.java:60)
    at com.ibm.db2.jcc.am.bd.a(bd.java:103)
    at com.ibm.db2.jcc.am.ResultSet.checkForClosedResultSet(ResultSet.java:4528)
    at com.ibm.db2.jcc.am.ResultSet.nextX(ResultSet.java:329)
    at com.ibm.db2.jcc.am.ResultSet.next(ResultSet.java:308)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.java:3110)


The following snippets can help to get a better understanding of why this error occurs.

db2> create table test(col1 varchar(10) ) ; // let us leave the table empty with no rows.

try {
            Context ctx = new InitialContext();
            DataSource ds = (DataSource)ctx.lookup("jdbc/db2");
            Connection con = ds.getConnection();           
            PreparedStatement pstmt = con.prepareStatement("select * from test");
            ResultSet rs  = pstmt.executeQuery();
            rs.next(); // this implicitly closes the ResultSet as there are no rows in the table test
            rs.next(); // this will result in the SQLException : Invalid operation: result set is closed.
      }   catch (Exception e){  }

As per the above code snippet, DB2 JCC Driver closes the result set implicitly when it find no more rows in the ResultSet and this is their default behavior. Hence calling ResultSet.next() on a closed resultset can lead to the exception "Invalid operation: result set is closed". 

 To get around this problem the jcc driver provides a property named "allowNextOnExhaustedResultSet" that can be set on the datasource as custom property in WAS.  Setting allowNextOnExhaustedResultSet = 1 ensures that ResultSet.next() returns false if the cursor was previously positioned after the last row of the ResultSet.

In the tWAS environment the property can be set as a datasource custom property and be sure to set the type as Integer and the value as 1. For liberty you can define the property in the server.xml like below:
<dataSource id="DB2" jndiName="jdbc/db2">                         
     <jdbcDriver libraryRef="DB2JCC4Lib"/>                        
     <properties.db2.jcc databaseName="SAMPLE" password="password" 
      portNumber="50000" serverName="localhost" user="user"
      allowNextOnExhaustedResultSet="1" />                       
</dataSource>                                                     

Disclaimer: These are my personal views and does not reflect my employer in any way!!


No comments: