Paging is required in methods which returns array as response. In case there is a lot of data in file which needs to be transferred the paging limit tada transfer. Especially if You build user interface (mobile application) based on aperio paging is verry useful. Not all records are loaded to to screen but limited number of records. The paging in aperio is based on two control parameters:
There is also information which should be returned back by method. This will give client possibility to calculate total number of pages:
Either “total” or “more” value should be returned by method program.
How to implement paging in RPG program? There are many ways to implemane paging. In aperio we have prepared number of examples with paging implemented. In our skeleton programs there are lines which are marked by:
*-PAGING-START-
*-PAGING-END-
The recommendation is to use SQLRPGLE program with embeded SQL to easy calculate total number of resords. In simple files al records can be read but only required ones will be put into response. This approach can be used in case the file is small (contains not much records) and it is hard to build SQL statement which retrieves all valid records. For example method which returns list of valid warehouses for user. It is verry hard to build SQL statement which includes authority control for each given record. but the file usually contains only few records (less than 100). Then program can lop through all records and do security validation for each of it. Then return to the response only these which are required. By looping through all records you can easily calculate total records in file
//Set default limit number of records to 50 if is not provided
lLimit = ctlGetLimit(50);
//Get offset
lOffset = ctlGetOffset();
Then next step is to calculate number of record which will end loop
lCounter = 0;
lCutOff = lLimit + lOffset;
Then loop through file and increase lCounter. If counter is less than lOffset, then omit these records. If counter is between lOffset and lCutOff value put the records values into response. Then if program reaches lCutOff value skip the rest of records but count them. At the end add total to the response.
if lCounter > lLimit OR lOffset > 0;
ctlSetTotal(lCounter);
endif;
This solution is very easy to implement and not complex to understand. There is only one disadvantage. Regardless of offset and limit values the file is always read from the beggining to th end. It may influence on performance.
lWhere = ' WHERE FLD01 = ''' + %TRIM(xxFldSelection) + '''';
Then build SQL statement to count all records in file
// Build SQL statement to select data and count total rec
lSQL = ' SELECT COUNT(*) FROM FILE01' + lWhere;
EXEC SQL PREPARE stmt FROM :lSQL;
EXEC SQL DECLARE c CURSOR FOR stmt;
EXEC SQL OPEN c;
EXEC SQL FETCH c INTO :lTotal;
EXEC SQL CLOSE c;
Then build once again SQL statement but this time use it to retrieve values for each row in file. Add limit and offset to SQL statement.
lSQL = ' SELECT FLD01, FLD02, FLD03 FROM FILE01' + lWhere;
// PAGING-START-------------------------------------------
lLimit = ctlGetLimit();
lOffset = ctlGetOffset();
lSQL += ' LIMIT ' + %char(lLimit);
lSQL += ' OFFSET ' + %char(lOffset);
if lOffset > 0 or lTotal > lLimit;
ctlSetTotal(lTotal);
endif;
// PAGING-END----------------------------------
EXEC SQL PREPARE stmt FROM :lSQL;
EXEC SQL OPEN c;
EXEC SQL FETCH c INTO :FLD01, :FLD02, :FLD03;
dow sqlcod = *zeros;
Then loop using cursor and put data into response.
lSQL = ' SELECT FLD01, FLD02, FLD03 FROM FILE01 WHERE FLD01 = '''
+ lFLD01Selection
+ '''';
// PAGING-START-------------------------------------------
lLimit = ctlGetLimit();
lOffset = ctlGetOffset();
lSQL += ' OFFSET ' + %char(lOffset);
// PAGING-END----------------------------------
EXEC SQL PREPARE stmt FROM :lSQL;
EXEC SQL OPEN c;
EXEC SQL FETCH c INTO :FLD01, :FLD02, :FLD03;
dow sqlcod = *zeros;
Then loop using cursor and put data into response. and count the records which are added to response. If number of records added to response is equal to limit then read one more record. do not put it into response but just set “more” to *ON instead. Then end program.
if lCounter > lLimit;
ctlSetMore(*ON);
endif;
// PAGING-END----------------------------------
...
Examples are in QSAMPLES sourcefile: