Initiate a dynamic SQL command.
CS_RETCODE ct_dynamic(cmd, type, id, idlen, buffer, buflen) CS_COMMAND *cmd; CS_INT type; CS_CHAR *id; CS_INT idlen; CS_CHAR *buffer; CS_INT buflen;
A pointer to the CS_COMMAND structure managing a client/server operation.
The type of dynamic SQL command to initiate.Table 3-24 lists the symbolic values for type.
A pointer to the statement identifier. This identifier is defined by the application and must conform to server standards.
The length, in bytes, of *id. If *id is null-terminated, pass idlen as CS_NULLTERM. If id is NULL, pass idlen as CS_UNUSED.
A pointer to data space.
The length, in bytes, of *buffer. If *buffer is null-terminated, pass buflen as CS_NULLTERM. If buffer is NULL, pass buflen as CS_UNUSED.
ct_dynamic returns the following values:
Return value: |
Meaning |
|---|---|
CS_SUCCEED |
The routine completed successfully. |
CS_FAIL |
The routine failed. |
CS_BUSY |
An asynchronous operation is already pending for this connection. See “Asynchronous programming”. |
Table 3-24 summarizes ct_dynamic usage.
Value of type |
Result |
*id is |
*buffer is |
|---|---|---|---|
CS_CURSOR_DECLARE |
Declares a cursor on a previously prepared SQL statement. |
The prepared statement identifier. |
The cursor name. |
CS_DEALLOC |
Deallocates a prepared SQL statement. |
The prepared statement identifier. |
NULL |
CS_DESCRIBE_INPUT |
Retrieves, from the server, a description of the input parameters required to execute a prepared statement. ct_results returns a CS_DESCRIBE_RESULT result_type value when the server has sent the description. An application can access this information by calling ct_res_info and ct_describe, ct_dynsqlda, or ct_dyndesc. |
The prepared statement identifier. |
NULL |
CS_DESCRIBE_OUTPUT |
Retrieves, from the server, a description of the row format of the result set that would be returned if the prepared statement were executed. ct_results returns a CS_DESCRIBE_RESULT result_type value when the server has sent the description. An application can access this information by calling ct_res_info and ct_describe, ct_dynsqlda, or ct_dyndesc. |
The prepared statement identifier. |
NULL |
CS_EXECUTE |
Executes a prepared SQL statement that requires zero or more parameters. |
The prepared statement identifier. |
NULL |
CS_EXEC_IMMEDIATE |
Executes a literal SQL statement. |
NULL |
The SQL statement to execute. |
CS_PREPARE |
Prepares a SQL statement. |
The prepared statement identifier. |
The SQL statement to prepare. |
ct_dynamic initiates dynamic Adaptive Server commands.
For an overview of dynamic SQL commands, see Chapter 8, “Using Dynamic SQL Commands,” in the Open Client Client-Library/C Programmer's Guide.
Initiating a command is the first step in sending it to a server. For a client application to execute a server command, Client-Library must convert the command to a symbolic command stream that can be sent to the server. The command stream contains information about the type of the command and the data needed for execution. For example, a dynamic SQL prepare command requires a statement identifier and the text of the statement to prepare. The steps for executing a dynamic SQL command are as follows:
Initiate the command by calling ct_dynamic. This routine sets up internal structures that are used in building a command stream to send to the server.
Pass parameters for the command, if required. Most applications pass parameters by calling ct_param or ct_setparam once for each parameter that the command requires, but it is also possible to pass parameters for a command by using ct_dyndesc or ct_dynsqlda.
Send the command to the server by calling ct_send.
Process the results of the command by calling ct_results.
A dynamic SQL command that executes a prepared statement returns fetchable results. The other dynamic SQL command types do not return fetchable results, but do return command status results. See “Results” for a discussion of processing results.
The following rules apply to the use of ct_dynamic:
When a command structure is initiated, an application must either send the initiated command or clear it before a new command can be initiated with ct_command, ct_cursor, ct_dynamic, or ct_sendpassthru.
After sending a command, an application must completely process or cancel all results returned by the command’s execution before initiating a new command on the same command structure.
An application cannot call ct_dynamic to initiate a command on a command structure that is managing a cursor. The application must deallocate the cursor first or use a different command structure.
Client-Library allows an application to resend a command by calling ct_send immediately after the application has processed the results from the previous execution. To resend a command, the application updates the contents of any parameter source variables that were specified with ct_setparam, then calls ct_send. The following dynamic SQL commands can be resent successfully:
Execute-immediate commands
Execute commands on a prepared statement
Describe-output or describe-input commands
If the application resends other dynamic SQL commands, they result in server processing errors. Client-Library allows an application to resend a command as long as a new command has not been initiated with ct_command, ct_cursor, ct_dynamic, or ct_sendpassthru.
To initiate a command to prepare a statement, an application calls ct_dynamic with type as CS_PREPARE, id as a unique statement identifier, and buffer as the statement text.
A prepared SQL statement is a SQL statement that is compiled and stored by a server. Each prepared statement is associated with a unique identifier.
An application can prepare an unlimited number of statements, but identifiers for prepared statements must be unique within a connection.
Although the command structure used to prepare a statement can be different from the one used to execute it, both of the command structures must belong to the same connection.
A prepared statement can be a Transact-SQL statement containing placeholders for values. Placeholders act like variables in the prepared statement. A placeholder is indicated by a question mark (?) in the statement. A placeholder can occur in the following locations:
In place of one or more values in an insert statement
In the set clause of an update statement
In the where clause of a select or update statement
When building a command to execute the prepared statement, the application substitutes a value for each dynamic parameter marker by calling ct_param, ct_setparam, ct_dyndesc, or ct_dynsqlda.
Once a statement is prepared, an application can send a dynamic SQL describe-input command to the server to get a description of the input parameters required to execute the statement.
To initiate a command to prepare a statement that executes a stored procedure, specify “exec sp_name” as the SQL text, where “sp_name” is the name of the stored procedure to be executed:
ct_dynamic(cmd, CS_PREPARE, “myid”, CS_NULLTERM,
“exec sp_2”, CS_NULLTERM);
Once a statement is successfully prepared, the application can execute it repeatedly until it is deallocated.
To initiate a command to declare a cursor on a prepared statement, an application calls ct_dynamic with type as CS_CURSOR_DECLARE.
After declaring a cursor on a prepared statement, an application can call ct_cursor(CS_CURSOR_OPTION) to set an option (“readonly” or “‘for update”) for the cursor-declaration command. This step is necessary only if the select statement does not include a for read only or for update of clause to specify which, if any, columns are to be updatable. The sequence of calls is:
ct_dynamic(CS_CURSOR_DECLARE)
ct_cursor(CS_CURSOR_OPTION)
ct_send
ct_results, as many times as necessary
A ct_dynamic cursor-declare command cannot be batched with subsequent ct_cursor cursor-rows or cursor-open commands.
After a cursor is declared on a prepared statement, use ct_cursor to initiate additional commands on the cursor.
An application must declare a cursor on a prepared statement prior to executing the prepared statement.
An application typically retrieves a description of prepared statement input parameters before executing the prepared statement for the first time.
To get a description of prepared statement input:
Call ct_dynamic with type as CS_DESCRIBE_INPUT to initiate a command to get the description.
Call ct_send to send the command to the server.
Call ct_results as necessary to process the results of the command. A CS_DESCRIBE_INPUT command generates a result set of type CS_DESCRIBE_RESULT. This result set contains no fetchable data but does contain descriptive information for each of the input values.
Call ct_res_info to retrieve the number of input values. This assumes that CS_DESCRIBE_RESULT was returned, as does the following step.
For each input value, call ct_describe.
Alternately, an application can use ct_dyndesc or ct_dynsqlda to retrieve the description. ct_dyndesc requires several calls to obtain the number of inputs and the format of each. ct_dynsqlda can retrieve a description with one call but requires an application-managed SQLDA structure. These alternatives are described in the following sections:
For a description of the ct_dynsqlda method, see “Sybase SQLDA: Retrieving input formats”.
For a description of the ct_dyndesc method, see “Getting descriptions of command inputs or outputs with ct_dyndesc”.
An application typically retrieves a description of prepared statement result columns before executing the prepared statement for the first time.
A single dynamic SQL batch may contain multiple SQL
statements. The description of the prepared statement output, however,
only describes the first resultset. You will receive full descriptions
of each resultset only when the dynamic SQL statement is executed.
To get a description of prepared statement output columns:
Call ct_dynamic with type as CS_DESCRIBE_OUTPUT to initiate a command to get the description.
Call ct_send to send the command to the server.
Call ct_results as necessary to process the results of the command. A ct_dynamic(CS_DESCRIBE_OUTPUT) command generates a result set of type CS_DESCRIBE_RESULT. This result set contains no fetchable data but does contain descriptive information for each output column.
Call ct_res_info to retrieve the number of output columns. This assumes that CS_DESCRIBE_RESULT was returned, as does the following step.
For each output column, call ct_describe.
Alternately, an application can use ct_dyndesc or ct_dynsqlda to retrieve the description. ct_dyndesc requires several calls to obtain the number of columns and the format of each. ct_dynsqlda can retrieve a description with one call but requires an application-managed SQLDA structure. These alternatives are described in the following sections:
For a description of the ct_dynsqlda method, see “Sybase SQLDA: Retrieving output formats”.
For a description of the ct_dyndesc method, see “Getting descriptions of command inputs or outputs with ct_dyndesc”.
To execute a prepared statement:
Call ct_dynamic with type as CS_EXECUTE to initiate a command to execute the statement.
Define the input values to the SQL statement. You can do this by:
Calling ct_param once for each parameter. ct_param and ct_setparam offer the best performance. ct_param does not allow the application to change parameter values before resending the command.
Calling ct_setparam once for each parameter. ct_setparam takes pointers to parameter source values. This method is the only one that allows parameter values to be changed before resending the command.
Calling ct_dyndesc several times to allocate a dynamic descriptor area, populate it with data values, and apply it to the command. See “Passing parameter values with ct_dyndesc” for more information. ct_dyndesc(CS_USE_DESC) calls ct_param internally.
By calling ct_dynsqlda to apply the contents of a user-allocated SQLDA structure to the command. See “Sybase SQLDA: Passing command input parameters” for more information. ct_dynsqlda(CS_SQLDA_PARAM) calls ct_param internally.
Call ct_send to send the command to the server.
Call ct_results as necessary to process the results of the command.
A dynamic SQL statement can be executed immediately if it meets the following criteria:
It does not return data (it is not a select statement).
It does not contain placeholders for parameters, which are indicated by a question mark (?) in the text of the statement.
Dynamic parameter markers act as placeholders that allow users to specify actual data to be substituted into a SQL statement at runtime.
To execute a literal statement:
Call ct_dynamic with type as CS_EXEC_IMMEDIATE, id as NULL, and buffer as the statement to execute.
Call ct_send to send the command to the server.
Call ct_results as necessary to process the results of the command.
To initiate a command to deallocate a prepared statement, an application calls ct_dynamic with type as CS_DEALLOC and id as the statement identifier.
ct_dyndesc, ct_dynsqlda, ct_param, ct_setparam, ct_send, ct_cursor
