Wednesday, October 20, 2021

Creating an auto-scrolling subfile

auto scrolling subfle rpg program

The idea for this post came from a question that was asked on Facebook. Someone wanted to create a subfile that would be projected onto a screen at an event. The subfile would scroll to the next screen of results every so many seconds without the need for someone to press a key on the keyboard.

I was surprised by the replies to the question, some said it was not possible, others gave over complicated examples of how they thought it could be achieved. I knew that to do this was simple, and all the information needed to do this exists in various posts on this website.

I decided to write a program to do what was asked before. I decided to make this a little more interesting:

  1. Sort the data by the person's name
  2. Subfile is displayed in person's name order
  3. Sort the data by the place of birth
  4. Subfile is displayed in place of birth order
  5. Return to number 1

First I need to show you the source of data. The DDL table PERSON contains just three columns. I am not going to explain what the columns contain as I think their long names do that for me:

01  CREATE TABLE MYLIB.PERSON
02   (FIRST_NAME FOR "FNAME" VARCHAR(25),
03    LAST_NAME FOR "LNAME" VARCHAR(30),
04    PLACE_OF_BIRTH FOR "PLACEBIRTH" VARCHAR(50)) ;

My display file has three record formats:

  1. SFL01:  Subfile record format
  2. CTL01:  Subfile control record format
  3. SCREEN2:  Just a regular non-subfile record format
01 A                                      DSPSIZ(24 80 *DS3)
02 A                                      INDARA
    *-------------------------------------------------------
03 A          R SFL01                     SFL
04 A            SFLRRN         4Y 0O  2  2EDTCDE(Z)
05 A            NAME          30A  O  2  7
06 A            PLACE         30   O  2 38
    *-------------------------------------------------------
07 A          R CTL01                     SFLCTL(SFL01)
08 A                                      SFLSIZ(9999)
09 A                                      SFLPAG(0010)
10 A                                      OVERLAY
11 A                                      LOCK
12 A  31                                  SFLDSP
13 A  30                                  SFLDSPCTL
14 A N30                                  SFLDLT
15 A  30                                  SFLEND(*MORE)
16 A            SCREENTOP      4S 0H      SFLRCDNBR(CURSOR *TOP)
17 A                                  1  2'Auto-scrolling subfile'
18 A                                      DSPATR(HI)
    *-------------------------------------------------------
19 A          R SCREEN2
20 A                                      LOCK
21 A                                  1  2'Going back to the +
22 A                                      first record in the SFL'
23 A                                      DSPATR(HI)
24 A                                  2  2'Changing sort order'

Interesting things to note in this display file's definition are:

Line 2: I always use the indicator area/data structure for the indicator handling between a display file and a RPG program as it allows me to give the indicators meaningful names in the program.

Lines 8 and 9: This is a "load all" subfile, therefore, the subfile size is greater than the subfile page number.

Lines 11 and 20: The LOCK keyword prevents keyboard input while these record formats are displayed.

Line 16: The SFLRCDNBR(CURSOR *TOP) keyword means that when I put a number into the field SCREENTOP it will position the subfile to display that number record at the top of the screen.

When I compile this display file I must ensure that Defer Write parameter must be "*NO":

Defer write  . . . . . . DFRWRT     > *NO 

I think the RPG program is simple. The definition part of the program is:

01  **free
02  ctl-opt option(*nodebugio:*srcstmt) dftactgrp(*no) ;

03  dcl-f TESTDSPF workstn indds(Dspf) sfile(SFL01:SFLRRN) ;

04  dcl-ds Dspf qualified ;
05    SflDspInds char(2) pos(30) ;
06  end-ds ;

07  dcl-ds Data qualified dim(100) ;
08    PersonName char(40) ;
09    PlaceOfBirth char(50) ;
10  end-ds ;

11  dcl-s Rows int(5) inz(%elem(Data)) ;
12  dcl-s SortOrder ind ;

Line 1: In 2021 it has to be totally free RPG.

Line 2: My favorite control options.

Line 3: Definition of the display file. The INDDS gives the name of the indicator data structure.

Lines 4 – 6: The definition of the indicator data structure. One of the many things I like about indicator data structures is that I do not have to define every subfield as an indicator. Here I have defined a two long character field that overlaps indicators 30 and 31.

Lines 7 – 10: This data structure array that will contain the data I fetch from the DDL table.

Line 11: This variable contains the number of elements that the Data data structure array has. I will be using this in the SQL fetch.

Line 12: I will explain the use of this variable when I use it in the program.

Now to the "main" part of the program.

13  GetData() ;

14  dow (1 = 1) ;
15    LoadSubfile() ;
16    SCREENTOP = 1 ;

17    dou (SCREENTOP > Rows) ;
18      write CTL01 ;
19      exec sql CALL QSYS2.QCMDEXC('DLYJOB DLY(2)') ;
20      SCREENTOP += 10 ;
21    enddo ;

22    write SCREEN2 ;
23    exec sql CALL QSYS2.QCMDEXC('DLYJOB DLY(3)') ;
24  enddo ;

25  *inlr = *on ;

Line 13: I fetch the data from the file into the Data structure array in this subprocedure. I will explain more below.

Lines 14 – 24: What I call a "never ending loop". This loop will be performed until I cancel the program.

Line 15: I load the subfile from Data.

Line 16: By moving 1 to SCREENTOP the first record of the subfile will be displayed. SCREENTOP has to contain a number that is the equivalent of a record in the subfile. If it does not, for example it is zero, then I will receive a "session or device error".

Lines 17 – 21: This loop is performed until all of the subfile records are displayed.

Line 18: I write the subfile control record so that the program continues without needing external intervention.

Line 19: I prefer to use the SQL QCMDEXC procedure as all I have to pass to it is the string I want executed, no need for string length etc. Here I delay the program by two seconds, meaning that the screen I displayed will be seen for two seconds before the program advances to the next statement.

Line 20: To advance to the next screen all I have to do is to increment SCREENTOP with the size of the subfile page, 10. This ensures that the 11th, 21st, 31st, 41st, etc. record of the subfile is the next one displayed.

When the value of SCREENTOP is greater than the number of rows of data fetched from the DDL table I exit this loop.

Line 22: I write SCREEN2, which informs the user that I have finished display all of the subfile entries.

Line 23: I delay the job again to give the user time to read it. Then the program loops up to line 14.

The GetData subprocedure is what gets the data from the DDL table. This is only executed once, as when the data has been loaded into the Data data structure I use it rather than have to get more data from the DDL table.

26  dcl-proc GetData ;
27    exec sql DECLARE C0 CURSOR FOR
28         SELECT LAST_NAME || ', ' || FIRST_NAME,
29                IFNULL(PLACE_OF_BIRTH,' ')
30           FROM PERSON
31            FOR READ ONLY ;

32    exec sql OPEN C0 ;

33    exec sql FETCH C0 FOR :Rows ROWS INTO :Data ;

32    exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;

34    exec sql CLOSE C0 ;
35  end-proc ;

Lines 27 – 31: This is my SQL statement to get the data from the DDL table.

Line 28: The first column of the result is the combined name of the person. As the LAST_NAME column is variable length I do not have to trim it before concatenating it with FIRST_NAME.

Line 29: I know that I do not have the place of birth for some of the people, in this DDL table this is null. For the place of birth I am using the IFNULL function to convert any null values to blanks.

Line 33: Here I am doing the multiple row fetch from the DDL table into the data structure array, Data. Multiple row fetches are so much faster than a RPG Read operation or SL single row fetch to get all the data from a DDL table or DDS file.

Line 32: Using GET DIAGNOSTICS I can get the number of rows that were fetched from the file. I can now use the value in the variable Rows when I load the subfile.

The other procedure is the one that loads the subfile from the data structure array.

36  dcl-proc LoadSubfile ;
37    if (SortOrder) ;
38      sorta %subarr(Data : 1 : Rows) %fields(PlaceOfBirth : PersonName) ;
39      SortOrder = *off ;
40    else ;
41      sorta %subarr(Data : 1 : Rows) %fields(PersonName : PlaceOfBirth) ;
42      SortOrder = *on ;
43    endif ;

44    Dspf.SflDspInds = '00' ;
45    write CTL01 ;
46    Dspf.SflDspInds = '11' ;

47    for SFLRRN = 1 to Rows ;
48      NAME = Data(SFLRRN).PersonName ;
49      PLACE = Data(SFLRRN).PlaceOfBirth ;
50      write SFL01 ;
51    endfor ;
52  end-proc ;

Line 37 – 43: I can only perform this code as I have the Fall 2021 Technology Refresh for IBM i 7.4 TR5 and 7.3 TR11.

Line 37: Now you see how I am using the indicator SortOrder.

Line 38: If SortOrder is "on" then I will sort the data structure array by the place of birth and person's name. I have to use the %SUBARR built in function to only sort the part of the array that contains data, which is from the first element to the number of rows retrieved. If I did a SORTA without the %SUBARR the unused elements of the array would come first.

Line 41: If the indicator is "off" I sort the data structure array by person's name and place of birth.

Lines 44 – 46: Here I set "off" both of the indicators that condition the subfile in the display file. This will delete the subfile if one exists. Then by setting them "on" the subfile and its contents will be displayed when I write the control record.

Lines 47 – 51: Here I load the subfile. I use a For group and perform the loop the same number of times as there were rows fetched from the DDL table.

I think that is a simple program.

So what does that look like when it is run:

Auto-scrolling subfile
  1 ALLEN, REG                     MARYLEBONE
  2 ASTON JR, JOHN                 MANCHESTER
  3 BENNION, RAYMOND
  4 BERRY, JOHNNY                  ALDERSHOT
  5 BIRCH, BRIAN                   SALFORD
  6 BLANCHFLOWER, JACKIE           BELFAST
  7 BOND, ERNEST                   PRESTON
  8 BULLOCK, JAMES                 GORTON
  9 BYRNE, ROGER                   GORTON
 10 CAREY, JOHNNY                  DUBLIN
                                                 More...

Auto-scrolling subfile
 11 CASSIDY, LAURENCE              MANCHESTER
 12 CHESTERS, ARTHUR               SALFORD
 13 CHILTON, ALLENBY               SOUTH HYLTON
 14 CLEMPSON, FRANK                SALFORD
 15 COCKBURN, HENRY                ASHTON-UNDER-LYNE
 16 CROMPTON, JACK                 HULME
 17 DALE, WILLIAM                  MANCHESTER
 18 DOWNIE, JOHN                   LANARK
 19 GALLIMORE, STANLEY             BUCKLOW HILL
 20 GIBSON, DONALD                 MANCHESTER
                                                 More...

Auto-scrolling subfile
 21 HILDITCH, LAL                  HARTFORD
 22 HOPKINSON, SAMUEL              KILLAMARSH
 23 JONES, MARK                    WOMBWELL
 24 JONES, THOMAS                  STANTON HILL
 25 LYDON, GEORGE                  NEWTON HEATH
 26 MCGLEN, WILLIAM                BEDLINGTON
 27 MCLENAHAN, HUGH                WEST GORTON
 28 MCNULTY, THOMAS                SALFORD
 29 MCSHANE, HAROLD                HOLYTOWN
 30 MELLOR, JACK                   OLDHAM
                                                 More...

Auto-scrolling subfile
 31 PARKER, THOMAS                 ECCLES
 32 PEARSON, STAN                  SALFORD
 33 RAMSDEN, CHARLES               BUCKLOW
 34 REDMAN, BILLY                  MANCHESTER
 35 ROWLEY, JACK                   WOLVERHAMPTON
 36 SILCOCK, JACK                  WIGAN
 37 STEWARD, ALFRED                MANCHESTER
 38 WALTON, JOHN                   HORWICH
 39 WHITEFOOT, JEFFREY             CHEADLE
 40 WILLIAMS, FRANK                CEFN-Y-BEDD
                                                 More...

Auto-scrolling subfile
 41 WILSON, JACK                   LEADGATE



                                                 Bottom

Going back to the first record in the SFL
Changing sort order

Now the program goes back into the LoadSubfile subprocedure, where it sorts the data structure array by place of birth and name.

Auto-scrolling subfile
  1 BENNION, RAYMOND
  2 BERRY, JOHNNY                  ALDERSHOT
  3 COCKBURN, HENRY                ASHTON-UNDER-LYNE
  4 MCGLEN, WILLIAM                BEDLINGTON
  5 BLANCHFLOWER, JACKIE           BELFAST
  6 RAMSDEN, CHARLES               BUCKLOW
  7 GALLIMORE, STANLEY             BUCKLOW HILL
  8 WILLIAMS, FRANK                CEFN-Y-BEDD
  9 WHITEFOOT, JEFFREY             CHEADLE
 10 CAREY, JOHNNY                  DUBLIN
                                                 More...

Auto-scrolling subfile
 11 PARKER, THOMAS                 ECCLES
 12 BULLOCK, JAMES                 GORTON
 13 BYRNE, ROGER                   GORTON
 14 HILDITCH, LAL                  HARTFORD
 15 MCSHANE, HAROLD                HOLYTOWN
 16 WALTON, JOHN                   HORWICH
 17 CROMPTON, JACK                 HULME
 18 HOPKINSON, SAMUEL              KILLAMARSH
 19 DOWNIE, JOHN                   LANARK
 20 WILSON, JACK                   LEADGATE
                                                 More...

Auto-scrolling subfile
 21 ASTON JR, JOHN                 MANCHESTER
 22 CASSIDY, LAURENCE              MANCHESTER
 23 DALE, WILLIAM                  MANCHESTER
 24 GIBSON, DONALD                 MANCHESTER
 25 REDMAN, BILLY                  MANCHESTER
 26 STEWARD, ALFRED                MANCHESTER
 27 ALLEN, REG                     MARYLEBONE
 28 LYDON, GEORGE                  NEWTON HEATH
 29 MELLOR, JACK                   OLDHAM
 30 BOND, ERNEST                   PRESTON
                                                 More...

Auto-scrolling subfile
 31 BIRCH, BRIAN                   SALFORD
 32 CHESTERS, ARTHUR               SALFORD
 33 CLEMPSON, FRANK                SALFORD
 34 MCNULTY, THOMAS                SALFORD
 35 PEARSON, STAN                  SALFORD
 36 CHILTON, ALLENBY               SOUTH HYLTON
 37 JONES, THOMAS                  STANTON HILL
 38 MCLENAHAN, HUGH                WEST GORTON
 39 SILCOCK, JACK                  WIGAN
 40 ROWLEY, JACK                   WOLVERHAMPTON
                                                 More...

Auto-scrolling subfile
 41 JONES, MARK                    WOMBWELL



                                                 Bottom

Going back to the first record in the SFL
Changing sort order

As the indicator is "on" the data structure array is sorted by name and place of birth, then repeats itself until I cancel the program.

 

This article was written for IBM i 7.4 TR5 and 7.3 TR11.

9 comments:

  1. Виктор ПоморцевOctober 20, 2021 at 7:48 AM

    “Thanks for sharing”

    ReplyDelete
  2. Simon, thanks for sharing.. great write up and examples. Keep the teaching moments coming and I will keep reading and learning,, again, thanks..

    ReplyDelete
  3. Very elegant example. Lots to learn here!

    ReplyDelete
  4. Great article as always.
    There's a wee typo in the create table statement.
    It should be :

    CREATE TABLE MYLIB.PERSON (
    FIRST_NAME FOR "FNAME" VARCHAR(25),
    LAST_NAME FOR "LNAME" VARCHAR(30),
    PLACE_OF_BIRTH FOR "PLACEBIRTH" VARCHAR(50)) ;

    ReplyDelete
    Replies
    1. Oops! Thank you for bringing that to my attention. I have made the correction.

      Delete
  5. I'd suggest to look at the INVITE keyword.

    ReplyDelete
  6. Very informative. Thank you for sharing.

    ReplyDelete
  7. Armando Sandoval MontañoOctober 26, 2021 at 4:34 AM

    Thank you for share

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.