| Specification: | DB2 CLI 6.0 | ODBC 3.0 |
SQLBulkOperations() is used to perform the following operations on a keyset driven cursor:
Syntax
SQLRETURN SQLBulkOperations (
SQLHSTMT StatementHandle,
SQLSMALLINT Operation);
Function Arguments
Table 27. SQLBulkOperations Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | Input | Statement handle. |
| SQLSMALLINT | Operation | Input | Operation to perform:
|
Usage
An application uses SQLBulkOperations() to perform the following operations on the base table or view that corresponds to the current query in a keyset driven cursor:
A generic application should first ensure that the required bulk operation is supported. To do so, it can call SQLGetInfo() with an InfoType of SQL_DYNAMIC_CURSOR_ATTRIBUTES1 and SQL_DYNAMIC_CURSOR_ATTRIBUTES2 (to see if SQL_CA1_BULK_UPDATE_BY_BOOKMARK is returned, for instance).
After a call to SQLBulkOperations(), the block cursor position is undefined. The application has to call SQLFetchScroll() to set the cursor position. An application should only call SQLFetchScroll() with a FetchOrientation argument of SQL_FETCH_FIRST, SQL_FETCH_LAST, SQL_FETCH_ABSOLUTE, or SQL_FETCH_BOOKMARK. The cursor position is undefined if the application calls SQLFetch(), or SQLFetchScroll() with a FetchOrientation argument of SQL_FETCH_PRIOR, SQL_FETCH_NEXT, or SQL_FETCH_RELATIVE.
A column can be ignored in bulk operations (calls to SQLBulkOperations()). To do so, call SQLBindCol() and set the column length/indicator buffer (StrLen_or_IndPtr) to SQL_COLUMN_IGNORE. This does not apply to SQL_DELETE_BY_BOOKMARK bulk operation. See SQLBindCol - Bind a Column to an Application Variable or LOB Locator for more information.
It is not necessary for the application to set the SQL_ATTR_ROW_OPERATION_PTR statement attribute when calling SQLBulkOperations() because rows cannot be ignored when performing bulk operations with this function.
The buffer pointed to by the SQL_ATTR_ROWS_FETCHED_PTR statement attribute contains the number of rows affected by a call to SQLBulkOperations().
When the Operation argument is SQL_ADD or SQL_UPDATE_BY_BOOKMARK, and the select-list of the query specification associated with the cursor contains more than one reference to the same column, an error is generated.
To insert data with SQLBulkOperations(), an application performs the following sequence of steps:
| Note: | The size of the array pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute should either be equal to SQL_ATTR_ROW_ARRAY_SIZE or SQL_ATTR_ROW_STATUS_PTR should be a null pointer. |
If an application binds column 0 before calling SQLBulkOperations() with an Operation argument of SQL_ADD, CLI will update the bound column 0 buffers with the bookmark values for the newly inserted row. For this to occur, the application must have set SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE before executing the statement.
Long data can be added in parts by SQLBulkOperations() using calls to SQLParamData() and SQLPutData(). For more information, see "Providing Long Data for Bulk Inserts and Updates" later in this section.
It is not necessary for the application to call SQLFetch() or SQLFetchScroll() before calling SQLBulkOperations().
If SQLBulkOperations() is called with an Operation argument of SQL_ADD on a cursor that contains duplicate columns, an error is returned.
Performing Bulk Updates Using Bookmarks
To perform bulk updates using bookmarks with SQLBulkOperations(), an application performs the following steps in sequence:
Calls SQLBindCol() to bind the data that it wants to update. The data is bound to an array with a size equal to the value of SQL_ATTR_ROW_ARRAY_SIZE. It also calls SQLBindCol() to bind column 0 (the bookmark column).
| Note: | The size of the array pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute should either be equal to SQL_ATTR_ROW_ARRAY_SIZE or SQL_ATTR_ROW_STATUS_PTR should be a null pointer. |
| Note: | If the application has set the SQL_ATTR_ROW_STATUS_PTR statement attribute, then it can inspect this array to see the result of the operation. |
Optionally calls SQLBulkOperations(StatementHandle, SQL_FETCH_BY_BOOKMARK) to fetch data into the bound application buffers to verify that the update has occurred.
If data has been updated, CLI changes the value in the row status array for the appropriate rows to SQL_ROW_UPDATED.
Bulk updates performed by SQLBulkOperations() can include long data by using calls to SQLParamData() and SQLPutData(). For more information, see "Providing Long Data for Bulk Inserts and Updates" later in this section.
Bookmarks in DB2 CLI do not persist across cursors. This means that an application cannot use bookmarks that it has stored from a previous cursor. Instead, it has to call SQLFetch() or SQLFetchScroll() to retrieve the bookmarks before updating by bookmarks.
If SQLBulkOperations() is called with an Operation argument of SQL_UPDATE_BY_BOOKMARK on a cursor that contains duplicate columns, an error is returned.
Performing Bulk Fetches Using Bookmarks
To perform bulk fetches using bookmarks with SQLBulkOperations(), an application performs the following steps in sequence:
| Note: | The size of the array pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute should either be equal to SQL_ATTR_ROW_ARRAY_SIZE, or the SQL_ATTR_ROW_STATUS_PTR statement attribute should be a null pointer. |
Bookmarks in DB2 CLI do not persist across cursors. This means that an application cannot use bookmarks that it has stored from a previous cursor. Instead, it has to call SQLFetch() or SQLFetchScroll() to retrieve the bookmarks before updating by bookmarks.
Performing Bulk Deletes Using Bookmarks
To perform bulk deletes using bookmarks with SQLBulkOperations(), an application performs the following steps in sequence:
| Note: | The size of the array pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute should either be equal to SQL_ATTR_ROW_ARRAY_SIZE, or the SQL_ATTR_ROW_STATUS_PTR statement attribute should be a null pointer. |
Bookmarks in DB2 CLI do not persist across cursors. This means that an application cannot use bookmarks that it has stored from a previous cursor. Instead, it has to call SQLFetch() or SQLFetchScroll() to retrieve the bookmarks before updating by bookmarks.
Providing Long Data for Bulk Inserts and Updates
Long data can be provided for bulk inserts and updates performed by calls to SQLBulkOperations(). To insert or update long data, an application performs the following steps in addition to the steps described in the "Performing Bulk Inserts" and "Performing Bulk Updates Using Bookmarks" sections earlier in this section.
The application places the result of the SQL_LEN_DATA_AT_EXEC(length) macro in the *StrLen_or_IndPtr buffer. If the SQL data type of the column is SQL_LONGVARBINARY, SQL_LONGVARCHAR, or a long, data source-specific data type and CLI returns "Y" for the SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo(), length is the number of bytes of data to be sent for the parameter; otherwise, it must be a non-negative value and is ignored.
| Note: | Although data-at-execution parameters are similar to data-at-execution
columns, the value returned by SQLParamData() is different for
each.
Data-at-execution columns are columns in a rowset for which data will be sent with SQLPutData() when a row is updated or inserted with SQLBulkOperations(). They are bound with SQLBindCol(). The value returned by SQLParamData() is the address of the row in the *TargetValuePtr buffer that is being processed. |
If the operation is canceled, or an error occurs in SQLParamData() or SQLPutData(), after SQLBulkOperations() returns SQL_NEED_DATA, and before data is sent for all data-at-execution columns, the application can call only SQLCancel(), SQLGetDiagField(), SQLGetDiagRec(), SQLGetFunctions(), SQLParamData(), or SQLPutData() for the statement or the connection associated with the statement. If it calls any other function for the statement or the connection associated with the statement, the function returns SQL_ERROR and SQLSTATE HY010 (Function sequence error).
If the application calls SQLCancel() while CLI still needs data for data-at-execution columns, CLI cancels the operation. The application can then call SQLBulkOperations() again; canceling does not affect the cursor state or the current cursor position.
Row Status Array
The row status array contains status values for each row of data in the rowset after a call to SQLBulkOperations(). The status values in this array are set after a call to:
This array is initially populated by a call to SQLBulkOperations() if SQLFetch() or SQLFetchScroll() has not been called prior to SQLBulkOperations(). This array is pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute. The number of elements in the row status arrays must equal the number of rows in the rowset (as defined by the SQL_ATTR_ROW_ARRAY_SIZE statement attribute). For information about this row status array, see SQLFetch - Fetch Next Row.
Return Codes
Diagnostics
Table 28. SQLBulkOperations SQLSTATEs
| SQLSTATE | Description | Explanation |
|---|---|---|
| 01000 | Warning. | Informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
| 01004 | Data truncated. | The Operation argument was SQL_FETCH_BY_BOOKMARK, and string or binary data returned for a column or columns with a data type of SQL_C_CHAR or SQL_C_BINARY resulted in the truncation of non-blank character or non-NULL binary data. |
| 01S07 | Invalid conversion. | The Operation argument was SQL_FETCH_BY_BOOKMARK, the data type
of the application buffer was not SQL_C_CHAR or SQL_C_BINARY, and the data
returned to application buffers for one or more columns was truncated.
(For numeric C data types, the fractional part of the number was
truncated. For time, timestamp, and interval C data types containing a
time component, the fractional portion of the time was truncated.)
(Function returns SQL_SUCCESS_WITH_INFO.) |
| 07006 | Restricted data type attribute violation. | The Operation argument was SQL_FETCH_BY_BOOKMARK, and the data
value of a column in the result set could not be converted to the data type
specified by the TargetType argument in the call to SQLBindCol().
The Operation argument was SQL_UPDATE_BY_BOOKMARK or SQL_ADD, and the data value in the application buffers could not be converted to the data type of a column in the result set. |
| 07009 | Invalid descriptor index. | The argument Operation was SQL_ADD and a column was bound with a column number greater than the number of columns in the result set. |
| 21S02 | Degree of derived table does not match column list. | The argument Operation was SQL_UPDATE_BY_BOOKMARK; and no columns were updatable because all columns were either unbound, read-only, or the value in the bound length/indicator buffer was SQL_COLUMN_IGNORE. |
| 22001 | String data right truncation. | The assignment of a character or binary value to a column in the result set resulted in the truncation of non-blank (for characters) or non-null (for binary) characters or bytes. |
| 22003 | Numeric value out of range. | The Operation argument was SQL_ADD or SQL_UPDATE_BY_BOOKMARK,
and the assignment of a numeric value to a column in the result set caused the
whole (as opposed to fractional) part of the number to be truncated.
The argument Operation was SQL_FETCH_BY_BOOKMARK, and returning the numeric value for one or more bound columns would have caused a loss of significant digits. |
| 22007 | Invalid datetime format. | The Operation argument was SQL_ADD or SQL_UPDATE_BY_BOOKMARK,
and the assignment of a date or timestamp value to a column in the result set
caused the year, month, or day field to be out of range.
The argument Operation was SQL_FETCH_BY_BOOKMARK, and returning the date or timestamp value for one or more bound columns would have caused the year, month, or day field to be out of range. |
| 22008 | Date/time field overflow. | The Operation argument was SQL_ADD or SQL_UPDATE_BY_BOOKMARK,
and the performance of datetime arithmetic on data being sent to a column in
the result set resulted in a datetime field (the year, month, day, hour,
minute, or second field) of the result being outside the permissible range of
values for the field, or being invalid based on the natural rules for
datetimes based on the Gregorian calendar.
The Operation argument was SQL_FETCH_BY_BOOKMARK, and the performance of datetime arithmetic on data being retrieved from the result set resulted in a datetime field (the year, month, day, hour, minute, or second field) of the result being outside the permissible range of values for the field, or being invalid based on the natural rules for datetimes based on the Gregorian calendar. |
| 22015 | Interval field overflow. | The Operation argument was SQL_ADD or SQL_UPDATE_BY_BOOKMARK,
and the assignment of an exact numeric or interval C type to an interval SQL
data type caused a loss of significant digits.
The Operation argument was SQL_ADD or SQL_UPDATE_BY_BOOKMARK; when assigning to an interval SQL type, there was no representation of the value of the C type in the interval SQL type. The Operation argument was SQL_FETCH_BY_BOOKMARK, and assigning from an exact numeric or interval SQL type to an interval C type caused a loss of significant digits in the leading field. The Operation argument was SQL_FETCH_BY_BOOKMARK; when assigning to an interval C type, there was no representation of the value of the SQL type in the interval C type. |
| 22018 | Invalid character value for cast specification. | The Operation argument was SQL_FETCH_BY_BOOKMARK; the C
type was an exact or approximate numeric, a datetime, or an interval data
type; the SQL type of the column was a character data type; and the
value in the column was not a valid literal of the bound C type.
The argument Operation was SQL_ADD or SQL_UPDATE_BY_BOOKMARK; the SQL type was an exact or approximate numeric, a datetime, or an interval data type; the C type was SQL_C_CHAR; and the value in the column was not a valid literal of the bound SQL type. |
| 23000 | Integrity constraint violation. | The Operation argument was SQL_ADD, SQL_DELETE_BY_BOOKMARK, or
SQL_UPDATE_BY_BOOKMARK, and an integrity constraint was violated.
The Operation argument was SQL_ADD, and a column that was not bound is defined as NOT NULL and has no default. The Operation argument was SQL_ADD, the length specified in the bound StrLen_or_IndPtr buffer was SQL_COLUMN_IGNORE, and the column did not have a default value. |
| 24000 | Invalid cursor state. | The StatementHandle was in an executed state but no result set was associated with the StatementHandle. |
| 40001 | Serialization failure. | The transaction was rolled back due to a resource deadlock with another transaction. |
| 40003 | Statement completion unknown. | The associated connection failed during the execution of this function and the state of the transaction cannot be determined. |
| 42000 | Syntax error or access violation. | DB2 CLI was unable to lock the row as needed to perform the operation requested in the Operation argument. |
| 44000 | WITH CHECK OPTION violation. | The Operation argument was SQL_ADD or SQL_UPDATE_BY_BOOKMARK, and the insert or update was performed on a viewed table or a table derived from the viewed table which was created by specifying WITH CHECK OPTION, such that one or more rows affected by the insert or update will no longer be present in the viewed table. |
| HY000 | General error. | An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLGetDiagRec() in the *MessageText buffer describes the error and its cause. |
| HY001 | Memory allocation error. | DB2 CLI was unable to allocate memory required to support execution or completion of the function. |
| HY008 | Operation canceled. | Asynchronous processing was enabled for the
StatementHandle. The function was called and before it
completed execution, SQLCancel() was called on
the StatementHandle. Then the function was called again on the
StatementHandle.
The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application. |
| 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. An asynchronously executing function (not this one) was called for the
StatementHandle and was still executing when this function was
called.
|
| HY011 | Operation invalid at this time. | The SQL_ATTR_ROW_STATUS_PTR statement attribute was set between calls to SQLFetch() or SQLFetchScroll() and SQLBulkOperations. |
| HY013 | Unexpected memory handling error. | DB2 CLI was unable to access memory required to support execution or completion of this function. |
| HY090 | Invalid string or buffer length. | The Operation argument was SQL_ADD or SQL_UPDATE_BY_BOOKMARK, a
data value was a null pointer, and the column length value was not 0,
SQL_DATA_AT_EXEC, SQL_COLUMN_IGNORE, SQL_NULL_DATA, or less than or equal to
SQL_LEN_DATA_AT_EXEC_OFFSET.
The Operation argument was SQL_ADD or SQL_UPDATE_BY_BOOKMARK, a data value was not a null pointer; the C data type was SQL_C_BINARY or SQL_C_CHAR; and the column length value was less than 0, but not equal to SQL_DATA_AT_EXEC, SQL_COLUMN_IGNORE, SQL_NTS, or SQL_NULL_DATA, or less than or equal to SQL_LEN_DATA_AT_EXEC_OFFSET. The value in a length/indicator buffer was SQL_DATA_AT_EXEC; the SQL type was either SQL_LONGVARCHAR, SQL_LONGVARBINARY, or a long data type; and the SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo() was "Y". The Operation argument was SQL_ADD, the SQL_ATTR_USE_BOOKMARK statement attribute was set to SQL_UB_VARIABLE, and column 0 was bound to a buffer whose length was not equal to the maximum length for the bookmark for this result set. (This length is available in the SQL_DESC_OCTET_LENGTH field of the IRD, and can be obtained by calling SQLDescribeCol(), SQLColAttribute(), or SQLGetDescField().) |
| HY092 | Invalid attribute identifier. | The value specified for the Operation argument was
invalid.
The Operation argument was SQL_ADD, SQL_UPDATE_BY_BOOKMARK, or SQL_DELETE_BY_BOOKMARK, and the SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY. The Operation argument was SQL_DELETE_BY_BOOKMARK, SQL_FETCH_BY_BOOKMARK, or SQL_UPDATE_BY_BOOKMARK, and the bookmark column was not bound or the SQL_ATTR_USE_BOOKMARKS statement attribute was set to SQL_UB_OFF. |
| HYC00 | Optional feature not implemented. | DB2 CLI or data source does not support the operation requested in the Operation argument. |
| HYT00 | Timeout expired. | The query timeout period expired before the data source returned the result set. The timeout period is set through SQLSetStmtAttr() with an Attribute argument of SQL_ATTR_QUERY_TIMEOUT. |
| HYT01 | Connection timeout expired. | The connection timeout period expired before the data source responded to the request. The connection timeout period is set through SQLSetConnectAttr(), SQL_ATTR_CONNECTION_TIMEOUT. |
Restrictions
None.
See the README file in the sqllib\samples\cli (or sqllib/samples/cli) subdirectory for a list of appropriate samples.
References