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 insert multiple rows into a table on a single call to SQLExecute() or SQLExecDirect().

 

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

This function can be used with SQLBindParameter() to set up a multiple-row INSERT statement. In order to accomplish this, the application must allocate storage for all of the data being inserted. This data must be organized in a row-wise fashion. This means that all of the data for the first row is contiguous, followed by all the data for the next row, and so on. The SQLBindParameter() function should be used to bind all of the input parameter types and lengths. In the case of a multiple-row INSERT statement, 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. This is used to pass in any null indicator values. 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 are inserted on the next processing of an INSERT statement using the statement handle. The INSERT statement must be of the multiple-row form. For example: 

 INSERT INTO CORPDATA.NAMES ? ROWS VALUES(?, ?)

The maximum number of database rows that can be specified in a multiple-row insert operation is 32 000. Therefore, SQLParamOptions allows only 32 000 rows to be specified at a time. Any additional rows need to be rebound and re-executed.

 

Return codes

 

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.

 

References

 

Parent topic:

DB2 UDB CLI functions