At 09:43 PM 18-02-01 +1300, you wrote:

>----- Original Message -----
>From: Helen Borrie <helebor@...>
>To: <IBObjects@yahoogroups.com>
>Sent: Sunday, February 18, 2001 12:10 AM
>Subject: Re: [IBO] Explicit transaction and FK Violation
>
>
>Hello Helen
>
>Yes there is wrong code in my first posted procedure. I acknowlege this, but
>the error is not because of doing an Unprepare before a Commit, as the FK
>violation happens at the Execute statement before the Commit. Here is a
>corrected post. The same error occurs if I use an IB_Query, IB_Cursor or an
>IB_DSQL. Remember, the error happens only if cascaded deletes are required.
>
>By introducing a trWork.Close before the trWork.StartTransaction the Fk
>violation does not occur.
>
>I am using D5, IBO 3.6Cf, Win2K, IB 6.0.0.627.
>
>I have tables SITES and SITE_DATA. Field SITE_DATA(SITE) has a foreign key
>constraint with field SITES(SITE). If a site 'Auckland' is deleted from
>SITES then a cascade delete should occur and all records of site 'Auckland'
>in SITE_DATA should be deleted.
>
>When I perform a 'delete from sites where site='Auckland'' using IBConsole
>there is no problem. The SITES record is deleted and all the 'Auckland'
>records in SITE_DATA are deleted.
>
>In my application I have a connection cn, a transaction trWork, and an
>IB_DSQL dsqlWork.
>
>If I specify dsqlWork to use a default transaction (its IB_Transaction is
><default>) then programatically doing a delete of site 'Auckland' from table
>SITES which has "Auckland' records in SITE_DATA proceeds with no error.
>
>Here is the procedure I use.
>
>procedure DoSQLWork( mySQL : String);
>begin
> with DataModule1.trWork do
> begin
> try
> {Close;}{open this line and the FK violation does not occur}
> StartTransaction;
> with DataModule1.ibDSQLWork do
> begin
> try
> SQL.Clear;
> SQL.Add(mySQL);
> Prepare;
> Execute;{FK violation happens here, on first time through, no error
>2nd time through; suggesting init problem}
> except
> begin
> Unprepare;
> {declare an error}
> {close transaction}
> Exit;
> end; {SQL failed}
> end;{with ibDSQLWork}
> Commit;
> {Unprepare the DSQL}
> {IB folklore says do this to avoid a bug. Haven't found a good reference
>to this bug. Not a Firebird listed bug.}
> except
> begin
> Rollback;
> {Unprepare DSQL}
> {declare internal error}
> end;{except}
> end;{try}
> end;{with trWork}
>end;{DoSQLWork}
>
>If I specify that dsqlWork use transaction trWork (Its IB_Transaction
>property is trWork) then the deletion yields a violation of the foreign key
>constraint at the point of ibDsqlWork.Execute. ISC Error Code 33554466, a
>violation of the foreign key relation. No records in either table are
>deleted. Perform the deletion a second time and the deletion is done without
>error. The error is produced no matter what the tr.AutoCommit value is.
>
>DoSQLWork is used for tasks requiring immediate effect.
>
>A solution is to set the ibDSQL.Work.IB_Transaction to <default> but I don't
>know if this makes sense, and it may leave a hidden problem dormant.
>Specifically I do not know what is happening when I use the <default>
>transaction. The <default> transaction documentation seems to be what the
>IB_Statement IBO Help page calls the nil transaction so from that page I
>assume a <default> transaction is created on demand and destroyed (I don't
>know ... I need to get the source).
>
>By including a trWork.Close statement before a trWork.StartTransaction the
>problem goes away. Could there be a faulty initialization sequence in
>StartTransaction? Should I need to call trWork.Close?

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.

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)

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.

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.

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...

Regards,
Helen


All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________