ct_setparam

Description

Specify source variables from which ct_send reads input parameter values for a server command.

Syntax

CS_RETCODE ct_setparam(cmd, datafmt, data, 
             datalenp, indp)
 
CS_COMMAND *cmd;
CS_DATAFMT    *datafmt;
CS_VOID            *data;
CS_INT               *datalenp;
CS_SMALLINT    *indp;

Parameters

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

datafmt: A pointer to a CS_DATAFMT structure that describes the parameter. ct_setparam copies the contents of *datafmt before returning. Client-Library does not reference datafmt afterwards.

data: The address of a value buffer. Client-Library reads the parameter’s current value from *data during subsequent calls to ct_send.

There are three ways to indicate a parameter with a null value:

Set * indp as -1 before calling ct_send. In this case, ct_send ignores *data and .

Set *datalenp to 0 before calling ct_send.

Call ct_setparam with data, datalenp, and indp as NULL.

datalenp: The address of an integer variable that specifies the length, in bytes, of parameter values in *data, or NULL if values for this parameter do not vary in length.

If datalenp is not NULL, subsequent ct_send calls read the current value’s length from *datalenp. A length of 0 indicates a null value.

If datalenp is NULL and data is not, datafmt−>maxlength specifies the length of all non-null values for this parameter. When datalenp is NULL, an indicator variable must be used to indicate null parameter values for subsequent calls to ct_send.

indp: The address of a CS_SMALLINT variable whose value indicates whether the parameter’s current value is NULL. To indicate a parameter with a null value, set *indp as -1. If *indp is -1, ct_send ignores *data and *datalenp.

Returns

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

Examples

Example 1

The example below shows ct_setparam being used in code that declares, opens, and reopens a cursor that takes parameters.

Example: ct_setparam for reopening a cursor

/*

 ** Data structures to describe a parameter and a cursor.

*/

 typedef struct _langparam

         CS_CHAR         *name;

         CS_INT          type;

         CS_INT          len;

         CS_INT          maxlen;

         CS_VOID         *data;

         CS_SMALLINT     indicator;

 } LANGPARAM;

typedef struct _cur_control

         CS_CHAR         *name;

         CS_CHAR         *query;

         LANGPARAM       *params;

         CS_INT          numparams;

 } CUR_CONTROL;

/*

 ** Static data for a parameterized cursor body.

*/

 CS_STATIC CS_MONEY  PriceVal;

 CS_STATIC CS_INT    SalesVal;

 CS_STATIC LANGPARAM Params [] =

         { "@price_val", CS_MONEY_TYPE,

            CS_SIZEOF(CS_MONEY), CS_SIZEOF(CS_MONEY),

            (CS_VOID *)&PriceVal, 0

},

         { "@sales_val", CS_INT_TYPE,

            CS_SIZEOF(CS_INT), CS_SIZEOF(CS_INT),

            (CS_VOID *)&SalesVal, 0

},

};

 #define NUMPARAMS  (CS_SIZEOF(Params) / CS_SIZEOF(LANGPARAM))

 #define QUERY \

 "select title_id, title, price, total_sales from titles \

  where price > @price_val and total_sales > @sales_val \

  for read only"

 CS_STATIC CUR_CONTROL Cursor_Control =

 { "curly", QUERY, Params, NUMPARAMS };

/*

 ** OpenCursor() -- Declare and open a new cursor or reopen

 **         an existing cursor (which must have been originally

 **         declared and opened using this function).

**

 **         If the open is successful, this function processes the cursor

 **         results up to the CS_CURSOR_RESULT result type value. In

 **         other words, the command handle is ready for

 **         ct_bind/ct_fetch/etc.

**

 ** Parameters

 **   cmd -- CS_COMMAND handle for the new cursor.

 **   cur_control -- address of a CUR_CONTROL structure that contains

 **     the cursor body statement plus parameter formats and value

 **     areas.

**

 **     If a first-time open is successful, OpenCursor() can be used to

 **     reopen the cursor with new parameter values.

**

 **     For later opens, the cursor must be closed.

**

 ** Returns

 **   CS_SUCCEED or CS_FAIL

*/

CS_RETCODE

 OpenCursor(cmd, cur_control)

 CS_COMMAND         *cmd;

 CUR_CONTROL        *cur_control;

   CS_RETCODE          ret;

   CS_INT              i;

   CS_DATAFMT          dfmt;

   LANGPARAM          *params;

   CS_BOOL             have_restorable_cursor;

/*

   ** Check whether a cursor-open command can be restored with this

   ** command handle.

*/

   ret = ct_cmd_props(cmd, CS_GET, CS_HAVE_CUROPEN,

                      &have_restorable_cursor, CS_UNUSED,

                      (CS_INT *)NULL);

   if (ret != CS_SUCCEED)

     ex_error(“OpenCursor: ct_cmd_props() failed!”);

     return CS_FAIL;

/*

   ** If CS_HAVE_CUROPEN is CS_FALSE, then this is a first-time open. So,

   ** we initiate a new declare command and bind to the parameter source

   ** variables in the CUR_CONTROL structure.

*/

   if (have_restorable_cursor != CS_TRUE)

/*

     ** Initiate the declare command.

*/

     ret = ct_cursor(cmd, CS_CURSOR_DECLARE,

                     cur_control->name, CS_NULLTERM,

                     cur_control->query, CS_NULLTERM,

                     CS_UNUSED);

     if (ret != CS_SUCCEED)

       ex_error(“OpenCursor: Initiate-declare failed”);

       return CS_FAIL;

/*

     ** Specify formats for the host language parameters in the cursor

     ** declare command.

*/

     params = cur_control->params;

     (CS_VOID *)memset(&dfmt, 0, sizeof(dfmt));

     dfmt.status = CS_INPUTVALUE;

    for (i = 0; i < cur_control->numparams; i++)

       dfmt.datatype = params[i].type;

       dfmt.maxlength = params[i].maxlen;

       strcpy(dfmt.name, params[i].name);

       dfmt.namelen = strlen(dfmt.name);

      ret = ct_setparam(cmd, &dfmt,

                         (CS_VOID *)NULL, (CS_INT *)NULL,

                         (CS_SMALLINT *)NULL);

       if (ret != CS_SUCCEED)

         ex_error(“OpenCursor: ct_setparam() failed”);

         return CS_FAIL;

/*

   ** Initiate or restore the cursor-open command.

**

   ** The first time we open the cursor, this call initiates an

   ** open-cursor command which gets batched with the declare command.

   ** Since there is no cursor to restore, ct_cursor ignores the

   ** CS_RESTORE_OPEN option.

**

   ** The second (and later) times we open the cursor, this call

   ** restores the cursor-open command so that we can send it again.

   ** The declare-cursor command (originally batched with the open

   ** command) is not restored.

*/

   ret = ct_cursor(cmd, CS_CURSOR_OPEN,

                   (CS_CHAR *)NULL, CS_UNUSED,

                   (CS_CHAR *)NULL, CS_UNUSED,

                   CS_RESTORE_OPEN);

   if (ret != CS_SUCCEED)

     ex_error(“OpenCursor: Initiate-open failed.”);

     return CS_FAIL;

/*

   ** For the first-time open, supply the address of variables that have

   ** values for the cursor parameters. These variables will be read by

   ** ct_send.

**

   ** The second (and later) times we open the cursor, we don’t have to

   ** call ct_setparam here -- the parameter bindings were restored by

   ** ct_cursor(OPEN, RESTORE_OPEN).

**

   ** In either case, we assume that our caller has already set the

   ** desired values, lengths, and indicators.

*/

   for (i = 0;

        ((have_restorable_cursor != CS_TRUE) &&

         (i < cur_control->numparams));

        i++)

     dfmt.datatype = params[i].type;

     dfmt.maxlength = params[i].maxlen;

     strcpy(dfmt.name, params[i].name);

     dfmt.namelen = strlen(dfmt.name);

    ret = ct_setparam(cmd, &dfmt,

                       params[i].data, &params[i].len,

                       &params[i].indicator);

     if (ret != CS_SUCCEED)

       ex_error(“OpenCursor: ct_setparam() failed”);

       return CS_FAIL;

/*

   ** Send the command batch.

*/

   ret = ct_send(cmd);

   if (ret != CS_SUCCEED)

     ex_error(“OpenCursor: ct_send() failed.”);

     return CS_FAIL;

/*

   ** GetToCursorRows() calls ct_results() until cursor rows are

   ** fetchable on the command structure. GetToCursorRows() fails if

   ** the declare or open command fails on the server.

*/

   ret = GetToCursorRows(cmd);

   if (ret != CS_SUCCEED)

     ex_error(“OpenCursor: Cursor could not be opened.”);

     return CS_FAIL;

  return CS_SUCCEED;

 } /* OpenCursor() */

/*

 ** GetToCursorRows() -- Flush results from a cursor-open command

 **   batch until ct_results returns a CS_CURSOR_RESULT result type.

**

 ** Parameters

 **   cmd -- The command handle to read results from.

**

 ** Returns

 **   CS_SUCCEED -- Cursor rows are ready to be fetched.

 **   CS_FAIL -- Failure. Could be due to any of the following:

 **     - No cursor results in the results stream.

 **     - Other kinds of fetchable results in the results stream.

 **     - ct_results failure.

*/

CS_STATIC CS_RETCODE

 GetToCursorRows(cmd)

 CS_COMMAND         *cmd;

   CS_RETCODE          results_ret;

   CS_RETCODE          ret;

   CS_INT              result_type = CS_END_RESULTS;

   CS_BOOL             failing = CS_FALSE;

   CS_INT              intval;

   CS_CHAR             scratch[512];

  while (((results_ret = ct_results(cmd, &result_type)) == CS_SUCCEED)

          && (result_type != CS_CURSOR_RESULT))

     switch ((int)result_type)

     case CS_CMD_SUCCEED:

     case CS_CMD_DONE:

       break;

    case CS_CMD_FAIL:

/*

       ** Declare or open failed on the server.

*/

       ret = ct_res_info(cmd, CS_CMD_NUMBER, (CS_VOID *)&intval,

                         CS_UNUSED, (CS_INT *)NULL);

       if (ret == CS_SUCCEED)

         sprintf(scratch, “Command %ld failed”, (long)intval);

         ex_error(scratch);

       failing = CS_TRUE;

       break;

    default:

/*

       ** Nothing else is expected. Just return fail and let the caller

       ** decide how to clean up.

*/

       ex_error(

          “Unexpected result types received for cursor declare/open.”);

       return CS_FAIL;

/*

   ** We are leaving the cursor results pending on the connection.

*/

   if (results_ret == CS_CANCELED)

/*

     ** Could happen if the connection has a timeout and the error

     ** handler did ct_cancel(CS_CANCEL_ATTN);

*/

     ex_error(“Cursor declare/open was canceled.”);

     failing = CS_TRUE;

   else if (results_ret != CS_SUCCEED)

     ex_error(“Cursor declare/open: ct_results failed.”);

     failing = CS_TRUE;

  return (failing == CS_TRUE) ? CS_FAIL : CS_SUCCEED;

 } /* GetToCursorRows() */

Usage

ct_setparam specifies program source variables for a server command’s input parameter values.

Initiating a command is the first step in executing it. Some commands require the application to define input parameters with ct_param or ct_setparam before calling ct_send to send the command to the server.

ct_setparam and ct_param perform the same function, except:

ct_param copies the contents of program variables.

ct_setparam copies the address of program variables, and subsequent calls to ct_send read the contents of the variables. ct_setparam allows the application to change parameter values when resending a command. For a description of this feature, see “Resending commands”.

Calls to ct_param and ct_setparam can be mixed.
ct_setparam may be required:
- To supply input parameter values for a cursor-open or cursor-update command that was initiated with ct_cursor, a language, message, or RPC command that was initiated with ct_command, or a dynamic-SQL execute command that was initiated with ct_dynamic. This use of ct_setparam is described under “Using ct_setparam to define input parameter sources”.
- To define the formats of host language variable formats for a cursor-declare command that was initiated with ct_cursor or ct_dynamic. This use of ct_setparam is described under “Using ct_setparam to define cursor parameter formats”. Cursor-declare commands cannot be resent, so there is no advantage to using ct_setparam rather than ct_param to define parameter formats.
- To define update columns for a cursor-declare command (initiated with ct_cursor or ct_dynamic). This use of ct_setparam is described under “Using ct_setparam to identify updatable cursor columns”. Note that cursor-declare commands can not be resent, so there is no advantage to using ct_setparam rather than ct_param to define update columns.
Client-Library does not perform any conversion on parameters before passing them to the server. The application must supply parameters in the datatype required by the server. If necessary, the application can call cs_convert to convert parameter values into the required datatype.

Using ct_setparam to define input parameter sources

An application may need to supply input parameter values for:

Client-Library cursor open commands
Client-Library cursor update commands
Dynamic SQL execute commands
Language commands
Message commands
Package commands
RPC commands

ct_setparam creates a binding between the variables passed as *data, *datalenp, and *indp and one command parameter. Subsequent calls to ct_send read the contents of these variables to determine whether the parameter value is null, and (if not null) the current value and length. A value is considered null if

*datalen is 0,
*indp is -1, or
data, datalenp, and indp were all passed as NULL in the call to ct_setparam.

The command parameter associated with each ct_setparam call is specified either by name or by position.

To specify by name, set datafmt−>name to the name of the parameter and datafmt−>namelen to the length of the name.
To specify by position, call ct_setparam in the order that the parameters occur in the SQL statement or stored procedure definition, with datafmt−>namelen as 0 for each call.

All parameters must be specified by name, or all parameters must be specified by position.

Client-Library cursor open commands require input parameter values when:

The cursor is declared with a Transact-SQL select statement containing host-language variables.
The cursor is declared with a Transact-SQL execute statement, and the called stored procedure requires parameters. In this case, *datafmt−>status should be CS_INPUTVALUE to indicate an input parameter.
The cursor is declared on a prepared dynamic SQL statement that contains placeholders (indicated by the ? character).

Client-Library cursor-update commands require input parameter values when the SQL text representing the update command contains host variables.

Dynamic SQL execute commands require input parameter values when the prepared statement being executed contains dynamic parameter markers (indicated by the ? character).

Language commands require input parameter values when the text of the language command contains host variables.

Message commands require input parameters values when the message takes parameters.

RPC and package commands require input parameter values when the stored procedure or package being executed takes parameters.

Message, RPC, and package commands can take return parameters, indicated by passing datafmt−>status as CS_RETURN.

A command that takes return parameters may generate a parameter result set that contains the return parameter values. See ct_results for a description of how an application retrieves values from a parameter result set.

Table 3-60 lists the fields in *datafmt that are used when passing input parameter values. A parameter’s format cannot be changed after ct_setparam returns:

**Table 3-60: CS_DATAFMT fields for passing input parameter values**
Field	Description
name	The name of the parameter. name is ignored for dynamic SQL execute commands.
namelen	The length, in bytes, of name, or 0 to indicate an unnamed parameter. namelen is ignored for dynamic SQL execute commands.
datatype	The datatype of the input parameter value. All standard Client-Library types are valid except for CS_TEXT_TYPE, CS_IMAGE_TYPE, and Client-Library user-defined types. If datatype is CS_VARCHAR_TYPE or CS_VARBINARY_TYPE then data must point to a CS_VARCHAR or CS_VARBINARY structure.
maxlength	When passing return parameters for RPC commands, maxlength represents the maximum length, in bytes, of data to be returned for this parameter. If the ct_setparam datalenp parameter is passed as NULL, maxlength also specifies the length of all input values for the parameter. In this case, the maximum length for the corresponding return parameter data must agree with the length of input values.
status	Set to CS_RETURN when passing return parameters for RPC commands; otherwise set to CS_INPUTVALUE.
All other fields are ignored.

Using ct_setparam to define cursor parameter formats

An application needs to define host variable formats for cursor declare commands when the cursor is declared with a select statement that contains host-language variables.

Host variable formats are defined with ct_param or ct_setparam after calling ct_cursor(CS_CURSOR_DECLARE) to initiate the cursor-declare command. Cursor-declare commands cannot be resent, so ct_setparam offers no advantage over ct_param in this situation.

To define the format of a host variable with ct_setparam, an application passes datafmt−>status as CS_INPUTVALUE, datafmt−>datatype as the datatype of the host variable, and data, datalenp, and indp as NULL.

An application defines host variable formats as part of a cursor-declare command but does not specify data values for the variables until after initiating a cursor-open command for the cursor.

When defining host variable formats, the host-language variables associated with each ct_setparam call can be specified either by name (with datafmt −>name and datafmt −> namelen set accordingly) or by the order of ct_setparam and ct_param calls (with datafmt-> namelen as 0). If one variable is named, all variables must be named.

The following table lists the fields in *datafmt that are used when defining host variable formats:

**Table 3-61: CS_DATAFMT fields for defining host variable formats**
Field	Description
name	The name of the host variable.
namelen	The length, in bytes, of name, or 0 to indicate an unnamed parameter.
datatype	The datatype of the host variable. All standard Client-Library types are valid except for CS_TEXT_TYPE, CS_IMAGE_TYPE, and Client-Library user-defined types.
status	CS_INPUTVALUE.
All other fields are ignored.

Using ct_setparam to identify updatable cursor columns

Some servers require a client application to identify update columns for a cursor-declare command if some, but not all, of the columns are updatable. Update columns can be used to change values in underlying database tables.

Adaptive Server does not require the application to specify update columns with additional ct_param/ct_setparam calls as described in this section. In fact, Adaptive Server ignores requests to identify update columns as described here. The application must use the Transact-SQL for read only or for update of syntax in the select statement to specify which (if any) columns are updatable (see the Adaptive Server Enterprise for a description of this syntax). Depending on its design, an Open Server application may require clients to specify a cursor’s update columns as described in this section.

If all of the cursor’s columns are updatable, an application does not need to call ct_param or ct_setparam to specify them individually.

To identify an update column for a cursor declare command, an application calls ct_param or ct_setparam with datafmt−>status as CS_UPDATECOL and *data as the name of the column.

The following table lists the fields in *datafmt that are used when ct_setparam is called to identify update columns for a cursor-declare command:

Table 3-62: CS_DATAFMT fields for identifying update columns
Field name

Set to

status

CS_UPDATECOL

All other fields are ignored.