Skip to content
≡

Administrator

Developer

Tools

External Tools

SQLBulkOperations

SQLBulkOperations#

This function performs bulk insertions or updates, deletes, and fetches bookmarked rows.

Syntax#

SQLRETURN  SQLBulkOperations (
    SQLHSTMT        stmt,
    SQLUSMALLINT    operation);

Argument#

Data Type Argument In/Output Description
SQLHSTMT stmt Input Statement handle
SQLUSMALLINT operation Input Operation to be performed. One of the following operations can be performed:
SQL_ADD
SQL_UPDATE_BY_BOOKMARK
SQL_DELETE_BY_BOOKMARK
SQL_FETCH_BY_BOOKMARK

Return Values#

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_NEED_DATA
SQL_STILL_EXECUTING
SQL_INVALID_HANDLE
SQL_ERROR

Description#

The following operations can be performed using this function.

Option Operation Cursor Type
SQL_ADD Inserts new data UPDATABLE
SQL_UPDATE_BY_BOOKMARK Updates bookmarked row UPDATABLE, SCROLLABLE, USE_BOOKMARK
SQL_DELETE_BY_BOOKMARK Deletes bookmarted row UPDATABLE, SCROLLABLE, USE_BOOKMARK
SQL_FETCH_BY_BOOKMARK Fetches bookmarked row UPDATABLE, SCROLLABLE, USE_BOOKMARK

In order to execute UPDATE, DELETE or FETCH commands on bookmarked rows, bookmarks must first be specified before calling this function. Also, since operational options differ according to cursor types, the appropriate cursor must be opened.

After calling SQLBulkOperations, the cursor position is left undefined. Therefore, the cursor position must be set by calling SQLFetchScroll with the SQL_FETCH_FIRST, SQL_FETCH_LAST, SQL_FETCH_ABSOLUTE or SQL_FETCH_BOOKMARK option. However, calling SQLFetch or SQLFetchScroll with the SQL_FETCH_PRIOR, SQL_FETCH_NEXT or SQL_FETCH_RELATIVE option does not define the cursor position.

If the user wishes to ignore a certain column in the bulk operation performed by this function, the column length/indicator argument of SQLBindCol should be set to SQL_COLUMN_IGNORE; however, this method does not apply to SQL_DELETE_BY_BOOKMARK bulk operations.

Rows cannot be ignored when performing bulk operations with this function. Therefore, there is no need to set the SQL_ATTR_ROW_OPERATION_PTR statement attribute when an application calls SQLBulkOperations.

The result of the bulk operation is returned to the array specified for the SQL_ATTR_ROW_STATUS_PTR statement attribute. The state of each row is displayed as SQL_ROW_ADDED, SQL_ROW_DELETED or SQL_ROW_UPDATED, according to its operational option; if an error occurs, SQL_ROW_ERROR is returned.

When performing bulk operations with the SQL_FETCH_BY_BOOKMARK option, if the driver detects a HOLE (a HOLE is a row which exists in an initial SELECT operation, but is not retrieved in a bulk fetch operation), the row status value is returned as SQL_ROW_DELETED. If this function is executed with the SQL_FETCH_BY_BOOKMARK option for SENSITIVE type cursors, the latest data from the database is retrieved; for non-SENSITIVE type cursors, existing data from the cache is retrieved.

Bulk Insert#

To insert data with SQLBulkOperations, write the program in the following order:

  1. Execute a SELECT query which returns a result set.

  2. Set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows to be inserted.

  3. Call SQLBindCol to bind the data to be inserted.

    • The data to be bound must be an array of the same size as the SQL_ATTR_ROW_ARRAY_SIZE value.

    • The size of the array specified for the SQL_ATTR_ROW_STATUS_PTR statement attribute must be equal to, or larger than SQL_ATTR_ROW_ARRY_SIZE

  4. Call SQLBulkOperations(stmt, SQL_ADD).

    • If the SQL_ATTR_ROW_STATUS_PTR statement attribute is set to a status array, the operational result of each row (whether or not it is SQL_ROW_ADDED) can be checked.

When executing a SELECT query to perform bulk insertions, the user is recommended to use only a simple query statement which searches for pure columns on a single table. If a column is contained multiple times or an expression is included in the SELECT list, bulk insertion can fail.

Using Bookmarks to Bulk Update#

When performing bulk updates with SQLBulkOperations and bookmarks, write the program in the following order:

  1. Set the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE.

  2. Execute a SELECT query which returns a result set.

  3. Set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows to be updated.

  4. Call SQLBindCol to bind the data to be updated.

    • The data to be bound must be an array of the same size as the SQL_ATTR_ROW_ARRAY_SIZE value.

    • The array which is to store the bookmark value of the row to be updated is bound to the 0th column (the bookmark column).

  5. Set data.

    • Copies the bookmark of the rows to be updated into the array bound to the 0th column.

    • Converts data of the bound buffer to values to be updated.

  6. Call SQLBulkOperations(stmt, SQL_UPDATE_BY_BOOKMARK).

    • If the SQL_ATTR_ROW_STATUS_PTR statement attribute is set to a status array, the operational result of each row (whether or not it is SQL_ROW_UPDATED) can be checked.

If the SQL_ATTR_ROW_STATUS_PTR statement attribute is set to a status array, the operational result of each row (whether or not it is SQL_ROW_UPDATED) can be checked.

The bookmark values specified for the 0th column must not be duplicated; on duplication, bulk update can fail.

Using Bookmarks to Bulk Fetch#

When performing bulk fetches with SQLBulkOperations and bookmarks, write the program in the following order:

  1. Set the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE.

  2. Execute a SELECT query which returns a result set.

  3. Set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows to be fetched.

  4. Call SQLBindCol to bind the data to be fetched.

    • The data to be bound must be an array of the same size as the SQL_ATTR_ROW_ARRAY_SIZE value.

    • The array which is to store the bookmark value of the row to be fetched is bound to the 0th column (the bookmark column).

  5. Copy the bookmark of the row to be fetched into the array buffer bound to the 0th column.

  6. Call SQLBulkOperations(stmt, SQL_FETCH_BY_BOOKMARK).

    • If the SQL_ATTR_ROW_STATUS_PTR statement attribute is set to a status array, the operational result of each row (whether it is SQL_ROW_SUCCESS or SQL_ROW_DELETED) can be checked.

If a SENSITIVE cursor was opened, the driver is capable of detecting a HOLE (a HOLE is a row which exists in an initial SELECT operation, but is not retrieved in a bulk fetch operation). If an application has set a status array (an array specified for the SQL_ATTR_ROW_STATUS_PTR statement attribute) and the driver detects a HOLE, SQL_ROW_DELETED is returned for the array element corresponding to the row that is a HOLE; if a status array has not been set, however, and the driver detects a HOLE, an error is returned.

Using Bookmarks to Bulk Delete#

When performing bulk deletion with SQLBulkOperations and bookmarks, write the program in the following order:

  1. Set the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE.

  2. Execute a SELECT query which returns a result set.

  3. Set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows to be fetched.

  4. Call SQLBindCol to bind the 0th column (the bookmark column).

  5. Copies the bookmark of the row to be deleted into the array buffer bound to the 0th column.

  6. Call SQLBulkOperations(stmt, SQL_DELETE_BY_BOOKMARK).

    • If the SQL_ATTR_ROW_STATUS_PTR statement attribute is set to a status array, the operational result of each row (whether or not it is SQL_ROW_ADDED) can be checked.

The bookmark values specified for the 0th column must not be duplicated; on duplication, bulk deletion can fail.

Diagnosis#

SQLSTATE Description Comments
HY000 General error
HY001 Memory allocation error Memory required for Altibase CLI driver to process and complete the function cannot be allocated
08S01 Communication channel error(data transmission failure) Communication channel error occurred before function process between Altibase CLI driver and DB completed
08003 Stmt was released or connection was not in a connected state
HY010 Function sequence error Given stmt cannot process the function. Asynchronous operations are unsupported
HY024 Invalid array size
21S02 Type and number of columns in derived table does not match column list
HY111 Invalid bookmark value
02502 Delete or update hole detected
24519 Hole detected but no indicator variable provided
07006 Restricted data type attribute violation Invalid data type used
HY003 Data type in program is out of range
01S01 Error(s) in row(s) When operation is SQL_ADD, an error occurred in one or more rows during operations are unsupported.
01001 01001 Cursor operation conflict
01S07 Fractional truncation
01004 String data truncated
07009 Invalid descriptor index
22003 Value out of range
22007 Invalid DATETIME format
22008 Datetime field overflow
24000 Invalid cursor state
HY008 Operation canceled 
SQLBindCol
SQLCancel
SQLFetchScroll
SQLGetDescField
SQLGetDescRec
SQLSetDescField
SQLSetDescRec
SQLSetPos
SQLSetStmtAttr