At 11:31 AM 12/01/2006 -0800, you wrote:

>Hello:
>
>In my desktop application, I'm datapumping as part of an update process
>for the user's database. At the beginning of the update process I drop
>all constraints and at the end I add them all back. During the debugging
>I found that a number of records were datapumped in a particular table
>that had foreign key violations because of non-existence in the related
>table. No problem, I added a routine to delete all records that did not
>have a corresponding record in the related table. But here's the
>problem:
>
>When I try to add the FK constraint back, I get the same error message
>as though the deletion never took place. I can add the FK constraint if
>I Disconnect and then Connect, but this seems like way overkill!

Erm, not really! The database engine won't let you add an FK constraint
unless the database is in a shutdown state. (This behaviour has been
improved in Fb 2.0 but, because it involves a change to the On-disk
structure, it can't be back-ported to Fb 1.5.x.)

However, even if you are working in a shutdown database, you will get an
Object in Use exception from the engine if you try to drop or add ANY
constraint to a table that has uncommitted DML on affected rows in the
parent or child records. That behaviour won't change - it is an essential
part of referential integrity.

>I have a BeginBusy/EndBusy and Transaction.Commit at the end of the DSQL
>Delete, but this is not enough. What am I missing here to really make
>sure the transaction is committed and available to the DB so that it no
>longer sees these FK violations?

IBO can't do any magic here to break the engine's rules!

1. Put the database into shutdown state using gfix or a Service Manager
call (you'll need IBOAdmin components for this).

2. Perform the DROP CONSTRAINT and commit.

3. Perform the updates and deletes and commit.

4. Reinstate the constraint and commit.

5. Put the database back online using gfix or a Service Manager call.

Note that any oversights in your handling of the relationships will cause
failure at 4). You'll need to be prepared to catch exceptions at each step
along the way.

An effective way to pre-empt failure at 4) would be to perform the whole
update and delete sequence inside a SP that includes exception handlers
which will allow the process to skip over problem rows and log them out to
an external log table. The external table will provide "hard" details of
the problem relationships regardless of whether step 4 succeeds (and is
committed) or fails (and is rolled back).

Helen