Author Topic: Periodic Update Definitions - Adding Sanity Checks  (Read 5061 times)

Chris Weitzel

  • Professional
  • ***
  • Posts: 30
Periodic Update Definitions - Adding Sanity Checks
« on: January 16, 2015, 09:33:56 AM »
A user recently got burned by a PUD that deleted 50k records (Actually the PUD was innocent.  It just did what she told it to do, but she couldn't remember what she did).  Thankfully we quickly recovered from a backup.  Had there been a message and exit point such as, "you're about to delete 50,000 records" prior to the actual deletion, much heartburn could have been avoided.

I've been reading Kevin's SB+ Solutions regarding PUDs and I've concluded (maybe incorrectly) that all I need is a simple Paragraph in the "Process after selection".  For lack of a knowledge of a better way to know how many records have been selected by the PUD, I execute the following code attempting to count the records in the select list.  The first READNEXT fails.  What's the right or better way to present the user with a count of records about to be updated / deleted?

Chris

Code: [Select]
LOCAL RECCOUNT                 
RECCOUNT = 0                   
@RTN.FLAG = 0                 
READNEXT @VALUE               
DISP 3,@VALUE:"HERE1":@RTN.FLAG
WHILE @RTN.FLAG = 0 DO         
  RECCOUNT = RECCOUNT + 1     
  READNEXT @VALUE             
REPEAT
                     


precisonline

  • President
  • Administrator
  • Rock Star
  • *****
  • Posts: 1612
    • Precision Solutions
Re: Periodic Update Definitions - Adding Sanity Checks
« Reply #1 on: January 16, 2015, 10:55:35 AM »
SB+ doesn't make it easy to get that information.  There are a few ways to do it, but the best way is not with the periodic update at all.  Best to get the number and stop the process before the periodic update gets called.

It can be a little involved to do this, but when records are going to be deleted it's worth the extra work to ensure that you're not mounting backup tapes later. ;D

Let's look at this from a usability point of view.  At the moment, the Periodic Update prompts for "Okay to Continue" or something innocuous like that, which just doesn't say enough.  In a perfect world it'd be nice to have a message pop up and say "I'm about to update xxxxxx records.  Are you sure?".  If the user responds affirmatively, then the periodic update could be called.

To do this, we'd need to start with a paragraph that would create a saved list of records to be processed.  This is vitally important, because we want to have that saved list for determining the count, and then use that same list for update.  Prelude has a subroutine that they use for saving a list after a /PD.S, you could use that.  We also have our SELECT.TO.LIST process, which you can get from my SB+ standard processes document here (http://www.precisonline.com/pdf/stdproc.pdf).

Once a list has been saved, the next step is to read the list and count the number of items there.  A /PD.S can be used for this.  Let's say you have a list called WRK.port like you might have with SELECT.TO.LIST.  The selection process would have selection criteria of WRK.:PORT (yes, type the colon and PORT exactly as it appears here) and options of RMV.  When this /PD.S is called from your paragraph it will return all of the keys in a mv'd @VALUE.  You can then get the count with:

L.CNT = DCOUNT(@VALUE,@VM)

And then display this in a message.  The whole thing might look like this:

LOCAL L.CNT
*
@VALUE = 'some select statement'
EXEC 'SELECT.TO.LIST'
*
L.CNT = DCOUNT(P('READ.WRK.LIST'),@VM) ;* Count the number of items in the list (READ.WRK.LIST is that /PD.S with RMV options)
DISP 4,'There are ' : L.CNT : ' records about to be updated.  Do you want to proceed?\No,Yes\1,0'
IF @RTN.FLAG = 0 THEN
  EXEC 'myPeriodicUpdateProcess'
END

This is off the top of my head so I can't guarantee it's perfect but it should be pretty close.  Good luck!  Let me know if you need anything more on this.
-Kevin
Accidents "happen"; success, however, is planned and executed.

DonQuixote

  • Rock Star
  • *****
  • Posts: 204
  • To Dream the Impossible Dream...
Re: Periodic Update Definitions - Adding Sanity Checks
« Reply #2 on: January 16, 2015, 03:55:36 PM »
Here is a method to stay with your original idea of using a /PUD
It gives you a record count and a warning message and allows a way out.

Use Kevin's methodology to create save-list of records as   WRK.:PORT 
(which is in your  PUDs 'Selection Criteria')

Create the following Process (I called it  WARNING.HIP) and call it in the "Process at Start"

LOCAL  R.CNT, R.ID
EXEC ">:GET-LIST WRK.":@PORT
R.CNT = -1
@RTN.FLAG = 0
WHILE @RTN.FLAG = 0 DO
   READNEXT R.ID
   R.CNT = R.CNT + 1
REPEAT
DISP 4,R.CNT:" records. Continue?\[Y]es,[N]o\0,1"



Chris Weitzel

  • Professional
  • ***
  • Posts: 30
Re: Periodic Update Definitions - Adding Sanity Checks
« Reply #3 on: January 19, 2015, 06:49:26 AM »
Helpful responses, guys.  I suppose now's the time toss a wrench into the works.  The user not only modifies the data to the right of the logical operator, but she also changes the fields on the left.  Examples: Update records with CONTRACT.NUM = "ABC"; Delete records with EXP.DATE = "01/01/15".  The ability to modify selection criteria on-the-fly is why we're using PUDs.  It's also the reason we're restoring data from backups.  :)

Maybe I'm misreading your suggestions, but as I understand them they don't use the PUD's selection criteria.  In my original post I assumed that the PUD's select list could be saved and retrieved.  Thoughts?

precisonline

  • President
  • Administrator
  • Rock Star
  • *****
  • Posts: 1612
    • Precision Solutions
Re: Periodic Update Definitions - Adding Sanity Checks
« Reply #4 on: January 19, 2015, 08:32:17 AM »
The selected items in a periodic update cannot be saved or even queried unfortunately.  Generate code for a /PD.U and you'll see how they do it.  Actually, I haven't generated code for a good long while maybe something has changed there?  It'd be worth checking out, I suppose.

Another thing you might consider... we have this utility called REMEMBER that will save a backup copy of an item before it is updated/deleted in a periodic update.  All you have to do is put the process name followed by a comma in the Process After Read, and each record that is processed will be coped to a file named REMEMBERED in a set (group) with the date and time in the name.

It might not save you from having to restore things, but it's a lot easier to restore from our REMEMBERED file than mounting a tape!
-Kevin
Accidents "happen"; success, however, is planned and executed.

Chris Weitzel

  • Professional
  • ***
  • Posts: 30
Re: Periodic Update Definitions - Adding Sanity Checks
« Reply #5 on: March 23, 2015, 07:52:13 AM »
I finally got what I wanted.  I just needed some time for a slightly modified approach to come to mind.  Once I realized how simple it was to access the selection criteria variables from the PUD, I was able to utilize your collective suggestions along with some of Prelude's standard code.  Here are the results.

Code: [Select]
LOCAL R.CNT, R.ID, UPD.TYPE
IF @RECORD<5> = "U" THEN
 UPD.TYPE = "UPDATE"
ELSE
 UPD.TYPE = "DELETE"
END
*Prepare vars for GRI.SEL from PUD
@PARMS(39) = @RECORD<6>; * selection criteria
@WORK<7,1> = @RECORD<3,1>; * file to update
EXEC "GRI.SEL"
CALL SAVE.LIST.SUB,PUD.CHECK.CES
CALL GET.LIST.SUB,PUD.CHECK.CES
R.CNT = -1
@RTN.FLAG = 0
WHILE @RTN.FLAG = 0 DO
   READNEXT R.ID
   R.CNT = R.CNT + 1
REPEAT
DISP 4,"You are about to ": UPD.TYPE : " " : R.CNT:" records. Continue?\[Y]es,[N]o\0,1"
CALL DELETE.LIST.SUB,PUD.CHECK.CES

Thanks everyone, for your help.
Chris W.