Re: JDBC - CURSORS and scrolling throught large tables

Igor,

I'd do this by modifying the SQL query ran when you click Next, e.g. if the
table has a numeric key and to read the first page you're doing say
SELECT * FROM mytable ORDER BY mykey

then you read through this in a while loop making sure you only read the
first 20 records.  When you read the last one, check the value of mykey and
use this to build up the URL for the Next button.  e.g.
http://myserver/mywebapp/ShowRecords?mykey=31

then you can run the same SQL query but make sure you start where you left
off by instead doing
SELECT * FROM mytable WHERE mykey > 31 ORDER BY mykey

You can do something similar if you also need a "Previous" button, by
putting the key value from the first record into the URL and doing ORDER BY
mykey DESC.

I'd prefer doing it like this rather than trying to leave cursors open -
what if the user *doesn't* click Next - somehow your app will have to
notice this and close down the cursor.

Hope this helps,

Nigel Gay,
Computer Patent Annuities.



|---------+------------------------------>
|         |           Igor.Beslic@volksba|
|         |           nk.hr              |
|         |           Sent by:           |
|         |           java400-l-bounces@m|
|         |           idrange.com        |
|         |                              |
|         |                              |
|         |           28/04/2005 12:11 PM|
|         |           Please respond to  |
|         |           Java Programming on|
|         |           and around the     |
|         |           iSeries / AS400    |
|         |                              |
|---------+------------------------------>
  
>--------------------------------------------------------------------------------------------------------------|
  |                                                                             
                                 |
  |       To:       java400-l@xxxxxxxxxxxx                                      
                                 |
  |       cc:                                                                   
                                 |
  |       Subject:  JDBC - CURSORS and scrolling throught large tables          
                                 |
  
>--------------------------------------------------------------------------------------------------------------|




Hello everybody!

I need advice on next problem:

I have servlet that performs SQL query to iSeries DB2 database. I use
AS400JDBCDriver to access DB... Evrything is OK and everything works fine.

Now, what I'm interested in?

I need advice how to FETCH just (in eg.) 20 records in my web-app and
disply them on html page. When user clicks on the next button I need to
FETCH next 20 records and so on.
I tried to DECLARE CURSOR and use it for navigation throught records but I
got message that statement isn't alowed.

I believe that there is solution because I'm already using web-app that
scrolls throught DB table...

It would be nice if you can recomend me some literature avaiable in
Information Centre.

Thanks,
Igor Bešlić, dipl. ing. rač.
VOLKSBANK d.d.
OJ Informatika
Zelinska 2, 10000 Zagreb
tel: +385 1 6326422
--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.








********************************************************************************
The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee; access to this
email by anyone else is unauthorised.

If you are not the intended recipient: (1) you are kindly requested
to return a copy of this message to the sender indicating that you
have received it in error, and to destroy the received copy; and (2)
any disclosure or distribution of this message, as well as any action
taken or omitted to be taken in reliance on its content, is prohibited
and may be unlawful.
********************************************************************************