Setting Parameter Values

To set the value of a parameter, the application simply sets the value of the variable bound to the parameter. It is not important when this value is set, as long as it is set before the statement is executed. The application can set the value before or after binding the variable, and it can change the value as many times as it wants. When the statement is executed, the driver simply retrieves the current value of the variable. This is particularly useful when a prepared statement is executed more than once; the application sets new values for some or all of the variables each time the statement is executed. For an example of this, see Prepared Execution, earlier in this section.

If a length/indicator buffer was bound in the call to SQLBindParameter, it must be set to one of the following values before the statement is executed:

  • The byte length of the data in the bound variable. The driver checks this length only if the variable is character or binary (ValueType is SQL_C_CHAR or SQL_C_BINARY).

  • SQL_NTS. The data is a null-terminated string.

  • SQL_NULL_DATA. The data value is NULL, and the driver ignores the value of the bound variable.

  • SQL_DATA_AT_EXEC or the result of the SQL_LEN_DATA_AT_EXEC macro. The value of the parameter is to be sent with SQLPutData. For more information, see Sending Long Data, later in this section.

The following table shows the values of the bound variable and the length/indicator buffer that the application sets for a variety of parameter values.

Parameter

value
Parameter

(SQL)

data type
Variable (C)

data type
Value in

bound

variable
Value in

length/indicator

buffer[d]
"ABC" SQL_CHAR SQL_C_CHAR ABC\0[a] SQL_NTS or 3
10 SQL_INTEGER SQL_C_SLONG 10 --
10 SQL_INTEGER SQL_C_CHAR 10\0[a] SQL_NTS or 2
1 P.M. SQL_TYPE_TIME SQL_C_TYPE_TIME 13,0,0[b] --
1 P.M. SQL_TYPE_TIME SQL_C_CHAR {t '13:00:00'}\0[a], [c] SQL_NTS or 14
NULL SQL_SMALLINT SQL_C_SSHORT -- SQL_NULL_DATA

[a] "\0" represents a null-termination character. The null-termination character is required only if the value in the length/indicator buffer is SQL_NTS.

[b] The numbers in this list are the numbers stored in the fields of the TIME_STRUCT structure.

[c] The string uses the ODBC date escape clause. For more information, see Date, Time, and Timestamp Literals.

[d] Drivers must always check this value to see whether it is a special value, such as SQL_NULL_DATA.

What a driver does with a parameter value at execution time is driver-dependent. If necessary, the driver converts the value from the C data type and byte length of the bound variable to the SQL data type, precision, and scale of the parameter. In most cases, the driver then sends the value to the data source. In some cases, it formats the value as text and inserts it into the SQL statement before sending the statement to the data source.