ct_dynamic

Description

Initiate a dynamic SQL command.

Syntax

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;

Parameters

cmd

A pointer to the CS_COMMAND structure managing a client/server operation.

type

The type of dynamic SQL command to initiate.Table 3-24 lists the symbolic values for type.

id

A pointer to the statement identifier. This identifier is defined by the application and must conform to server standards.

idlen

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.

buffer

A pointer to data space.

buflen

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.

Returns

ct_dynamic returns the following values:

Usage

Table 3-24 summarizes ct_dynamic usage.

• ct_dynamic initiates dynamic SQL 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:

  1. 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.

  2. 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.

  3. Send the command to the server by calling ct_send.

  4. 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.

Preparing a statement

• 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.

Declaring a cursor on a prepared statement

• 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 neccessary 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.

Getting a description of prepared statement input

• 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:

  1. Call ct_dynamic with type as CS_DESCRIBE_INPUT to initiate a command to get the description.

  2. Call ct_send to send the command to the server.

  3. 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.

  4. Call ct_res_info to retrieve the number of input values. This assumes that CS_DESCRIBE_RESULT was returned, as does the following step.

  5. 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”.

Getting a description of prepared statement output

• An application typically retrieves a description of prepared statement result columns before executing the prepared statement for the first time.

• To get a description of prepared statement output columns:

  1. Call ct_dynamic with type as CS_DESCRIBE_OUTPUT to initiate a command to get the description.

  2. Call ct_send to send the command to the server.

  3. 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.

  4. Call ct_res_info to retrieve the number of output columns. This assumes that CS_DESCRIBE_RESULT was returned, as does the following step.

  5. 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”.

Executing a prepared statement

• To execute a prepared statement:

  1. Call ct_dynamic with type as CS_EXECUTE to initiate a command to execute the statement.

  2. 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.

  3. Call ct_send to send the command to the server.

  4. Call ct_results as necessary to process the results of the command.

Executing a literal statement

• 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:

  1. Call ct_dynamic with type as CS_EXEC_IMMEDIATE, id as NULL, and buffer as the statement to execute.

  2. Call ct_send to send the command to the server.

  3. Call ct_results as necessary to process the results of the command.

Deallocating a prepared statement

• 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.

See also

ct_dyndesc, ct_dynsqlda, ct_param, ct_setparam, ct_send, ct_cursor

---

Copyright © 2003. Sybase Inc. All rights reserved.

View this book as PDF