SQLBindParameter - Bind a parameter marker to a buffer

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.

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

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() 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 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

Error conditions

References