Just had a problem come up in a database that was previously working -
at least I thought it was. I'm using PHP as the client.
Table Nodes has ID.
Table Routes has foreign key NODE_ID.
Table Elevations has foreign key ROUTE_ID.
The foreign keys have cascade update & delete.
As part of an update process, I first delete all routes & elevations for
a given node. Theoretically, I could rely on the cascade delete, but
(using a stored procedure) for a given Node I identify the associated
routes, then explicitly delete all elevations for the routes, then
delete the routes. And for convenience, the proc returns the node ID.
I call this procedure with an explicit read/write transaction and then
commit the transaction. Then I proceed with inserting the new values.
Just ran into an issue where Firebird via PHP was reporting foreign key
errors. I tried a few debug lines, then examined my logic. In my PHP
client, I start a transaction, perform the deletions, then do the
insert/updates, then commit. The theory being a full all-or-nothing
update. But since that now wasn't working...thought I'd try explicitly
committing the delete first, then performing the updates.
Same issue. Now I'm really confused. So...break out FlameRobin. I
perform the steps manually...
select NID from NODES - gives me an one.
select RID from ROUTES where NODE_ID = NID - gives me routes.
select EID from ELEVATIONS where ROUTE_ID = RID gives me a
lot.
Ok...everything's there. Now execute:
select NID from purge_routes(1234)
Now commit in that window. Then execute the route & elevation queries
again, and I shouldn't see anything...but I do! Close them and run
again...now they're clear. I haven't had this happen before - either
I'm doing something stupid, or have I got some kind of corruption?
--
Daniel
