Declares a name for a dynamic SQL statement buffer.
exec sql [at connection_name] prepare statement_name from {:host_variable | "string"};
An identifier used to reference the statement. statement_name must uniquely identify the statement buffer and must conform to the SQL identifier rules for naming variables. The statement_name can also be a host_variable string containing a valid SQL identifier. statement_name can be up to 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;
CS_CHAR dyn_buffer[128];
CS_CHAR state[2];
exec sql end declare section;
-- The select into table_name statement returns no
-- results to the program, so it does not
-- need a cursor.
dyn_buffer = "select * into #work from authors"
+ "where state = ?”;
printf("State? ");
gets(state);
exec sql prepare make_work from :dyn_buffer;
exec sql execute make_work using :state;
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
