MDAC 2.5 SDK - ODBC Programmer's Reference
Chapter 12: Updating Data

Updating Rows in the Rowset with SQLSetPos

The update operation of SQLSetPos makes the data source update one or more selected rows of a table, using data in the application buffers for each bound column (unless the value in the length/indicator buffer is SQL_COLUMN_IGNORE). Columns that are not bound will not be updated.

To update rows with SQLSetPos, the application does the following:

1. Places the new data values in the rowset buffers. For information on how to send long data with SQLSetPos, see "Long Data and SQLSetPos and SQLBulkOperations," later in this chapter.
   
   
2. Sets the value in the length/indicator buffer of each column as necessary. This is the byte length of the data or SQL_NTS for columns bound to string buffers, the byte length of the data for columns bound to binary buffers, and SQL_NULL_DATA for any columns to be set to NULL.
   
   
3. Sets the value in the length/indicator buffer of those columns which are not to be updated to SQL_COLUMN_IGNORE. Although the application can skip this step and resend existing data, this is inefficient and risks sending values to the data source that were truncated when they were read.
   
   
4. Calls SQLSetPos with Operation set to SQL_UPDATE and RowNumber set to the number of the row to update. If RowNumber is 0, all rows in the rowset are updated.

After SQLSetPos returns, the current row is set to the updated row.

When updating all rows of the rowset (RowNumber is equal to 0), an application can disable the update of certain rows by setting the corresponding elements of the row operation array (pointed to by the SQL_ATTR_ROW_OPERATION_PTR statement attribute) to SQL_ROW_IGNORE. The row operation array corresponds in size and number of elements to the row status array (pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute). To update only those rows in the result set that were successfully fetched and have not been deleted from the rowset, the application uses the row status array from the function that fetched the rowset as the row operation array to SQLSetPos.

For every row that is sent to the data source as an update, the application buffers should have valid row data. If the application buffers were filled by fetching and if a row status array has been maintained, its values at each of these row positions should not be SQL_ROW_DELETED, SQL_ROW_ERROR, or SQL_ROW_NOROW.

For example, the following code allows a user to scroll through the Customers table and update, delete, or add new rows. It places the new data in the rowset buffers before calling SQLSetPos to update or add new rows. An extra row is allocated at the end of the rowset buffers to hold new rows; this prevents existing data from being overwritten when data for a new row is placed in the buffers.

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

SQLUINTEGER    CustIDArray[11];
SQLCHAR        NameArray[11][51], AddressArray[11][51], 
               PhoneArray[11][11];
SQLINTEGER     CustIDIndArray[11], NameLenOrIndArray[11], 
               AddressLenOrIndArray[11],
               PhoneLenOrIndArray[11];
SQLUSMALLINT   RowStatusArray[10], Action, RowNum;
SQLRETURN      rc;
SQLHSTMT       hstmt;

// Set the SQL_ATTR_ROW_BIND_TYPE statement attribute to use column-wise 
// binding. Declare the rowset size with the SQL_ATTR_ROW_ARRAY_SIZE 
// statement attribute. Set the SQL_ATTR_ROW_STATUS_PTR statement 
// attribute to point to the row status array.
SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, 10, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, RowStatusArray, 0);

// Bind arrays to the CustID, Name, Address, and Phone columns.
SQLBindCol(hstmt, 1, SQL_C_ULONG, CustIDArray, 0, CustIDIndArray);
SQLBindCol(hstmt, 2, SQL_C_CHAR, NameArray, sizeof(NameArray[0]), NameLenOrIndArray);
SQLBindCol(hstmt, 3, SQL_C_CHAR, AddressArray, sizeof(AddressArray[0]),
            AddressLenOrIndArray);
SQLBindCol(hstmt, 4, SQL_C_CHAR, PhoneArray, sizeof(PhoneArray[0]),
            PhoneLenOrIndArray);

// 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);
DisplayData(CustIDArray, CustIDIndArray, NameArray, NameLenOrIndArray, AddressArray,
            AddressLenOrIndArray, PhoneArray, PhoneLenOrIndArray, RowStatusArray);

// 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);
         DisplayData(CustIDArray, CustIDIndArray,
                     NameArray, NameLenOrIndArray,
                     AddressArray, AddressLenOrIndArray,
                     PhoneArray, PhoneLenOrIndArray, RowStatusArray);
         break;

      case UPDATE_ROW:
         // Place the new data in the rowset buffers and update the 
         // specified row.
         GetNewData(&CustIDArray[RowNum - 1], &CustIDIndArray[RowNum - 1],
                  NameArray[RowNum - 1], &NameLenOrIndArray[RowNum - 1],
                  AddressArray[RowNum - 1], &AddressLenOrIndArray[RowNum - 1],
                  PhoneArray[RowNum - 1], &PhoneLenOrIndArray[RowNum - 1]);
         SQLSetPos(hstmt, RowNum, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
         break;

      case DELETE_ROW:
         // Delete the specified row.
         SQLSetPos(hstmt, RowNum, SQL_DELETE, SQL_LOCK_NO_CHANGE);
         break;

      case ADD_ROW:
         // Place the new data in the rowset buffers at index 10. 
         // This is an extra element for new rows so rowset data is 
         // not overwritten. Insert the new row. Row 11 corresponds 
         // to index 10.
         GetNewData(&CustIDArray[10], &CustIDIndArray[10],
                     NameArray[10], &NameLenOrIndArray[10],
                     AddressArray[10], &AddressLenOrIndArray[10],
                     PhoneArray[10], &PhoneLenOrIndArray[10]);
         SQLSetPos(hstmt, 11, SQL_ADD, SQL_LOCK_NO_CHANGE);
         break;
   }
}

// Close the cursor.
SQLCloseCursor(hstmt);