|
|
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.
Application Programs Only
REFETCH CursorName INTO {:HostVariable
[[INDICATOR] :Indicator]}[,...]
- 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).
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.
You do not need authorization to use REFETCH.
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
|