Using Dynamic SQL › Executing Prepared SELECT Statements › Sample Program

Sample Program

  IDENTIFICATION DIVISION.
  PROGRAM-ID.    EMPVIEW1.

  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.
  01  LIMITS-AND-CONSTANTS.
      02  MAX-TEXT-LINES     PIC S9  COMP VALUE 5.

  01  FIRST-PART-OF-STATEMENT.
      02  FILLER             PIC X(32)  VALUE
          'SELECT EMP_ID, EMP_FNAME,'.
      02  FILLER             PIC X(32)  VALUE
          ' EMP_LNAME, DEPT_ID,'.
      02  FILLER             PIC X(32)  VALUE
          ' MANAGER_ID, START_DATE '.
      02  FILLER             PIC X(32)  VALUE
          ' FROM DEMO.EMPL_VIEW_1 '.

  01  HEADING-LINE.
      02  FILLER             PIC X(31)  VALUE
          'ID #    FIRST NAME '.
      02  FILLER             PIC X(23)  VALUE
          'LAST NAME '.
      02  FILLER             PIC X(31)  VALUE
          'DEPT    MGR     START DATE'.
  01  DETAIL-LINE.
      02  EMP-ID             PIC 9(5).
      02  FILLER             PIC X(3)   VALUE SPACES.
      02  EMP-FNAME          PIC X(20).
      02  FILLER             PIC X(3)   VALUE SPACES.
      02  EMP-LNAME          PIC X(20).
      02  FILLER             PIC X(3)   VALUE SPACES.
      02  DEPT-ID            PIC 9(5).
      02  FILLER             PIC X(3)   VALUE SPACES.
      02  MANAGER-ID         PIC 9(5).
      02  FILLER             PIC X(3)   VALUE SPACES.
      02  START-DATE         PIC X(10).
  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(128).
      02  VARIABLE-PART.
          03  TEXT-LINES OCCURS 5 TIMES PIC X(80).
  EXEC SQL    BEGIN  DECLARE SECTION           END-EXEC
  77  DBNAME                 PIC X(8).
  01  STATEMENT-TEXT         PIC X(641).
  01  FETCH-BUFFER.
  EXEC SQL
     INCLUDE TABLE DEMO.EMPL_VIEW_1
               (EMP_ID, EMP_FNAME, EMP_LNAME,
                 DEPT_ID, MANAGER_ID, START_DATE)
               NUMBER OF ROWS 50
               LEVEL 02
  END-EXEC.

  EXEC SQL    END   DECLARE SECTION           END-EXEC

 *********************************************************
 *****         DECLARE CURSORS                       *****
  EXEC SQL
       DECLARE CURSOR1 CURSOR FOR SELECT_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-LINE (TEXT-CTR)
      ELSE
         MOVE SPACES     TO TEXT-LINE (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 SELECT
         PREPARE SELECT_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
            BULK :FETCH-BUFFER
      END-EXEC.
      MOVE 1 TO ROW-CTR.
      PERFORM DISPLAY-ROW
         UNTIL ROW-CTR > SQLCNRP.


  DISPLAY-ROW SECTION.
      MOVE CORRESPONDING EMPL-VIEW-1 (ROW-CTR) TO DETAIL-LINE.
      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.