Using Dynamic SQL › Executing Prepared SELECT Statements › What to Do

What to Do

Declaring a Cursor

To execute a prepared SELECT statement, the program must first declare a cursor for the prepared statement.

The sample program declares this cursor:

EXEC SQL
      DECLARE CURSOR1 CURSOR FOR SELECT_STATEMENT
END-EXEC.

Preparing the Statement

Before opening a cursor defined with a dynamic SQL statement, the program must prepare the statement.

The sample program issues this PREPARE statement:

EXEC SQL
   PREPARE SELECT_STATEMENT FROM :STATEMENT-TEXT
END-EXEC.

Building the Statement Text

In the sample program, the host variable STATEMENT-TEXT contains a character string consisting of a fixed portion of the statement to which input text is added when the program executes.

The fixed portion of the statement specifies table and columns from which data is selected. This part of the statement is initialized in working storage:

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 '.

The variable portion of the statement, which can specify additional selection criteria such as an ORDER BY or a WHERE clause, is completed when BUILD-SQL-STATEMENT section of the program executes.

Declaring a Host Variable Array

The sample program performs a bulk fetch after it opens the cursor. The bulk fetch requires a host variable array to receive the data.

The sample program declares the host variable array within an SQL declaration section using this INCLUDE statement:

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.

Executing the Fetch

After the program builds the statement text, prepares the statement, and opens the cursor, it issues the bulk fetch:

FETCH-ROWS SECTION.
     EXEC SQL
        FETCH CURSOR1
           BULK :FETCH-BUFFER
     END-EXEC.
     MOVE 1 TO ROW-CTR.
     PERFORM DISPLAY-ROW
        UNTIL ROW-CTR > SQLCNRP.