Prepares a statement to be executed later, or defines a cursor.
SyntaxPREPARE statement-name FROM statement [ FOR { UPDATE [ cursor-concurrency ] | READ ONLY } ] [ DESCRIBE describe-type INTO [ [ SQL ] DESCRIPTOR ] descriptor ] [ WITH EXECUTE ]
statement-name : identifier | hostvar
statement : string | hostvar
describe-type : [ ALL | BIND VARIABLES | INPUT | OUTPUT | SELECT LIST ] [ LONG NAMES [ [ [ OWNER. ]TABLE. ]COLUMN ] | WITH VARIABLE RESULT ]
cursor-concurrency : BY { VALUES | TIMESTAMP | LOCK }
Parametersstatement-name The statement name can be an identifier or host variable. However, you should not use an identifier when using multiple SQLCAs. If you do, two prepared statements may have the same statement number, which could cause the wrong statement to be executed or opened. Also, using an identifier for a statement name is not recommended for multithreaded applications where the statement name may be referenced by multiple threads concurrently.
DESCRIBE clause If DESCRIBE INTO DESCRIPTOR 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.
FOR UPDATE | FOR READ ONLY Defines the cursor updatability if the statement is used by a cursor. A FOR READ ONLY cursor cannot be used in an UPDATE (positioned) or a DELETE (positioned) operation. FOR READ ONLY is the default.
In response to any request for a cursor that specifies FOR UPDATE, SQL Anywhere provides either a value-sensitive cursor or a sensitive cursor. Insensitive and asensitive cursors are not updatable.
BY VALUES | BY TIMESTAMP The database server utilizes a keyset-driven cursor to enable the application to be informed when rows have been modified or deleted as the result set is scrolled.
BY LOCK clause The database server acquires intent row locks on fetched rows of the result set. These are long-term locks that are held until the transaction is committed or rolled back.
WITH EXECUTE clause 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 DESCRIBE INTO DESCRIPTOR and WITH EXECUTE clauses may improve performance because they cut down on the required client/server communication.
WITH VARIABLE RESULT clause 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 server 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 re-describing is required.
For compatibility reasons, preparing COMMIT, PREPARE TO COMMIT, and ROLLBACK statements is still supported. However, it is recommended 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.
RemarksThe 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 or CALL statement. The 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, 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 defined by the database during the PREPARE statement, and you do not need to initialize it.
PermissionsNone.
Side effectsAny statement previously prepared with the same name is lost.
The statement is dropped after use only if you use WITH EXECUTE and the execution is successful. You should ensure that you DROP the statement after use in other circumstances. If you do not, the memory associated with the statement is not reclaimed.
See also Standards and compatibilitySQL/2008 PREPARE is part of optional SQL/2008 language feature B031, "Basic dynamic SQL". The optional FOR UPDATE, FOR READ ONLY, DESCRIBE, and WITH EXECUTE clauses are vendor extensions.
ExampleThe following statement prepares a simple query:
EXEC SQL PREPARE employee_statement FROM 'SELECT Surname FROM Employees'; |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |