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 --
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.