Brief Overview of Using Embedded SQL with RPG

A cursor is defined in the RPG or RPGLE source code. Then FETCH is used in a loop to access the data from the file. In this loop to process the fetched data, RPG/RPGLE code can be used and the output can be sent to the spool or to the display.

The following are the steps in sequence.

1) Declare the SQL Communication Area. This is a must.

     C/EXEC SQL 
     C+ INCLUDE SQLCA 
     C/END-EXEC 

2) SQL Cursor Declaration

     C/EXEC SQL 
     C+ DECLARE GETREC CURSOR FOR SELECT XABCDE, XFGHIJ, XKLMN, FROM 
     C+ XFILE WHERE 
     C+ XSTRIN LIKE :STRING 
     C/END-EXEC 
     :STRING IS A VARIABLE

3) SQL FETCH Subroutine Declaration 

     C Fetch begsr 
     C 
     C/EXEC SQL 
     C+ FETCH NEXT FROM GETREC INTO :XABCD, :XEFGH
     C/END-EXEC
     ENDSR

4) SQL Close Subroutine Declaration (To Close the Cursor) 

     C Close begsr 
     C 
     C/EXEC SQL 
     C+ CLOSE GETREC 
     C/END-EXEC 
     C endsr 

5) * SQL Open Subroutine Declaration (To Open the Cursor) 

     C Open begsr 
     C 
     C/EXEC SQL 
     C+ OPEN GETREC 
     C/END-EXEC 
     C endsr 

First step in main process would be to open the cursor
     
     EXSR OPEN
     sqlcod doune 0 
     exsr Fetch
     rpg code 

     rpg code
     enddo
     exsr close 

The source type for RPG/400 with embedded SQL is SQLRPG, with ILE it is SQLRPGLE.