WHILE LOOP in Automation not Evaluating Condition Correctly

Toad Data Point & Toad Intelligence Central
1

I have an Automation Script that worked fine in TDP 3.0.1. I now have TDP 3.6 (and have also tried it in 3.5) but the WHILE LOOP in the Automation Script are not evaluating the condition correctly once it has gone through the loop. Here’s what I mean:

I have a variable that determines the status of a job in my application. I then have a WHILE LOOP to check the status, and to wait 15 minutes before checking again. So, for example, this works fine:

  • STATUS is set, and STATUS = 0 (which means the job has completed).

  • Script enters the WHILE LOOP, where the condition is #STATUS#=1

  • TDP correctly evaluates that #STATUS#= is FALSE, and exits the WHILE loop and continues as expected.
    However, the following does NOT work:

  • STATUS is set, and STATUS = 1 (which means the job is still running).

  • Script enters the WHILE LOOP, where the condition is #STATUS#=1

  • TDP correctly evaluates that #STATUS#=1 is TRUE, then pauses for 15 minutes, and then sets STATUS=0 before looping.

  • The log shows STATUS=0, but also shows that the WHILE condition (#STATUS#=1) is TRUE. When it is clearly telling me that it is 0.

  • The script continues to get a status of 0, but evaluate the condition to TRUE until the loop limit is reached, and the rest of my script fails.
    Please help!! What am I doing wrong?!

Andrea

2

Can you please send me (igor.manokhin@quest.com) your scripts for an investigation? It would be great if you still have the original script created in 3.0.1 that was NEVER open in 3.6. If not then just send me failing script from 3.6.

Thanks,

Igor.

3

I have a suggestion of what is going wrong. You probably have two Set Variable activities in your script that work with STATUS - the one that initiates STATUS to zero located before loop activity and another one - inside loop activity that assigns STATUS another value. If this is the case then you have two different STATUS variables. The visibility of a variable is defined by a block in which the variable is defined - in your case it’s a loop activity NOT INCLUDING loop activity itself. The STATUS variable in the loop activity’s condition expression is a STATUS variable defined outside of the loop activity.

If this is the case then you have to replace STATUS’ Set Variable activity inside loop with Set Variable Value activity in which you’ll point STATUS variable defined outside of the loop.

It was working in 3.0 b/c 3.0 had a bug in this particular area.

Hope this will help. If not, please send me your script for an investigation.

Thanks,

Igor.

4

Please find an example of how Set Variable Value activity works in the attachment.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/36/3487.Script_5F00_1.tas:550:0]

5

Igor - You are right, I initially set the “STATUS” variable outside of the WHILE activity using a SQL script. My condition in the WHILE loop is evaluating that status.

I understand what you are saying about setting the STATUS variable outside of the loop, but then how do I perform the query again to check the status and re-evaluate the while condition each time it loops?

6

I noticed that there are two set variables now, Set Variable and Set Variable Value, what is the difference on how you should use them. You need to be able to set the variables initial value outstide of the loop and then monitor the variable as it gets updated inside the loop and even sometimes then test the value after exiting the loop to determine if you hit max loops or if it met the condition for exiting. Very interested in the answer to this.

7

Looking at the help, It looks like you would use the Set Variable to create and initialize the variable outside of the loop (there is some exception for using SQL type variables). Then you would use Set Variable Value to change the value of the variable inside of the loop. It looks like if you use Set Variable with the same name inside the loop it creates a new Loop level variable of the same name but independent from the one outside of the loop. I don’t really like this feature as it lends towards confusion, I prefer one variable name per automation script, it is easy enough to create a new name for inner levels if needed,.

8

That is what I am understanding as well Greg. BUT - my variable is a SQL variable - so I need to know if there is a way that I can use a loop in automation that will work. I feel like I have tried everything!!

Andrea

9

Create another variable outside of the loop. Set the variable value = to the sql variable value set in a prior step. Then use the Set Variable Value on the new variable inside of the loop. Again if you are using a sql type variable inside the loop as well then after setting the sql variable set the new variable value from outside the loop = to the loop sql variable. Hope that works, let us know if it does.

10

Thanks Greg, I appreciate your help. Maybe I’ve been looking at this far too long… but I’m not following your post. I have the SET VARIABLE that is a SQL query (i.e. select STATUS from table where job_id = 123) that sets [tag:STATUS]#

I then put in the WHILE LOOP, with the condition [tag:STATUS]# <> 2.

The first action inside the loop (to execute when the condition is TRUE) is to pause for 15 minutes, then the second action is another SET VARIABLE that runs the same SQL and ‘resets’ [tag:STATUS]# before evaluating the WHILE condition again. This worked in the older version, but not now.

So all total, using the method above, I had 2 SET VARIABLE actions, both using a SQL query. One outside the loop and one inside the loop.

Where do I add the variables you mentioned above? How many SET VARIABLEs and SET VARIABLE VALUEs should I have total?

Thanks so much for your help. This is making me crazy!

Andrea

11

Inside the loop try using SET VARIABLE VALUE instead of SET VARIABLE. If this does not work. Then create another variable using Set Variable outside of the loop right after the step where you set the [tag:STATUS]# from the SQL statement. Make that new variable, call it [tag:LoopStatus]# = [tag:STATUS]#. Then inside the loop use Set Variable Value to change the value of [tag:LoopStatus]#. You will need to change your while condition to look at the new [tag:LoopStatus]# variable instead of the [tag:Status]# variable. Hope this helps.

12

Thanks Greg, that’s what I thought you meant. I am sad to say that it didn’t work. I can’t use a SET VARIABLE VALUE inside the loop because my first SET VARIABLE
(outside of the loop) was a SQL variable.

For the second suggestion, I tried this (and a few other things) but it either A) Doesn’t reevaluate the variable or B) Is still looking for the variable that
is outside of the loop.

Any other way anyone can think of to check the status of a job, pause and check again if it is not done, or continue if it is?

Andrea

From: GregDavis11009 [mailto:bounce-GregDavis11009@toadworld.com]

Sent: Tuesday, December 02, 2014 9:52 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] WHILE LOOP in Automation not Evaluating Condition Correctly

RE: WHILE LOOP in Automation not Evaluating Condition Correctly

Reply by GregDavis11009

Inside the loop try using SET VARIABLE VALUE instead of SET VARIABLE. If this does not work. Then create another variable using Set Variable outside of the loop right after the step where you
set the
#STATUS# from the SQL statement. Make that new variable, call it
#LoopStatus# =
#STATUS#. Then inside the loop use Set Variable Value to change the value of

#LoopStatus#. You will need to change your while condition to look at the new

#LoopStatus# variable instead of the
#Status# variable. Hope this helps.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message.

13

Here is a link that might help from another post:

www.toadworld.com/.../20420.aspx

If you are trying to set the variable inside the loop with a another sql variable, you may need to do the same thing inside the loop. That is get your sql variable value and then Set Variable Value for #LoopStatus# = your inside #Status# variable (I would give the inside Status variable a different name just to be safe). Your while condition should be #LoopStatus# = 1. Make sure the variable type is integer.

14

Set Variable declares new variable AND assigns an initial value to it. Set Variable Value takes PREVIOUSLY defined variable and assigns new value to it.

Starting 3.3 we re-factored variables introducing, in particular, variable scope of visibility. Variable scope of visibility in Automation script is a block where activity that introduces the variable is placed. Blocks are: Group activity, branches of Loop, While, If…Else activities. The top-most block is a script itself.

Having said that, a variable A defined outside the while activity (of course, if while activity is not nested itself) is visible in the whole script including all the blocks that might be defined in the script. The variable B defined in the while activity is visible in the while activity branch only but including all the blocks that might be nested in this branch which is exactly the case we’re discussing.

Variable A is being used in the while activity expression but is overwritten inside while block by variable B. Even more, it is overwritten EACH time the Set Variable activity inside while block is executed.

This exact use case forced us to introduce Set Variable Value activity. In our scenario, put it inside while activity instead of Set Variable activity that defines variable B. If variable A was defined before while activity then it will be listed in the dropdown of Set Variable Value. For the variable value set something like #A# + 1 or whatever expression you need. This way, the same variable A will be used everywhere. You’ll be also able to inspect it’s value after While activity.

Please take a look at the script I posted earlier.

Sorry for the late response,

Igor.

15

Hi Igor,

I did look at the script you posted before, and I understand it. I also understand the difference between the Set Variable activity and the Set Variable Value activity. My problem is that I cannot use the Set Variable Value activity with a SQL variable, because it does not re-run the initial Set Variable query. I need that query to run each time it loops so I can see when the status changes to complete.

Andrea

16

Sorry, I misunderstood this. Then I would suggest the approach provided by Greg. I attached another script to make it a bit clearer.

One small note. SQL variable returns a result set rather than single value. In case you reference SQL variable (let’s say it has SQL_STATUS name) as #SQL_STATUS#, then the value from 1st row and 1st column is returned as a SQL variable value. In our case you seem to return a result set that is single column set with single row meaning you can reference a variable as #SQL_STATUS#. In my example I use #.# notation just to emphasize the fact that we’re working with data set.

Hopefully, I understand the problem correctly this time,

Igor.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/36/Script_5F00_2.tas:550:0]

17

Take a look at this post for some help:

www.toadworld.com/…/20420.aspx

18

Can you post your automation script, log file and any dependent files? i will take a look.

19

Somehow this thread was branched. Do you guys think it’s better to continue discussing where it started?

www.toadworld.com/…/50477.aspx

Igor.

20

Yes, you are right. Thanks, Igor