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.

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:

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

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.

Restrictions

None.

Example

Refer to Array Input Example.

References


[ Top of Page | Previous Page | Next Page ]