Screen at a time subfile using SQL

In my last post I gave an example of how to write a "screen at a time" subfile in RPG. When I finished writing that post I thought to myself how would it be possible to do the same using SQL.

There are two ways I could read, Fetch, data from the input file:

1. Single row fetch
2. Multiple row fetch

The single row Fetch is almost as inefficient as a RPG read operation, retrieving one record at a time from the file. The multiple row fetch is more efficient and the method I am using in this example.

The data file this program uses, PERSONP, is the same as in my last post, therefore, I am not going to describe it. If you care to refresh your memory then you should go to that post.

I made three changes to the display.

01 A DSPSIZ(24 80 *DS3) 02 A PRINT 03 A INDARA 04 A ERRSFL 05 A REF(PERSONP) 06 A CA03(03 'F3=Exit') 07 A CA05(05 'F5=Refresh') 08 A R SFL01 SFL 09 A ZRRN 2S 0H 10 A NAME 40A O 5 3 11 A LASTNAME R H 12 A FIRSTNAME R H 13 A ARRAYELEM 8S 0H 14 A R CTL01 SFLCTL(SFL01) 15 A SFLSIZ(0010) 16 A SFLPAG(0010) 17 A OVERLAY 18 A PAGEDOWN(25) 19 A PAGEUP(26) 20 A 30 SFLDSPCTL 21 A 31 SFLDSP 22 A 32 SFLEND(*MORE) 23 A 33 SFLCLR 24 A 1 8'R P G P G M . C O M' 25 A 2 2'Screen at a time subfile + 26 A 'example' 27 A 3 2'Position to' 28 A ZPOSITION R B 3 14REFFLD(LASTNAME) 29 A 4 3'Person name' 30 A R FOOT01 31 A 23 3'F3=Exit F5=Refresh'

Line 7: The F5 key will be used by the RPG program to say that the user wants to reload the data from the file.

Line 13: I added a hidden field in the subfile record format to contain the array element the subfile record was loaded from.

Line 31: As I added another function key I added it to the FOOTER record format so that the users would know that it is available.

If you have heard my previous post this program will look familiar. Let me start by describing the definitions.

001 **free 002 ctl-opt main(Main) 003 option(*nodebugio:*srcstmt:*nounref) 004 dftactgrp(*no) ; 005 dcl-f DSPFILE workstn indds(Dspf) 006 sfile(SFL01:ZRRN) usropn ; 007 dcl-ds Dspf qualified ; 008 Exit ind pos(3) ; 009 Reload ind pos(5) ; 010 PageDown ind pos(25) ; 011 PageUp ind pos(26) ; 012 SflInds char(4) pos(30) ; 013 SflDspCtl ind pos(30) ; 014 SflDsp ind pos(31) ; 015 SflEnd ind pos(32) ; 016 SflClr ind pos(33) ; 017 end-ds ; 018 dcl-ds Data qualified dim(32766) ; 019 Last char(30) ; 020 First char(25) ; 021 Name char(40) ; 022 end-ds ; 023 dcl-s DataLookup char(30) dim(%elem(Data)) ascend ; 024 dcl-s SflSize like(ZRRN) inz(10) ; 025 dcl-s LastRecord packed(8) ; 026 dcl-s Pos packed(8) ; 027 dcl-s SavedPosition like(ZPOSITION) ;

Lines 1: In 2019 why would I not write in free format RPG?

Lines 2 – 4: In my control options I am "flagging" that this program uses a Main procedure, line 2. I add the control options on line 3 to all my RPG as they debugging easier. And I need line 4 as this program contains subprocedures.

Lines 5 - 6: This is the only file I need to define, the display file. Notice that it has an indicator data structure, INDDS, on line 5. And that the USROPN on line 6 means I will be opening and closing this file.

Lines 7 – 17: This is the indicator data structure, which allows me to map the number indicators used in the display file to meaningful names. The only change to this data structure, when compared to the one I used in the previous post, is the addition of indicator 5, line 9, which is the indicator used by the F5 key in the display file.

Lines 18 – 22: This data structure array will be used by the SQL Fetch statement to contain the rows retrieved from the file. In this example having 32,766 elements is excessive, but I wanted to show what the largest size this data structure array can be. In IBM i 7.3 an array can have up to 16,773,104 elements (and you thought 32,766 was excessive!), the size limit is from the SQL Fetch. The documentation states that a Fetch can retrieve up to 32,767 rows, but I found I was getting SQL compiler errors with that number. When I reduced the number by one, the code compiles.

Line 23: As this subfile program is going to have a "position to" field on the display file I need to have a way to perform a lookup in the data structure array for the closest matching pattern. To be able to achieve this standalone array needs to be sorted in either ascending or descending order. I have not found how to do that with a data structure array, therefore, this separate array will contain the "position to" key, last name. This array can then be searched for the closest matching element.

Lines 24 – 27: Definitions of various variables I will be using in this program.

Line 24: This variable contains the same number as there are elements in the data structure array. If I ever change the number of records in the subfile record format I only need to change the number here, rather than have to search the source.

Line 25 and 26: PACKED(8) is excessive for the array I have coded here. In the future if I change this program so the data structure array has the maximum allowable number of elements then that number can be accommodated in these fields.

In the control option, line 2, I said there was going to be a Main procedure, so here it is:

028 dcl-proc Main ; 029 exec sql SET OPTION CLOSQLCSR = *ENDMOD ; 030 open DSPFILE ; 031 GetData() ; 032 SubfileDown() ; 033 dow (1 = 1) ; 034 write FOOT01 ; 035 exfmt CTL01 ; 036 if (Dspf.Exit) ; 037 leave ; 038 elseif (Dspf.Reload) ; 039 GetData() ; 040 SubfileDown() ; 041 elseif (Dspf.PageDown) ; 042 if not(Dspf.SflEnd) ; 043 SubfileDown() ; 044 endif ; 045 elseif (Dspf.PageUp) ; 046 SubfileUp() ; 047 elseif (ZPOSITION <> SavedPosition) ; 048 Position() ; 049 endif ; 050 enddo ; 051 on-exit ; 052 close *all ; 053 end-proc ;

Line 29: I always put SQL options into my source code to make sure they are not "forgotten" at compile time. This time I only need the option that says that any open cursors will be closed at the end of the module (program).

Line 30: I open the display file.

Line 31: This is the subprocedure that fetches the data from the file.

Line 32: I think the name of the subprocedure is descriptive of its function. It loads the subfile with the first set of records.

Lines 33 – 50: This is the "guts" of the program where all the hard work is done and called from. The only way to leave the Do-loop is to press the F3 key.

Lines 34 and 35: The bottom part of the subfile, which displays the function keys text, is written. Followed by the subfile control record being EXFMT-ed, which shows the subfile on the screen.

Lines 36 and 37: When F3 is pressed the Do-loop is left.

Lines 38 – 40: If the F5 key is pressed the data structure array is reloaded, by GetData, and the subfile is reloaded too, by SubfileDown.

Lines 41 – 44: When the Page Down key is pressed if the subfile end indicator is on there are no more array elements to display, therefore, the SubfileDown subprocedure is not called. Of course if the end of subfile has not been encountered the subprocedure is called.

Lines 45 and 46: When the Page Up key is pressed the SubfileUp subprocedure is called.

Lines 47 and 48: If a value is entered into the "position to" field on the display file then the Position subprocedure is called.

Lines 51 and 52: I am using the ON-EXIT as it is performed at the end of procedure even if there is an error occurs within the procedure, or it is cancelled. This way the open display file is always closed, none of that mess of having files left open when the program ends in error.

The first subprocedure I need to show gets the data from the file and into the data structure array.

054 dcl-proc GetData ; 055 dcl-s Elements packed(8) inz(%elem(Data)) ; 056 exec sql DECLARE C0 CURSOR FOR 057 SELECT LASTNAME,FIRSTNAME, 058 RTRIM(LASTNAME) || ', ' || FIRSTNAME 059 FROM PERSONP 060 ORDER BY LASTNAME,FIRSTNAME 061 FOR READ ONLY ; 062 exec sql OPEN C0 ; 063 exec sql FETCH C0 FOR :Elements ROWS INTO :Data ; 064 exec sql GET DIAGNOSTICS :LastRecord = ROW_COUNT ; 065 exec sql CLOSE C0 ; 066 DataLookup(*) = Data(*) ; 067 Pos = 0 ; 068 ZPOSITION = ' ' ; 069 SavedPosition = ' ' ; 070 end-proc ;

Line 55: I need this variable when I fetch the rows/records from the file. This variable is initialized with the number of elements in the data structure array, therefore, if I ever change the number of elements in the array I know I don't have to change anything here.

Lines 56 – 61: Definition of my SQL cursor.

Lines 57 and 58: The results will consist of three columns/fields:

1. Last name
2. First name
3. Full name. I have used the RTRIM to remove trailing blanks from the string.

I am sure some SQL programmers are going to criticize me for using the double pipes ( || ) as the concatenation characters.

Line 60: I want the results to be fetched in last name and first name order.

Line 61: FOR READ ONLY is the equivalent of defining a file as input only.

Line 62: Once the cursor is defined it has to be opened before it can be used.

Line 63: Now the rows/records can be fetched from the cursor. This is where I am using the Elements variable for the number of rows I want to fetch from the cursor. The fetched rows are placed in the data structure array.

Line 64: I need to know how many rows were fetched from the cursor. The Get diagnostics allows me to do that in a simple SQL statement.

Line 65: I am done with fetching data from the cursor, therefore, I close it.

Line 66: I am moving the 30 left most characters, the last name subfield, from the data structure array into the standalone array I will be using for "position to".

Lines 67 – 69: Initializing the variables I will use later in the program to control the subfile, etc.

The subprocedure performed when the Page Down key is pressed is just three lines of code.

071 dcl-proc SubfileDown ; 072 LoadSubfile() ; 073 end-proc ;

The subprocedure executed when the Page Up keyed is pressed has just one more line of code. This line determines where in the data structure array the next subfile screen should start at, line 75. The start position is all relative to the last array element added to the subfile. As the subfile may not have all its records filled I use the subfile's relative record field, ZRRN, minus 1, and the size of the subfile variable.

074 dcl-proc SubfileUp ; 075 Pos -= (SflSize + (ZRRN - 1)) ; 076 LoadSubfile() ; 077 end-proc ;

Now I am at the place in the data structure array I need to be I can load the subfile.

078 dcl-proc LoadSubfile ; 079 Dspf.SflInds = '0001' ; 080 write CTL01 ; 081 Dspf.SflInds = '1000' ; 082 if (Pos < 1) ; 083 Pos = 1 ; 084 elseif (Pos > LastRecord) ; 085 Pos = LastRecord ; 086 endif ; 087 for ZRRN = 1 to SflSize ; 088 NAME = Data(Pos).Name ; 089 LASTNAME = Data(Pos).Last ; 090 FIRSTNAME = Data(Pos).First ; 091 ARRAYELEM = Pos ; 092 write SFL01 ; 093 if (Pos = LastRecord) ; 094 leave ; 095 else ; 096 Pos += 1 ; 097 endif ; 098 endfor ; 099 if (ZRRN > 0) ; 100 Dspf.SflDsp = *on ; 101 endif ; 102 if (ARRAYELEM = LastRecord) ; 103 Dspf.SflEnd = *on ; 104 endif ; 105 end-proc ;

Lines 79 – 81: This initialized the subfile, and sets the indicators that the subfile control will be displayed.

Lines 82 – 86: This ensures that the variable I will be using for the data structure array element has a valid value in it. If it is less than 1, then it is set to 1. If its value is greater than the number of the elements fetched into the array it is set to the value of the maximum number of the elements.

Lines 87 – 98: I am using a For-loop to be performed the same number of times as number of records in the subfile, the value in the variable SflSize.

Lines 88 – 92: The values from the data structure array element is moved to the subfile's fields, and then written to the subfile.

Lines 93 and 94: If the array element variable is equal to the number of rows fetched from the cursor then it is time to leave the For-group.

Lines 95 and 96: If it has not I increment the array element variable.

Lines 99 – 101: When the subfile has been load the subfile display indicator is turned on if the subfile contains at least one record.

Lines 102 – 104: If the array element number in the last of the subfile's records is the same as the number of rows fetched from the cursor turn of the end of subfile indicator.

The last subprocedure is executed when the "position to" field on the display file is changed.

106 dcl-proc Position ; 107 Pos = %lookupge(ZPOSITION:DataLookup:1:LastRecord) ; 108 LoadSubfile() ; 109 SavedPosition = ZPOSITION ; 110 end-proc ;

Line 107: This is where the stand alone array is used. I am using the %LOOKUPGE built in function. It acts like a the SETLL operation code in RPG. In this case it returns the number of the first element where the search string is either or equal or greater.

Line 108: The subfile can be loaded from that point.

Line 109: The value in the "position to" field is moved to another variable, therefore, I can tell when the "position to" has been changed.

After compiled the program when I call it the screens look exactly the same as the ones in the previous post, but I am sure I can notice a difference in speed with the subfile being re-displayed faster in this program that it was in the previous post's program.

R P G P G M . C O M Screen at a time subfile example Position to Person name ALLEN, REG ASTON JR, JOHN BENNION, RAYMOND BERRY, JOHNNY BIRCH, BRIAN BLANCHFLOWER, JACKIE BOND, ERNEST BULLOCK, JAMES BYRNE, ROGER CAREY, JOHNNY More...

R P G P G M . C O M Screen at a time subfile example Position to Person name HILDITCH, LAL HOPKINSON, SAMUEL JONES, MARK JONES, THOMAS LYDON, GEORGE MCGLEN, WILLIAM MCLENAHAN, HUGH MCNULTY, THOMAS MCSHANE, HAROLD MELLOR, JACK More...

R P G P G M . C O M Screen at a time subfile example Position to Person name PARKER, THOMAS PEARSON, STAN RAMSDEN, CHARLES REDMAN, BILLY ROWLEY, JACK SILCOCK, JACK STEWARD, ALFRED WALTON, JOHN WHITEFOOT, JEFFREY WILLIAMS, FRANK More...

R P G P G M . C O M Screen at a time subfile example Position to Person name WILSON, JACK Bottom

When I enter a value in the "position to" I get to see the closest match at the top of the subfile.

R P G P G M . C O M Screen at a time subfile example Position to SIL Person name SILCOCK, JACK STEWARD, ALFRED WALTON, JOHN WHITEFOOT, JEFFREY WILLIAMS, FRANK WILSON, JACK Bottom

I can even page up to before the "position to" I entered.

R P G P G M . C O M Screen at a time subfile example Position to SIL Person name MCGLEN, WILLIAM MCLENAHAN, HUGH MCNULTY, THOMAS MCSHANE, HAROLD MELLOR, JACK PARKER, THOMAS PEARSON, STAN RAMSDEN, CHARLES REDMAN, BILLY ROWLEY, JACK More...

I am still stumped why this is a good idea.

 

After showing written these two examples if I ever had to use a "screen at a time" subfile I would use the method described in this post, rather than the purely RPG approach shown in the previous post.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.