SQL Cursor Definitions
Preparing SQL cursors
Scroll cursors support modifications as well as all cursor directional options such as FETCH NEXT, FETCH FIRST, FETCH LAST, FETCH PRIOR, FETCH RELATIVE and FETCH ABSOLUTE. In addition, changes to the underlying tables are reflected in the cursor. The downside is that the cursor is re-evaluated on each call. The example listed shows a scroll cursor being prepared from a variable character string. The string is formatted to use as the select statement in the PREPARE clause. The DECLARE clause defines the cursor as a scroll-type cursor.
Scroll Cursor
IF %parms >= 1 and orderby > *blanks ;
OrderString = %trim(orderBy) ;
ELSE ;
OrderString = dftorder ;
ENDIF ;
IF %parms > 1 and SelectOnly > *blanks ;
SelectString = %trim(SelectOnly) ;
ELSE ;
SelectString = dftSelect ;
ENDIF ;
QRYstring = %trim(SelectString) + ' ' + %trim(OrderString) ;
// Declare the SQL cursor to hold the data
// Open the SQL cusror
EXEC SQL PREPARE selStmt FROM :QRYstring ;
EXEC SQL DECLARE XFCursor SCROLL CURSOR FOR selStmt ;
EXEC SQL OPEN XFCursor ;
IF SQLSTT = SQLSTTOK ;
erroroccurred = *OFF ;
ELSE ;
erroroccurred = *ON ;
ENDIF ;
Dynamic Cursor
The code below defines a dynamic cursor. The selection is prepared with the question marks representing a replacement variable as filters in the WHERE clause. When the cursor is opened, the OPEN statement must include the USING clause to supply the variables to be inserted into the select statement.
Dcl-Proc SetPaymentCursor Export;
Dcl-PI SetPaymentCursor ind ;
End-PI ;
Dcl-s isFound ind inz(*off) ;
Dcl-s errorOccurred ind inz(*off) ;
Dcl-s qt char(1) inz('''') ;
Dcl-s QRYstring char(1024) ;
Dcl-s len zoned(3:0) ;
//*---------------------------------
//* Substring and concatenate the
//* transaction date to cast as a
//* date type field for the compare.
//*---------------------------------
QRYstring = 'Select * From VayPay Where vaLoan = ? +
and vtxfer = ? and vacode <> ''cnv'' and +
CAST( concat(substr(DIGITS(VATRDTE),1,4),concat(''-'', +
concat(substr(DIGITS(VATRDTE),5,2),concat(''-'', +
substr(DIGITS(VATRDTE),7,2))))) AS DATE) +
>= ? and +
CAST( concat(substr(DIGITS(VATRDTE),1,4),concat(''-'', +
concat(substr(DIGITS(VATRDTE),5,2),concat(''-'', +
substr(DIGITS(VATRDTE),7,2))))) AS DATE) +
<= ? ';
EXEC SQL PREPARE selectString FROM :QRYstring;
EXEC SQL DECLARE payment4048Cursor
SCROLL CURSOR FOR selectString;
IF SQLSTT = SQLSTTOK ;
ErrorOccurred = *off ;
ELSE ;
ErrorOccurred = *ON ;
ENDIF ;
RETURN ErrorOccurred ;
End-Proc SetPaymentCursor ;
.
.
.
Exec SQL open Payment4048Cursor
using :shloan,:shtxfer,:shblfrdt,:shbltodt;
If SQLstt = SQLsttOK;
csrOpen = *on;
EndIF;
Insensitive Cursor
The insensitive cursor executes the query and stores the entire set of data that is used for the cursor in tempdb. Thus, the query is only run once and the data is static. The result of this is good performance, since the cursor query is only run once, but no modifications to the cursor can be made, nor are changes made to the underlying tables reflected in the cursor. With an insensitive cursor, only FETCH NEXT is supported.
Exec SQL
select count(*) into :rowCount
From EFSCTLPF
Where ecarea = :thisArea and ecname = :thisName ;
EXEC SQL
declare EFScript240 insensitive cursor for
(select * from EFSCTLPF
Where ecArea = :thisArea and ecName = :thisName
order by ecseqn);
Exec SQL open EFScript240 ;
EXEC SQL
fetch EFScript240 for :rowCount rows into :detail;