AS/400 ToolBox JDBC 2.0 using scrolling a ResultSet

[Timothy Sullivan <TSullivan@xxxxxxxxxxxxxxxxxxxxx>]

Hello,

        We are having problems using scrolling ResultSet with the AS/400.  I
have created a Swing table that depends on the ability to use a scrolling
ResultSet to handle large user queries.  We want to keep the transfer of
data from the AS/400 to a minimum for large queries.  The problem is that
only ResultSet.next() works as described.  ResultSet.previous() works but
has some problems,  ResultSet.last() never works, ResultSet.first() always
works and ResultSet.absolute(int) sometimes works.  Does anyone have
experience with scrolling ResultSets using the AS/400 Toolbox.  Here are
some details:

1) JDBC Driver:  com.ibm.as400.access.AS400JDBCDriver() - V4R2  (This is the
client JDBC driver not the local native AS/400 JDBC driver).
2) AS/400 V4R4 
3) Some sample code with are results and analysis that detail the problem:

  // Get the result set from a query done elsewhere; connect and statement
are still open.
  ResultSet rs = getResultSet(); 
  try
  {
    // Make sure the resultSet is not null
   if (rs != null)
   {
  
    boolean ans = rs.next();
    System.out.println("ans next1 = " + ans);
    System.out.println("rowCount1 = " + rs.getRow());

    ans = rs.next();
    System.out.println("ans next2 = " + ans);
    System.out.println("rowCount2 = " + rs.getRow());

     ans = rs.next();
     System.out.println("ans next3 = " + ans);
     System.out.println("rowCount3 = " + rs.getRow());

     ans = rs.next();
     System.out.println("ans next4 = " + ans);
     System.out.println("rowCount4 = " + rs.getRow());

     ans = rs.next();
     System.out.println("ans next5 = " + ans);
     System.out.println("rowCount5 = " + rs.getRow());

     ans = rs.previous();
     System.out.println("ans previous6 = " + ans);
     System.out.println("rowCount6 = " + rs.getRow());
                                
     ans = rs.previous();
     System.out.println("ans previous7 = " + ans);
     System.out.println("rowCount7 = " + rs.getRow());
                                
     ans = rs.previous();
     System.out.println("ans previous8 = " + ans);
     System.out.println("rowCount8 = " + rs.getRow());
                                
     ans = rs.previous();
     System.out.println("ans previous9 = " + ans);
    System.out.println("rowCount9 = " + rs.getRow());

    ans = rs.previous();
    System.out.println("ans previous10 = " + ans);
    System.out.println("rowCount10 = " + rs.getRow());
                        
    ans = rs.last();
    System.out.println("ans last11 = " + ans);
    System.out.println("rowCount11= " + rs.getRow());

    ans = rs.previous();
    System.out.println("ans previous12 = " + ans);
    System.out.println("rowCount12 = " + rs.getRow());

    ans = rs.absolute(13);
    System.out.println("ans absolute13 = " + ans);
    System.out.println("rowCount13= " + rs.getRow());

    ans = rs.first();
    System.out.println("ans first14 = " + ans);
    System.out.println("rowCount14= " + rs.getRow());
} 
catch (SQLException ex)
{
  ex.printStackTrace();
}

****************** OUTPUT ***************************
ans next1 = true
rowCount1 = 1
ans next2 = true
rowCount2 = 2
ans next3 = true
rowCount3 = 3
ans next4 = true
rowCount4 = 4
ans next5 = true
rowCount5 = 5
ans previous6 = true
rowCount6 = 4
ans previous7 = true
rowCount7 = 3
ans previous8 = true
rowCount8 = 2
ans previous9 = true
rowCount9 = 1
ans previous10 = false
rowCount10 = 0
ans last11 = true
rowCount11= 0
ans previous12 = true
rowCount12 = 0
ans absolute13 = true
rowCount13= 13
ans first14 = true
rowCount14= 1


****** ANALYSIS *******

a) It seems that rs.next() works fine scrolling through the ResultSet.
b) rs.previous() works except in the case where we are already positioned
before the first row.  In this case it returns "true" (see rowCount12).  The
JDBC 2.0 documentation for the previous call states the return value will be
"true if the cursor is on a valid row; false if it is off the result set".
Thus calling previous when not position on a valid row should return false,
not true.  It should matter where you are coming from.
c) rs.absolute(int) works in this case.  However, the same code fails on
other runs over different files(tables) where next() and previous() worked
as described above.
d) rs.last() never works giving a row number of 0 and returning  _true_.
Why?  
e) rs.first() seems to always work.
f) No sqlExceptions are thrown so we do not have a TYPE_FORWARD_ONLY
resultSet.  (Getting the resultSet type confirmed this!).  


/Thanks
Timothy Sullivan
tsullivan@computer-guidance.com

+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---