Dyanamic Cursor Example

Defining a dynamic cursor requires supplying program variables to the statement.

Dynamic Cursor

Declaring a dynamic cursor means that the program will have to supply the variables used on the SELECT statement.


The DECLARE in RPG looks like the example below. The question mark (?) denotes that a program variable should supply the necessary value to complete the SELECT statement. 

      //*----------------------------------------------------------------------
      //* SetPaymentCursor - this procedure creates a dynamic cursor for the use
      //*    in fetching the payment records for the loan statement.
      //*
      //* Parameter       ATR      I/O       Description
      //* ----------      ---      ---       -----------
      //* error indicator    n       O       Error indicator
      //*
      //*----------------------------------------------------------------------

     p SetPaymentCursor...
     p                 b                   export
     d SetPaymentCursor...
     d                 pi              n
     d isFound         s               n   inz(*off)
     d errorOccurred   s               n   inz(*off)
     d qt              s              1a   inz('''')
     d QRYstring       s           1024a
     d len             s              3S 0
      /free

         //*---------------------------------
         //* 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-free
     p SetPaymentCursor...
     p                 e

Four question marks in the DECLARE indicates that four values must be supplied when using the cursor. 

         noMorePayments = *Off;

         If csrOpen;
            Exec SQL Close Payment4048Cursor;
            csrOpen = *off;
         ENDIF;

         //*---------------------------------
         //* Open cursor for payment file;
         //* the transaction must fall within
         //* the from and to date of the statement
         //*---------------------------------

         Exec SQL open Payment4048Cursor
            using :shloan,:shtxfer,:shblfrdt,:shbltodt;

         If SQLstt = SQLsttOK;
           csrOpen = *on;
         EndIF;

         //*---------------------------------
         //* Read payments file and retrieve:
         //*---------------------------------

         DoU noMorePayments;

            Clear Payment;
            Clear feesPaid;

            Exec SQL
               Fetch Payment4048Cursor Into :payment;

            If SQLstt = SQLsttOK;
.
.
.
            Else;

               noMorePayments = *on;

            EndIf;

         EndDo;

         If csrOpen;
            Exec SQL Close Payment4048Cursor;
            csrOpen = *off;
         ENDIF;

EXECUTE IMMEDIATE

Use the EXECUTE IMMEDIATE statement to perform tasks equivalent to what the PREPARE, EXECUTE, and FREE statements accomplish, but as a single operation. 

>>-EXECUTE IMMEDIATE-------------------------------------------->

>--+- ' -statement--+-------------------------+--+----+-- ' +--><
   |                |           .- ;--------. |  '- ; '     |   
   |                |  (1)      V           | |             |   
   |                '------- ; ---statement-+-'             |   
   +-statement_var------------------------------------------+   
   |  (2)                                                   |   
   '-------char_expr----------------------------------------'   
.
.
.

{
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
char Qstring[100] = "INSERT INTO WORK_TABLE

SELECT * FROM EMPPROJACT
WHERE ACTNO >= 100";
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA;


EXEC SQL EXECUTE IMMEDIATE :Qstring;
return;
}