Using Dynamic SQL › Executing Prepared CALL Statements › Sample Program

Sample Program

        IDENTIFICATION DIVISION.
        PROGRAM-ID.    DYNCALL.
      *---------------------------------------------------------*
      *                                                         *
      *                                                         *
      * DYNCALL will read a procedure-reference and execute it  *
      * dynamically.                                            *
      *                                                         *
      *   It is assumed that the procedure has 3 parameters,    *
      *   P1 and P2 are numeric, P3 is alphanumeric.            *
      *---------------------------------------------------------*
        ENVIRONMENT DIVISION.
        DATA DIVISION.
        WORKING-STORAGE SECTION.

        01  SQLMSGS.
            02  SQLMMAX            PIC S9(8) COMP VALUE +6.
            02  SQLMSIZE           PIC S9(8) COMP VALUE +80.
            02  SQLMCNT            PIC S9(8) COMP.
            02  SQLMLINE           OCCURS 6 TIMES PIC X(80).

        01  REQ-WK.
            02  REQUEST-CODE       PIC S9(8) COMP.
            02  REQUEST-RETURN     PIC S9(8) COMP.

        77  LINE-CNT               PIC S9(8) COMP.
        01  LIMITS-AND-CONSTANTS.
            02  MAX-TEXT-LINES     PIC S9  COMP VALUE 5.

        01  FIRST-PART-OF-STATEMENT.
            02  FILLER             PIC X(8)  VALUE
                'CALL '.

        01  HEADING-LINE.
            02  FILLER             PIC X(13)  VALUE
                'P1 PIC 9(10)'.
            02  FILLER             PIC X(13)  VALUE
                'P2 PIC 9(10)'.
            02  FILLER             PIC X(33)  VALUE
                'P3 X(32)'.

        EXEC SQL    BEGIN   DECLARE SECTION           END-EXEC
        01  DETAIL-LINE.
            02  P1                 PIC 9(10).
            02  FILLER             PIC X(3)   VALUE SPACES.

            02  P2                 PIC 9(10).
            02  FILLER             PIC X(3)   VALUE SPACES.
            02  P3                 PIC X(32)  VALUE SPACES.
            02  FILLER             PIC X(3)   VALUE SPACES.

        01  DBNAME                 PIC X(8).
        01  STATEMENT-TEXT         PIC X(641).

        EXEC SQL    END   DECLARE SECTION           END-EXEC

        01  WORK-FIELDS.
            02  ROW-CTR            PIC S99 COMP.
            02  TEXT-CTR           PIC S99 COMP.
            02  INPUT-LINE.
                03  END-CHAR       PIC X.
                  88 END-STATEMENT    VALUE ';'.
                03  FILLER         PIC X(79).
            02  SQLVALUE           PIC ----9.

        01  STATEMENT-TXT2.
            02  FIXED-PART         PIC X(8).
            02  VARIABLE-PART.
                03  TEXT-LINES OCCURS 5 TIMES PIC X(80).

      *********************************************************
      *****         DECLARE CURSORS                       *****

        EXEC SQL

             DECLARE CURSOR1 CURSOR FOR CALL_STATEMENT
        END-EXEC
      *********************************************************

        PROCEDURE DIVISION.

        EXEC SQL
          WHENEVER SQLERROR GO TO SQL-ERROR
        END-EXEC.

        MAINLINE SECTION.
            ACCEPT DBNAME.
            MOVE FIRST-PART-OF-STATEMENT TO FIXED-PART.
            MOVE 1 TO TEXT-CTR.
            PERFORM BUILD-SQL-STATEMENT
               UNTIL TEXT-CTR > MAX-TEXT-LINES.
            IF END-STATEMENT
               PERFORM PREPARE-AND-OPEN-CURSOR

               PERFORM FETCH-ROWS
                 UNTIL SQLCODE = 100
               EXEC SQL  COMMIT RELEASE   END-EXEC.
            GOBACK.

        BUILD-SQL-STATEMENT SECTION.
            IF NOT END-STATEMENT
               ACCEPT INPUT-LINE
               DISPLAY INPUT-LINE.
            IF NOT END-STATEMENT
               MOVE INPUT-LINE TO TEXT-LINES(TEXT-CTR)

            ELSE
               MOVE SPACES     TO TEXT-LINES(TEXT-CTR).
            ADD 1 TO TEXT-CTR.



        PREPARE-AND-OPEN-CURSOR SECTION.
            EXEC SQL                 -- CONNECT TO DATABASE
               CONNECT TO :DBNAME
            END-EXEC.

            EXEC SQL                 -- SET ISOLATION MODE

               SET TRANSACTION TRANSIENT READ
            END-EXEC.

            MOVE STATEMENT-TXT2 TO STATEMENT-TEXT.
            EXEC SQL                 -- PREPARE THE CALL
               PREPARE CALL_STATEMENT FROM :STATEMENT-TEXT
            END-EXEC.

            EXEC SQL                 -- OPEN THE CURSOR
               OPEN CURSOR1
            END-EXEC.

            DISPLAY ' '.
            DISPLAY ' '.
            DISPLAY HEADING-LINE.
            DISPLAY ' '.


        FETCH-ROWS SECTION.
            EXEC SQL
               FETCH CURSOR1
                 INTO :P1, :P2, :P3

            END-EXEC.
            MOVE 1 TO ROW-CTR.
            PERFORM DISPLAY-ROW
               UNTIL ROW-CTR > SQLCNRP.

        DISPLAY-ROW SECTION.
            DISPLAY DETAIL-LINE.
            ADD 1 TO ROW-CTR.

        SQL-ERROR         SECTION.
            DISPLAY '****************** ERROR IN SQL STATEMENT'
                    ' ******************'.
            DISPLAY 'PROGRAM          ' SQLPGM
            DISPLAY 'COMPILED         ' SQLDTS
            MOVE SQLCLNO TO SQLVALUE.
            DISPLAY 'SQL LINE NUMBER  ' SQLVALUE
            MOVE SQLCODE TO SQLVALUE.
            DISPLAY 'SQLCODE          ' SQLVALUE
            MOVE SQLCERC  TO SQLVALUE.
            DISPLAY 'REASON CODE      ' SQLVALUE

            MOVE SQLCERC  TO SQLVALUE.
            DISPLAY 'ERROR CODE       ' SQLVALUE
            MOVE SQLCNRP  TO SQLVALUE.
            DISPLAY 'ROWS PROCESSED   ' SQLVALUE

            MOVE 4 TO REQUEST-CODE.
            CALL 'IDMSIN01' USING SQLRPB, REQ-WK,

                  SQLCA, SQLMSGS.
            IF REQUEST-RETURN NOT = 4
              MOVE 1 TO LINE-CNT
              PERFORM DISP-MSG UNTIL LINE-CNT > SQLMCNT.

        DISP-MSG SECTION.
            DISPLAY SQLMLINE (LINE-CNT).
            ADD 1 TO LINE-CNT.