REFETCH

The REFETCH statement allows Read Committed (RC) and Read Uncommitted (RU) transactions to acquire intended-for-update locks on data objects and to revalidate data before an update operation is issued. A refetch should always be done in RC and RU transactions before updating data to avoid update anomalies.

Scope

Application Programs Only

SQL Syntax

  REFETCH CursorName INTO {:HostVariable [[ INDICATOR] :Indicator]}[,...]

Parameters

CursorName

identifies a cursor. The cursor's active set is determined when the cursor is opened. The cursor's current position in the active set is determined by the last FETCH statement. The REFETCH statement retrieves the current row.

The cursor specified in the REFETCH statement must be declared for update and must be updatable.

INTO

The INTO clause defines where to place the row fetched.

HostVariable

identifies the host variable corresponding to one column in the fetched row.

Indicator

names the indicator variable, an output host variable whose value (see following) depends on whether the host variable contains a null value:

0: the value is not NULL
-1: the value is NULL
> 0: the value is truncated (for CHAR, VARCHAR, BINARY, and VARBINARY values only).

Description

The purpose of the REFETCH statement is to revalidate data prior to carrying out an update when using the Read Committed (RC) or Read Uncommitted (RU) isolation level in a transaction. If you do not use the REFETCH statement prior to updating a row in a RC or RU transaction, you may not be able to determine whether the row has already been modified by some other transaction.
The comparison of the refetched data with the data selected with the RC or RU statement must be on a row by row basis rather than the whole buffer because slack or filler bytes between columns are not initialized and can incorrectly influence the comparison.
Because UPDATE WHERE CURRENT does not accept a DESCRIPTOR clause for input values, the REFETCH statement does not support the USING DESCRIPTOR clause found in the FETCH statement.
No BULK option is available.
This statement cannot be used interactively or in procedures.
If there is no current row during a REFETCH, you receive the following message in the SQLCODE:
Row not found.

Authorization

You do not need authorization to use REFETCH.

Example

   label 1000;
   var
      EXEC SQL INCLUDE SQLCA;
      EXEC SQL BEGIN DECLARE SECTION;
        sqlmessage : packed array [1..132] of char;
        host1, host2, updatevalue : integer;
      EXEC SQL END DECLARE SECTION;
 
   begin
   .
   .
   .
     EXEC SQL BEGIN WORK RU;
     EXEC SQL DECLARE C1 CURSOR FOR UPDATE OF Col1 FROM T1 WHERE Predicate;
     EXEC SQL OPEN C1;
 
     repeat
       EXEC SQL FETCH C1 INTO :Host1;
       if SQLCA.sqlcode <> 0 then
          begin
             EXEC SQL SQLEXPLAIN :sqlmessage;
             write sqlmessage;
             goto 1000;
          end;
       write Host1;

Read Input. If an update is needed:

          begin
             read updatevalue;
             EXEC SQL REFETCH C1 INTO :Host2;
             if SQLCA.sqlcode <> 0 then
                 begin
                    EXEC SQL SQLEXPLAIN :sqlmessage;
                    write sqlmessage;
                    goto 1000;
                 end;
             if Host1 = Host2 then
                EXEC SQL UPDATE T1 SET Col1 = updatevalue
                     WHERE CURRENT OF C1;
             else
                write "data changed to ", Host2;
          end;
   1000:
     until SQLCA.sqlcode = 100

No More Rows Found