EXECUTE

Processes dynamic SQL statements.

Syntax:

>>-EXEC SQL-----.-------------------.------EXECUTE------>
                +--FOR :host_integer---+

 >-prepared_stmt_name-.--------------------------------.->
                      +-USING DESCRIPTOR :sqlda_struct-+
                      |       +- , -+                  |
                      |       V     |                  |
                      +-USING :hvar--------------------+ 
    
 >-----END-EXEC-----<>
 

Parameters:

:host_integer A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
prepared_stmt_name A previously prepared SQL statement.
:sqlda_struct A previously declared SQLDA data structure containing a description of the input values. The colon is optional to provide compatibility with other embedded SQL implementations.
:hvar One or more input host variables.

Comments:

Do not use the FOR clause if the EXECUTE is part of a DECLARE CURSOR statement.

The EXECUTE statement runs the specified prepared SQL statement after substituting values for any parameter markers. (Prepared statements are created using the PREPARE statement.) Only statements that do not return results are permitted.

If the prepared statement contains parameter markers, the EXECUTE statement must include either the USING :hvar option with the same number of host variables or the USING DESCRIPTOR :sqlda_struct option identifying a SQLDA data structure already populated by the application.

The number of parameter markers in the prepared statement must match the number of sqldata entries (USING DESCRIPTOR :sqlda) or host variables (USING :hvar).

Example:

* Store statement to be dynamically executed...
     MOVE "INSERT INTO staff VALUES(?,?,?,?,?)" TO stmtbuf.

* Ensure attempt is not made to insert an existing record
     EXEC SQL
        DELETE FROM staff WHERE staff_id = 99
     END-EXEC

* Prepare the statement
     EXEC SQL
        PREPARE st FROM :stmtbuf
     END-EXEC.

     MOVE 99 TO staff-id
     MOVE 'Lee' TO last-name
     MOVE 'Phil' TO first-name
     MOVE 19 TO age
     MOVE '1997-01-01' TO employment-date

* Execute the statement with current values.
     EXEC SQL
        EXECUTE st USING :staff-id, :last-name
                        ,:first-name, :age, :employment-date
     END-EXEC
     IF SQLCODE = ZERO
        DISPLAY 'Statement executed.'
     ELSE
        DISPLAY 'Error: Could not execute statement.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Finally, remove the entry
     EXEC SQL
        DELETE FROM staff where staff_id = 99
     END-EXEC
     IF SQLCODE = ZERO
        DISPLAY 'Values deleted.'
     ELSE
        DISPLAY 'Error: Could not delete inserted values.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF