User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To prepare a statement to be executed later or used for a cursor.

Syntax

     PREPARE statement-name
          FROM statement
          ...[ DESCRIBEdescribe-typeINTO [ [ SQL]DESCRIPTOR ]descriptor ]
          ...[ WITH EXECUTE ]

Parameters

     statement-name:    identifier, or host-variable

     statement :    string, or host-variable

     describe-type:
          { ALL | BIND VARIABLES| INPUT | OUTPUT | SELECT LIST }
          ...[
               LONG NAMES [ [ OWNER.]TABLE.]COLUMN ]
              | WITH VARIABLE RESULT
          ]

Usage

Embedded SQL.

Permissions

None.

Side effects

Any statement previously prepared with the same name is lost.

See also

• DECLARE CURSOR statement
• DESCRIBE statement
• OPEN statement
• EXECUTE statement
• DROP STATEMENT statement

Description

The PREPARE statement prepares a SQL statement from the statement and associates the prepared statement with statement-name. This statement name is referenced to execute the statement, or to open a cursor if the statement is a SELECT statement. Statement-name may be a host variable of type a_sql_statement_number defined in the sqlca.h header file that is automatically included. If an identifier is used for the statement-name, then only one statement per module may be prepared with this statement-name.

If a host variable is used for statement-name, it must have the type short int. There is a typedef for this type in sqlca.h called a_sql_statement_number. This type is recognized by the SQL preprocessor and can be used in a declare section. The host variable is filled in by the database during the PREPARE statement and need not be initialized by the programmer.

If the DESCRIBE INTO DESCRIPTOR clause is used, the prepared statement is described into the specified descriptor. The describe type may be any of the describe types allowed in the DESCRIBE statement.

If the WITH EXECUTE clause is used, the statement is executed if and only if it is not a CALL or SELECT statement, and it has no host variables. The statement is immediately dropped after a successful execution. If the prepare and the describe (if any) are successful but the statement cannot be executed, a warning SQLCODE 111, SQLSTATE 01W08 is set, and the statement is not dropped.

The DESRIBE INTO DESCRIPTOR and WITH EXECUTE clauses may improve performance, as they cut down on the required client/server communication.

Describing variable result sets

The WITH VARIABLE RESULT clause is used to describe procedures that may have more than one result set, with different numbers or types of columns.

If WITH VARIABLE RESULT is used, the database engine sets the SQLCOUNT value after the describe to one of the following values:

• 0 The result set may change: the procedure call should be described again following each OPEN statement.
• 1 The result set is fixed. No redescribing is required.

Statements that can be prepared

The following is a list of statements that can be PREPARED.

• ALTER
• CALL
• COMMENT ON
• CREATE
• DELETE
• DROP
• GRANT
• INSERT
• REVOKE
• SELECT
• SET OPTION
• UPDATE
• VALIDATE TABLE.

Compatibility issue For compatibility reasons, preparing COMMIT, PREPARE TO COMMIT, and ROLLBACK statements is still supported. However, we recommend that you do all transaction management operations with static Embedded SQL because certain application environments may require it. Also, other Embedded SQL systems do not support dynamic transaction management operations.Drop statement after use You should make sure that you DROP the statement after use. If you do not, then the memory associated with the statement is not reclaimed.

Example

     EXEC SQL PREPARE employee_statement FROM
     'SELECT * from employee';