Examples: ILE RPG - Host code for ODBC procedures

In this example, the program, SPROC2, is called from the client as a procedure via IBM® i Access ODBC using a CALL statement. It returns data to the client from the PARTS database file.

ILE-RPG example:

      * This example is written in ILE-RPG
      *
      * Define option and part as integer
     D#opt             s             10i 0
     D#part            s             10i 0
      * Define part as packed 5/0
     Dpart             s              5p 0

     C     *entry        plist
     C                   parm                    #opt
     C     part          parm                    #part

     C     #opt          caseq     1             onerec
     C     #opt          caseq     2             allrec
     C                   endcs

     C                   eval      *inlr = *on
     C                   return
      *
      ****************************
     C     onerec        begsr
      ****************************
      * Process request for a single record.
     C/EXEC SQL DECLARE C1 CURSOR FOR
     C+   SELECT
     C+   PARTNO,
     C+   PARTDS,
     C+   PARTQY,
     C+   PARTPR,
     C+   PARTDT
     C+
     C+   FROM PARTS               -- FROM PART MASTER FILE
     C+
     C+   WHERE PARTNO  = :PART
     C+
     C+
     C+  FOR FETCH ONLY            -- READ ONLY CURSOR
     C/END-EXEC
     C*
     C/EXEC SQL
     C+  OPEN C1
     C/END-EXEC
     C*
     C/EXEC SQL
     C+  SET RESULT SETS CURSOR C1
     C/END-EXEC
     C                   endsr
      ****************************
     C     allrec        begsr
      ****************************
      * Process request to return all records
     C/EXEC SQL DECLARE C2 CURSOR FOR
     C+   SELECT
     C+   PARTNO,
     C+   PARTDS,
     C+   PARTQY,
     C+   PARTPR,
     C+   PARTDT
     C+
     C+   FROM PARTS       -- FROM PART MASTER FILE
     C+
     C+
     C+  ORDER BY PARTNO   -- SORT BY PARTNO
     C+
     C+  FOR FETCH ONLY    -- READ ONLY CURSOR
     C/END-EXEC
     C*
     C/EXEC SQL
     C+  OPEN C2
     C/END-EXEC
     C*
     C/EXEC SQL
     C+  SET RESULT SETS CURSOR C2
     C/END-EXEC
     C                   endsr