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

What to Do

Declaring a Cursor

To execute a prepared CALL statement, the program must first declare a cursor for the prepared statement. The sample program declares this cursor:

EXEC SQL
     DECLARE CURSOR1 CURSOR FOR CALL_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 CALL_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 the CALL statement. This part of the statement is initialized in working storage:

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

The variable portion of the statement, which specifies the procedure-reference in the form of [schema].procedure [parameters], is completed when BUILD-SQL-STATEMENT section of the program executes.

Declaring Host Variables for 3 Parameters

The sample program performs a fetch into 3 host variables after it opens the cursor.

The sample program declares the following host variables within an SQL declaration:

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

Executing the Fetch

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

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.