RE: SQL Paging Mechanism

I may be wrong, but I assumed this request was not for loading a subfile.

________________________________________
From: rpg400-l-bounces@xxxxxxxxxxxx [rpg400-l-bounces@xxxxxxxxxxxx] on behalf of Alan Campin [alan0307d@xxxxxxxxx]
Sent: 20 November 2012 20:45
To: RPG programming on the IBM i / System i
Subject: Re: SQL Paging Mechanism

Not sure what the issue here is. Is the code that I shared I have a
statement that looks like this to bring in a page for a single page load
subfile.

Exec Sql Declare C1 Scroll Cursor For
Select TDFILENAME,
TDLIBNAME,
TDACTIVE,
TDSRVPGM,
TDSRVLIB,
TDSRVPROC
From TGCTRLD
Where TDFILENAME >= :O_CURPOSTO
Order By TDFILENAME,
TDLIBNAME;


g_RecordsLoadedOnPage = 0;

NumberToLoad = cRecordsPerPage; // SQL cannot use constant.
Exec Sql Fetch Next
From C1
For :NumberToLoad Rows
Into :g_dsRecordIn;
Select;
When SqlState = cSqlStateNoRow;
RecordsRead = 0;
When SqlState <> cSqlStateOk;
ERRH_Throw('CPF9898'
:
cCPFMessageFileName
:
'Fetch from cursor C1 failed! Sql State = ' +
SqlState);
Other;
RecordsRead = SQLERRD(3);
EndSl;

You declare the cursor as scroll to move it forward and backwards, the data
structure is mapped to a user space and you get as many records as you want
on a page. You always fetch the rows into a data structure and I map to a
user space. Not sure I see what the complexity is about?


On Tue, Nov 20, 2012 at 10:56 AM, Paul Holm <pholm@xxxxxxxxxxxxxxxxx> wrote:

Richard,

This can be a complex topic depending on your requirements if you need
to factor things like cursor sensitivity, page forward, page back, cache
for performance, retaining open locks, etc.

We solved this problem generically by creating a module/component called
a "ROW COLLECTION" that remembers the SQL, the starting row index,
ending row index, how many rows you want at a time, boolean for if more
records exist, and a behavior to control cursor sensitivity. Depending
on your cursor config setting, when user asks to page down, the SQL is
reexecuted and the result set cursor position is set the the "next"
record. Row Collection is returned to the UI and automatically
presented. Next and Previous are generated based on if there are
previous rows and more rows. You can also retain the result set and
continue paging down thru it but that retains a connection from your
pool as well as a lock.

The resulting benefit is that we have not had to write any "paging" code
for SQL applications for the past 8 years. It was done once and is
reused. We use Java but the logic should be applicable to RPG, .NET,
etc.

Page 21 of this presentation gives an illustration.
https://docs.google.com/open?id=0B2SXIgWQ9pn7bFpabkc2Tld4LW8

Here is an example if paging in action. This app shows where AS400
users are located geographically and optionally what software they are
using. I put in paging examples for 10, 50, and 100 records at a time.
Look under IBM i Paging menu group.

Page 10 records at a time.

http://www.planetjavainc.com/wowADDONDEV/runApp?opid=1311&_pj_lib=wowsamp

Page 50 records at a time.

http://www.planetjavainc.com/wowADDONDEV/runApp?opid=1310&_pj_lib=wowsamp

Page 100 records at a time
http://www.planetjavainc.com/wowADDONDEV/runApp?opid=1312&_pj_lib=wowsamp

Thanks, Paul Holm
IBM i Apps in Seconds:
www.planetjavainc.com



On 11/20/2012 5:44 AM, Richard Schoen wrote:

I am about to put together an SQL based paging mechanism over a

resultset that could be very large.

Can anyone share their preferred mechanism for paging efficiently thru

an SQL resultset without bringing back all records at the same time.

I have played with putting a row number on each row and setting the

starting row number and that seems to be OK, but any thoughts would be
appreciated.

Regards,
Richard Schoen
RJS Software Systems Inc.
Where Information Meets Innovation
Document Management, Workflow, Report Delivery, Forms and Business

Intelligence

Email: richard@xxxxxxxxxxxxxxx
Web Site: http://www.rjssoftware.com
Tel: (952) 736-5800
Fax: (952) 736-5801
Toll Free: (888) RJSSOFT

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.