PREPARE

The PREPARE dynamic compilation statement dynamically compiles an SQL statement for later execution in the application program.

You can use this statement only in SQL that is embedded in a program.

Authorization

To issue the PREPARE statement, you must have the privileges required to issue the statement being prepared.

Syntax

►►─ PREPARE statement-name FROM ─┬─ :statement-text ──┬─────────────────►
                                 └─ 'statement-text' ─┘
 ►──┬──────────────────────────────┬────────────────────────────────────►◄
    ├─ describe-output-expression ─┤
    └─ describe-input-expression ──┘

Expansion of describe-output-expression

►►─ DESCRIBE output USING sql DESCRIPTOR descriptor-area-name1 ────────────►

 ►─┬────────────────────────────────────────────────────┬──────────────────►◄
   └─ INPUT USING sql DESCRIPTOR descriptor-area-name2 ─┘

Expansion of describe-input-expression

►►─ DESCRIBE INPUT USING sql DESCRIPTOR descriptor-area-name2 ─────────────►

 ►─┬─────────────────────────────────────────────────────┬─────────────────►◄
   └─ OUTPUT USING sql DESCRIPTOR descriptor-area-name1 ─┘

Parameters

statement-name

Specifies the name to be assigned to the compiled statement. It must be unique within its associated scope.

Note: For more information, see Expansion of Statement-name.

FROM

Identifies the statement to be compiled.

:statement-text

Identifies a host variable, local variable, or a routine parameter containing a preparable SQL statement. statement-text must be previously declared in the application program or SQL routine. It must be defined as an elementary data item with no sub-elements. Do not specify the colon when statement-text is a local variable or routine parameter.

'statement-text'

Specifies a preparable SQL statement enclosed in single quotation marks. Do not include the SQL prefix or terminator within the statement.

Parameters for Expansion of describe-output-expression

DESCRIBE OUTPUT USING SQL DESCRIPTOR descriptor-area-name1

Specifies the SQL descriptor area in which CA IDMS is to return information about the output values to be returned when the dynamically-compiled statement is executed. Descriptor-area-name1 is the name of the SQL descriptor area.

INPUT USING SQL DESCRIPTOR descriptor-area-name2

Specifies the SQL descriptor area in which CA IDMS is to return information about the dynamic parameters used within the statement.

Descriptor-area-name2 is the name of the SQL descriptor area.

Parameters for Expansion of describe-input-expression

DESCRIBE INPUT USING SQL DESCRIPTOR descriptor-area-name2

Specifies the SQL descriptor area in which CA IDMS is to return information about the dynamic parameters used within the statement.

Descriptor-area-name2 is the name of the SQL descriptor area.

OUTPUT USING SQL DESCRIPTOR descriptor-area-name1

Usage

Preparable Statements

The following SQL statements are preparable:

All authorization and logical data description statements
CALL
COMMIT
cursor-specification
DELETE
EXPLAIN
INSERT
RELEASE
ROLLBACK
SUSPEND SESSION
UPDATE

Additionally, all CA IDMS utility and physical data description statements are preparable.

Specifying Dynamic Parameters

Dynamic parameters are variables whose values are supplied when the statement is executed, or in the case of a SELECT or a CALL statement, when its associated cursor is opened.

Dynamic parameters are specified as question marks (?) within the text of the SQL statement. They may appear wherever a host variable is permitted with certain exceptions.

Describing Dynamic Parameters

The INPUT option is used to return information about dynamic parameters that may be embedded in the SQL statement being described. The SQLD field of the descriptor area indicates the number of dynamic parameter that appear in the statement. If no dynamic parameters are used, this field is zero (0).

If dynamic parameters do appear in the statement, CA IDMS returns descriptions of the parameters in the descriptor area. The data type information is derived from the context in which the dynamic parameter appears.

Describing Output Values

The OUTPUT option is used to return information about values output from CA IDMS:

For a SELECT or a CALL statement, CA IDMS returns a description of the result table defined by the statement. The SQLD field of the descriptor area indicates the number of columns in the result table.
For a statement other than SELECT or CALL, CA IDMS returns the value zero (0) in the SQLD field of the descriptor area.

No Host Variables, Local Variables, or Routine Parameters in a Dynamically Compiled Statement

An SQL statement that is to be compiled dynamically cannot include any host variables, local variables, or routine parameters.

Re-executing a PREPARE Statement

When reexecuting a PREPARE statement, CA IDMS replaces the previously prepared statement with the statement currently identified in the PREPARE statement. If the previously prepared statement is a SELECT or a CALL statement associated with an open cursor, CA IDMS closes the cursor.

Duration of Dynamically Compiled Statements

Dynamically-compiled statements are available for execution until the transaction terminates or until destroyed using a DEALLOCATE PREPARE statement.

Specifying the Maximum Number of Column Entries

The application program must specify the maximum number of entries it can accept by setting the value of the SQLN field in the descriptor area before issuing the PREPARE statement. If the number of entries is insufficient, CA IDMS returns the number of entries needed into the SQLD field but does not return any descriptions.

Examples

Specifying the Statement Explicitly

The following PREPARE statement dynamically compiles the specified SELECT statement. A subsequent DESCRIBE statement must provide a descriptor area for the description of the result table before the dynamically compiled statement can be executed.

EXEC SQL
   PREPARE DYN_TMP_SEL_1
      FROM 'SELECT * FROM TEMP_BUDGET'
END-EXEC

Using a Host Variable

The following PREPARE statement dynamically compiles the statement contained in the host variable SELECT-BUFF. Information about the output from the dynamically compiled statement is returned in the descriptor area named BUFF-1-SQLDA.

EXEC SQL
   PREPARE DYN_PROJ_SELECT
      FROM :SELECT-BUFF
      DESCRIBE USING DESCRIPTOR BUFF-1-SQLDA
END-EXEC

More Information

For more information about the dynamic compilation of SQL statements, see the CA IDMS SQL Programming Guide.
For more information about the SQL descriptor area, see SQL Descriptor Area.
For more information about destroying a dynamically-compiled statement, see DEALLOCATE PREPARE.
For more information about the structure of the SQL descriptor area, see SQL Descriptor Area.
For more information about dynamic parameters, see Dynamic Parameters.