Home

	Expand Search

Open Client Client-Library/C Reference Manual

Chapter 3 Routines

ct_setparam

Usage

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.

• 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-58 lists the fields in *datafmt that are used when passing input parameter values. A parameter's format cannot be changed after ct_setparam returns:

  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.

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

  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.

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

  CS_DATAFMT fields for identifying update columns

  Field name	Set to
  status	CS_UPDATECOL
  All other fields are ignored.