PREPARE

Syntax:

>>--EXEC SQL---PREPARE---stmt_name---.-------------.----->
                                     +-INTO :sqlda-+

 >---FROM---:hvar---END-EXEC-----><

Parameters:

`stmt_name`	The prepared statement name. This can be used by a subsequent EXECUTE or OPEN statement, and/or a previous DECLARE CURSOR statement.
:`sqlda`	The output SQL descriptor area (SQLDA) data structure to be populated. The colon is optional to provide compatibility with other embedded SQL implementations.
:`hvar`	The host variable that contains the SQL statement.

Comments:

You can use a prepared statement in one of two ways:

You can open a cursor that references a prepared statement.
You can execute a prepared statement.

If the prepared statement is used by an EXECUTE statement, :hvar cannot contain a SQL statement that returns results.

Because singleton SELECT statements (SELECT INTO) are not allowed in dynamic SQL statements, they cannot be prepared.

When using PREPARE, the SQL statement in :hvar cannot contain host variables or comments, but it can contain parameter markers. Also, the SQL statement cannot contain SQL keywords that pertain exclusively to Embedded SQL.

The INTO :sqlda option merges the functionality of DESCRIBE and PREPARE so that this example code:

 EXEC SQL 
     PREPARE stmt1 INTO :sqlda FROM :stmt-buf
 END-EXEC

is identical to:

 EXEC SQL
     PREPARE stmt1 FROM :stmt-buf 
 END-EXEC
 EXEC SQL
     DESCRIBE stmt1 INTO :sqlda 
 END-EXEC

For more details about using the PREPARE statement, see your Database Access Guide.

Example:

 PROGRAM-ID. progname.
     
 WORKING-STORAGE SECTION.
 EXEC SQL INCLUDE SQLCA END-EXEC
 EXEC SQL BEGIN DECLARE SECTION END-EXEC
 01 prep           PIC X(80).
 01 nme            PIC X(20).
 01 car            PIC X(20).
 01 n60            PIC x(5).
 EXEC SQL END DECLARE SECTION END-EXEC.

 PROCEDURE DIVISION.
     EXEC SQL CONNECT TO 'srv1' USER 'sa' END-EXEC
     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not connect to database.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRMC
        STOP RUN
     END-IF

* Ensure attempt is not made to recreate an existing table...
     EXEC SQL DROP TABLE mf_table END-EXEC

* Create a table...
     EXEC SQL CREATE TABLE mf_table
                 (owner          char(20)
                 ,car_col        char(20)
                 ,nought_to_60   char(5))
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not create table'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT CURRENT END-EXEC
        STOP RUN
     END-IF

* Insert an SQL statement into host variable prep...
     MOVE "insert into mf_table values(?,?,?)" TO prep

* Prepare the statement...
     EXEC SQL
        PREPARE prep_stat FROM :prep
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not prepare statement'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT CURRENT END-EXEC
        STOP RUN
     END-IF

     MOVE "Owner" TO nme
     MOVE "Lamborghini" TO car
     MOVE "4.9" TO n60

* Execute the prepared statement using the above host variables...
     EXEC SQL
        EXECUTE prep_stat USING :nme, :car, :n60
     END-EXEC
     
     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not execute prepared statement.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT CURRENT END-EXEC
        STOP RUN
     END-IF

* Finally, drop the now unwanted table...
     EXEC SQL
        DROP TABLE mf_table
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not drop table.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT CURRENT END-EXEC
        STOP RUN
     END-IF

     DISPLAY 'All statements executed.'
     EXEC SQL DISCONNECT CURRENT END-EXEC
     STOP RUN.