SQLBindParameter--Bind a parameter marker to a buffer

Purpose

Specification:

DB2 CLI 2.1

ODBC 2.0

SQLBindParameter() is used to associate (bind) parameter markers in an SQL statement to application variables, for all C data types. In this case data is transferred from the application to the DBMS when SQLExecute() or SQLExecDirect() is called. Data conversion might occur when the data is transferred.

Syntax

SQLRETURN  SQL_API SQLBindParameter(
                SQLHSTMT          StatementHandle,  /* hstmt */
                SQLUSMALLINT      ParameterNumber,  /* ipar */
                SQLSMALLINT       InputOutputType,  /* fParamType */
                SQLSMALLINT       ValueType,        /* fCType */
                SQLSMALLINT       ParameterType,    /* fSqlType */
                SQLUINTEGER       ColumnSize,       /* cbColDef */
                SQLSMALLINT       DecimalDigits,    /* ibScale */
                SQLPOINTER        ParameterValuePtr,/* rgbValue */
                SQLINTEGER        BufferLength,     /* cbValueMax */
                SQLINTEGER *FAR   StrLen_or_IndPtr);/* pcbValue */

Function arguments

Table 34. SQLBindParameter arguments

Data type	Argument	Use	Description
SQLHSTMT	`StatementHandle`	input	Statement handle.
SQLUSMALLINT	`ParameterNumber`	input	Parameter marker number, ordered sequentially left to right, starting at one.
SQLSMALLINT	`InputOutputType`	input	The type of parameter. The supported type is: SQL_PARAM_INPUT: When the statement is executed, the actual data value for the parameter is sent to the server; the ParameterValuePtr buffer must contain valid input data values and the `StrLen_or_IndPtr` buffer must contain the corresponding length value or SQL_NTS, or SQL_NULL_DATA. DB2 Everyplace does not support `SQLPutData()`, so you should not store the parameter value in the ParameterValuePtr buffer. SQL_PARAM_INPUT_OUTPUT: The parameter marker is associated with an input/output parameter of the called stored procedure. When the statement is executed, actual data values for the parameter are sent to the server. The `ParameterValuePtr` buffer must contain valid input data values; the `StrLen_or_IndPtr` buffer must contain the corresponding length value or SQL_NTS, SQL_NULL_DATA. SQL_PARAM_OUTPUT: The parameter marker is associated with an output parameter of the called stored procedure or the return value of the stored procedure. After the statement is executed, data for the output parameter is returned to the application buffer specified by `ParameterValuePtr` and `StrLen_or_IndPtr`, unless both are NULL pointers, in which case the output data is discarded. If an output parameter does not have a return value then `StrLen_or_IndPtr` is set to SQL_NULL_DATA.
SQLSMALLINT	`ValueType`	input	C data type of the parameter. The following types are supported: SQL_C_BINARY SQL_C_BIT SQL_C_CHAR SQL_C_DOUBLE SQL_C_FLOAT SQL_C_LONG SQL_C_SHORT SQL_C_TYPE_DATE SQL_C_TYPE_TIME SQL_C_TYPE_TIMESTAMP SQL_C_TINYINT Specifying SQL_C_DEFAULT causes data to be transferred from its default C data type to the type indicated in `ParameterType`.
SQLSMALLINT	`ParameterType`	input	SQL data type of the parameter. The supported types are: SQL_BLOB SQL_CHAR SQL_DECIMAL SQL_INTEGER SQL_SMALLINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_VARCHAR
SQLUINTEGER	`ColumnSize`	input	Precision of the corresponding parameter marker. If `ParameterType` denotes a binary or single-byte character string (such as SQL_CHAR, SQL_BLOB), this is the maximum length in bytes for this parameter marker. If not, this argument is ignored.
SQLSMALLINT	`DecimalDigits`	input	Scale of the corresponding parameter if `ParameterType` is SQL_DECIMAL.
SQLPOINTER	`ParameterValuePtr`	input (deferred), output (deferred), or both	On input (`InputOutputType` set to SQL_PARAM_INPUT or SQL_PARAM_INPUT_OUTPUT): At execution time, if `StrLen_or_IndPtr` does not contain SQL_NULL_DATA, `ParameterValuePtr` points to a buffer that contains the actual data for the parameter. On output (InputOutputType set to SQL_PARAM_OUTPUT or SQL_PARAM_INPUT_OUTPUT): `ParameterValuePtr` points to the buffer where the output parameter value of the stored procedure is stored. A null `ParameterValuePtr` indicates unbinding the parameter.
SQLINTEGER	`BufferLength`	input	For character and binary data, `BufferLength` specifies the length of the `ParameterValuePtr` buffer. For non-character and non-binary data, this argument is ignored and the length of the `ParameterValuePtr` buffer is assumed to be the length associated with the C data type. For output parameters, `BufferLength` is used to determine whether to truncate data.
SQLINTEGER *	`StrLen_or_IndPtr`	input (deferred), output (deferred), or both	If this is an input or input/output parameter: This is the pointer to the location that contains (when the statement is executed) the length of the parameter marker value stored at `ParameterValuePtr`. To specify a null value for a parameter marker, this storage location must contain SQL_NULL_DATA. If `ValueType` is SQL_C_CHAR, this storage location must contain either the exact length of the data stored at `ParameterValuePtr`, or SQL_NTS if the contents at `ParameterValuePtr` is null-terminated. If it contains the exact length, no null character is allowed in the data stored at ParameterValuePtr. If `ValueType` indicates character data (explicitly, or implicitly using SQL_C_DEFAULT), and this pointer is set to NULL, the application must provide a null-terminated string in `ParameterValuePtr`. This also implies that this parameter marker never has a null value. If this is an output parameter (`InputOutputType` is set to SQL_PARAM_OUTPUT): This must be an output parameter or return value of a stored procedure CALL and points to one of the following, after the execution of the stored procedure: Number of bytes available to return in `ParameterValuePtr`, excluding the null-termination character. SQL_NULL_DATA

Usage

A parameter marker is represented by a ? character in an SQL statement and is used to indicate a position in the statement where an application-supplied value is to be substituted when the statement is executed. This value can be obtained from an application variable. SQLBindParameter() is used to bind the application storage area to the parameter marker.

The application must bind a variable to each parameter marker in the SQL statement before executing the SQL statement. For this function, ParameterValuePtr and StrLen_or_IndPtr are deferred arguments. The storage locations must be valid and contain input data values when the statement is executed. This means that either the SQLExecDirect() or SQLExecute() call must be kept in the same procedure scope as the SQLBindParameter() calls, or these storage locations must be dynamically allocated or declared statically or globally.

Parameter markers are referenced by number (ColumnNumber) and are numbered sequentially from left to right, starting at one.

All parameters bound by this function remain in effect until one of the following functions is called:

SQLFreeStmt() is called with the SQL_RESET_PARAMS option.
SQLFreeHandle() is called with HandleType set to SQL_HANDLE_STMT.
SQLBindParameter() is called again for the same parameter ParameterNumber number.

After the SQL statement is executed and the results processed, the application might want to reuse the statement handle to execute a different SQL statement. If the parameter marker specifications are different (number of parameters, length, or type) then SQLFreeStmt() must be called with SQL_RESET_PARAMS to reset or clear the parameter bindings.

The C buffer data type given by ValueType must be compatible with the SQL data type indicated by ParameterType, or an error occurs.

Because the data in the variables referenced by ParameterValuePtr and StrLen_or_IndPtr is not verified until the statement is executed, data content or format errors are not detected or reported until SQLExecute() or SQLExecDirect() is called.

For this function, ParameterValuePtr and StrLen_or_IndPtr are deferred arguments. In the case where InputOutputType is set to SQL_PARAM_INPUT, the storage locations must be valid and contain input data values when the statement is executed. This means that either the SQLExecDirect() or SQLExecute() call must be kept in the same procedure scope as the SQLBindParameter() calls, or these storage locations must be dynamically allocated or declared statically or globally.

DB2 Everyplace supports SQL_PARAM_INPUT, SQL_PARAM_INPUT_OUTPUT, and SQL_PARAM_OUTPUT. DB2 Everyplace does not support SQLPutData(), so you should not store the parameter value in the ParameterValuePtr buffer.

For character and binary C data, the BufferLength argument specifies the length of the ParameterValuePtr buffer. For all other types of C data, the BufferLength argument is ignored.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 35. SQLBindParameter SQLSTATEs

SQLSTATE	Description	Explanation
07006	Invalid conversion.	The conversion from the data value identified by the `ValueType` argument to the data type identified by the `ParameterType` argument is not a meaningful conversion. (For example, conversion from SQL_C_DATE to SQL_DOUBLE.)
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
58004	Unexpected system failure.	Unrecoverable system error.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY003	Program type out of range.	The value specified by the argument `ParameterNumber` is not a valid data type or SQL_C_DEFAULT.
HY004	SQL data type out of range.	The value specified for the argument `ParameterType` is not a valid SQL data type.
HY009	Invalid argument value.	The argument `ParameterValuePtr` is a null pointer, and the argument `StrLen_or_IndPtr` is a null pointer, and `InputOutputType` is not SQL_PARAM_OUTPUT.
HY013	Unexpected memory handling error.	DB2 CLI is unable to access memory required to support execution or completion of the function.
HY090	Invalid string or buffer length.	The value specified for the argument `BufferLength` is less than 0.
HY093	Invalid parameter number.	The value specified for the argument `ValueType` is less than one or greater than the maximum number of parameters supported by the server.
HY094	Invalid scale value.	The value specified for `ParameterType` is either SQL_DECIMAL or SQL_NUMERIC, and the value specified for `DecimalDigits` is less than 0 or greater than the value for the argument `ParamDef` (precision).
HY104	Invalid precision value.	The value specified for `ParameterType` is either SQL_DECIMAL or SQL_NUMERIC, and the value specified for `ParamDef` is less than one.
HY105	Invalid parameter type.	`InputOutputType` is not SQL_PARAM_INPUT.
HYC00	Driver not capable.	DB2 CLI or data source does not support the conversion specified by the combination of the value specified for the argument `ValueType` and the value specified for the argument `ParameterType`. The value specified for the argument `ParameterType` is not supported by either DB2 CLI or the data source.

Related reference