Processing UPDATE, DELETE, INSERT, and MERGE statements

In some cases, you might need to use a cursor if you perform a positioned UPDATE or DELETE in your application. Otherwise, you need to check only for diagnostic messages.

About this task

If a statement modifies data (UPDATE, DELETE, INSERT, or MERGE statements), no action is required, other than the normal check for diagnostic messages. In this case, use SQLRowCount() to obtain the number of rows the SQL statement affects.

If the SQL statement is a positioned UPDATE or DELETE, you need to use a cursor. A cursor is a moveable pointer to a row in the result table of an active query statement. (This query statement must contain the FOR UPDATE OF clause to ensure that the query is not opened as read-only.) In embedded SQL, the names of cursors are used to retrieve, update or delete rows. In Db2 ODBC, a cursor name is needed only for positioned UPDATE or DELETE SQL statements as they reference the cursor by name.

Procedure

To perform a positioned update or delete in your application:

  1. Issue a SELECT statement to generate a result set.
  2. Call SQLGetCursorName() to retrieve the name of the cursor on the result set that you generated in the preceding step.
    You use this cursor name in the UPDATE or DELETE statement.
    Tip: Use the name that Db2 automatically generates. Although you can define your own cursor names by using SQLSetCursorName(), use the name that Db2 generates. All error messages reference the Db2 generated name, not the name that you define with SQLSetCursorName().
  3. Allocate a second statement handle to execute the positioned update or delete.

    To update or delete a row that has been fetched, you use two statement handles: one handle for the fetch and one handle for the update of the delete. You cannot reuse the fetch statement handle to execute a positioned update or delete because this handle holds the cursor while the positioned update or delete executes.

  4. Call SQLFetch() to position the cursor on a row in the result set.
  5. Create the UPDATE or DELETE SQL statement with the WHERE CURRENT of clause and specify the cursor name that you obtained in step 2. 
    sprintf((char *)stmtPositionedUpdate,                            
"UPDATE org SET location = 'San Jose'  WHERE CURRENT of %s",       
 cursorName);
  6. Execute the positioned update or delete statement.