I’ve noticed this has been unanswered for a while. I have some expertise in RPG, not COBOL (sorry), but maybe this will help since RPG and COBOL on the iSeries are similar. I’m assuming the embedded SQL is the same.
In the sample code below I move SQL results into RPG (program) variables. Those program variables could easily be SFL fields. Program variables are designated in SQL EXECs by preceding them with “:” (colon) . In this example :sID is a program variable used to determine which records to select. The flow is:
<li>In my main routine, I first call the ‘Open’ routine</li><li>The first bit of RPG code in this subroutine makes sure the cursor is closed</li><li>Embedded SQL then declares a cursor for an SQL Statement</li><li>Embedded SQL opens the cursor</li><li>The next bit of RPG checks for an open cursor, and if so, calls a fetch routine</li><li>The fetch routine is all embedded SQL in an RPG subroutine wrapper. It grabs one record and places the results into program variables ORORD#, ORINV, and CoOCDESC</li><li>In my main routine, I keep calling the fetch routine (not the Open routine) to fill my SFL until the EOF (coEof=True).</li>
Hopefully someone much smarter than I with COBOL skills will improve this answer. In the mean time, I hope this helps.
* Open SQL
C CoSQLOpen BegSR
* Close the SQL Cursor if it’s open
C CoOpen Ifeq True
C Exsr CoSQLClose
* Create the SQL statement and cursor
C+ Declare CoCursor Cursor
C+ Select C.OCORD#, B.ORINV, C.OCDESC
C+ From COMMENTLY C, ORDBILL B
C+ Where C.OCORD# = B.ORODR#
C+ And C.OCTYP = ‘Y’
C+ And C.OCDLT <>’D’
C+ And C.OCDESC Like :sID
* Open Cursor
C+ Open CoCursor
* Check for a resultset. If we got one fetch first record. Set flags
If (SQLCod <> 0) Or (SQLWn0 <> *BLANK);
Eval CoOpen = False;
Eval CoEof = True;
Eval CoOpen = True;
* Fetch next record
C CoSQLFetch Begsr
C+ Fetch Next
C+ From CoCursor
C+ Into :OCORD#,
Eval CoEof = SqlCod = SqCdNoRow;
Since the above RPG is here for comparison, I’ll add some ILE SQL CBL code that’s extracted (and modified) out of a larger program. The code shown shows a possible way of filling a 200 element array that could then be the basis of a SUBFILE that can hold 200 rows:<pre>
05 SF-Row occurs 200.
10 SF-SomeF1 PIC X(10).
10 SF-SomeF2 PIC X(10).
10 SF-SomeOpt PIC X.
01 C1-Status pic 9 value 0.
88 C1-not-open value 0.
88 C1-Open value 1.
Set C1-Open to true.
Declare C1 cursor for
Select SomeF1, SomeF2, SomeOpt from SFTABLE
order by SomeF1, SomeF2
EXEC SQL open C1
Fetch C1 for 200 rows
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(1) SF-SomeF2(1)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(2) SF-SomeF2(2)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(3) SF-SomeF2(3)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(4) SF-SomeF2(4)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(5) SF-SomeF2(5)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(6) SF-SomeF2(6)
Of course, the DISPLAY statements are simply for sample testing.
The FETCH grabs 200 rows into the array. The program would use those to WRITE subfile rows, maybe all 200 or maybe a page at a time.
Depending on how the program should work, different variations of the FETCH might be used. E.g., the subfile attributes of the DSPF might be queried to see how many rows are on each subfile page. The FETCH might then only fetch that many rows. Or the FETCH might be controlled ‘relative’ to the current cursor position, sometimes fetching the previous set of rows, and other times continuing forward for the next set of rows.