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 equalling 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
* 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
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:
**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 --
Hi Nick,
Does your IBM i RPG Subfile program run in OS version V5R4 , because I’ve got several highlighted lines in the source code ?
Thanks in advance.
Ummm, I don’t know! V54R end of service was back in 2013… I can’t remember if the old version of IBM i supported fully free format RPG? Did you try it?
Not likely Laurent as new features of free format RPG at V7R1 won’t be supported by V5R4.
Hello Nick.
This is a very good example.
If you have this program with add and update?
Thanks, Mic
I do. This code is several years old and I’ve used it in various update scenarios since then… I will rustle together the update example and post it ASAP.
and/or a dynamic-ORDER BY clause.
Example is when you have multiple of input fields as key-fields when starting to sort, and when these input fields (on the CTL-record-format) have changed it’s values.
Maybe an exec-sql PREPARE is necessary?
Please advise. Thank you.
Hello Nick! I stumbled upon your free-format RPG and embedded SQL from your website, about single-page subfiles.
I like your coding style! Thank you! …
By the way, do you have code-snippets or logic where the program/dspf requires a “Position To”?
Thanks again!
Why waste indicators on function keys andPageUp/Down… just use the AID byte.
I used to use the DSPF AID byte all the time – but for when creating simple examples I find the mapping the *indicator array a clearer way to explain the function keys. It could, of course, be argued both ways. Both just as good as each other. 🙂
If you need two elseif statements… I would recommend select/when/endsl which is easier to read.
Again, agree totally. But.. in this code example I was writing it and showing it to a .net programmer that preferred if/elseif/elseif layout… so his preference just kind of stuck with me. 😉
When I’m writing my own code I tend to use *SELECT* groups in both RPG and CLLE because I like the indentation layout of them… seems cleaner to my eyes.
Argh! I didn’t have **FREE starting in position 1. Now it compiles fine. Sorry for the dumb comments above. I can’t seem to delete my comments.
Deleted for you! 😉
that doesn’t even look like rpg….pretty slick.
Thanks Nick, very interesting and ussefull.