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 application storage to a parameter of a stored procedure where the parameter can be input, output, or both.
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
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:
|
SQLSMALLINT | ValueType | Input | C data type of the parameter. The following
types are supported:
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:
|
SQLINTEGER | ColumnSize | Input | Precision of the corresponding parameter
marker.
|
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 |
|
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. To specify an extended indicator value for a parameter marker, this storage location must contain SQL_UNASSIGNED or SQL_DEFAULT_PARAM. The SQL_ATTR_EXTENDED_INDICATORS connection attribute must be set to SQL_TRUE for either of these values to be honored. 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 |
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 as the corresponding ? appears in the statement text, 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® for i 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 for i 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 for i CLI discards the output value. If the data source does not return a value for an output parameter, Db2 for i 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()
orSQLExecute()
call in the same procedure scope as theSQLBindParameter()
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 for
i 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;
For decimal floating point data types, a precision of 32, 64, or 128 can be specified by using the default symbolic C data type constants. For example, to specify a decimal floating point data type with a precision of 128 bytes, ValueType can be set to SQL_C_DECIMAL128.
Return codes
- SQL_SUCCESS
- SQL_SUCCESS_WITH_INFO
- SQL_ERROR
- SQL_INVALID_HANDLE
Error conditions
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 for i 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 for i 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 12. |
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 for
i 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 for i CLI or the data source. |