 |
» |
|
|
|
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.
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:
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
|
