Using the WHERE CURRENT OF Clause (ESQL/C, SPL)

Use the WHERE CURRENT OF clause to update the current row of the active set of a cursor in the current element of a collection cursor.

The UPDATE statement does not advance the cursor to the next row, so the current row position remains unchanged.

For table hierarchies of Dynamic Server, you cannot use this clause if you are selecting from only one table in a table hierarchy. That is, you cannot use this option if you use the ONLY keyword.

To use the WHERE CURRENT OF keywords, you must have previously used the DECLARE statement to define the cursor with the FOR UPDATE option.

If the DECLARE statement that created the cursor specified one or more columns in the FOR UPDATE clause, you are restricted to updating only those columns in a subsequent UPDATE ... WHERE CURRENT OF statement. The advantage to specifying columns in the FOR UPDATE clause of a DECLARE statement is speed. The database server can usually perform updates more quickly if columns are specified in the DECLARE statement.

Before you can use the CURRENT OF keywords, you must declare a cursor with the FOREACH statement.

The following ESQL/C example illustrates the CURRENT OF form of the WHERE clause. In this example, updates are performed on a range of customers who receive 10-percent discounts (assume that a new column, discount, is added to the customer table). The UPDATE statement is prepared outside the WHILE loop to ensure that parsing is done only once.

char answer [1] = 'y';
EXEC SQL BEGIN DECLARE SECTION;
   char fname[32],lname[32];
   int low,high;
EXEC SQL END DECLARE SECTION;
main()
{
   EXEC SQL connect to 'stores_demo';
   EXEC SQL prepare sel_stmt from
      'select fname, lname from customer \
       where cust_num between ? and ? for update';

EXEC SQL declare x cursor for sel_stmt;
   printf("\nEnter lower limit customer number: ");
   scanf("%d", &low);
   printf("\nEnter upper limit customer number: ");
   scanf("%d", &high);
   EXEC SQL open x using :low, :high;
   EXEC SQL prepare u from
      'update customer set discount = 0.1  where current of x';
   while (1)
      {
      EXEC SQL fetch x into :fname, :lname;
       if ( SQLCODE == SQLNOTFOUND) break;
       }
   printf("\nUpdate %.10s %.10s (y/n)?", fname, lname);
   if (answer = getch() == 'y')
      EXEC SQL execute u;
   EXEC SQL close x;
}