Hi Helen

Helen Borrie wrote
[ notes about the problem Russell was having are deleted]

> I think this is just getting more and more complicated and it just

**isn't** this hard!! :) What *else* do you have on your form? If, for
example, you have a master-detail set up involving the two tables in
question, and the datasets are attached to the default transaction, and are
open, then the FK violation is occurring because trWork can't visit the
detail rows in order to delete them prior to deleting the requested master
row.

>

[rb] Yes I have these on the form and handled the other transaction by
Commit -ing it before doing a delete with trWork. I am not used to thinking
in terms of IB/SQL and IBO (ex BDE/Paradox) and had wandered into doing a
simple thing a hard way. Transaction trWork is a utility transaction and the
main transaction is trSite for an Assistant (wizard) for managing sites
connecting to a document production package using MS Word as a document
engine. A simpler way to delete a site is to do qrSite.Delete and then
refresh all the datasets in trSite by doing trSite.Refresh(true), otherwise
records deleted are not commited and still appear in a grid in the
Assistant.

[rb]However, the unnecessarily messy method I was using should still have
worked and the symptoms I saw suggests an "annoyance" in some
initialization.

> Is this the situation? It would explain the behaviour you are describing

(putting aside the extra "noise" of attempting to unprepare as an
error-handling strategy...it's not suitable this way)

[rb]The "unprepare" used as error handling seems to be wrong because I
misunderstood the "XDSQLA bug".

>
> If so, then executing the dsql within the same (default?) transaction will

work...trying to execute the dsql in a different transaction will not. And
if you do have the datasets on the form, you don't need a DSQL to perform a
delete on a selected master - its own Delete method will take care of it.
If you need some special statement or procedure call to service the delete,
use the DeleteSQL property for it.

>

[rb]I have followed your advice here and it works and yields simpler code. I
had lost my way when I saw the problem.

> Don't use the Close method of TIB_Transaction to do what you are trying

to. Rather, always test to see if it is InTransaction. To complete a
transaction, either commit it or roll it back. Don't try to unprepare a
statement without completing the transaction, one way or the other. Handle
db errors in the context of a failed commit. Note that, if a commit fails,
the transaction doesn't roll back on its own. You must roll it back
yourself and handle the error.

>

[rb]Noted.

> You are correct that the IB_Transaction will default to being the

internally created transaction if you don't assign one yourself. But you
can still take control of the default transaction and prevent autocommit,
viz.

>
> with MyDataset.IB_Transaction do
> begin
> if InTransaction then
> Commit
> else
> Rollback; /* and this will indicate that there is an unhandled error

somewhere */

>
> I don't know whether this sheds any light on your problem...
>

[rb]Your intution here is correct. Many thanks.

Russell Belding