Hello David,

Tuesday, June 8, 2004, 1:24:31 AM, you wrote:

> <snip>
> I wrote
>>> Not at all. I believe the idea is that after COMMIT AND CHAIN, you
>>> see
>>> all updates of other transactions committed before your commit. I
>>> think the purpose is to avoid requiring an immediate BEGIN TRANSACTION
>>> call. However, I don't know exactly what they mean by access mode or
>>> diagnostics area limit
>>
> Lester Caine wrote
>> Since the SQL2003 is a typical vague standard, it's the similarities
>> and
>> differences I'm trying to master.
>> So AND CHAIN would be
>> COMMIT X
>> SET TRANSACTION X ......
>> where ...... matches the original transaction
>> Is the bit I am missing here that RETAIN keeps it's CURSOR settings as
>> they were before the COMMIT? Since I'm normally accessing Firebird via
>> IBO or PHP, some of the subtleties get hidden :)
>>

> I'm getting into areas I'm not 100% certain of here, but, sticking my
> neck out, I think:
> COMMIT AND CHAIN

> is the same as
> COMMIT
> (see all effects of other committed transactions)
> BEGIN TRANSACTION
> (no relationship between first and second transaction)

> I think sql has no concept of a snapshot. My understanding of
> firebird's COMMIT RETAINING is that it applies permanently to durable
> storage all changes made to the snapshot as of the transaction start,
> and starts a new transaction based on this modified snapshot. No
> changes made by other transactions are visible to the "retained"
> transaction. As a side effect, cursor positions should be unaffected.

> In other databases, or running at read committed "isolation", there are
> no snapshots, so +- retaining should have no effect.

So basically, by following SQL-99 and SQL-2003 standards, to have the
equivalent of FB's COMMIT RETAINING would be:

DECLARE <cursor_name> CURSOR WITH HOLD
FOR SELECT F1, F2 FROM TABLE1 FOR UPDATE;
OPEN <cursor_name>;
...
COMMIT AND CHAIN;

The WITH HOLD in the DECLARE CURSOR statement is to tell the RDBMS
that the cursor remains open when a COMMIT takes place, and will only
be closed when a CLOSE <cursor_name> statement is executed.

--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)