PREPARE

The PREPARE statement creates an executable SQL statement from a string form of the statement. The character-string form is called a statement string. The executable form is called a prepared statement.

Invocation

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

Syntax


>>-PREPARE--statement-name--FROM--host-variable----------------><

Description

The following keyword parameters are defined for the PREPARE statement:

statement-name

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

FROM

Specifies the statement string. The statement string is the value of the identified host-variable.

host-variable: Must identify a host variable that is described in the application program in accordance with the rules for declaring variable-length string variables for statement string. The length of the SQL statement cannot be over 32767.

Notes

Rules for statement strings: The value of the specified statement-name is called the statement string. The statement string must be declared with a varying-length character host variable. The first two bytes must contain the length of the SQL statement. The maximum length of the SQL statement is 32,767. For example:

01  STMTSTR.                                   
    49  STMTSTR-LEN  PIC S9(4) COMP VALUE +180.
    49  STMTSTR-TXT  PIC X(180) VALUE SPACES.

The statement string must be one of the following SQL statements:

DELETE
INSERT
SELECT
UPDATE

The statement string must not:

Begin with EXEC SQLIMS
End with END-EXEC or a semicolon
Include references to host variables

Parameter markers: Although a statement string cannot include references to host variables, it can include parameter markers. The parameter markers are replaced by the values of host variables when the prepared statement is executed. A parameter marker is a question mark (?) that appears where a host variable could appear if the statement string were a static SQL statement. For an explanation of how parameter markers are replaced by values, see EXECUTE and OPEN.

Error checking: When a PREPARE statement is executed, the statement string is parsed and checked for errors. If the statement string is invalid, a prepared statement is not created and the error condition that prevents its creation is reported in the SQLIMSCA.

Reference and execution rules: Prepared statements can be referred to in the following kinds of statements, with the following restrictions shown:

DESCRIBE: No restrictions
DECLARE CURSOR: Must be SELECT when the cursor is opened
EXECUTE: Must not be SELECT

Scope of a statement name: The scope of a statement-name is the same as the scope of a cursor-name. See DECLARE CURSOR for more information about the scope of a cursor-name.

Examples

Example 1: Prepare a dynamic SELECT statement with a host variable on the PREPARE statement. The text of the SELECT statement is in a variable named SELECT-STATEMENT.

In the example, the statement text in host variable SELECT-STATEMENT is SELECT HOSPCODE, HOSPNAME, WARDNAME, PATNAME FROM PCB01.HOSPITAL, WARD,PATIENT.

EXEC SQLIMS
DECLARE C1 CURSOR FOR DYSQL
END-EXEC.

EXEC SQLIMS
PREPARE DYSQL FROM :SELECT-STATEMENT
END-EXEC

EXEC SQLIMS OPEN C1 END-EXEC.

EXEC SQLIMS FETCH C1 INTO :HOSPCODE, :HOSPNAME, :WARDNAME, :PATNAME END-EXEC.

IF SQLIMSCODE = 100
PERFORM DATA-NOT-FOUND
ELSE
PERFORM GET-REST-OF-HOSP
UNTIL SQLIMSCODE IS NOT EQUAL TO ZERO.

EXEC SQLIMS CLOSE C1 END-EXEC.

Example 2: Prepare a dynamic INSERT statement with parameter markers and is executed.

For the INSERT statement:

INSERT INTO PCB01.HOSPITAL HOSPCODE, HOSPNAME VALUES(?,?)

The following statement prepares and executes the INSERT statement with parameter marker. Before execution, the values for the parameter markers are read into the host variables S1, S2.

EXEC SQLIMS
PREPARE DYSQL FROM :INSERT-STATEMENT
END-EXEC

EXEC SQLIMS
EXECUTE USING :S1, :S2
END-EXEC.