getQuery with blocksize parameter problem

[Mark Ma]

I am using getQuery method with blocksize parameter to retrieve data from database and display in grid. I hard code the blocksize ie. 50, if total records in database is less then 50, I got error. my application deal with multiple tables, I don't know how many records in each table. if I really like to using blocksize parameter, how should I set up the blocksize ?

[mcarniel]

"I got error" is a phrase useless!
Please describe in detail what EXACTLY you mean when you talk about an error. Is there a stack trace? an exception?
Include a piece of code related to the point where the error happens.

[Mark Ma]

Hi mcarniel,

we are using an applet to connect to DB2 9.5 database, here is the error message:
com.ibm.db2.jcc.am.SqlException:  Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

the following is the code:
public Response loadData(
      int action,
      int startIndex,
      Map filteredColumns,
      ArrayList currentSortedColumns,
      ArrayList currentSortedVersusColumns,
      Class valueObjectType,
      Map otherGridParams) {  
  try {
  ArrayList vars = new ArrayList();      
  GridParams tmpGridParams = new GridParams(
              action,
              startIndex,
              filteredColumns,
              currentSortedColumns,
              currentSortedVersusColumns,
              otherGridParams
            );
  return myQueryUtil.getQuery(
  conn,
  selectSql,
  vars,
  attribute2dbField,
  GenericGridVO.class,
  "Y",
  "N",
  tmpGridParams,
  this.MAXROWS,
  true
  );
 

    }
    catch (Exception ex) {
      ex.printStackTrace();
      return new ErrorResponse(ex.getMessage());
    }

Here is some information I found:
https://www-304.ibm.com/support/docview.wss?uid=swg21461670

Please help.

[mcarniel]

I don't know what myQueryUtil does, but it seems that the problem is in that class, which is not an OpenSwing class.
Anyway, a conn variable is passed to that class and I suppose it's a Connection object and maybe the problem is that the Connection has been already closed by db2..

[Mark Ma]

Hi Mcarniel,

I figured out the problem, "The IBM Data Server Driver for JDBC and SQLJ conforms to the JDBC standard to have the cursor automatically closed when all rows have been retrieved from a ResultSet. When ResultSet.next is executed after the cursor is closed the SQLException is thrown. "
this is only happened for DB2.
in framework OpenSwing2.4.5 org.openswing.swing.server.QueryUtil
private static Response getQuery(
      Connection conn,
      UserSessionParameters userSessionPars,
      String baseSQL,
      String select,
      String from,
      String where,
      String group,
      String having,
      String order,
      ArrayList values,
      Map attribute2dbField,
      Class valueObjectClass,
      String booleanTrueValue,
      String booleanFalseValue,
      ServletContext context,
      int blockSize,
      int rowsToRead,
      boolean logQuery,
      int action,
      int startPos,
      boolean fetchTotalResultSetLength
  )
in line 2064:
      ….
      boolean moreRows = false;
      if (rowsToRead==1) {   //  change this line to ==> if (rowsToRead==1 && !rset.isClosed()) {
        if (action == GridParams.NEXT_BLOCK_ACTION && rset.next())
          moreRows = true;
        else if (action==GridParams.PREVIOUS_BLOCK_ACTION && rset.previous())
          moreRows = true;
      }
     ……
Added condition && !rset.isClosed() in line 2064 resolved the problem.