[firebird-support] Delete visibility

 

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

__._,_.___

---

Posted by: "Daniel Miller" <dmiller@amfes.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 3

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___