SQLParamOptions - Specify an input array for a parameter
SQLParamOptions()
provides
the ability to set multiple values for each parameter set by SQLBindParameter()
.
This allows the application to run INSERT, UPDATE, DELETE, and MERGE
statements providing multiple sets of arguments on a single call to SQLExecute()
or SQLExecDirect()
.
Syntax
SQLRETURN SQLParamOptions (SQLHSTMT StatementHandle,
SQLINTEGER Crow,
SQLINTEGER *FetchOffsetPtr);
Function arguments
Data type | Argument | Use | Description |
---|---|---|---|
SQLHSTMT | StatementHandle | Input | Statement handle. |
SQLINTEGER | 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. |
SQLINTEGER * | FetchOffsetPtr | Output (deferred) | Not currently used. |
Usage
This function can be
used with SQLBindParameter()
to set up a multiple-row
INSERT statement, or to process UPDATE, DELETE, and MERGE statements
with multiple sets of parameter values. It is assumed that the storage
containing the data which represents the parameters is allocated and
available to CLI. This data can be organized in a either a row-wise
or a column-wise fashion. Row-wise binding is the term used for the
case where all the data for the first row is contiguous, followed
by all the data for the next row, and so on. Column-wise binding
is used to describe the case where the data for each individual parameter
marker is contiguous. For this case, each parameter marker's
data can be provided in an array that does not need to be contiguous
with data for the other parameter markers. The SQLBindParameter()
function
should be used to bind all of the input parameter types and lengths.
Here is an example of the set up necessary for a multiple-row
statement with row-wise binding. In this case, the addresses provided
on SQLBindParameter()
are used to reference the first
row of data. All subsequent rows of data are referenced by incrementing
those addresses by the length of the entire row.For instance, the
application intends to insert 100 rows of data into a table, and each
row contains a 4-byte integer value, followed by a 10-byte character
value. To do this, the application allocates 1400 bytes of storage,
and fills each 14-byte piece of storage with the appropriate data
for the row.
Also, the indicator pointer passed on the SQLBindParameter()
must
reference an 800-byte piece of storage (100 rows x 2 columns x 4 bytes
for each indicator). The indicator array is used to pass in NULL values
for the corresponding parameter marker and row. This storage is also
row-wise, so the first 8 bytes are the 2 indicators for the first
row, followed by the 2 indicators for the next row, and so on. The SQLParamOptions()
function
is used by the application to specify how many rows of pararmeter
values are provided.
The maximum number of database
rows that can be specified in a multiple-row insert operation is 32,000.
Therefore, SQLParamOptions
allows only 32,767 rows
to be specified at a time. Any additional rows need to be rebound
and re-executed.
SQLSetStmtAttr ()
provides
an alternative means of setting the number of rows for a multiple-row
statement using the SQL_ATTR_PARAMSET_SIZE option.
Return codes
- SQL_SUCCESS
- SQL_SUCCESS_WITH_INFO
- SQL_ERROR
- SQL_INVALID_HANDLE
Error conditions
SQLSTATE | Description | Explanation |
---|---|---|
HY009 | Argument value that is not valid | The value in the argument Crow is less than 1. |
HY010 | Function sequence error | The function is called while in a
data-at-processing (SQLParamData() , SQLPutData() )
operation. |
Restrictions
None.