SQLParamOptions - Specify an input array for a parameter

Start of change 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(). End of change

Syntax

SQLRETURN   SQLParamOptions  (SQLHSTMT          StatementHandle,   
                              SQLINTEGER        Crow,             
                              SQLINTEGER        *FetchOffsetPtr);

Function arguments

Table 1. SQLParamOptions 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

Start of change 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. End of change

Start of change 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. End of change

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.

Start of change 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. End of change

Start of change SQLSetStmtAttr () provides an alternative means of setting the number of rows for a multiple-row statement using the SQL_ATTR_PARAMSET_SIZE option. End of change

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Error conditions

Table 2. SQLParamOptions SQLSTATEs
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.