Example Single Page Subfile

What are single page subfiles?

A single page subfile is a SCREEN OF DISPLAYED DATA, loaded one page at a time. The displayed data is equal to the maximum number of records that can be displayed at a time.

In other words, in a single page subfile, all loaded records are displayed at a time. We delete all previously loaded records from the subfile whenever we need to load the next page of the subfile.

A single page subfile is characterized by the subfile size equaling the page size. Also, in a single page subfile, both ROLLUP and ROLLDOWN keywords must be defined in the DDS of the subfile control format.

This is because the PAGEUP/PAGEDOWN activities are handled by the program

Example Single Page Subfile in SQL RPGLE

The Table (or Physical File) is SAMPLETBL

Now - after you have compiled this file, populate it with some data. The easiest way is to use DFU to quickly type in as many names address as you want. Just use UPDDTA SAMPLETBL and get keying...

* Description... Sample Customer Database
* File Name..... SAMPLETBL.PF
* Author........ Nick Litten (https://www.nicklitten.com)
A UNIQUE
A R CUSRCDFMT
A CODE 10 COLHDG('Customer' 'Code')
A FORENAME 20 COLHDG('Customer' 'First Name')
A SURNAME 20 COLHDG('Customer' 'SurName')
A BIRTHDAY 8 0 COLHDG('Customer' 'Birthday')
A PHONE 15 0 COLHDG('Customer' 'Phone')
A EMAIL 100 COLHDG('Customer' 'Email')
A STREET1 50 COLHDG('Customer' 'Address 1')
A STREET2 50 COLHDG('Customer' 'Address 2')
A CITY 50 COLHDG('Customer' 'City')
A STATECODE 50 COLHDG('Customer' 'State')
A ZIP 20 COLHDG('Customer' 'Zip')
A COUNTRY 50 COLHDG('Customer' 'Country')
A K CODE

Display File is SAMPLEPNL

NOTE: We are using a 27x132 screen size in this Display File. Ensure your 5250 session is configured to use the default size of 27x132 (rather than the old 24x80). This size should absolutely be the session default IMHO.

This is in IBM i DDS DSPF Format:

* Description... Sample Customer Database
* File Name..... SAMPLEPNL.DSPF
* Author........ Nick Litten (https://www.nicklitten.com)
A DSPSIZ(27 132 *DS4)
A REF(SAMPLETBL)
A PRINT
A INDARA
A ERRSFL
A HELP(01)
A CA03(03)
A CA05(05)
A CA12(12)
A PAGEUP(25)
A PAGEDOWN(26)
A R SFL01 SFL
A SFLRRN 5S 0H
A EMAIL R H
A STREET1 R H
A STREET2 R H
A CITY R H
A STATECODE R H
A ZIP R H
A COUNTRY R H
A CODE R O 5 5
A FORENAME R O 5 17
A SURNAME R O 5 39
A BIRTHDAY R O 5 60
A EDTWRD(' / / ')
A PHONE R O 5 71
A CALCADD 40A O 5 87TEXT('Combined Address')
A R CTL01 SFLCTL(SFL01)
A SFLSIZ(0020)
A SFLPAG(0020)
A OVERLAY
A 30 SFLDSP
A 31 SFLDSPCTL
A 32 SFLCLR
A 35 SFLEND(*SCRBAR *MORE)
A 1 3'Sample Subfile Program in IBM i RP-
A GLE'
A COLOR(WHT)
A 1 98'System'
A COLOR(BLU)
A 1105SYSNAME
A 1118'Date'
A COLOR(BLU)
A 1123DATE
A EDTCDE(Y)
A 2100'User'
A COLOR(BLU)
A 2105USER
A 2118'Time'
A COLOR(BLU)
A 2123TIME
A 3 5'Customer ID'
A COLOR(BLU)
A 3 17'Customer'
A COLOR(BLU)
A 3 39'Customer'
A COLOR(BLU)
A 3 61'Customer'
A COLOR(BLU)
A 3 71'Customer'
A COLOR(BLU)
A POSITION R O 4 5REFFLD(CODE SAMPLETBL)
A COLOR(BLU)
A 31 DSPATR(UL)
A 31 DSPATR(PC)
A N30 DSPATR(ND)
A 4 17'Forename'
A COLOR(BLU)
A 4 39'Surname'
A COLOR(BLU)
A 4 61'Birthday'
A COLOR(BLU)
A 4 71'Contact Num'
A COLOR(BLU)
A 4 87'Address'
A COLOR(BLU)
A R CMD01
A OVERLAY
A MOUSECLICK 2Y 0B 25 3PSHBTNFLD((*NUMROW 1) (*GUTTER 1))
A PSHBTNCHC(1 '>Enter')
A PSHBTNCHC(3 'e>Xit')
A PSHBTNCHC(5 '>Refresh')
A ERRORMSG 120A O 26 2COLOR(RED)

SQLRPGLE for Single Page Subfile Processing

Now the program is RPGLE but using SQL to load the tables:

(remember to copy every single line and make sure you don't lose any quotes or asterisks in your copy/paste)

**FREE

// Description... Sample Customer Database Subfile Program
// File Name..... SAMPLEPGM.SQLRPGLE
// Author........ Nick Litten (https://www.nicklitten.com)
// Date.......... Nov 18th 2016
//
// Compile Instructions:
//
// CRTSQLRPGI SRCFILE(yourlib/QRPGLESRC)
// SRCMBR(SAMPLEPGM)
// OBJTYPE(*MODULE)
//
// CRTPGM PGM(yourlib/SAMPLEPGM)
//

ctl-opt
debug
option(*nodebugio:*srcstmt)
datfmt(*iso-) timfmt(*iso.)
indent('| ') truncnbr(*yes) expropts(*resdecpos)
copyright('| SAMPLEPNL V000 Example Single Page Subfile');

dcl-f SAMPLEPNL workstn sfile(SFL01:rrn) indDs(dspf);

dcl-s p_Indicators pointer inz(%addr(*in));
dcl-ds dspf qualified based(p_Indicators);
help ind pos(01);
exit ind pos(03);
refresh ind pos(05);
previous ind pos(12);
pageup ind pos(25);
pagedown ind pos(26);
sflclr ind pos(32);
sfldsp ind pos(30);
sflctl ind pos(31);
sflEnd ind pos(35);
end-ds;

dcl-ds dsp_fields extname('SAMPLETBL') end-ds;

dcl-s rrn like(sflrrn);
dcl-s previousPosition like(position);
dcl-s pagesize zoned(2) inz(20);

exec sql
set option commit = *none,
closqlcsr = *endmod;

// initially let's force a refresh to load first page
dspf.refresh = *on;

dou dspf.exit or dspf.previous;

if dspf.refresh;
clear position;
clearSubfile();
setPosition();
loadSubfile();
elseif dspf.pageUp;
setPosition();
pageUp();
elseif dspf.pagedown;
pageDown();
endif;

showSubfile();

enddo;

*inlr = *on;
return;





// Clear Subfile procedure...
dcl-proc clearSubfile;

dspf.sflEnd = *off;
dspf.sflclr = *on;
write ctl01;
dspf.sflclr = *off;

end-proc;





// Build Subfile procedure...
dcl-proc loadSubfile;

dspf.sflEnd = *off;
rrn = 0;

exec sql
fetch next from mycursor
into :dsp_fields;

previousPosition = code;

dow sqlcode >= 0 and sqlcode < 100;
rrn += 1;
write SFL01;
if rrn = pagesize;
leave;
endif;

exec sql
fetch next from mycursor
into :dsp_fields;
enddo;

// if we didnt load a full page then set END OF SUBFILE
if rrn < pagesize;
dspf.sflEnd = *on;
endif;

end-proc;





// Display Subfile procedure...
dcl-proc showSubfile;

if rrn > 0;
dspf.sfldsp = *on;
else;
dspf.sfldsp = *off;
errormsg = 'No data loaded from SAMPLETBL!';
endif;

dspf.sflctl = *on;

write cmd01;
exfmt ctl01;

clear ERRORMSG;

end-proc;





// Declare cursor procedure...
dcl-proc setPosition;

exec sql
close mycursor;

exec sql
declare mycursor scroll cursor for
select code,
forename,
surname,
birthday,
phone,
email,
street1,
street2,
city,
statecode,
zip,
country
from sampletbl
where code >= :position
order by code
for read only;

exec sql
open mycursor;

end-proc;





// Page up procedure...
dcl-proc pageUp;

// set start cursor RRN at first row of previous page
if rrn = pagesize;
position = previousPosition;
else;
clear position;
endif;

// Build the subfile starting at *position*
clearSubfile();
loadSubfile();

// If the cursor already in the top the list
if rrn <= pagesize;
errormsg = 'Start of customer list';
endif;

end-proc;





// Page down procedure...
dcl-proc pageDown;

// Build the subfile starting at *position*
clearSubfile();
loadSubfile();

// If more to read then show sflend
if rrn < pagesize;
errormsg = 'You have reached the bottom of the customer list.';
endif;

end-proc;
// -- end of code --
Done?
 Mark lesson complete
Previous Lesson
Next Lesson
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>