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

Thanks

Russell Belding