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
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?