Re: AS/400 ToolBox JDBC 2.0 using scrolling a ResultSet

This is working "as designed".  I am not saying the design is good, but I
don't see any bugs.  Note that the row number is not necessarily an
indication of whether or not the method "worked".  If you change your test
code to print a value from the row (say the first column), you should find
out that they all work as you would expect.  This is not documented very
well, I admit and will update the Javadoc in the next Toolbox release.

The phenomenon that you are observing stems from the fact that we lose
track of the row number after certain operations.  It turns out that the
database does not know (or report) the size of the result set.  The Toolbox
JDBC driver can keep track of the row number if you start at the beginning
and just move forward using next().  However, as soon as you call last(),
the JDBC driver no longer knows the row number and just reports zero.

Specifically...

(b) In the case of #11, you call last(), positioning your cursor on the
last row (even though getRow() reports row 0, you are on the last row,
which you can easily confirm by getting data from the row).  When you call
previous() in #12, it backs you up to the 2nd-to-last row, which is valid,
so it returns true.  Again, working as designed.

(d) rs.last() does in fact work, which is why it returns true.  Its just
that it does not know the row number (the row number is not available), so
it returns 0.

Hope that explains it.

- Clif
_______________________________________
Clifton M. Nock (cnock@us.ibm.com)
AS/400 Toolbox for Java


---------------------- Forwarded by Dave Wall/Rochester/IBM on 04/03/2000
03:52 PM ---------------------------

Timothy Sullivan <TSullivan@computer-guidance.com> on 04/03/2000 02:41:25
PM

Please respond to JAVA400-L@midrange.com

To:   JAVA400-L@midrange.com
cc:
Subject:  AS/400 ToolBox JDBC 2.0 using scrolling a ResultSet




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
+---







+---
| 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
+---