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

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

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” in Chapter 8, “SQL Statements.” [d] Drivers must always check this value to see if 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.