IBM Books

SQL Reference

PREPARE

The PREPARE statement is used by application programs to dynamically prepare an SQL statement for execution. The PREPARE statement creates an executable SQL statement, called a prepared statement, from a character string form of the statement, called a statement string.

Invocation

This statement can be embedded only in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

For statements where authorization checking is performed at statement preparation time (DML), the privileges held by the authorization ID of the statement must include those required to execute the SQL statement specified by the PREPARE statement. For statements where authorization checking is performed at statement execution (DDL, GRANT, and REVOKE statements), no authorization is required to use the statement; however, the authorization is checked when the prepared statement is executed.

Syntax 

>>-PREPARE--statement-name----+------------------------+-------->
                              '-INTO--descriptor-name--'
 
>----FROM--host-variable---------------------------------------><

Description

statement-name
Names the prepared statement. If the name identifies an existing prepared statement, that previously prepared statement is destroyed. The name must not identify a prepared statement that is the SELECT statement of an open cursor.

INTO
If INTO is used, and the PREPARE statement is successfully executed, information about the prepared statement is placed in the SQLDA specified by the descriptor-name.

descriptor-name
Is the name of an SQLDA. 93

FROM
Introduces the statement string. The statement string is the value of the specified host variable.

host-variable
Must identify a host variable that is described in the program in accordance with the rules for declaring character string variables. It must be a character-string variable (either fixed-length or varying-length).

Rules

Notes

Examples

Example 1:  Prepare and execute a non-select-statement in a COBOL program. Assume the statement is contained in a host variable HOLDER and that the program will place a statement string into the host variable based on some instructions from the user. The statement to be prepared does not have any parameter markers. 

  EXEC SQL  PREPARE STMT_NAME FROM :HOLDER  
  END-EXEC.
 
  EXEC SQL  EXECUTE STMT_NAME  
  END-EXEC.

Example 2:  Prepare and execute a non-select-statement as in example 1, except code it for a C program. Also assume the statement to be prepared can contain any number of parameter markers. 

  EXEC SQL  PREPARE STMT_NAME FROM :holder;
  EXEC SQL  EXECUTE STMT_NAME USING DESCRIPTOR :insert_da;

Assume that the following statement is to be prepared: 

  INSERT INTO DEPT VALUES(?, ?, ?, ?)

The columns in the DEPT table are defined as follows: 

   DEPT_NO   CHAR(3) NOT NULL, -- department number
   DEPTNAME  VARCHAR(29), -- department name
   MGRNO     CHAR(6), -- manager number
   ADMRDEPT  CHAR(3)  -- admin department number



REQTEXT

To insert department number G01 named COMPLAINTS, which has no manager and reports to department A00, the structure INSERT_DA should have the above values before issuing the EXECUTE statement.

Footnotes:

93
The DESCRIBE statement may be used as an alternative to this clause. See DESCRIBE.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]