Connecting to Your Database
Using Embedded SQL with Oracle

After a FETCH statement completes successfully, you are positioned on a current row within the cursor. At this point, you can execute an UPDATE or DELETE statement using the WHERE CURRENT OF cursor_name syntax to update or delete the row. PowerBuilder enforces Oracle cursor update restrictions, and any violation results in an execution error.

Example 1

This cursor example illustrates how you can loop through a result set. Assume the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has been executed.

The statements retrieve rows from the employee table and then display a message box with the employee name in each row that is found.

// Declare the emp_curs cursor.
DECLARE emp_curs CURSOR FOR
   SELECT emp_name FROM EMPLOYEE 
      WHERE emp_state = :sle_1.text;
// For UPDATE WHERE CURRENT OF cursor_name and
// DELETE WHERE CURRENT OF cursor_name to work
// correctly in Oracle 7, include the FOR UPDATE
// clause in the SELECT statement.
// Declare a destination variable for employee 
// names.
string    emp_name_var
// Execute the SELECT statement with the
// current value of sle_1.text.
OPEN emp_curs;
// Fetch the first row from the result set.
FETCH emp_curs INTO :emp_name_var;
// Loop through result set until exhausted.
DO WHILE SQLCA.sqlcode = 0
// Display a message box with the employee name.
   MessageBox("Found an employee!",emp_name_var)
// Fetch the next row from the result set.
   FETCH emp_curs INTO :emp_name_var;
LOOP
// All done, so close the cursor.
CLOSE emp_curs;

Example 2

This cursor example illustrates how to use a cursor to update or delete rows. The statements use emp_curs to retrieve rows from the employee table and then ask whether the user wants to delete the employee:

// Declare the emp_curs cursor.
DECLARE emp_curs CURSOR FOR
   SELECT  emp_name FROM employee
      WHERE emp_state = :sle_1.text;
// Declare a destination variable for employee
// names. 
string emp_name_var
// Declare a return variable for the MessageBox.
int return_var 
// Execute the SELECT statement with the current
// value of sle_1.text.
OPEN emp_curs;
// Fetch the first row from the result set.
FETCH emp_curs INTO :emp_name_var;
// Loop through result set until it is
// exhausted.
DO WHILE SQLCA.sqlcode = 0
// Ask the user to confirm the deletion.
   return_var = MessageBox( "Want to delete?",& 
   emp_var_name, Question!, YesNo!, 2 )
// Delete?
If ( return_var = 1 ) then
// Yes - delete the employee.
         DELETE FROM employee 
            WHERE CURRENT OF emp_curs;
      End If
// Fetch the next row from the result set.
   FETCH emp_curs INTO :emp_name_var;
LOOP
// All done, so close the cursor.
CLOSE emp_curs;