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

Resolved A query about the SQLPreview Event

  1. How-to
0
Votes
Undo
  1. Krishna Mohan Gullap
    Krishna Mohan Gullap
  2. PowerBuilder
  3. Wednesday, 17 July 2024 14:18 PM UTC

I have a question about the SQLPreview Event of DataWindow

When you want to skip your current query based on a specific condition, for example, if I want to skip a DELETE query from being sent to the database, I can return 2 from this event, and it will skip the current query pass entirely, and will move on to the next query (whatever records are waiting to get UPDATED or DELETED or INSERTED)

When we use a return 2, at the end of the SQLPreview event, maybe because we decide to skip the current operation, the DataWindow removes the record from the DELETE buffer for a Delete statement ( I believe it would set the status of the row to NotModified for that row in the Primary buffer in case we skip an INSERT operation, I haven't tested this, but it's just a guess)

I have written some code to take a backup of this record in a datastore (which rather looks ugly :-) ), and use it later and this code works (although what I might do later with this copied row is TBD)

Is there a way I can instruct PowerBuilder to keep the row while I also decide to skip the current query? I think NOT. This indeed looks like a weird scenario. I can alternately return 1 but that will stop subsequent updates/deletes/inserts from happening

Any suggestion helps!

Thanks,

Krishna

PB22 R3 - save credentials
Error getting OAuth access token using RESTClient
Accepted Answer
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 9 December 2024 17:28 PM UTC
  2. PowerBuilder
  3. # Permalink
@Krishna - A word of advice, if I may... If there is ANY chance that someone else will be maintaining or enhancing this application in the future or if you will need to revisit it after this work is completed, then it would be in everybody's interest for you to carefully and completely document this code... including not only WHAT the code does, but WHY - largely because this will be very non-typical use of the DataWindow. IMHO, your SME is taking a large risk by insisting on you coding such non-standard behavior. Good luck!
Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Thursday, 12 December 2024 19:44 PM UTC
Great best practice tip John! Thanks!
  1. Helpful
There are no comments made yet.
Responses (6)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 9 December 2024 17:28 PM UTC
  2. PowerBuilder
  3. # 1
@Krishna - A word of advice, if I may... If there is ANY chance that someone else will be maintaining or enhancing this application in the future or if you will need to revisit it after this work is completed, then it would be in everybody's interest for you to carefully and completely document this code... including not only WHAT the code does, but WHY - largely because this will be very non-typical use of the DataWindow. IMHO, your SME is taking a large risk by insisting on you coding such non-standard behavior. Good luck!
Comment
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Friday, 26 July 2024 06:03 AM UTC
  2. PowerBuilder
  3. # 2

When you don't want the delete to happen, why not you change the rowstatus of the row to New! or NewModified! and then delete that row from datawindow?

Happiness Always
BKR Sivaprakash

 

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 18 July 2024 07:50 AM UTC
  2. PowerBuilder
  3. # 3

Maybe, before doing your dw update(), you can set a filter to filter out the rows in the delete buffer and then do the update?

regards.

Comment
René Ullrich
  1. René Ullrich
  2. Thursday, 18 July 2024 08:34 AM UTC
The datawindow filter function have no effect on rows in delete buffer and update function also updates filtered rows!
  1. Helpful 2
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 18 July 2024 10:31 AM UTC
Ha, I didn't know that, thanks!
  1. Helpful
There are no comments made yet.
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Thursday, 18 July 2024 05:37 AM UTC
  2. PowerBuilder
  3. # 4

I think (not tested in your case with SQLPreview) that PB will change the column and row status after all updates are made and only if resetflag parameter is TRUE (the default value).

You can set resetflag to FALSE to keep row status unchanged. Rows in Delete! buffer should be still there. You can reset the row status with ResetUpdate function. But this function does update all row status! If you want to keep some row status unchanged you have to use SetItemStatus function (to reset new and changed rows) or DeleteRow (to remove rows from Delete! buffer). 

Comment
Krishna Mohan Gullap
  1. Krishna Mohan Gullap
  2. Thursday, 18 July 2024 15:15 PM UTC
If I pass the ResetFlag parameter as False, the rows in the DELETE buffer will be retained even after DELETES are sent to the database, and for the rows INSERTED, these will still have the status as NewModified and a subsequent update() will trigger these INSERTS and DELETES again. And if I use ResetUpdate, I will reset all the rows, thereby, the ones I set aside for a subsequent update will also be reset. So, there is no way I can achieve this without an external intervention



Initially, I had used DataStores to keep track of these rows but due to some reason that eludes my rusty brain, the SME is very particular not to use anything external to the DataWindow. I have tried so many different approaches, and explained the problems with all of these, but to no avail. All of it boils down to the fact that we are all at the mercy of the DataWindow and I respect and applaud that. I usually refrain from playing with Item Statuses and intricate events like SQLPreview because I want to just let PB do its job using its already robust mechanism. If I keep touching these, the DW will feel like what I would feel if someone keeps poking their nose into my work and telling me how to do it instead of what they want. PB is going to hate me for this :-)
  1. Helpful
René Ullrich
  1. René Ullrich
  2. Friday, 19 July 2024 04:38 AM UTC
Yes, your requirements are very special and not standard datawindow functionality. I don't know why you need this. So I think it is good you have a way to do it with datawindows using SetItemStatus and and DeleteRow instead of ResetUpdate.
  1. Helpful 1
Krishna Mohan Gullap
  1. Krishna Mohan Gullap
  2. Monday, 9 December 2024 17:27 PM UTC
Exactly, but you know how some people can be so stubborn LOL ! They hear about this from someone who doesn't have an understanding of the implications of these changes and these are not standard SOPs at all, but still want something fancy though it doesn't serve the purpose. He is also not a techie and doesn't understand the alternate approaches that we suggested. LOL, people can sometimes be like that !!!



Thank you all for your words of advice !
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 17 July 2024 14:40 PM UTC
  2. PowerBuilder
  3. # 5

Hi Krishna

   As per the PB Help on that event ...

Return Values

Long. Set the return code to affect the outcome of the event:

0 -- Continue processing

1 -- Stop processing

2 -- Skip this request and execute the next request

HTH

Regards .. Chris

Comment
Krishna Mohan Gullap
  1. Krishna Mohan Gullap
  2. Wednesday, 17 July 2024 14:44 PM UTC
And there is this one too in the help



2 -- Skip this request and execute the next request



I don't see a way to control what PB does with the Buffer :-)
  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.