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:
- Generate the result set that the update or delete will
be performed on by issuing and executing the SELECT SQL statement.
- 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().
- 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.
- Fetch data from the result set by calling SQLFetch() or SQLFetchScroll().
- 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);
- Position the cursor on the row of the data fetched and
execute the positioned update or delete statement.