Call Level Interface Guide and Reference

SQLBindParameter - Bind A Parameter Marker to a Buffer or LOB Locator

Purpose

Specification:

DB2 CLI 2.1

ODBC 2.0

SQLBindParameter() is used to associate (bind) parameter markers in an SQL statement 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 may 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 an application storage to a parameter of a stored procedure CALL statement where the parameter may be input, output or both. This function is essentially an extension of SQLSetParam().

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 21. 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, actual data value for the parameter is sent to the server: the ParameterValuePtr buffer must contain valid input data value(s); 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 executed, actual data value for the parameter is sent to the server: the ParameterValuePtr buffer must contain valid input data value(s); 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 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_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_TINYINT SQL_C_UBIGINT 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_BLOB SQL_BLOB_LOCATOR SQL_CHAR SQL_CLOB SQL_CLOB_LOCATOR SQL_DBCLOB SQL_DBCLOB_LOCATOR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_GRAPHIC SQL_INTEGER SQL_LONGVARBINARY SQL_LONGVARCHAR SQL_LONGVARGRAPHIC SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_VARBINARY SQL_VARCHAR SQL_VARGRAPHIC 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.
SQLUINTEGER	ColumnSize	input	Precision of the corresponding parameter marker. If ParameterType denotes: A binary or single byte character string (e.g. SQL_CHAR, SQL_BLOB), this is the maximum length in bytes for this parameter marker. A double byte character string (e.g. SQL_GRAPHIC), this is the maximum length in double-byte characters for this parameter. SQL_DECIMAL, SQL_NUMERIC, this is the maximum decimal precision. Otherwise, this argument is ignored. If the column size is not known in advance then the application can set this value to zero. See ColumnSize Not Known in Advance for more information.
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) and/or output (deferred)	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.
SQLINTEGER	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.
SQLINTEGER *	StrLen_or_IndPtr	input (deferred) and/or output (deferred)	- 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.
SQLINTEGER *	StrLen_or_IndPtr (cont)	input (deferred) and/or output (deferred)	If SQLParamOptions() is used 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. - 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 SQL_NO_TOTAL if the number of bytes available to return cannot be determined.

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() (or SQLSetParam()) is used to bind the application storage area to the parameter marker.

• A LOB value from the database server (by specifying a LOB locator).

  SQLBindParameter() (or SQLSetParam()) is used to bind a LOB locator to the parameter marker. The LOB value itself is supplied by the database server, so only the LOB locator is transferred between the database server and the application.

  An application can use a locator with SQLGetSubString(), SQLGetPosition() or SQLGetLength(). SQLGetSubString() can either return another locator, or the data itself. All locators remain valid until the end of the transaction in which they were created (even when the cursor moves to another row, or until it is freed using the FREE LOCATOR statement.

• A file (within the applications environment) containing a LOB value.

  SQLBindFileToParam() is used to bind a file to a LOB parameter marker. When SQLExecDirect() is executed, DB2 CLI will transfer the contents of the file directly to the database server.

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

SQLBindParameter() (or SQLSetParam()) can be called before SQLPrepare() if the columns in the result set are known; otherwise, the attributes of the result set can be obtained after the statement is prepared.

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

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

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

After the SQL statement has been executed, and the results processed, the application may wish 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() should 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 will occur.

An application can pass the value for a parameter either in the ParameterValuePtr buffer or with one or more calls to SQLPutData(). In latter case, these parameters are data-at-execution parameters. The application informs DB2 CLI of a data-at-execution parameter by placing the SQL_DATA_AT_EXEC value in the StrLen_or_IndPtr buffer. 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.

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.

SQLBindParameter() essentially extends the capability of the 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 SQLParamOptions() is used in conjunction with SQLBindParameter(). SQLSetParam() can still be used to bind single element application variables to parameter markers that are not part of a stored procedure CALL statement.

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 may 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, DB2 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 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 CLI discards the output value. If the data source does not return a value for an output parameter, DB2 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.

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

An application can pass the value for a parameter either in the ParameterValuePtr buffer or with one or more calls to SQLPutData(). In latter case, these parameters are data-at-execution parameters. The application informs DB2 CLI of a data-at-execution parameter by placing the SQL_DATA_AT_EXEC value in the StrLen_or_IndPtr buffer. 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.

When SQLBindParameter() is used to bind an application variable to an output parameter for a stored procedure, DB2 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;

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

ColumnSize Not Known in Advance

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

In previous releases, DB2 CLI would use the maximum size for the column's datatype when ColumnSize was set to 0. In some cases this resulted in the allocation of unnecessarily large blocks of memory. As of version 6 this behavior has changed.

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

Input Parameter

A 0 ColumnSize means that DB2 CLI will use the actual data length of the input value - determined at the time the statement is executed - as the size of the column or the stored procedure parameter. DB2 CLI will perform necessary conversion using this size.

Output Parameter (Stored Procedures only)

A 0 ColumnSize means that DB2 CLI will use BufferLength as the parameter's size. Note that this means that the store procedure must not return more than BufferLength bytes of data or a truncation error will occur.

For Input-output parameter (Store Procedure only)

A 0 ColumnSize means that DB2 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 is expected to be returned.

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

Parameter Binding Offsets

When an application needs to change parameter bindings it can call SQLBindParameter() a second time. This will change the bound parameter buffer address and the corresponding length/indicator buffer address used.

Instead of multiple calls to SQLBindParameter(), DB2 CLI also supports parameter binding offsets. Rather than re-binding each time, an offset can be used to specify new buffer and length/indicator addresses which will be used in a subsequent call to SQLExecute() or SQLExecDirect(). This cannot be used with column wise array inserts, but will work whether the application binds parameters individually or using an array.

See Parameter Binding Offsets for the list of steps required to use an offset.

Descriptors

How a parameter is bound is determined by fields of the APDs and IPDs. 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 ARD 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 this descriptor from the other statements before calling SQLBindParameter().

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

1. Calls SQLGetStmtAttr() to obtain the APD handle.
2. Calls SQLGetDescField() to get the APD's SQL_DESC_COUNT field, and if the value of the ColumnNumber argument exceeds the value of SQL_DESC_COUNT, calls SQLSetDescField() to increase the value of SQL_DESC_COUNT to ColumnNumber.
3. 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 or interval subtype, it sets SQL_DESC_TYPE to SQL_DATETIME or SQL_INTERVAL respectively, sets SQL_DESC_CONCISE_TYPE to the concise identifier, and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime or interval 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.

4. Calls SQLGetStmtAttr() to obtain the IPD handle.
5. Calls SQLGetDescField() to get the IPD's SQL_DESC_COUNT field, and if the value of the ColumnNumber argument exceeds the value of SQL_DESC_COUNT, calls SQLSetDescField to increase the value of SQL_DESC_COUNT to ColumnNumber.
6. 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 or interval subtype, it sets SQL_DESC_TYPE to SQL_DATETIME or SQL_INTERVAL respectively, sets SQL_DESC_CONCISE_TYPE to the concise identifier, and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime or interval subcode.
   • Sets one or more of SQL_DESC_LENGTH, SQL_DESC_PRECISION, and SQL_DESC_DATETIME_INTERVAL_PRECISION, as appropriate for ParameterType.
   • Sets SQL_DESC_SCALE to the value of DecimalDigits.

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 22. 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 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 have 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_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 6.
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.	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.

Restrictions

In DB2 CLI v5 and ODBC 2.0, this function has replaced SQLSetParam().

A new 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 have the concept of 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().

CLI Sample tbread.c

The example shown below binds a variety of data types bound to a set of parameters. For an additional example refer to Stored Procedure Example.

(The complete sample tbread.c is also available here .)

 
/* From the CLI sample TBREAD.C */
/* ... */
 
    /* bind divisionParam to the statement */
    printf("    Bind divisionParam to the statement\n");
    printf("        %s\n", stmt);
    sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                             SQL_CHAR, 15, 0, divisionParam, 15, NULL);
    
 

References

• SQLExecDirect - Execute a Statement Directly
• SQLExecute - Execute a Statement
• SQLParamData - Get Next Parameter For Which A Data Value Is Needed
• SQLParamOptions - Specify an Input Array for a Parameter
• SQLPutData - Passing Data Value for A Parameter