Positioned Update and Delete Statements
To use a positioned update or delete statement, the application must create a
result set with a SELECT FOR UPDATE statement. The syntax of this statement is:
SELECT [ALL | DISTINCT] select-list
The application then positions the cursor on the row to be updated or deleted.
It can do this by calling SQLFetchScroll to retrieve a rowset containing the required row and calling SQLSetPos to position the rowset cursor on that row. The application then executes the
positioned update or delete statement on a different statement than the
statement being used by the result set. The syntax of these statements is:
UPDATE table-name
DELETE FROM table-name WHERE CURRENT OF cursor-name
Notice that these statements require a cursor name. The application can either
specify a cursor name with SQLSetCursorName before executing the statement that creates the result set or it can let the
data source automatically generate a cursor name when the cursor is created. In
the latter case, the application retrieves this cursor name for use in
positioned update and delete statements by calling SQLGetCursorName.
For example, the following code allows a user to scroll through the Customers
table and delete customer records or update their addresses and phone numbers.
It calls SQLSetCursorName to specify a cursor name before it creates the result set of customers and
uses three statement handles: hstmtCust for the result set, hstmtUpdate for a positioned update statement, and hstmtDelete for a positioned delete statement. Although the code could bind separate
variables to the parameters in the positioned update statement, it updates the
rowset buffers and binds the elements of these buffers. This keeps the rowset
buffers synchronized with the updated data.
#define POSITIONED_DELETE 101
SQLUINTEGER CustIDArray[10];
SQLCHAR NameArray[10][51], AddressArray[10][51], PhoneArray[10][11];
SQLINTEGER CustIDIndArray[10], NameLenOrIndArray[10],
AddressLenOrIndArray[10],
PhoneLenOrIndArray[10];
SQLUSMALLINT RowStatusArray[10], Action, RowNum;
SQLHSTMT hstmtCust, hstmtUpdate, hstmtDelete;
// Set the SQL_ATTR_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(hstmtCust, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN, 0);
SQLSetStmtAttr(hstmtCust, SQL_ATTR_ROW_ARRAY_SIZE, 10, 0);
SQLSetStmtAttr(hstmtCust, SQL_ATTR_ROW_STATUS_PTR, RowStatusArray, 0);
// Bind arrays to the CustID, Name, Address, and Phone columns.
SQLBindCol(hstmtCust, 1, SQL_C_ULONG, CustIDArray, 0, CustIDIndArray);
SQLBindCol(hstmtCust, 2, SQL_C_CHAR, NameArray, sizeof(NameArray[0]),
NameLenOrIndArray);
SQLBindCol(hstmtCust, 3, SQL_C_CHAR, AddressArray, sizeof(AddressArray[0]),
AddressLenOrIndArray);
SQLBindCol(hstmtCust, 4, SQL_C_CHAR, PhoneArray, sizeof(PhoneArray[0]),
PhoneLenOrIndArray);
// Set the cursor name to Cust.
SQLSetCursorName(hstmtCust, "Cust", SQL_NTS);
// Prepare positioned update and delete statements.
SQLPrepare(hstmtUpdate,
"UPDATE Customers SET Address = ?, Phone = ? WHERE CURRENT OF Cust",
SQL_NTS);
SQLPrepare(hstmtDelete, "DELETE FROM Customers WHERE CURRENT OF Cust",
SQL_NTS);
// Execute a statement to retrieve rows from the Customers table.
SQLExecDirect(hstmtCust,
"SELECT CustID, Name, Address, Phone FROM Customers FOR UPDATE OF Address,
Phone",
SQL_NTS);
// Fetch and display the first 10 rows.
SQLFetchScroll(hstmtCust, 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(hstmtCust, Action, RowNum);
DisplayData(CustIDArray, CustIDIndArray, NameArray, NameLenOrIndArray,
AddressArray, AddressLenOrIndArray, PhoneArray,
PhoneLenOrIndArray, RowStatusArray);
break;
case POSITIONED_UPDATE:
// Get the new data and place it in the rowset buffers.
GetNewData(AddressArray[RowNum - 1], &AddressLenOrIndArray[RowNum -
1],
PhoneArray[RowNum - 1], &PhoneLenOrIndArray[RowNum - 1]);
// Bind the elements of the arrays at position RowNum-1 to the
parameters
// of the positioned update statement.
SQLBindParameter(hstmtUpdate, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,
50, 0, AddressArray[RowNum - 1],
sizeof(AddressArray[0]),
&AddressLenOrIndArray[RowNum - 1]);
SQLBindParameter(hstmtUpdate, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,
10, 0, PhoneArray[RowNum - 1], sizeof(PhoneArray[0]),
&PhoneLenOrIndArray[RowNum - 1]);
// Position the rowset cursor. The rowset is 1-based.
SQLSetPos(hstmtCust, RowNum, SQL_POSITION, SQL_LOCK_NO_CHANGE);
// Execute the positioned update statement to update the row.
SQLExecute(hstmtUpdate);
break;
case POSITIONED_DELETE:
// Position the rowset cursor. The rowset is 1-based.
SQLSetPos(hstmtCust, RowNum, SQL_POSITION, SQL_LOCK_NO_CHANGE);
// Execute the positioned delete statement to delete the row.
SQLExecute(hstmtDelete);
break;
}
}
// Close the cursor.
SQLCloseCursor(hstmtCust);
FROM table-reference-list
[WHERE search-condition]
FOR UPDATE OF [column-name [, column-name]...]
SET column-identifier = {expression | NULL}
[, column-identifier = {expression | NULL}]...
WHERE CURRENT OF cursor-name