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