Updating and deleting data in CLI applications

Updating and deleting data is part of the larger task of processing transactions in CLI. There are two types of update and delete operations available in CLI programming: simple and positioned. A simple update or delete operation only requires that you issue and execute the UPDATE or DELETE SQL statements as you would any other SQL statement. You could, in this case, use SQLRowCount() to obtain the number of rows affected by the SQL statement. Positioned updates and deletes involve modifying the data of a result set. A positioned update is the update of a column of a result set, and a positioned delete is when a row of a result set is deleted. Positioned update and delete operations require cursors to be used. This document describes how to perform positioned update and delete operations by first getting the name of the cursor associated with the result set, and then issuing and executing the UPDATE or DELETE on a second statement handle using the retrieved cursor name.

Before you begin

Before you perform a positioned update or delete operation, ensure that you have initialized your CLI application.

Procedure

To perform a positioned update or delete operation:

  1. Generate the result set that the update or delete will be performed on by issuing and executing the SELECT SQL statement.
  2. Call SQLGetCursorName() to get the name of the cursor, using the same statement handle as the handle that executed the SELECT statement. This cursor name will be needed in the UPDATE or DELETE statement.

    When a statement handle is allocated, a cursor name is automatically generated. You can define your own cursor name using SQLSetCursorName(), but it is recommended that you use the name that is generated by default because all error messages will reference the generated name, not the name defined using SQLSetCursorName().

  3. Allocate a second statement handle that will be used to execute the positioned update or delete.

    To update a row that has been fetched, the application uses two statement handles, one for the fetch and one for the update. You cannot reuse the fetch statement handle to execute the positioned update or delete, because it is still in use when the positioned update or delete is executing.

  4. Fetch data from the result set by calling SQLFetch() or SQLFetchScroll().
  5. Issue the UPDATE or DELETE SQL statement with the WHERE CURRENT of clause and specify the cursor name obtained in step 2. For example: 
        sprintf((char *)stmtPositionedUpdate,
            "UPDATE org SET location = 'Toronto' WHERE CURRENT of %s",
            cursorName);
  6. Position the cursor on the row of the data fetched and execute the positioned update or delete statement.