Embedded Cusor Example

The code below is a procedure designed to set a file cursor for use in an RPG program using SQL file access instead of native file access.


      *=================================
      * Set the cursor position for JFI
      *=================================
     p SetXrefCursor   b                   export
     d SetXrefCursor   pi              n
     d   orderBy                    256a   CONST OPTIONS(*NOPASS)
     d   selectOnly                 256a   CONST OPTIONS(*NOPASS)
     d isFound         s               n   inz(*off)
     d errorOccurred   s               n   inz(*off)
     d dftSelect       s             30a   inz('select * from EDP025 ')
     d dftorder        s             30a   inz('order by custid,pinum')
     d genericString   s             10a
     d orderString     s            256a
     d SelectString    s            256a
     d qt              s              1a   inz('''')
     d QRYstring       s            500a
     d len             s              3S 0
      /free

         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);

       //*-----------------------------------------
       //  Prepare the select from the query string
       //  Declare the SQL cursor to hold the data
       //  Open the cursor
       //*-----------------------------------------
          EXEC SQL PREPARE selStmt FROM :QRYString ;
          EXEC SQL DECLARE JFIxrefCursor SCROLL CURSOR FOR selStmt;
          EXEC SQL OPEN JFIxrefCursor;
          IF sqlstt = sqlsttok;
             ErrorOccurred = *OFF;
          ELSE;
             ErrorOccurred = *on;
          ENDIF;
          RETURN ErrorOccurred;
      /end-free
     p SetXrefCursor   e

With the V5R3 compliler, the entries in column 6 and 7 of the code line were requiredso SQL statements are best separated from free-format RPG and moved to a separate subroutine to avoid having to repeatedly enter and exit free format syntax. Here is a file cusrsor procedure written in V5R3 style.could appear in-line in free-format code. It makes t

	
      *=================================
      * Set the cursor position for gate
      *=================================
     p SetAttrCursor   b                   export
     d SetAttrCursor   pi              n
     d   orderBy                    256a   CONST OPTIONS(*NOPASS)
     d   selectOnly                 256a   CONST OPTIONS(*NOPASS)

     d isFound         s               n   inz(*off)
     d errorOccurred   s               n   inz(*off)
     d DftSelect       s             30a   inz('select * from RATATRPF ')
     d DftOrder        s             50a   inz('order by rtrtab, rtrcod, -
     d                                       rtrnam')
     d orderString     s            256a
     d SelectString    s            256a
     d qt              s              1a   inz('''')
     d QRYstring       s            550a
     d len             s              3S 0
      /free

         IF %parms >= 1;
            OrderString = %trim(orderBy);
         ELSE;
            OrderString = DftOrder;
         ENDIF;

         IF %parms > 1;
            SelectString = %trim(SelectOnly);
         ELSE;
            SelectString = DftSelect;
         ENDIF;

         IF SelectString = *blanks;
            SelectString = DftSelect;
         ENDIF;
         IF OrderString = *blanks;
            OrderString = DftOrder;
         ENDIF;

         QRYstring = %trim(SelectString) + ' ' +  %trim(orderString);

      /end-free
     C/EXEC SQL
     C+   PREPARE selStmt FROM :QRYstring
     C/END-EXEC
      *---
      * Declare the SQL cursor to hold the data retrieved from the SELECT
      *---
     C/EXEC SQL
     C+ DECLARE AttrCursor SCROLL CURSOR FOR selStmt
     C/END-EXEC
      *---
      * Open the SQL cursor.
      *---
     C/EXEC SQL
     C+ OPEN AttrCursor
     C/END-EXEC
     C                   IF        SQLSTT = SQLSTTOK
     C                   EVAL      ERROROCCURRED = *OFF
     C                   ELSE
     C                   EVAL      ERROROCCURRED= *ON
     C                   ENDIF
     C                   RETURN    ERROROCCURRED
     p SetAttrCursor   e