Updating rows in a rowset with SQLSetPos()

You can use SQLSetPos() to update any row in a rowset. SQLSetPos() operates on your current rowset through a dynamic cursor. SQLSetPos() updates a row by using data in the application buffers for each bound column. All bound columns with a data length equal to the value of SQL_COLUMN_IGNORE are not updated, and all unbound columns are not updated.

Before you begin

Before you can call SQLSetPos(), you must call SQLFetch() or SQLFetchScroll().

Procedure

To update rows with SQLSetPos():

  1. Call SQLBindCol() to bind the data that you want to update.
    1. For each bound column, place the new data value in the buffer that is specified by the rgbValue argument, and the length of that value in the buffer that is specified by the pcbValue argument.
    2. Set the length of the data value of those columns that are not to be updated to SQL_COLUMN_IGNORE.
  2. Call SQLSetPos() with Operation set to SQL_UPDATE and RowNumber set to the number of the row to update.
    If you set RowNumber to 0, all rows in the rowset are updated.

    If you set RowNumber to 0, but you want to update only certain rows, you can disable the update of the other rows by setting the corresponding elements of the row operation array that is pointed to by the SQL_ATTR_ROW_OPERATION_PTR statement attribute to SQL_ROW_IGNORE.

Example

The following example is an application that uses SQLSetPos() to update a row in table CUSTOMER.
#define ROWSET_SIZE 10
/* Declare and initialize local variables        */
SQLCHAR sqlstmt[] =
  "SELECT Cust_Num, First_Name, Last_Name FROM CUSTOMER";
SQLINTEGER Cust_Num;
SQLCHAR First_Name[ROWSET_SIZE][21];
SQLCHAR Last_Name[ROWSET_SIZE][21];
SQLINTEGER Cust_Num_L[ROWSET_SIZE];
SQLINTEGER First_Name_L[ROWSET_SIZE];
SQLINTEGER Last_Name_L[ROWSET_SIZE];
SQLUSMALLINT rowStatus[ROWSET_SIZE];
/* Set up dynamic cursor type                    */
rc = SQLSetStmtAttr(hstmt,
  SQL_ATTR_CURSOR_TYPE,
  (SQLPOINTER) SQL_CURSOR_DYNAMIC,
  0);
/* Set pointer to row status array               */
rc = SQLSetStmtAttr(hstmt,
  SQL_ATTR_ROW_STATUS_PTR,
  (SQLPOINTER) rowStatus,
  0);
/* Set number of rows to fetch                   */
rc = SQLSetStmtAttr(hstmt,
  SQL_ATTR_ROW_ARRAY_SIZE,
  (SQLPOINTER) ROWSET_SIZE,
  0);
/* Fetch the first rowset                        */
rc = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
/* Call SQLBindCol() for each result set column  */
rc = SQLBindCol(hstmt,
  1,
  SQL_C_LONG,
  (SQLPOINTER) Cust_Num,
  (SQLINTEGER) sizeof(Cust_Num)/ROWSET_SIZE,
  Cust_Num_L);
rc = SQLBindCol(hstmt,
  2,
  SQL_C_CHAR,
  (SQLPOINTER) First_Name,
  (SQLINTEGER) sizeof(First_Name)/ROWSET_SIZE,
  First_Name_L);
rc = SQLBindCol(hstmt,
  3,
  SQL_C_CHAR,
  (SQLPOINTER) Last_Name,
  (SQLINTEGER) sizeof(Last_Name)/ROWSET_SIZE,
  Last_Name_L);
…
/* For each column, place the new data value in  */
/* the rgbValue buffer, and set the length of    */
/* the value in the buffer specified by the      */
/* pcbValue argument                             */
…
/* Update the first row in the rowset            */
rc = SQLSetPos(hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);