Call Level Interface Guide and Reference

SQLParamOptions - Specify an Input Array for a Parameter

Status of this Function since DB2 CLI Version 5
Note:
In ODBC version 3, SQLParamOptions() has been deprecated and replaced with SQLSetStmtAttr(); see SQLSetStmtAttr - Set Options Related to a Statement for more information.
Although this version of DB2 CLI continues to support SQLParamOptions(), we recommend that you begin using SQLSetStmtAttr() in your DB2 CLI programs so that they conform to the latest standards.
See DB2 CLI Functions Deprecated for Version 5 for more information on this and other deprecated functions.

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLParamOptions() provides the ability to set multiple values for each parameter set by SQLBindParameter(). This allows the application to perform batched processing of the same SQL statement with one set of prepare, execute and SQLBindParameter() calls.

Syntax

SQLRETURN   SQLParamOptions  (SQLHSTMT          StatementHandle,   /* hstmt */
                              SQLUINTEGER       Crow,              /* crow */
                              SQLUINTEGER  FAR  *FetchOffsetPtr);  /* pirow */

Function Arguments

Table 134. SQLParamOptions Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle Input Statement handle.
SQLUINTEGER Crow Input Number of values for each parameter. If this is greater than 1, then the rgbValue argument in SQLBindParameter() points to an array of parameter values, and pcbValue points to an array of lengths.
SQLUINTEGER * FetchOffsetPtr Output (deferred) Pointer to the buffer for the current parameter array index. As each set of parameter values is processed, FetchOffsetPtr is set to the array index of that set. If a statement fails, FetchOffsetPtr can be used to determine how many statements were successfully processed. Nothing is returned if the FetchOffsetPtr pointer is NULL.

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLUINTEGER	Crow	Input	Number of values for each parameter. If this is greater than 1, then the `rgbValue` argument in `SQLBindParameter()` points to an array of parameter values, and `pcbValue` points to an array of lengths.
SQLUINTEGER *	FetchOffsetPtr	Output (deferred)	Pointer to the buffer for the current parameter array index. As each set of parameter values is processed, `FetchOffsetPtr` is set to the array index of that set. If a statement fails, `FetchOffsetPtr` can be used to determine how many statements were successfully processed. Nothing is returned if the `FetchOffsetPtr` pointer is NULL.

Usage

As a statement executes, FetchOffsetPtr is set to the index of the current array of parameter values. If an error occurs during execution for a particular element in the array, execution halts and SQLExecute(), SQLExecDirect() or SQLParamData() returns SQL_ERROR.

The contents of FetchOffsetPtr have the following uses:

When SQLParamData() returns SQL_NEED_DATA, the application can access the value in FetchOffsetPtr to determine which set of parameters is being assigned values.
When SQLExecute() or SQLExecDirect() returns an error, the application can access the value in FetchOffsetPtr to find out which element in the parameter value array failed.
When SQLExecute(), SQLExecDirect(), SQLParamData(), or SQLPutData() succeeds, the value in FetchOffsetPtr is set to the input value in Crow to indicate that all elements of the array have been processed successfully.

The output argument FetchOffsetPtr indicates how many sets of parameters were successfully processed. If the statement processed is a query, FetchOffsetPtr indicates the array index associated with the current result set returned by SQLMoreResults() and is incremented each time SQLMoreResults() is called.

In environments where the underlying support allows Compound SQL (DB2 Universal Database, or DRDA environments with DB2 Connect V2.3), all the data in the array(s) together with the execute request are packaged together as one network flow.

When connected to DB2 Universal Database V2.1 or later, the application has the option of choosing ATOMIC or NOT ATOMIC Compound SQL. With ATOMIC Compound SQL (which is the default), either all the elements of the array are processed successfully, or none at all. With NOT ATOMIC Compound SQL, execution will continue even if an error is detected with one of the intermediate array elements. The application can choose to select the type of Compound SQL by setting the SQL_ATTR_PARAMOPT_ATOMIC attribute of the SQLSetStmtAttr() call.

For DRDA environments, the underlying Compound SQL support is always NOT ATOMIC COMPOUND SQL (and therefore the default in DRDA scenarios).

If the application is not sure what the current value of the SQL_ATTR_PARAMOPT_ATOMIC attribute, it should call SQLGetStmtOption().

When connected to servers that do not support compound SQL, DB2 CLI prepares the statement, and executes it repeatedly for the array of parameter markers.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 135. SQLParamOptions SQLSTATEs

SQLSTATE Description Explanation
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY010 Function sequence error. The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
HY107 Row value out of range. The value in the argument Crow was less than 1.

SQLSTATE	Description	Explanation
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY010	Function sequence error.	The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
HY107	Row value out of range.	The value in the argument `Crow` was less than 1.

Restrictions

None.

Example

Refer to Array Input Example.

References

[ Top of Page | Previous Page | Next Page ]