The following example uses prepare and fetch, and prompts the user for an order by clause in a select statement:
exec sql begin declare section end-exec
01 AGE PIC S9(2) COMP.
01 R-AGE PIC S9(2).
01 ROYALTY PIC S9(9) COMP.
01 TITLE PIC X(25).
01 MANAGER PIC X(25).
01 SQLSTR2 PIC X(100).
01 I-TITLE PIC S9(4) COMP.
01 I-AGE PIC S9(4) COMP.
exec sql end declare section end-exec
01 DSP-AGE PIC 9(2).
01 DSP-ROYALTY PIC -ZZZ,ZZZ,ZZZ.
PROCEDURE DIVISION.
MOVE 60 TO R-AGE.
MOVE "select age, royalty, title, manager from
- " inprogr where age !=?" TO SQLSTR2
MOVE 0 TO I-AGE.
exec sql prepare statement2 from :SQLSTR2
end-exec.
exec sql declare C1 cursor for statement2
end-exec
exec sql whenever not found goto NOT-FOUND
end-exec
exec sql open C1 using :R-AGE indicator :I-AGE
end-exec.
RET-LOOP.
MOVE 0 TO I-TITLE.
exec sql fetch C1 into
:AGE, :ROYALTY,
:TITLE indicator :I-TITLE,
:MANAGER end-exec.
MOVE AGE TO DSP-AGE.
MOVE ROYALTY TO DSP-ROYALTY.
IF I-TITLE = -1
MOVE "Null" TO TITLE.
DISPLAY "Age = " DSP-AGE
" Royalty = " DSP-ROYALTY
" Title = " TITLE
" Manager = " MANAGER.
DISPLAY " ".
GO TO RET-LOOP.
NOT-FOUND.
exec sql close C1 end-exec.
