PREPARE statement (Preprocess SQL)

Function

The PREPARE statement preprocesses so that the SQL statement given in character-string can be executed, and assigns either an SQL statement identifier or an extended statement name to the SQL statement. In addition, by specifying either the OUTPUT or INPUT operand, the user can also fetch the retrieval information or input/output information obtained by the DESCRIBE [OUTPUT] statement or the DESCRIBE INPUT statement.

Privileges

None.

Format

PREPARE {SQL-statement-identifier | extended-statement-name}
       FROM {'character-string'|:embedded-variable}
            [WITH {SQLNAME|[ALL] TYPE}
            [, {SQLNAME|[ALL] TYPE}] OPTION]
            [OUTPUT[:]SQL-descriptor-area-name
                  [[:]column-name-descriptor-area-name]
               TYPE[:]type-name-descriptor-area-name]]
               [CHARACTER_SET [:]character-set-descriptor-area-name]]
             [INPUT[:]SQL-descriptor-area-name
                  [[:]column-name-descriptor-area-name]
               [CHARACTER_SET [:]character-set-descriptor-area-name]]

Operands

SQL-statement-identifier
Specifies the name assigned to the SQL statement in order to identify the SQL that is to be prepared.
For details about SQL statement identifiers, see 1.1.7 Specification of names.
HiRDB reserved words (other than SELECT and WITH) can be used as SQL statement identifiers. Even if an SQL statement identifier identical to a reserved word is used, it must not be enclosed in double quotation marks.
extended-statement-name
When a cursor is to be allocated by the ALLOCATE CURSOR statement, this operand specifies the extended statement name that was assigned to the SQL statement to identify the SQL statement to be preprocessed.
For extended statement names, see 2.26 Extended statement name.
character-string
Specifies directly as a character literal the character string representing the SQL to be preprocessed.
The SQL prefix or the SQL terminator cannot be specified in the character string to be preprocessed.
An apostrophe in the character literal specification of an SQL to be preprocessed must be specified as two apostrophes in succession.
The maximum length of an SQL to be preprocessed is 2,000,000 bytes. If an SQL of the embedded type is specified directly as a character literal, the maximum length is the maximum length of character literals in the host language.
embedded-variable
Specifies an embedded variable of the variable-length character type.
Character sets other than the default character set are prohibited.
SQLNAME
Specifies that column information on retrieval items and attribute names of a user-defined data type are to be received by specifying a Column Name Descriptor Area in a DESCRIBE or DESCRIBE TYPE statement. If a Column Name Descriptor Area name is specified in either the OUTPUT or INPUT clause, the SQLNAME operand can be omitted.
[ALL ] TYPE
Specifies that type name information on retrieval items is to be received by specifying a Type Name Descriptor Area in a DESCRIBE statement.
The TYPE option can be omitted if a Type Name Descriptor Area name is specified in the OUTPUT clause. The ALL TYPE option should be specified if definition information of a user-defined type is to be received by the DESCRIBE TYPE statement. ALL TYPE cannot be omitted even when a Type Name Descriptor Area name is specified in the OUTPUT clause.
SQL-descriptor-area-name
Specifies the name of the SQL descriptor area that receives SQL retrieval item information (if the preprocessed SQL statement is a SELECT statement) or output ? parameter information (if the preprocessed SQL statement is a CALL statement).
For SQL descriptor areas, see the HiRDB Version 9 UAP Development Guide.
column-name-descriptor-area-name
Specifies either the name of a retrieval item or the Column Name Descriptor Area that receives a routine parameter name.
For Column Name Descriptor Areas, see the HiRDB Version 9 UAP Development Guide.
type-name-descriptor-area-name
Specifies the Type Name Descriptor Area that receives the user-defined type name for a retrieval item.
For Type Name Descriptor Areas, see the HiRDB Version 9 UAP Development Guide.
character-set-descriptor-area-name
Specifies the character set descriptor area into which the character set name for the retrieval item information (if the preprocessed SQL is a SELECT statement), or output ? parameter information (if the preprocessed SQL is a CALL statement) is to be stored.
For details about character set descriptor areas, see the HiRDB Version 9 UAP Development Guide.
SQL-descriptor-area-name
Specifies the name of the SQL descriptor area that receives input ? parameter information.
For details about the SQL descriptor area, see the HiRDB Version 9 UAP Development Guide.
column-name-descriptor-area-name
Specifies the Column Name Descriptor Area that receives either the name of a retrieval item or the parameter name of a routine.
For details about Column Name Descriptor Areas, see the HiRDB Version 9 UAP Development Guide.
character-set-descriptor-area-name
Specifies the character set descriptor area into which the character set name for the input ? parameter information is to be stored.
For details about character set descriptor areas, see the HiRDB Version 9 UAP Development Guide.

Common rules

  1. The type of an embedded variable is the following structure:

    struct {
      long  xxxxxxx;     /* Effective length of SQL statement */
      char  yyyyyyy[n];  /* SQL statement storage area */
    } zzzzzzz;

    The characters xxxxxxx indicate the effective length of the character string stored in character array yyyyyyy.
    1 [Figure] (value of xxxxxxx) [Figure] 2000000
    The effective length of a character string does not include the character 0 (zero) that indicates the end of the character string.
    n is any value.
  2. SQLNAME cannot be specified more than once. Similarly, [ALL ] TYPE cannot be specified more than once.
  3. Before executing the PREPARE statement, the UAP should assign the number of SQLVAR flags (the SQLN area) in the SQL descriptor area.
  4. Because SQLDATA and SQLIND are cleared when the DESCRIBE statement is executed, or when a PREPARE statement is executed in which INPUT or OUTPUT is specified, if you use the DESCRIBE statement, or if you use a PREPARE statement in which INPUT or OUTPUT is specified, a value must be assigned to SQLDATA or SQLIND after those statements are executed.
  5. Specify a Column Name Descriptor Area only when the name of a retrieval item or the parameter name of a routine is to be received. Note that the parameter name of a routine can be received only when the ? parameter is specified by itself in an argument of the CALL statement. If a value expression including the ? parameter is specified, the length of the name of the Column Name Descriptor Area is 0.
  6. Specify a Type Name Descriptor Area name only when the user-defined type name of the retrieval result is to be received.
  7. Specify a character set descriptor area name only if the character set name is to be retrieved.

Notes

  1. The results of preprocessing produced in a transaction remain in effect only within that transaction. Therefore, any DESCRIBE, EXECUTE, OPEN, FETCH, or CLOSE statement that references the prepared SQL must be executed in the same transaction. However, if the preprocessed SQL is a holdable cursor, the following takes place:
    • When the SQL is preprocessed in the transaction and committed:
      The preprocessing result remains valid until a DISCONNECT statement is executed.
    • When the SQL is preprocessed in the transaction and rolled back:
      The preprocessing result is valid only within the transaction.
  2. An SQL to be preprocessed by the PREPARE statement must be preprocessed in advance.
    The following SQLs can be preprocessed by the PREPARE statement:
    • Data manipulation SQLs:
      ASSIGN LIST statement (executed with EXECUTE statement)
      CALL statement (executed with EXECUTE statement)
      DELETE statement (executed with EXECUTE statement)
      Preparable dynamic DELETE statement: locating (executed by the EXECUTE statement)
      DROP LIST statement (executed with EXECUTE statement)
      INSERT statement (executed with EXECUTE statement)
      PURGE TABLE statement (executed with EXECUTE statement)
      SELECT statement (executed with OPEN, FETCH, and CLOSE statements)
      Single-row SELECT statement (executed by the EXECUTE statement)
      Dynamic SELECT statement (executed by the OPEN, FETCH, or CLOSE statement)
      UPDATE statement (executed with EXECUTE statement)
      Preparable dynamic UPDATE statement: locating (executed by the EXECUTE statement)
      Assignment statement (executed by the EXECUTE statement)
    • Control SQLs:
      LOCK TABLE statement (executed by EXECUTE statement)
      SET SESSION AUTHORIZATION statement (executed by the EXECUTE statement)
    • Definition SQLs:
      ALTER INDEX, ALTER PROCEDURE, ALTER ROUTINE, ALTER TABLE, ALTER TRIGGER, COMMENT, CREATE AUDIT, CREATE CONNECTION SECURITY, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE SCHEMA, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, DROP AUDIT, DROP CONNECTION SECURITY, DROP DATA TYPE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP SCHEMA, DROP SEQUENCE, DROP TABLE, DROP TRIGGER, DROP VIEW, GRANT, and REVOKE statements (all definition SQLs executed by the EXECUTE statement)
  3. An SQL statement identifier, similar to an embedded variable name, is effective only within a compile-unit module. Multiple SQLs relative to the same SQL statement identifier cannot be used in multiple modules.
  4. When the dynamic SELECT statement preprocessed by the PREPARE statement is being executed (after the OPEN statement has been executed and before the CLOSE statement is executed), any table specified in the FROM clause of the dynamic SELECT statement should not be updated by another SQL statement.
  5. If the SQL statement identifier or extended statement name that is specified already identifies another SQL statement, the DEALLOCATE PREPARE statement is implicitly executed, and the previously identified SQL statement is nullified. After that, the specified SQL statement identifier or the extended statement name identify the SQL statement that was preprocessed by the PREPARE statement. However, if an error occurs in the implicitly executed DEALLOCATE PREPARE statement, the previously identified SQL statement remains unchanged.
  6. If OUTPUT is specified in the PREPARE statement, the PREPARE statement is treated in the same way as the execution of the DESCRIBE [OUTPUT] statement. Similarly, if INPUT is specified in the PREPARE statement, the PREPARE statement is treated in the same way as the execution of the DESCRIBE [INPUT] statement. For details about the OUTPUT and INPUT options, see DESCRIBE.

Examples

  1. Preprocess the SQL 'SELECT * FROM STOCK' provided in a character string for execution; assume that the SQL identifier assigned to the preprocessed SQL is named 'PRESQL':

    PREPARE PRESQL FROM
     'SELECT * FROM STOCK'

  2. Prepare the SQL character string specified in an embedded variable named XSQL; assume that the SQL identifier assigned to the preprocessed SQL is named 'PRESQL':

    PREPARE PRESQL FROM :XSQL