HP 3000 Manuals HP 3000 Manuals
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.
* 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