Functions for binding parameters in SQL statements

Both SQLPrepare(), followed by SQLExecute(), and SQLExecDirect() enable you to execute an SQL statement that uses parameter markers in place of expressions or host variables (for embedded SQL).

Parameter markers are question mark characters (?) that you place in SQL statements. When you execute a statement that contains parameter markers, these markers are replaced with the contents of host variables.

Binding associates an application variable to a parameter marker. Your application must bind an application variable to each parameter marker in an SQL statement before it can execute that statement. To bind a parameter, call SQLBindParameter() with the appropriate arguments to indicate the numerical position of the parameter, the SQL type of the parameter, the data type of the variable, a pointer to the application variable, and length of the variable.

You refer to parameter markers in an SQL statement sequentially, from left to right, starting at 1, in ODBC function calls. You can call SQLNumParams() to determine the number of parameters in a statement.

The bound application variable and its associated length are called deferred input arguments. These arguments are called deferred because only pointers are passed when the parameter is bound; no data is read from the variable until the statement is executed. Deferred arguments enable you to modify the contents of bound parameter variables and execute SQL statements that use the most recent value with another call to SQLExecute().

Information for each parameter remains in effect until the application overrides or unbinds the parameter, or drops the statement handle. If the application executes the SQL statement repeatedly without changing the parameter binding, Db2 ODBC uses the same pointers to locate the data on each execution. The application can also change the parameter binding to a different set of deferred variables. The application must not deallocate or discard deferred input fields between the time it binds the fields to parameter markers and the time Db2 ODBC accesses them at execution time.

You can bind parameters to a variable with a different data type than the SQL statement requires. Your application must indicate the C data type of the source, and the SQL type of the parameter marker. Db2 ODBC converts the contents of the variable to match the SQL data type you specified. For example, the SQL statement might require an integer value, but your application has a string representation of an integer. You can bind the string to the parameter, and Db2 ODBC will convert the string to the corresponding integer value when you execute the statement. Not every C data type can be bound to a parameter marker.

Use SQLDescribeParam() to determine the data type of a parameter marker. If the application indicates an incorrect data type for the parameter marker, an extra conversion by the database server or an error can occur.

When you use an SQL predicate that compares a distinct type to a parameter marker, you must either cast the parameter marker to the distinct type or cast the distinct type to a source type. Otherwise, an error occurs.