Declares a name for a dynamic SQL statement buffer.
exec sql [at connection_name] prepare statement_name from
{:host_variable | "string"} end-exec
An identifier used to reference the statement. The statement_name must uniquely identify the statement buffer and must conform to the SQL identifier rules for naming variables. It can also be a host_variable string containing a valid SQL identifier. statement_name must not be longer than 255 characters.
A character-string host variable that contains an executable SQL statement. Place dynamic parameter markers (“?”) anywhere in the select statement where a host variable value will be substituted.
A literal string that can be used in place of host_variable.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 DEMO-BUFFER PIC X(120).
01 STATE PIC X(3).
EXEC SQL END DECLARE SECTION END-EXEC.
...
* The 'select into table' statement returns no results
* to the program, so it does not need a cursor.
MOVE "select * into #work from authors where state = ?" TO
- DEMO-BUFFER.
DISPLAY "STATE ? ".
ACCEPT STATE.
EXEC SQL PREPARE dynstmt FROM :DEMO-BUFFER END-EXEC.
EXEC SQL EXECUTE dynstmt USING :STATE END-EXEC.
EXEC SQL DEALLOCATE PREPARE dynstmt END-EXEC.
In the current implementation, Sybase creates a temporary stored procedure for a dynamic SQL statement stored in a character string literal or host variable.
prepare sends the contents of host_variable to the Adaptive Server to convert into a temporary stored procedure. This temporary stored procedure remains in tempdb on Adaptive Server until the statement is deallocated or the connection is disconnected.
The scope of statement_name is global to your program but local to the connection connection_name. The statement persists until the program either deallocates it or closes the connection.
prepare is valid with Dynamic SQL methods 2, 3, and 4.
With method 2, (prepare and execute), an execute statement substitutes values from host variables, if any, into the prepared statement and sends the completed statement to Adaptive Server. If there are no host variables to substitute and no results, you can use execute immediate, instead.
With method 3, prepare and fetch, a declare cursor statement associates the saved select statement with a cursor. An open statement substitutes values from host variables, if any, into the select statement and sends the result to Adaptive Server for execution.
With methods 2, 3, and 4, prepare and fetch with parameter descriptors, the dynamic parameter descriptors, represented by question marks (“?”), indicate where host variables will be substituted.
A prepared statement must be executed on the same connection on which it was prepared. If the prepared statement is used to declare a cursor, all operations on that cursor use the same connection as the prepared statement.
The statement in host_variable can contain dynamic parameter markers that indicate where to substitute values of host variables into the statement.
declare cursor, execute, execute immediate, deallocate prepare
