SQLBulkOperations() - Add, update, delete or fetch a set of rows

SQLBulkOperations() adds new rows to the base table or view that is associated with a dynamic cursor for the current query.

ODBC specifications for SQLBulkOperations()

Table 1. SQLBulkOperations() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
3.0 Yes Yes

Syntax

SQLRETURN SQLBulkOperations (
             SQLHSTMT      StatementHandle,
             SQLSMALLINT   Operation);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLBulkOperations arguments
Data type Argument Use Description
SQLHSTMT StatementHandle Input Statement handle.
SQLSMALLINT Operation Input Operation to perform: SQL_ADD.
Db2 ODBC does not support the following operations:
  • SQL_UPDATE_BY_BOOKMARK
  • SQL_DELETE_BY_BOOKMARK
  • SQL_FETCH_BY_BOOKMARK

Usage

Before calling SQLBulkOperations(), you need to ensure that the required bulk operation is supported. To check for support, call SQLGetInfo() with an InfoType of SQL_DYNAMIC_CURSOR_ATTRIBUTES1 or SQL_DYNAMIC_CURSOR_ATTRIBUTES2. Check the following attributes to verify that support is available:
  • SQL_CA1_BULK_ADD
  • SQL_CA1_BULK_UPDATE_BY_BOOKMARK
  • SQL_CA1_BULK_DELETE_BY_BOOKMARK
  • SQL_CA1_BULK_FETCH_BY_BOOKMARK

A column can be ignored when bulk operations are performed with SQLBulkOperations(). To ignore a column, call SQLBindCol(), and set the column length and indicator buffer (pcbValue) to SQL_COLUMN_IGNORE.

After a call to SQLBulkOperations():
  • The buffer to which the SQL_ATTR_ROWS_FETCHED_PTR statement attribute points contains the number of rows that are affected by the call.
  • The row status array, to which the SQL_ATTR_ROW_STATUS_PTR statement attribute points, contains the result of the operation.
  • The block cursor position is undefined. The application must call SQLFetchScroll() to set the cursor position. The application needs to call SQLFetchScroll() with a FetchOrientation argument of SQL_FETCH_FIRST, SQL_FETCH_LAST, or SQL_FETCH_ABSOLUTE. The cursor position is undefined if the application calls SQLFetch(), or calls SQLFetchScroll() with a FetchOrientation argument of SQL_FETCH_PRIOR, SQL_FETCH_NEXT, or SQL_FETCH_RELATIVE.
The application does not need to:
  • Call SQLFetch() or SQLFetchScroll() before calling SQLBulkOperations().
  • Set the SQL_ATTR_ROW_OPERATION_PTR statement attribute for SQLBulkOperations() calls. Rows cannot be ignored when bulk operations are performed with SQLBulkOperations().

When the Operation argument is SQL_ADD, and the select list of the query that is associated with the cursor contains more than one reference to the same column, an error is generated.

Row status array: The row status array contains status values for each row of data in the rowset after a call to SQLBulkOperations(). This array is initially populated by a call to SQLBulkOperations() if SQLFetch() or SQLFetchScroll() has not been called before SQLBulkOperations() is called. The SQL_ATTR_ROW_STATUS_PTR statement attribute points to the row status array. The number of elements in the row status array should equal the number of rows in the rowset, as defined by the SQL_ATTR_ROW_ARRAY_SIZE statement attribute.

Return codes

  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_NEED_DATA
  • SQL_STILL_EXECUTING
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

Table 3. SQLBulkOperations SQLSTATEs
SQLSTATE Description Explanation
01000 Warning. Informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
07006 Invalid conversion. The Operation argument was 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.
22001 String data right truncation. The assignment of a character or binary value to a column 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. 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.
22008 Invalid datetime format or datetime field overflow. One of the following conditions occurred:
  • The Operation argument was SQL_ADD. The assignment of a date or timestamp value to a column in a result set caused the year, month, or day field to be out of range.
  • The Operation argument was SQL_ADD. A datetime arithmetic operation on data that was 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 that was outside the permissible range of values for the field, or was invalid based on the natural rules for datetime values for the Gregorian calendar.
22018 Error in assignment. The argument Operation was SQL_ADD. The data value that was assigned to a column was incompatible with the data type of the associated column in the result set.
23000 Integrity constraint violation. An integrity constraint was violated. One of the following conditions occurred:
  • The Operation argument was SQL_ADD. A column that was not bound is defined as NOT NULL and has no default.
  • The Operation argument was SQL_ADD. The length that was specified in the bound pcbValue 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 Transaction rollback. The transaction in which the fetch was executed was terminated to prevent deadlock.
40003 Statement completion unknown. The associated connection failed during the execution of this function. The state of the transaction cannot be determined.
42xxx1 Syntax error or access rule violation. These SQLSTATEs indicate one of the following errors:
  • For 425xx, the authorization ID does not have permission to perform the operation that was requested in the Operation argument.
  • For 42xxx, a variety of syntax or access problems with the statement occur.
44000 WITH CHECK OPTION violation. The Operation argument was SQL_ADD. An insert or update was performed on a viewed table or a table that was derived from the viewed table. The viewed table was created by specifying WITH CHECK OPTION. One or more rows that were affected by the insert are no longer present in the viewed table.
HY000 General error. An error occurred for which there was no specific SQLSTATE. The error message that was returned by SQLGetDiagRec() in the *MessageText buffer describes the error and its cause.
HY001 Memory allocation error. Db2 ODBC was unable to allocate memory required to support execution or completion of the function. Process-level memory might have been exhausted for the application process. Consult the operating system configuration for information on process-level memory limitations.
HY010 Function sequence error. The function was called while in a data-at-execute (SQLParamData() or SQLPutData()) operation.
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 ODBC was unable to access memory that was required to support execution or completion of this function.
HY090 Invalid string or buffer length. One of the following conditions occurred:
  • The Operation argument was SQL_ADD. 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. A data value was not a null pointer. The C data type was SQL_C_BINARY or SQL_C_CHAR. 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.
HY092 Invalid attribute identifier. The Operation argument was SQL_ADD. The SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY.
HYC00 Driver not capable. Db2 ODBC or the data source does not support the operation that was requested in the Operation argument.
Note:
  1. xxx refers to any SQLSTATE with that class code. For example, 37xxx refers to any SQLSTATE with class code '37'.