×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
midrange.com
