Creating an auto-scrolling subfile

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.