• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Use SQL Preview to perform update on tables linked through Foreign Keys

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Use SQL Preview to perform update on tables linked through Foreign Keys

  1. How-to
0
Votes
Undo
  1. Krishna Mohan Gullap
    Krishna Mohan Gullap
  2. PowerBuilder
  3. Wednesday, 10 July 2024 13:57 PM UTC

Hello Gurus -

 

Sorry about raising this question as Critical. I need an urgent solution to the problem at hand

So, I have a screen where there is a DW which retrieves data from a parent table say CUSTOMER_MASTER, and 2 DWs which retrieve data from 2 child tables CUSTOMER_STATE and CUSTOMER_TERRITORY linked to the parent table thro the CUST_ID column

Simultaneous deletes and inserts can happen across these 3 datawindows at any point in time. I wrote a simple logic while saving to handle Deletes (Delete Child First followed by Parent) and Inserts (Insert Parent First followed by Child) separately and commit all of them at one shot. But, the customer side SME is very particular to use a 2 pass method and utilize the SQLPreview event only for this operation

Now, I know SQLPreview event gets triggered for every record which has been modified/deleted/inserted into a DataWindow when I call the Update function on it. So, if I use the SQLPreview Event, I am thinking of the below approach. This may be crude and crusty, but I am just typing what came to my mind

1. When updating the Child DW, for Inserts - Check if the parent DW has a record in Primary buffer with NewModified! status. If Yes, skip the current update and update the Parent first, and later update the Child DW (the SQLSyntax can be saved to a file specific to the child in the meantime)

2. When updating the Parent DW, for Deletes - Check if the Child has all its records in the Delete buffer. If yes, skip the current update and update the Child first, and later update the Parent DW

But, the above appears to be pretty dirty and not an elegant way to do stuff with PowerBuilder's potential

 

Any suggestions?

 

Thanks,

Krishna

PBAutoBuild and compiler error code message descri...
Remote server denied curl access ...
Responses (4)
Krishna Mohan Gullap
Krishna Mohan Gullap Accepted Answer Pending Moderation
  1. Wednesday, 10 July 2024 15:12 PM UTC
  2. PowerBuilder
  3. # 1

And one more thing. If the Binding is set to ON in the Database, this approach goes down the drain as I can't get the value of the arguments being passed to the query. Doesn't it?

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 11 July 2024 23:33 PM UTC
Hi Krishna;

BTW, you never gave us ...

DBMS version & build.

PB version & build.

DB client being used.

Connection settings being used.

Regards ... Chris
  1. Helpful
Krishna Mohan Gullap
  1. Krishna Mohan Gullap
  2. Friday, 12 July 2024 01:00 AM UTC
Am so sorry, the code snippet looks like I put it there while I was drunk, LOL :-) Just kidding



BTW, I got this code to work. I will post the modified code and explanation of what I have done to achieve this touch-your-nose-from-around-your-head approach. Maybe it will be useful to some developer being pressed by a 'I-want-only-this-approach' kind of person like in my case
  1. Helpful
There are no comments made yet.
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Wednesday, 10 July 2024 16:17 PM UTC
  2. PowerBuilder
  3. # 2

Hi

The only real issue is deletes, all other updates will work if you apply them to the parent first and then the child. With deletes these can cascade in the foreign key so deleting the child is unnecessary.

I'm assuming that you have logic to delete the rows from the child DW currently if the parent is deleted? I would change this to be a DW reset to clear the data.

I hope that helps. If I have misunderstood then let me know.

Regards

David

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 10 July 2024 16:48 PM UTC
  2. PowerBuilder
  3. # 3

Hi Krishna;

  The DBMS might handle the Parent table row & related Child table rows automatically if it supports the DB Schema "cascade on delete" option. You should check with your DBA team on the best strategy to use.  HTH 

Regards ... Chris 

Comment
There are no comments made yet.
Krishna Mohan Gullap
Krishna Mohan Gullap Accepted Answer Pending Moderation
  1. Wednesday, 10 July 2024 17:55 PM UTC
  2. PowerBuilder
  3. # 4

Problem is - There isn't a CASCADE OPTION set in the Database table and they wouldn't do that too upon request

Previously, I devised a method to segregate Deletes and Inserts thro a de-coupling logic. But the SME is very particular to use the SQLPreview but won't spill a clue :-)

I have written code to first hit the DB and look at the return code on a DELETE performed in the parent DW SQLPreview event, and if it succeeds, then return 0 (Continue processing) and if it fails, roll it back and call a return 2 (Skip and move to the next DML operation in the DW). After I return 2, I try to update the parent DW once all the child DWs have been updated (though I am not sure at this point if I can reissue the queries which I skipped earlier)

Not sure how this will turn out to be and it is cumbersome as I need to do this in the SQLPreview event of each of the child DWs too to check for Insert Operation integrity with the Parent

Will keep this thread posted with findings after a DEBUG !!! God be with ME :-)

Comment
Krishna Mohan Gullap
  1. Krishna Mohan Gullap
  2. Thursday, 11 July 2024 21:33 PM UTC
I tried using an approach where I hit the DB with a different transaction object and check if it works first, and then if it works, I let the query pass through. Else, I save the row for later updates. However, there is an issue I run into, using this approach too



The first time I use EXECUTE IMMEDIATE "sqlcommand' using NewTransObject



Based on the outcome of NewTransObject.SQLCode, I rollback or commit the transaction in the code to ensure no threads are left dangling

When the loop executes for the second time, the same EXECUTE IMMEDIATE gets stuck, LIKE, FOREVER. I am able to query the table and I don't see any LOCKS on the table as well, but this statement just doesn't get through. I even tried adding a 'DBGetTime=5' to the DBParm, this is supposed to kick out the transaction if it waits for more than 5 seconds, or atleast so I thought



Any suggestions?
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.