From: @marlene.GUN.de (Andrew Renney)
Message-ID: <521@marlene.GUN.de>
Date: 9 Sep 1994 03:05:45 +0100 (GMT)
Newsgroups: comp.databases.rdb,comp.databases.oracle
Subject: What happens here ?
Distribution: World
Organization: Babes in the Wood
Keywords: SQL Anomalies
Summary: Open / Delete / Fetch - Anomalie
Lines: 36



 Hi, I have the following admittedly rather strange piece of embedded SQL (Rdb
6.0, C):

PSEUDOCODE:

open write transaction
declare statement x as "select * from xyz where x = 1000"
open cursor from statement x
delete from xyz where x = 1000
fetch cursor
while (found)
	modify record
	insert record 
    fetch cursor
end while
close transaction

Well, I can hear you say "What ?!". What the original piece of code really does
is to renumerate some counter contained in the record. I have rewritten the
whole thing so that all records ( that is the information neccessary) are
fetched and after that updated. The thing which really bugs me is that at RDB
6.0 it will fetch exactly ONE record (even if more than one where x = 1000). I
assume this is because an open cursor also fetches the first record internally.
Now in an Oracle version of the same piece of code it will actually work (you
see, it was first developed on Oracle). I assume this is because there were
always less than, say, 30 rows matching the criteria and as far as I know
Oracle has a bigger internal fetch buffer. However I do not think that it would
work with large number of records. Is there a standard on this ? I believe
either NO or ALL records should be found but not SOME... Oh yes, all of the
above code is using dynamic SQL (SQLDA / SQLDA2).

Cheers from a confused RDB/Oracle user....

Andrew (renney@marlene.gun.de)