Paging Cursors
October 22, 2014 Paul Tuohy
|
Note: The code accompanying this article is available for download here. I would like to share with you one of the techniques I use for paging large lists when using embedded SQL in RPG. This method came about when I needed to write a routine which could be used in both an interactive (green screen) and web environment. There were two main challenges:
The Requirements As well as retrieving the required page of information, there were a few other requirements about how information was requested and some additional information that would be returned from the routine. The routine required input parameters to specify:
The routine required output parameters that specify:
The call interface for one of these SQL paging routines is shown in the next piece of code.
d get_list_Employees...
d pr extProc('get_list_Employees')
d department 10a const
d currentPage 10i 0 const
d nextPage 10i 0 const
d pageSizeIn 10i 0 const
d currentPageIs 10i 0
d totalpages 10i 0
d totalRows 10i 0
d gotRows 10i 0
d data likeDS(b_list_Employees)
d dim(9999)
Now let’s look at a piece of code that shows the definition of template b_list_Employees, which is used in the definition of the host variable array used in retrieving data. d b_list_Employees... d Ds qualified template d employee 5a d fullName 50a varying d library 10a d salary 15p 2 The SQL Bit The fact that the cursor is opened and closed on each call means that the embedded SQL is fairly straight forward. The next snippet of code below shows an SQL paging routine. The process is as follows:
p get_list_Employees...
p b export
d pi
d department 10a const
d currentPage 10i 0 const
d nextPage 10i 0 const
d pageSizeIn 10i 0 const
d currentPageIs 10i 0
d totalpages 10i 0
d totalRows 10i 0
d gotRows 10i 0
d data likeDS(b_list_Employees)
d dim(9999)
d pageSize s 10i 0
d rowOffset s 10i 0
/free
exec SQL
declare C001 insensitive scroll cursor for
select empno, firstNme || ' ' || lastname, salary
from employee
where workdept = :department
order by empno
for read only;
exec SQL
open C001;
exec SQL
get diagnostics :totalRows = DB2_NUMBER_ROWS;
calculate_Page_Data(currentPage
:nextPage
:pageSizeIn
:%elem(data)
:currentPageIs
:totalpages
:totalRows
:pageSize
:rowOffset
);
exec SQL
fetch relative :rowOffset from C001
for :pageSize rows into :data;
gotRows = SQLERRD(3);
exec SQL
close C001;
return;
/end-Free
p e
The calculate_Page_Data() Subprocedure Since the process of calculating page data will be the same in all the SQL paging procedures that use this method, it makes sense to put it in its own subprocedure: calculate_Page_Data() is shown in the code below. The main purpose of the subprocedure is to calculate the total number of pages in the result set, based on the total number of rows in the result set divided by the page size, and to calculate the offset to the first row of the requested page, based on the requested page plus the next page parameter multiplied by the number of rows per page. calculate_Page_Data() also ensures that requested parameters (request page, rows per page, etc.) are valid and within the boundaries of the result set.
p calculate_Page_Data...
p b export
d pi
d currentPage 10i 0 const
d nextPage 10i 0 const
d pageSizeIn 10i 0 const
d maxPageSize 10i 0 const
d currentPageIs 10i 0
d totalpages 10i 0
d totalRows 10i 0
d pageSize 10i 0
d rowOffset 10i 0
/free
// Ensure valid page size - default to size of requested array
pageSize = pageSizeIn;
if (pageSize < 1 or pageSize > maxPageSize);
pageSize = maxPageSize;
endIf;
// Calculate total pages in result set
totalpages = (totalRows / pageSize);
if (%rem(totalRows: pageSize) <> 0);
totalpages += 1;
endIf;
// Calculate the current page to retrieve
// Ensure it is within the result set
// - default to 1st or last page, if not
currentPageIs = currentPage + nextPage;
if (currentPageIs < 1);
currentPageIs = 1;
endIf;
if (currentPageIs > totalpages);
if (totalpages < 1);
currentPageIs = 1;
else;
currentPageIs = totalPages;
endIf;
endIf;
// Calculate offset to 1st row to retrieve
rowOffset = ((currentPageIs - 1) * pageSize) + 1;
/end-Free
p e
Putting It Into Practice The program, which you can download here, demonstrates how to use the example shown in this article. The example makes use of the EMPLOYEE table in the standard SQL sample database, but feel free to change it to a table of your choice. I would usually have all of the subprocedures in service programs and the calculate_Page_Data() subprocedure, at least, belongs in a service program. I hope you find it useful. Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.
|
