ALLBASE/SQL Reference Manual (36216-90216)

476 Chapter11
SQL Statements E - R
REFETCH
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.