Subject Re: [IBO] Commit retaining
Author Josh Higgs
Helen and Jason

Much appreciate the time you have both spent giving me a better

> A request (statement) is not the same thing as a transaction. Requests
> must happen inside a transaction, but many requests can be submitted
> through the same transaction.
> A transaction has a "start" and a "finish". Once a transaction is
> it does not finish until either a COMMIT or a ROLLBACK request is received
> by the server and is accepted.

For control, every database action in our program is done inside an explicit
StartTransaction/Rollback/Commit construct, even if only one query is to be
executed/opened. Perhaps this is slightly overkill, but it saves me having
to think :)

> CommitRetaining is a full commit. Once CommitRetaining is successfully
> called, the transaction is finished and can't be rolled back. The
> difference between CR and a "hard COMMIT" is that 1) the server does not
> destroy the cursors and other resources associated with the (now finished)
> transaction and 2) that it causes a new transaction to be started
> immediately after, using the old resources.

If as you suggest, a CR causes a new transaction to be started on the
server, How come the TIB_Transaction.Started, InTransaction, and
TransactionIsActive all return false?

I have done a couple of tests:

Iterate through a dataset (TIB_Cursor), doing updates
Call CommitRetaining
Call StartTransaction (!This works - it should have failed with "Already
in a transaction")
Call (Exception - Unknown cursor)

And another scenario

Iterate through a dataset (TIB_Cursor) doing updates
Call CommitRetaining
Call Commit - (IBObjects does nothing (stepped through source - it just
fires event in this case))

How do you commit the "New" transaction if for whatever reason, no further
updates are performed. When are the Server (cursor etc) resources freed?

> >2) Since the txn is fully committed in a CommitRetaining, where does the
> >dataset that is kept open get's it's remaining data from?

I believe the answer to this one now is "It get's it from the new
transaction that IB starts
since all the cursors are still (now) available inside the new transaction"

> fwiw, CommitRetaining is a trick that was added to InterBase by Borland as
> a way to make BDE applications act as though they were working with a
> desktop database (notably Paradox). From the POV of client/server design,
> it is an abomination, as the Harrison and Starkey will be delighted to
> you. :-)

Our product is full 3 tier, so obviously no data aware controls, sounds like
SavePoint is what I am after in my framework. It is simply to allow the
user to cancel operations that may take hours, and not have to start all
over again should they wish to temporarily hold up processing to do other

ps. One simple unrelated question - What is your opinion on database sizes
> 4GB all in one file? ok on W2k and XP? how about 98? IB6.1.0.6