SQLBulkOperations

Conformance

Version Introduced:ODBC 3.0
Standards Compliance:ODBC

Summary

SQLBulkOperations performs bulk insertions and bulk bookmark operations, including update, delete, and fetch by bookmark.

Syntax

SQLRETURN SQLBulkOperations(
SQLHSTMTStatementHandle,
SQLUSMALLINTOperation);

Arguments

StatementHandle

[Input]
Statement handle.

Operation

[Input]
Operation to perform:

SQL_ADD
SQL_UPDATE_BY_BOOKMARK
SQL_DELETE_BY_BOOKMARK
SQL_FETCH_BY_BOOKMARK

For more information, see “Comments.”

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLBulkOperations returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value can be obtained by calling SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle. The following table lists the SQLSTATE values commonly returned by SQLBulkOperations and explains each one in the context of this function; the notation “(DM)” precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

For all those SQLSTATEs that can return SQL_SUCCESS_WITH_INFO or SQL_ERROR (except 01xxx SQLSTATEs), SQL_SUCCESS_WITH_INFO is returned if an error occurs on one or more, but not all, rows of a multirow operation, and SQL_ERROR is returned if an error occurs on a single-row operation.

Comments

Caution   For information about what statement states SQLBulkOperations can be called in and what it needs to do for compatibility with ODBC 2.x applications, see the “Block Cursors, Scrollable Cursors, and Backward Compatibility” section in Appendix G, “Driver Guidelines for Backward Compatibility.”

An application uses SQLBulkOperations to perform the following operations on the base table or view that corresponds to the current query:

• Add new rows.
  
  
• Update a set of rows where each row is identified by a bookmark.
  
  
• Delete a set of rows where each row is identified by a bookmark.
  
  
• Fetch a set of rows where each row is identified by a bookmark.

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 performed by a call to SQLBulkOperations by setting the column length/indicator buffer specified in the call to SQLBindCol, to SQL_COLUMN_IGNORE.

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, it is driver-defined whether:

• An error is generated, or
  
  
• The driver ignores the duplicated references and performs the requested operations.

For more information about using SQLBulkOperations, see “Updating Data with SQLBulkOperations” in Chapter 12, “Updating Data.”

Performing Bulk Inserts

To insert data with SQLBulkOperations, an application performs the following sequence of steps:

1. Executes a query that returns a result set.
   
   
2. Sets the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows that it wants to insert.
   
   
3. Calls SQLBindCol to bind the data that it wants to insert. The data is bound to an array with a size equal to the value of SQL_ATTR_ROW_ARRAY_SIZE.

   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.

4. Calls SQLBulkOperations(StatementHandle, SQL_ADD) to perform the insertion.
   
   
5. 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.

If an application binds column 0 before calling SQLBulkOperations with an Operation argument of SQL_ADD, the driver 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. (This does not work with an ODBC 2.x driver.)

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 function reference.

It is not necessary for the application to call SQLFetch or SQLFetchScroll before calling SQLBulkOperations (except when going against an ODBC 2.x driver; see “Backward Compatibility and Standards Compliance” in Chapter 17, “Programming Considerations”).

It is driver-defined what the behavior is if SQLBulkOperations with an Operation argument of SQL_ADD is called on a cursor that contains duplicate columns. The driver can return a driver-defined SQLSTATE, add the data to the first column that appears in the result set, or perform other driver-defined behavior.

Performing Bulk Updates Using Bookmarks

To perform bulk updates using bookmarks with SQLBulkOperations, an application performs the following steps in sequence:

1. Sets the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE.
   
   
2. Executes a query that returns a result set.
   
   
3. Sets the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows that it wants to update.

   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).

4. Copies the bookmarks for rows that it is interested in updating into the array bound to column 0.
   
   
5. Updates the data in the bound buffers.

   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.

6. Calls SQLBulkOperations(StatementHandle, SQL_UPDATE_BY_BOOKMARK).

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, the driver 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 function reference.

If bookmarks persist across cursors, then there is no need for the application to call SQLFetch or SQLFetchScroll before updating by bookmarks. It can use bookmarks that it has stored from a previous cursor. If bookmarks do not persist across cursors, then the application has to call SQLFetch or SQLFetchScroll to retrieve the bookmarks.

It is driver-defined what the behavior is if SQLBulkOperations with an Operation argument of SQL_UPDATE_BY_BOOKMARK is called on a cursor that contains duplicate columns. The driver can return a driver-defined SQLSTATE, update the first column that appears in the result set, or perform other driver-defined behavior.

Performing Bulk Fetches Using Bookmarks

To perform bulk fetches using bookmarks with SQLBulkOperations, an application performs the following steps in sequence:

1. Sets the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE.
   
   
2. Executes a query that returns a result set.
   
   
3. Sets the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows that it wants to fetch.
   
   
4. Calls SQLBindCol to bind the data that it wants to fetch. 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).
   
   
5. Copies the bookmarks for rows that it is interested in fetching into the array bound to column 0. (This assumes that the application has already obtained the bookmarks separately.)

   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.

6. Calls SQLBulkOperations(StatementHandle, SQL_FETCH_BY_BOOKMARK).
   
   
7. 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.

If bookmarks persist across cursors, then there is no need for the application to call SQLFetch or SQLFetchScroll before fetching by bookmarks. It can use bookmarks that it has stored from a previous cursor. If bookmarks do not persist across cursors, then the application has to call SQLFetch or SQLFetchScroll once to retrieve the bookmarks.

Performing Bulk Deletes Using Bookmarks

To perform bulk deletes using bookmarks with SQLBulkOperations, an application performs the following steps in sequence:

1. Sets the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE.
   
   
2. Executes a query that returns a result set.
   
   
3. Sets the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows that it wants to delete.
   
   
4. Calls SQLBindCol to bind column 0 (the bookmark column).
   
   
5. Copies the bookmarks for rows that it is interested in deleting into the array bound to column 0.

   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.

6. Calls SQLBulkOperations(StatementHandle, SQL_DELETE_BY_BOOKMARK).
   
   
7. 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.

If bookmarks persist across cursors, then there is no need for the application to call SQLFetch or SQLFetchScroll before deleting by bookmarks. It can use bookmarks that it has stored from a previous cursor. If bookmarks do not persist across cursors, then the application has to call SQLFetch or SQLFetchScroll once to retrieve the 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.

1. When it binds the data using SQLBindCol, the application places an application-defined value, such as the column number, in the *TargetValuePtr buffer for data-at-execution columns. The value can be used later to identify the column.

   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 the driver 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 nonnegative value and is ignored.

2. When SQLBulkOperations is called, if there are data-at-execution columns, the function returns SQL_NEED_DATA and proceeds to step 3 below. (If there are no data-at-execution columns, the process is complete.)
   
   
3. The application calls SQLParamData to retrieve the address of the *TargetValuePtr buffer for the first data-at-execution column to be processed. SQLParamData returns SQL_NEED_DATA. The application retrieves the application-defined value from the *TargetValuePtr buffer.

   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.

4. The application calls SQLPutData one or more times to send data for the column. More than one call is needed if all the data value cannot be returned in the *TargetValuePtr buffer specified in SQLPutData; note that multiple calls to SQLPutData for the same column are allowed only when sending character C data to a column with a character, binary, or data source – specific data type or when sending binary C data to a column with a character, binary, or data source – specific data type.
   
   
5. The application calls SQLParamData again to signal that all data has been sent for the column.
   • If there are more data-at-execution columns, SQLParamData returns SQL_NEED_DATA and the address of the TargetValuePtr buffer for the next data-at-execution column to be processed. The application repeats steps 4 and 5.
     
     
   • If there are no more data-at-execution columns, the process is complete. If the statement was executed successfully, SQLParamData returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO; if the execution failed, it returns SQL_ERROR. At this point, SQLParamData can return any SQLSTATE that can be returned by SQLBulkOperations.

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 the driver still needs data for data-at-execution columns, the driver 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 driver sets the status values in this array after a call to SQLFetch, SQLFetchScroll, SQLSetPos, or SQLBulkOperations. 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.

Code Example

The following example fetches 10 rows of data at a time from the Customers table. It then prompts the user for an action to take. To reduce network traffic, the example buffer updates, deletes, and inserts locally in the bound arrays, but at offsets past the rowset data. When the user chooses to send updates, deletes, and inserts to the data source, the code sets the binding offset appropriately and calls SQLBulkOperations. For simplicity, the user cannot buffer more than 10 updates, deletes, or inserts.

#define UPDATE_ROW    100
#define DELETE_ROW    101
#define ADD_ROW     102
#define SEND_TO_DATA_SOURCE 103

#define UPDATE_OFFSET 10
#define INSERT_OFFSET 20
#define DELETE_OFFSET 30

// Define a structure to hold the customer data.  Assume we know the maximum bookmark
// size to be 10 bytes.
typedef tagCustStruct {
 SQLCHAR   Bookmark[10];
 SQLINTEGER  BookmarkLen;
 SQLUINTEGER CustID;
 SQLINTEGER  CustIDInd;
 SQLCHAR   Name[51];
 SQLINTEGER  NameLenOrInd;
 SQLCHAR   Address[51];
 SQLINTEGER  AddressLenOrInd;
 SQLCHAR   Phone[11];
 SQLINTEGER  PhoneLenOrInd;
} CustStruct;

// Allocate 40 of these structures. Elements 0-9 are for the current rowset,
// elements 10-19 are for the buffered updates, elements 20-29 are for
// the buffered inserts, and elements 30-39 are for the buffered deletes.
CustStruct CustArray[40];

SQLUSMALLINT RowStatusArray[10], Action, RowNum, NumUpdates = 0, NumInserts = 0,
     NumDeletes = 0;
SQLINTEGER BindOffset = 0;
SQLRETURN  rc;
SQLHSTMT   hstmt;

// Set the following statement attributes:
//  SQL_ATTR_CURSOR_TYPE:   Keyset-driven
//  SQL_ATTR_ROW_BIND_TYPE:   Row-wise
//  SQL_ATTR_ROW_ARRAY_SIZE:  10
//  SQL_ATTR_USE_BOOKMARKS:   Use variable-length bookmarks
//  SQL_ATTR_ROW_STATUS_PTR:  Points to RowStatusArray
//  SQL_ATTR_ROW_BIND_OFFSET_PTR: Points to BindOffset
SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, sizeof(CustStruct), 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, 10, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_USE_BOOKMARKS, SQL_UB_VARIABLE, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, RowStatusArray, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_OFFSET_PTR, &BindOffset, 0);

// Bind arrays to the bookmark, CustID, Name, Address, and Phone columns.
SQLBindCol(hstmt, 0, SQL_C_VARBOOKMARK, CustArray[0].Bookmark,
     sizeof(CustArray[0].Bookmark), &CustArray[0].BookmarkLen);
SQLBindCol(hstmt, 1, SQL_C_ULONG, &CustArray[0].CustID, 0, &CustArray[0].CustIDInd);
SQLBindCol(hstmt, 2, SQL_C_CHAR, CustArray[0].Name, sizeof(CustArray[0].Name),
     &CustArray[0].NameLenOrInd);
SQLBindCol(hstmt, 3, SQL_C_CHAR, CustArray[0].Address, sizeof(CustArray[0].Address),
     &CustArray[0].AddressLenOrInd);
SQLBindCol(hstmt, 4, SQL_C_CHAR, CustArray[0].Phone, sizeof(CustArray[0].Phone),
     &CustArray[0].PhoneLenOrInd);

// Execute a statement to retrieve rows from the Customers table.
SQLExecDirect(hstmt, "SELECT CustID, Name, Address, Phone FROM Customers", SQL_NTS);

// Fetch and display the first 10 rows.
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
DisplayCustData(CustArray, 10);

// Call GetAction to get an action and a row number from the user.
while (GetAction(&Action, &RowNum)) {
 switch (Action) {

  case SQL_FETCH_NEXT:
  case SQL_FETCH_PRIOR:
  case SQL_FETCH_FIRST:
  case SQL_FETCH_LAST:
  case SQL_FETCH_ABSOLUTE:
  case SQL_FETCH_RELATIVE:
   // Fetch and display the requested data.
   SQLFetchScroll(hstmt, Action, RowNum);
   DisplayCustData(CustArray, 10);
   break;

  case UPDATE_ROW:
   // Check if we have reached the maximum number of buffered updates.
   if (NumUpdates < 10) {
    // Get the new customer data and place it in the next available element of
    // the buffered updates section of CustArray, copy the bookmark of the row
    // being updated to the same element, and increment the update counter.
    // Checking to see we have not already buffered an update for this
    // row not shown.
    GetNewCustData(CustArray, UPDATE_OFFSET + NumUpdates);
    memcopy(CustArray[UPDATE_OFFSET + NumUpdates].Bookmark,
        CustArray[RowNum - 1].Bookmark,
        CustArray[RowNum - 1].BookmarkLen);
    CustArray[UPDATE_OFFSET + NumUpdates].BookmarkLen =
                   CustArray[RowNum - 1].BookmarkLen;
    NumUpdates++;
   } else {
    DisplayError("Buffers full. Send buffered changes to the data source.");
   }
   break;

  case DELETE_ROW:
   // Check if we have reached the maximum number of buffered deletes.
   if (NumDeletes < 10) {
    // Copy the bookmark of the row being deleted to the next available element
    // of the buffered deletes section of CustArray and increment the delete
    // counter. Checking to see we have not already buffered an update for
    // this row not shown.
    memcopy(CustArray[DELETE_OFFSET + NumDeletes].Bookmark,
        CustArray[RowNum - 1].Bookmark,
        CustArray[RowNum - 1].BookmarkLen);
    CustArray[DELETE_OFFSET + NumDeletes].BookmarkLen =
                   CustArray[RowNum - 1].BookmarkLen;
    NumDeletes++;
   } else {
    DisplayError("Buffers full. Send buffered changes to the data source.");
   }
   break;
  case ADD_ROW:
   // Check if we have reached the maximum number of buffered inserts.
   if (NumInserts < 10) {
    // Get the new customer data and place it in the next available element of
    // the buffered inserts section of CustArray and increment the insert
    // counter.
    GetNewCustData(CustArray, INSERT_OFFSET + NumInserts);
    NumInserts++;
   } else {
    DisplayError("Buffers full. Send buffered changes to the data source.");
   }
   break;

  case SEND_TO_DATA_SOURCE:
   // If there are any buffered updates, inserts, or deletes, set the array size
   // to that number, set the binding offset to use the data in the buffered
   // update, insert, or delete part of CustArray, and call SQLBulkOperations to
   // do the updates, inserts, or deletes. Because we will never have more than
   // 10 updates, inserts, or deletes, we can use the same row status array.
   if (NumUpdates) {
    SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, NumUpdates, 0);
    BindOffset = UPDATE_OFFSET * sizeof(CustStruct);
    SQLBulkOperations(hstmt, SQL_UPDATE_BY_BOOKMARK);
    NumUpdates = 0;
   }

   if (NumInserts) {
    SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, NumInserts, 0);
    BindOffset = INSERT_OFFSET * sizeof(CustStruct);
    SQLBulkOperations(hstmt, SQL_ADD);
    NumInserts = 0;
   }

   if (NumDeletes) {
    SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, NumDeletes, 0);
    BindOffset = DELETE_OFFSET * sizeof(CustStruct);
    SQLBulkOperations(hstmt, SQL_DELETE_BY_BOOKMARK);
    NumDeletes = 0;
   }

   // If there were any updates, inserts, or deletes, reset the binding offset
   // and array size to their original values.
   if (NumUpdates || NumInserts || NumDeletes) {
    SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, 10, 0);
    BindOffset = 0;
   }
   break;
 }
}

// Close the cursor.
SQLFreeStmt(hstmt, SQL_CLOSE);

Related Functions