PREPARE

The PREPARE dynamic compilation statement dynamically compiles an SQL statement for later execution in the application program.
idmscu19
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.
This article describes the following information:
2
2
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. 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
    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.
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