December 1

15 comments

Example of an IBM i RPG Single Page Subfile

By NickLitten

December 1, 2020

subfile, example, RPG, RPGLE, sample, SFL, SFLPAG, 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

Rpg code snippet single page subfile

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

Want to learn how to write RPGLE programs?

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

  • 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!

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

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

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >