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

Use the WHERE CURRENT OF clause to update the current row of a cursor that was declared FOR UPDATE, or to update the current element of a Collection cursor.

Here the cursor name cannot be specified as a host variable.

The current row is the most recently fetched row. Because the UPDATE statement does not advance the cursor to the next row, the current row position within the active set of the cursor is not changed by this operation.

For table hierarchies of HCL OneDB™, 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.

In ESQL/C routines, to include 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.

In SPL routines, you can specify a cursor after the WHERE CURRENT OF keywords in an UPDATE statement only if you declared the cursor_id in the FOREACH statement of SPL. You cannot use the DECLARE statement in an SPL routine to declare the name of a dynamic cursor and to associate that cursor with the statement identifier of a prepared object that the PREPARE statement has declared in the same SPL routine.

Note: An Update cursor can perform updates that are not possible with the UPDATE statement.

The following 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;
}