RE: AS/400 ToolBox JDBC 2.0 using scrolling a ResultSet -- JAVA400-L

Just for the record, the AS/400 Native JDBC driver has similar behavior.
The issue is really with what information we can gain from the database and
the compromises that had to be made are basically the same for both JDBC
drivers.


Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"TRUE! nervous, very, very dreadfully nervous I had been and am; but why
WILL you say that I am mad?
The disease had sharpened my senses, not destroyed, not dulled them. "

- Edgar Allan Poe
"The Tell-Tale Heart"




dawall@us.ibm.com on 04/05/2000 09:02:22 AM

Please respond to JAVA400-L@midrange.com

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




There is an example (and full javadoc) in the AS/400 Toolbox for Java
Programmer's Guide (which is available for download from
http://www.ibm.com/as400/toolbox).  By default, ResultSets are read only -
- as dictated by the JDBC specification.  In order to make a ResultSet
updatable, there are really 2 extra steps you have to take:

1.  When you create the Statement, PreparedStatement, or CallableStatement,
you have to specify a result set conncurency of CONCUR_UPDATABLE.  This is
business as usual for JDBC:

    Statement s = c.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE);


2.  When the AS/400 database opens a cursor (on behalf of a ResultSet), it
does so as either read-only or updateable.  When you execute your query,
you have to tell it to open the cursor as updateable by using the FOR
UPDATE clause:

     ResultSet rs = s.executeQuery ("SELECT NAME,ID FROM MYLIBRARY.MYTABLE
FOR UPDATE");


The ResultSet will be updateable.  Hope that helps,
- Clif
_______________________________________
Clifton M. Nock (cnock@us.ibm.com)
AS/400 Toolbox for Java


---------------------- Forwarded by Dave Wall/Rochester/IBM on 04/04/2000
08:09 PM ---------------------------

Timothy Sullivan <TSullivan@computer-guidance.com> on 04/04/2000 07:02:15
PM

To:   Dave Wall/Rochester/IBM@IBMUS
cc:
Subject:  RE: AS/400 ToolBox JDBC 2.0 using scrolling a ResultSet




Mr. Clifton M. Nock:

     Thanks for the quick reply.  Your answer was very helpful.  As you
may have guessed we are having trouble understanding some of the
capabilities of the AS400 ToolBox JDBC driver.  So far we have not been
able
to locate any hard technical documentation on it capabilities.  The
"getRow()" is an instance of this.  Would you have any suggestion on where
to look?

     One immediate problem we are have which we have no information is
using updateable ResultSet(s).  My tests over simple files have only
yielded
ResultSet that are read only.  I'm familiar with using the JDBC and made a
number of test programs but can not figure out what conditions must be meet
for us to get an updateable ResultSet.   Obviously, not all ResultSet can
be
updateable depending on the complexity of the query but then again there
should be queries that are simple enough to always guarantee an updateable
ResultSet.  Any information you have or that you can point us to is greatly
appreciated.


/Timothy Sullivan
tsullivan@computer-guidance.com



> -----Original Message-----
> From:   dawall@us.ibm.com [SMTP:dawall@us.ibm.com]
> Sent:   Tuesday, April 04, 2000 7:42 AM
> To:     JAVA400-L@midrange.com
> Subject:     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
> +---







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