SQLBindParameter - Bind a parameter marker to a buffer

SQLBindParameter - Bind a parameter marker to a buffer

SQLBindParameter() is used to associate (bind) parameter markers in an SQL statement to application variables. Data is transferred from the application to the Database Management System (DBMS) when SQLExecute() or SQLExecDirect() is called. Data conversion might occur when the data is transferred.
This function must also be used to bind an application storage to a parameter of a stored procedure CALL statement where the parameter can be input, output, or both. This function is essentially an extension of SQLSetParam().

Syntax
SQLRETURN SQLBindParameter(SQLHSTMT          StatementHandle,
                           SQLSMALLINT      ParameterNumber,
                           SQLSMALLINT       InputOutputType,
                           SQLSMALLINT       ValueType,
                           SQLSMALLINT       ParameterType,
                           SQLINTEGER        ColumnSize,
                           SQLSMALLINT       DecimalDigits,
                           SQLPOINTER        ParameterValuePtr,
                           SQLINTEGER        BufferLength,
                           SQLINTEGER       *StrLen_or_IndPtr);
Function arguments

Table 1. SQLBindParameter arguments
Data type Argument Use Description

SQLHSTMT StatementHandle Input Statement handle.
SQLSMALLINT ParameterNumber Input Parameter marker number, ordered sequentially left to right, starting at 1.
SQLSMALLINT InputOutputType Input The type of parameter. The value of the SQL_DESC_PARAMETER_TYPE field of the implementation parameter descriptor is also set to this argument. The supported types are:

SQL_PARAM_INPUT: The parameter marker is associated with an SQL statement that is not a stored procedure CALL; or, it marks an input parameter of the CALLed stored procedure.
When the statement is processed, the actual data value for the parameter is sent to the data source: 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, or (if the value should be sent via SQLParamData() and SQLPutData()) SQL_DATA_AT_EXEC.

SQL_PARAM_INPUT_OUTPUT: The parameter marker is associated with an input/output parameter of the CALLed stored procedure.
When the statement is processed, actual data value for the parameter is sent to the data source: 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, or (if the value should be sent via SQLParamData() and SQLPutData()) SQL_DATA_AT_EXEC.

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 processed, 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_BIGINT

SQL_BINARY

SQL_BLOB

SQL_BLOB_LOCATOR

SQL_CHAR

SQL_CLOB

SQL_CLOB_LOCATOR

SQL_DATETIME

SQL_DBCLOB

SQL_DBCLOB_LOCATOR

SQL_DECIMAL

SQL_DOUBLE

SQL_FLOAT

SQL_GRAPHIC

SQL_INTEGER

SQL_NUMERIC

SQL_REAL

SQL_SMALLINT

SQL_TYPE_DATE

SQL_TYPE_TIME

SQL_TYPE_TIMESTAMP

SQL_VARBINARY

SQL_VARCHAR

SQL_VARGRAPHIC

SQL_WCHAR

SQL_WVARCHAR

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.
SQLINTEGER ColumnSize Input Precision of the corresponding parameter marker.

If ParameterType denotes a binary or single-byte character string (for example, SQL_CHAR), this is the maximum length in bytes for this parameter marker.

If ParameterType denotes a double-byte character string (for example, SQL_GRAPHIC), this is the maximum length in double-byte characters for this parameter.

If ParameterType denotes SQL_DECIMAL or SQL_NUMERIC, this is the maximum decimal precision.

Otherwise, this argument is ignored.

SQLSMALLINT DecimalDigits Input Scale of the corresponding parameter if ParameterType is SQL_DECIMAL or SQL_NUMERIC. If ParameterType is SQL_TYPE_TIMESTAMP, this is the number of digits to the right of the decimal point in the character representation of a timestamp (for example, the scale of yyyy-mm-dd hh:mm:ss.fff is 3).
Other than for the ParameterType values mentioned here, DecimalDigits is ignored.
SQLPOINTER ParameterValuePtr
Input (deferred), or output (deferred), or both

On input (InputOutputType set to SQL_PARAM_INPUT, or SQL_PARAM_INPUT_OUTPUT), the following situations are true:
At processing time, if StrLen_or_IndPtr does not contain SQL_NULL_DATA or SQL_DATA_AT_EXEC, then ParameterValuePtr points to a buffer that contains the actual data for the parameter.
If StrLen_or_IndPtr contains SQL_DATA_AT_EXEC, then ParameterValuePtr is an application-defined 32-bit value that is associated with this parameter. This 32-bit value is returned to the application via a subsequent SQLParamData() call.
If SQLParamOptions() is called to specify multiple values for the parameter, then ParameterValuePtr is a pointer to an input buffer array of BufferLength bytes.

On output (InputOutputType set to SQL_PARAM_OUTPUT, or SQL_PARAM_INPUT_OUTPUT), the following situations are true:
ParameterValuePtr points to the buffer where the output parameter value of the stored procedure is stored.
If InputOutputType is set to SQL_PARAM_OUTPUT, and both ParameterValuePtr and StrLen_or_IndPtr are NULL pointers, then the output parameter value or the return value from the stored procedure call is discarded.

SQLINTEGER BufferLength Input Not used.
SQLINTEGER * StrLen_or_IndPtr Input (deferred), output (deferred)
If this is an input or input/output parameter, this is the pointer to the location that contains (when the statement is processed) 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 content at ParameterValuePtr is null-terminated.
For all values of ParameterValuePtr, if ValueType indicates LOB data, this storage location must contain the length of the data stored at ParameterValuePtr. This length value must be specified in bytes, not the number of double-byte characters.
If ValueType indicates character data (explicitly, or implicitly using SQL_C_DEFAULT), and this pointer is set to NULL, it is assumed that the application always provides a null-terminated string in ParameterValuePtr. This also implies that this parameter marker never has a null value.
If ValueType specifies any form of double-byte character data, then StrLen_or_IndPtr must be the number of double-byte characters, not the number of bytes.
When SQLExecute() or SQLExecDirect() is called, and StrLen_or_IndPtr points to a value of SQL_DATA_AT_EXEC, the data for the parameter is sent with SQLPutData(). This parameter is referred to as a data-at-execution parameter.

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 processed. This value is obtained from an application variable.
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 processed. This means either keeping the SQLExecDirect() or SQLExecute() call 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 referred to by number (ParameterNumber) and are numbered sequentially from left to right, starting at 1.
All parameters bound by this function remain in effect until SQLFreeStmt() is called with either the SQL_DROP or SQL_RESET_PARAMS option, or until SQLBindParameter() is called again for the same parameter ParameterNumber number.
After the SQL statement and the results have been processed, the application might want to reuse the statement handle to process a different SQL statement. If the parameter marker specifications are different (number of parameters, length or type), then SQLFreeStmt() should be called with SQL_RESET_PARAMS to reset or clear the parameter bindings.
The C buffer data type that is given by ValueType must be compatible with the SQL data type that is 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 processed, data content or format errors are not detected or reported until SQLExecute() or SQLExecDirect() is called.
SQLBindParameter() essentially extends the capability of the SQLSetParam() function by providing a method of specifying whether a parameter is input, input and output, or output. This information is necessary for the proper handling of parameters for stored procedures.
The InputOutputType argument specifies the type of the parameter. All parameters in the SQL statements that do not call procedures are input parameters. Parameters in stored procedure calls can be input, input/output, or output parameters. Even though the DB2^® stored procedure argument convention typically implies that all procedure arguments are input/output, the application programmer can still choose to specify more exactly the input or output nature on the SQLBindParameter() to follow a more rigorous coding style. Also, note that these types should be consistent with the parameter types specified when the stored procedure is registered with the SQL CREATE PROCEDURE statement.

If an application cannot determine the type of a parameter in a procedure call, set InputOutputType to SQL_PARAM_INPUT; if the data source returns a value for the parameter, DB2 UDB CLI discards it.

If an application has marked a parameter as SQL_PARAM_INPUT_OUTPUT or SQL_PARAM_OUTPUT and the data source does not return a value, DB2 UDB CLI sets the StrLen_or_IndPtr buffer to SQL_NULL_DATA.

If an application marks a parameter as SQL_PARAM_OUTPUT, data for the parameter is returned to the application after the CALL statement has been processed. If the ParameterValuePtr and StrLen_or_IndPtr arguments are both null pointers, DB2 UDB CLI discards the output value. If the data source does not return a value for an output parameter, DB2 UDB CLI sets the StrLen_or_IndPtr buffer to SQL_NULL_DATA.

For this function, both ParameterValuePtr and StrLen_or_IndPtr are deferred arguments. In the case where InputOutputType is set to SQL_PARAM_INPUT or SQL_PARAM_INPUT_OUTPUT, the storage locations must be valid and contain input data values when the statement is processed. This means either keeping the SQLExecDirect() or SQLExecute() call in the same procedure scope as the SQLBindParameter() calls, or, these storage locations must be dynamically allocated or statically / globally declared.
Similarly, if InputOutputType is set to SQL_PARAM_OUTPUT or SQL_PARAM_INPUT_OUTPUT, the ParameterValuePtr and StrLen_or_IndPtr buffer locations must remain valid until the CALL statement has been processed.
When SQLBindParameter() is used to bind an application variable to an output parameter for a stored procedure, DB2 UDB CLI can provide some performance enhancement if the ParameterValuePtr buffer is placed consecutively in memory after the StrLen_or_IndPtr buffer. For example:
    struct {  SQLINTEGER  StrLen_or_IndPtr;
              SQLCHAR     ParameterValuePtr[MAX_BUFFER];
           } column;
Return codes

SQL_SUCCESS

SQL_SUCCESS_WITH_INFO

SQL_ERROR

SQL_INVALID_HANDLE

Error conditions

Table 2. SQLBindParameter SQLSTATEs
SQLSTATE Description Explanation

07006 Conversion not valid 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 fails before the function is completed.
58004 Unexpected system failure Unrecoverable system error.
HY001 Memory allocation failure DB2 UDB CLI is unable to allocate memory required to support the processing or completion of the function.
HY003 Program type out of range The value specified by the argument ParameterNumber 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 Argument value not valid The argument ParameterValuePtr is a null pointer and the argument StrLen_or_IndPtr is a null pointer, and InputOutputType is not SQL_PARAM_OUTPUT.
HY010 Function sequence error Function is called after SQLExecute() or SQLExecDirect() has returned SQL_NEED_DATA, but data has not been sent for all data-at-execution parameters.
HY013 Unexpected memory handling error DB2 UDB CLI is unable to access memory required to support the processing or completion of the function.
HY014 Too many handles The maximum number of handles has been allocated.
HY021 Inconsistent descriptor information The descriptor information checked during a consistency check is not consistent.
HY090 String or buffer length not valid The value specified for the BufferLength argument is less than 0.
HY093 Parameter number not valid The value specified for the ValueType argument is less than 1 or greater than the maximum number of parameters supported by the data source.
HY094 Scale value not valid 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).
The value specified for ParameterType is SQL_C_TIMESTAMP and the value for ParameterType is either SQL_CHAR or SQL_VARCHAR and the value for DecimalDigits is less than 0 or greater than 6.
HY104 Precision value not valid The value specified for ParameterType is either SQL_DECIMAL or SQL_NUMERIC and the value specified for ParamDef is less than 1.
HY105 Parameter type not valid InputOutputType is not one of SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, or SQL_PARAM_INPUT_OUTPUT.
HYC00 Driver not capable DB2 UDB 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 UDB CLI or the data source.

References

SQLExecDirect - Execute a statement directly

SQLExecute - Execute a statement

SQLParamData - Get next parameter for which a data value is needed

SQLPutData - Pass data value for a parameter

Parent topic:
DB2 UDB CLI functions

Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLSMALLINT	ParameterNumber	Input	Parameter marker number, ordered sequentially left to right, starting at 1.
SQLSMALLINT	InputOutputType	Input	The type of parameter. The value of the SQL_DESC_PARAMETER_TYPE field of the implementation parameter descriptor is also set to this argument. The supported types are: SQL_PARAM_INPUT: The parameter marker is associated with an SQL statement that is not a stored procedure CALL; or, it marks an input parameter of the CALLed stored procedure. When the statement is processed, the actual data value for the parameter is sent to the data source: 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, or (if the value should be sent via `SQLParamData()` and `SQLPutData()`) SQL_DATA_AT_EXEC. SQL_PARAM_INPUT_OUTPUT: The parameter marker is associated with an input/output parameter of the CALLed stored procedure. When the statement is processed, actual data value for the parameter is sent to the data source: 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, or (if the value should be sent via `SQLParamData()` and `SQLPutData()`) SQL_DATA_AT_EXEC. 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 processed, 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_BIGINT SQL_BINARY SQL_BLOB SQL_BLOB_LOCATOR SQL_CHAR SQL_CLOB SQL_CLOB_LOCATOR SQL_DATETIME SQL_DBCLOB SQL_DBCLOB_LOCATOR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_GRAPHIC SQL_INTEGER SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_VARBINARY SQL_VARCHAR SQL_VARGRAPHIC SQL_WCHAR SQL_WVARCHAR 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.
SQLINTEGER	ColumnSize	Input	Precision of the corresponding parameter marker. If ParameterType denotes a binary or single-byte character string (for example, SQL_CHAR), this is the maximum length in bytes for this parameter marker. If ParameterType denotes a double-byte character string (for example, SQL_GRAPHIC), this is the maximum length in double-byte characters for this parameter. If ParameterType denotes SQL_DECIMAL or SQL_NUMERIC, this is the maximum decimal precision. Otherwise, this argument is ignored.
SQLSMALLINT	DecimalDigits	Input	Scale of the corresponding parameter if ParameterType is SQL_DECIMAL or SQL_NUMERIC. If ParameterType is SQL_TYPE_TIMESTAMP, this is the number of digits to the right of the decimal point in the character representation of a timestamp (for example, the scale of yyyy-mm-dd hh:mm:ss.fff is 3). Other than for the ParameterType values mentioned here, DecimalDigits is ignored.
SQLPOINTER	ParameterValuePtr	Input (deferred), or output (deferred), or both	On input (InputOutputType set to SQL_PARAM_INPUT, or SQL_PARAM_INPUT_OUTPUT), the following situations are true: At processing time, if StrLen_or_IndPtr does not contain SQL_NULL_DATA or SQL_DATA_AT_EXEC, then ParameterValuePtr points to a buffer that contains the actual data for the parameter. If StrLen_or_IndPtr contains SQL_DATA_AT_EXEC, then ParameterValuePtr is an application-defined 32-bit value that is associated with this parameter. This 32-bit value is returned to the application via a subsequent `SQLParamData()` call. If `SQLParamOptions()` is called to specify multiple values for the parameter, then ParameterValuePtr is a pointer to an input buffer array of BufferLength bytes. On output (InputOutputType set to SQL_PARAM_OUTPUT, or SQL_PARAM_INPUT_OUTPUT), the following situations are true: ParameterValuePtr points to the buffer where the output parameter value of the stored procedure is stored. If InputOutputType is set to SQL_PARAM_OUTPUT, and both ParameterValuePtr and StrLen_or_IndPtr are NULL pointers, then the output parameter value or the return value from the stored procedure call is discarded.
SQLINTEGER	BufferLength	Input	Not used.
SQLINTEGER *	StrLen_or_IndPtr	Input (deferred), output (deferred)	If this is an input or input/output parameter, this is the pointer to the location that contains (when the statement is processed) 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 content at ParameterValuePtr is null-terminated. For all values of ParameterValuePtr, if ValueType indicates LOB data, this storage location must contain the length of the data stored at ParameterValuePtr. This length value must be specified in bytes, not the number of double-byte characters. If ValueType indicates character data (explicitly, or implicitly using SQL_C_DEFAULT), and this pointer is set to NULL, it is assumed that the application always provides a null-terminated string in ParameterValuePtr. This also implies that this parameter marker never has a null value. If ValueType specifies any form of double-byte character data, then StrLen_or_IndPtr must be the number of double-byte characters, not the number of bytes. When `SQLExecute()` or `SQLExecDirect()` is called, and StrLen_or_IndPtr points to a value of SQL_DATA_AT_EXEC, the data for the parameter is sent with `SQLPutData()`. This parameter is referred to as a data-at-execution parameter.

SQLSTATE	Description	Explanation
07006	Conversion not valid	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 fails before the function is completed.
58004	Unexpected system failure	Unrecoverable system error.
HY001	Memory allocation failure	DB2 UDB CLI is unable to allocate memory required to support the processing or completion of the function.
HY003	Program type out of range	The value specified by the argument ParameterNumber 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	Argument value not valid	The argument ParameterValuePtr is a null pointer and the argument StrLen_or_IndPtr is a null pointer, and InputOutputType is not SQL_PARAM_OUTPUT.
HY010	Function sequence error	Function is called after `SQLExecute()` or `SQLExecDirect()` has returned SQL_NEED_DATA, but data has not been sent for all data-at-execution parameters.
HY013	Unexpected memory handling error	DB2 UDB CLI is unable to access memory required to support the processing or completion of the function.
HY014	Too many handles	The maximum number of handles has been allocated.
HY021	Inconsistent descriptor information	The descriptor information checked during a consistency check is not consistent.
HY090	String or buffer length not valid	The value specified for the BufferLength argument is less than 0.
HY093	Parameter number not valid	The value specified for the ValueType argument is less than 1 or greater than the maximum number of parameters supported by the data source.
HY094	Scale value not valid	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). The value specified for ParameterType is SQL_C_TIMESTAMP and the value for ParameterType is either SQL_CHAR or SQL_VARCHAR and the value for DecimalDigits is less than 0 or greater than 6.
HY104	Precision value not valid	The value specified for ParameterType is either SQL_DECIMAL or SQL_NUMERIC and the value specified for ParamDef is less than 1.
HY105	Parameter type not valid	InputOutputType is not one of SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, or SQL_PARAM_INPUT_OUTPUT.
HYC00	Driver not capable	DB2 UDB 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 UDB CLI or the data source.