At 09:24 PM 17-02-01 +1300, you wrote:

>Hello Team IBO
>
>I have not been able to solve the following problem and would appreciated advice. (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
> StartTransaction;
> with DataModule1.ibDSQLWork do
> begin
> SQL.Clear;
> SQL.Add(mySQL);
> Prepare;
> Execute;
> Unprepare;{IB folklore says do this to avoid a bug. Haven't found a good reference to this bug.}

Here is your problem. You are attempting to Unprepare the statement before it is committed. This invalidates any values passed in your SQL.

> end;{with ibDSQLWork}
> finally
> Commit;
> end;{try}
> end;{with trWork}
>end;{DoSQLWork}

Do it this way:

procedure DoSQLWork( mySQL : String);
begin
with DataModule1.trWork do
begin
try
if InTransaction then
try
Commit
except
Rollback;
{{ and do something }};
end;
StartTransaction;
with DataModule1.ibDSQLWork do
begin
if Prepared then
Prepared := False;
SQL.Clear;
SQL.Add(mySQL);
Prepare;
Execute;
end;{with ibDSQLWork}
finally
Commit;
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.
>
>I assume the properties of my explicit tr are different than the <default> transaction but haven't dug deeper.
>
>Any help appreciated.
>
>My 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.

It wouldn't make a difference if the default transaction was not in use with another, possibly conflicting statement. It is a good idea to keep a named transaction by for use in quick DSQL like this so that you can isolate the task and respond to any errors without interfering with other possibly ongoing tasks.

You said:
{IB folklore says do this to avoid a bug. Haven't found a good reference to this bug.}

It's not IB folklore. It's a bug that has been around since v. 5 and wasn't fixed in v.6. I believe Claudio has tabled it in the Firebird bug tracker.

Cheers,
Helen

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