SQLBindParameter function (CLI) - Bind a parameter marker to a buffer or LOB locator

Binds parameter markers in an SQL statement to application variables, arrays of application variables, or lob locators.

Specification:

CLI 2.1
ODBC 2.0

SQLBindParameter() binds parameter markers to either:

Application variables or arrays of application variables (storage buffers) 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 as the data is transferred.
A LOB locator, for SQL LOB data types. In this case a LOB locator value, not the LOB data itself, is transferred from the application to the server when the SQL statement is executed.
Alternatively, LOB parameters can be bound directly to a file using SQLBindFileToParam()

This function must also be used to bind a parameter of a stored procedure CALL statement to the application where the parameter can be input, output or both.

Syntax

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

Function arguments

Table 1. 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 1.
SQLSMALLINT	InputOutputType	input	The type of parameter. The value of the SQL_DESC_PARAMETER_TYPE field of the IPD 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 executed, the data for the parameter is sent to the server and as such, the ParameterValuePtr buffer must contain valid input data value(s), unless the StrLen_or_IndPtr buffer contains SQL_NULL_DATA or SQL_DATA_AT_EXEC (if the value should be sent via SQLParamData() and SQLPutData()). SQL_PARAM_INPUT_OUTPUT: The parameter marker is associated with an input/output parameter of the CALLed stored procedure. When the statement is executed, the data for the parameter is sent to the server and as such, the ParameterValuePtr buffer must contain valid input data value(s), unless the StrLen_or_IndPtr buffer contains SQL_NULL_DATA or SQL_DATA_AT_EXEC (if the value should be sent via SQLParamData() and SQLPutData()). 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_BLOB_LOCATOR SQL_C_CHAR SQL_C_CLOB_LOCATOR SQL_C_DBCHAR SQL_C_DBCLOB_LOCATOR SQL_C_DECIMAL_IBM SQL_C_DOUBLE SQL_C_FLOAT SQL_C_LONG SQL_C_NUMERIC ^a SQL_C_SBIGINT SQL_C_SHORT SQL_C_TYPE_DATE SQL_C_TYPE_TIME SQL_C_TYPE_TIMESTAMP SQL_C_TYPE_TIMESTAMP_EXT SQL_C_TYPE_TIMESTAMP_EXT_TZ SQL_C_TINYINT SQL_C_UBIGINT SQL_C_UTINYINT SQL_C_WCHAR Specifying SQL_C_DEFAULT causes data to be transferred from its default C data type to the type indicated in ParameterType. a Windows 32-bit only
SQLSMALLINT	ParameterType	input	SQL data type of the parameter. The supported types are: SQL_BIGINT SQL_BINARY SQL_BIT SQL_BLOB SQL_BLOB_LOCATOR SQL_CHAR SQL_CLOB SQL_CLOB_LOCATOR SQL_DBCLOB SQL_DBCLOB_LOCATOR SQL_DECFLOAT SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_GRAPHIC SQL_INTEGER SQL_LONGVARBINARY SQL_LONGVARCHAR SQL_LONGVARGRAPHIC SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_TINYINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP_WITH_TIMEZONE SQL_VARBINARY SQL_VARCHAR SQL_VARGRAPHIC SQL_WCHAR SQL_XML Note: SQL_BLOB_LOCATOR, SQL_CLOB_LOCATOR, SQL_DBCLOB_LOCATOR are application related concepts and do not map to a data type for column definition during a CREATE TABLE statement.
SQLULEN	ColumnSize	input	Precision of the corresponding parameter marker. If ParameterType denotes: A binary or single byte character string (for example, SQL_CHAR, SQL_BLOB), this is the maximum length in bytes for this parameter marker. A double byte character string (for example, SQL_GRAPHIC), this is the maximum length in double-byte characters for this parameter. SQL_DECIMAL, SQL_NUMERIC, this is the maximum decimal precision. An XML value (SQL_XML) for an external routine argument, this is the maximum length in bytes, n, of the declared XML AS CLOB(n) argument. For all other parameters of type SQL_XML, this argument is ignored. Otherwise, this argument is ignored.
SQLSMALLINT	DecimalDigits	input	If ParameterType is SQL_DECIMAL or SQL_NUMERIC, DecimalDigits represents the scale of the corresponding parameter and sets the SQL_DESC_SCALE field of the IPD. If ParameterType is SQL_TYPE_TIMESTAMP or SQL_TYPE_TIME, Decimal Digits represents the precision of the corresponding parameter and sets the SQL_DESC_PRECISION field of the IPD. The precision of a time timestamp value is the number of digits to the right of the decimal point in the string representation of a time or 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), 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 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 a input buffer array of BufferLength bytes. 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 will be 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.
SQLLEN	BufferLength	input	For character and binary data, BufferLength specifies the length of the ParameterValuePtr buffer (if is treated as a single element) or the length of each element in the ParameterValuePtr array (if the application calls SQLParamOptions() to specify multiple values for each parameter). For non-character and non-binary data, this argument is ignored -- the length of the ParameterValuePtr buffer (if it is a single element) or the length of each element in the ParameterValuePtr array (if SQLParamOptions() is used to specify an array of values for each parameter) is assumed to be the length associated with the C data type. For output parameters, BufferLength is used to determine whether to truncate character or binary output data in the following manner: For character data, if the number of bytes available to return is greater than or equal to BufferLength, the data in ParameterValuePtr is truncated to BufferLength-1 bytes and is null-terminated (unless null-termination has been turned off). For binary data, if the number of bytes available to return is greater than BufferLength, the data in ParameterValuePtr is truncated to BufferLength bytes.
SQLLEN *	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 which 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 ValueType indicates character data (explicitly, or implicitly using SQL_C_DEFAULT), and this pointer is set to NULL, it is assumed that the application will always provide a null-terminated string in ParameterValuePtr. This also implies that this parameter marker will never have a null value. If ParameterType denotes a graphic data type and the ValueType is SQL_C_CHAR, the pointer to StrLen_or_IndPtr can never be NULL and the contents of StrLen_or_IndPtr can never hold SQL_NTS. In general for graphic data types, this length should be the number of octets that the double byte data occupies; therefore, the length should always be a multiple of 2. In fact, if the length is odd, then an error will occur when the statement is executed. When SQLExecute() or SQLExecDirect() is called, and StrLen_or_IndPtr points to a value of SQL_DATA_AT_EXEC, the data for the parameter will be sent with SQLPutData(). This parameter is referred to as a data-at-execution parameter. When SQLBindParameter() or SQLExtendedBind() method is called through after setting the SQL_ATTR_EXTENDED_INDICATORS attribute, the `StrLen_or_IndPtr` argument allows SQL_UNASSIGNED and SQL_DEFAULT_PARAM constant to pass through the method.
SQLINTEGER *	StrLen_or_IndPtr (cont)	input (deferred), output (deferred), or both	If SQLSetStmtAttr() is used with the SQL_ATTR_PARAMSET_SIZE attribute to specify multiple values for each parameter, StrLen_or_IndPtr points to an array of SQLINTEGER values where each of the elements can be the number of bytes in the corresponding ParameterValuePtr element (excluding the null-terminator), or SQL_NULL_DATA. The StrLen_or_IndPtr represents the size of the parameter. If you have an output parameter, StrLen_or_IndPtr is a memory address (a pointer) to an SQLINTEGER and the value will contain either: The length of the buffer (minus the NULL terminator). -1 (SQL_NULL_DATA), which means that the value is NULL, and you can ignore the actual value. -4 (SQL_NO_TOTAL), which is only used for LOB type of data, and is used to indicate that the number of bytes available to return cannot be determined.

Usage

SQLBindParameter() extends the capability of the deprecated SQLSetParam() function, by providing a method of:

Specifying whether a parameter is input, input / output, or output, necessary for proper handling of parameters for stored procedures.
Specifying an array of input parameter values when SQLSetStmtAttr() with the SQL_ATTR_PARAMSET_SIZE attribute is used in conjunction with SQLBindParameter().

This function can be called before SQLPrepare() if the data types and lengths of the target columns in the WHERE or UPDATE clause, or the parameters for the stored procedure are known. Otherwise, you can obtain the attributes of the target columns or stored procedure parameters after the statement is prepared using SQLDescribeParam(), and then bind the parameter markers.

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

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

All parameters bound by this function remain in effect until one of the following event takes place:

SQLFreeStmt() is called with the SQL_RESET_PARAMS option, or
SQLFreeHandle() is called with HandleType set to SQL_HANDLE_STMT, or SQLFreeStmt() is called with the SQL_DROP option, or
SQLBindParameter() is called again for the same ParameterNumber, or
SQLSetDescField() is called, with the associated APD descriptor handle, to set SQL_DESC_COUNT in the header field of the APD to zero (0).

A parameter can only be bound to either a file or a storage location, not both. The most recent parameter binding function call determines the bind that is in effect.

Parameter type

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.

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, 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, 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, CLI discards the output value. If the data source does not return a value for an output parameter, CLI sets the StrLen_or_IndPtr buffer to SQL_NULL_DATA.
For this function, 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 executed. 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 executed.

ParameterValuePtr and StrLen_or_IndPtr arguments

ParameterValuePtr and StrLen_or_IndPtr are deferred arguments, so the storage locations they point to must be valid and contain input data values when the statement is executed. This means either keeping the SQLExecDirect() or SQLExecute() call in the same application function scope as the SQLBindParameter() calls, or dynamically allocating or statically or globally declaring these storage locations.

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

An application can pass the value for a parameter either in the ParameterValuePtr buffer or with one or more calls to SQLPutData(). In the latter case, these parameters are data-at-execution parameters. The application informs CLI of a data-at-execution parameter by placing the SQL_DATA_AT_EXEC value in the buffer pointed to by StrLen_or_IndPtr. It sets the ParameterValuePtr input argument to a 32-bit value which will be returned on a subsequent SQLParamData() call and can be used to identify the parameter position.

BufferLength argument

For character and binary C data, the BufferLength argument specifies the length of the ParameterValuePtr buffer if it is a single element; or, if the application calls SQLSetStmtAttr() with the SQL_ATTR_PARAMSET_SIZE attribute to specify multiple values for each parameter, BufferLength is the length of each element in the ParameterValuePtr array, including the null-terminator. If the application specifies multiple values, BufferLength is used to determine the location of values in the ParameterValuePtr array. For all other types of C data, the BufferLength argument is ignored.

ColumnSize argument

When actual size of the target column or output parameter is not known, the application can specify 0 for the length of the column. (ColumnSize set to 0).

If the column's data type is of fixed-length, the CLI driver will base the length from the data type itself. However, setting ColumnSize to 0 means different things when the data type is of type character, binary string or large object:

Input parameter: A 0 ColumnSize means that CLI will use the maximum length for the SQL type provided as the size of the column or stored procedure parameter. CLI will perform any necessary conversions using this size.
Output parameter (stored procedures only): A 0 ColumnSize means that CLI will use BufferLength as the parameter's size. Note that this means that the stored procedure must not return more than BufferLength bytes of data or a truncation error will occur.
For Input-output parameter (store procedures only): A 0 ColumnSize means that CLI will set both the input and output to BufferLength as the target parameter. This means that the input data will be converted to this new size if necessary before being sent to the stored procedure and at most BufferLength bytes of data are expected to be returned.

Setting ColumnSize to 0 is not recommended unless it is required; it causes CLI to perform costly checking for the length of the data at run time.

Descriptors

How a parameter is bound is determined by fields of the APD and IPD. The arguments in SQLBindParameter() are used to set those descriptor fields. The fields can also be set by the SQLSetDescField() functions, although SQLBindParameter() is more efficient to use because the application does not have to obtain a descriptor handle to call SQLBindParameter().

Note: Calling SQLBindParameter() for one statement can affect other statements. This occurs when the APD associated with the statement is explicitly allocated and is also associated with other statements. Because SQLBindParameter() modifies the fields of the APD, the modifications apply to all statements with which this descriptor is associated. If this is not the required behavior, the application should dissociate the descriptor from the other statements before calling SQLBindParameter().

Conceptually, SQLBindParameter() performs the following steps in sequence:

Calls SQLGetStmtAttr() to obtain the APD handle.
Calls SQLGetDescField() to get the SQL_DESC_COUNT header field from the APD, and if the value of the ParameterNumber argument exceeds the value of SQL_DESC_COUNT, calls SQLSetDescField() to increase the value of SQL_DESC_COUNT to ParameterNumber.
Calls SQLSetDescField() multiple times to assign values to the following fields of the APD:
- Sets SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE to the value of ValueType, except that if ValueType is one of the concise identifiers of a datetime, it sets SQL_DESC_TYPE to SQL_DATETIME, sets SQL_DESC_CONCISE_TYPE to the concise identifier, and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime subcode.
- Sets the SQL_DESC_DATA_PTR field to the value of ParameterValue.
- Sets the SQL_DESC_OCTET_LENGTH_PTR field to the value of StrLen_or_Ind.
- Sets the SQL_DESC_INDICATOR_PTR field also to the value of StrLen_or_Ind.
The StrLen_or_Ind parameter specifies both the indicator information and the length for the parameter value.
Calls SQLGetStmtAttr() to obtain the IPD handle.
Calls SQLGetDescField() to get the IPD's SQL_DESC_COUNT field, and if the value of the ParameterNumber argument exceeds the value of SQL_DESC_COUNT, calls SQLSetDescField() to increase the value of SQL_DESC_COUNT to ParameterNumber.
Calls SQLSetDescField() multiple times to assign values to the following fields of the IPD:
- Sets SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE to the value of ParameterType, except that if ParameterType is one of the concise identifiers of a datetime, it sets SQL_DESC_TYPE to SQL_DATETIME, sets SQL_DESC_CONCISE_TYPE to the concise identifier, and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime subcode.
- Sets one or more of SQL_DESC_LENGTH, SQL_DESC_PRECISION, and SQL_DESC_SCALE as appropriate for ParameterType.

If the call to SQLBindParameter() fails, the content of the descriptor fields that it would have set in the APD are undefined, and the SQL_DESC_COUNT field of the APD is unchanged. In addition, the SQL_DESC_LENGTH, SQL_DESC_PRECISION, SQL_DESC_SCALE, and SQL_DESC_TYPE fields of the appropriate record in the IPD are undefined and the SQL_DESC_COUNT field of the IPD is unchanged.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 2. 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_TYPE_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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
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	Invalid argument value.	The argument ParameterValuePtr was a null pointer and the argument StrLen_or_IndPtr was a null pointer, and InputOutputType is not SQL_PARAM_OUTPUT.
HY010	Function sequence error.	Function was called after SQLExecute() or SQLExecDirect() had returned SQL_NEED_DATA, but data has not been sent for all data-at-execution parameters.
HY013	Unexpected memory handling error.	Db2 CLI was unable to access memory required to support execution or completion of the function.
HY021	Inconsistent descriptor information	The descriptor information checked during a consistency check was not consistent.
HY090	Invalid string or buffer length.	The value specified for the argument BufferLength was less than 0.
HY093	Invalid parameter number.	The value specified for the argument ValueType was less than 1 or greater than the maximum number of parameters supported by the server.
HY094	Invalid scale value.	The value specified for ParameterType was either SQL_DECIMAL or SQL_NUMERIC and the value specified for DecimalDigits was less than 0 or greater than the value for the argument ParamDef (precision). The value specified for ParameterType was SQL_C_TYPE_TIMESTAMP and the value for ParameterType was either SQL_CHAR or SQL_VARCHAR and the value for DecimalDigits was less than 0 or greater than 9. The value specified for ParameterType was SQL_C_TIMESTAMP_EXT and the value for ParameterType was either SQL_CHAR or SQL_VARCHAR and the value for DecimalDigits was less than 0 or greater than 12.
HY104	Invalid precision value.	The value specified for ParameterType was either SQL_DECIMAL or SQL_NUMERIC and the value specified for ParamDef was less than 1.
HY105	Invalid parameter type.	InputOutputType is not one of SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, or SQL_PARAM_INPUT_OUTPUT.
HYC00	Driver not capable.	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 CLI or the data source.

Restrictions

SQLBindParameter() replaces the deprecated SQLSetParam() API in CLI V5 and later, and ODBC 2.0 and later.

An additional value for StrLen_or_IndPtr, SQL_DEFAULT_PARAM, was introduced in ODBC 2.0, to indicate that the procedure is to use the default value of a parameter, rather than a value sent from the application. Since Db2 stored procedure arguments do not support default values, specification of this value for StrLen_or_IndPtr argument will result in an error when the CALL statement is executed since the SQL_DEFAULT_PARAM value will be considered an invalid length.

ODBC 2.0 also introduced the SQL_LEN_DATA_AT_EXEC(length) macro to be used with the StrLen_or_IndPtr argument. The macro is used to specify the sum total length of the entire data that would be sent for character or binary C data via the subsequent SQLPutData() calls. Since the Db2 ODBC driver does not need this information, the macro is not needed. An ODBC application calls SQLGetInfo() with the SQL_NEED_LONG_DATA_LEN option to check if the driver needs this information. The Db2 ODBC driver will return 'N' to indicate that this information is not needed by SQLPutData().

Example

  SQLSMALLINT parameter1 = 0;

  /* ... */
  
  cliRC = SQLBindParameter(hstmt,
                           1,
                           SQL_PARAM_INPUT,
                           SQL_C_SHORT,
                           SQL_SMALLINT,
                           0,
                           0,
                           &parameter1,
                           0,
                           NULL);