Toad World® Forums

Interactive input of bind variables in Automation

I have an Automation script that uses two variables (Month and Year). I use a Set Variable to create them and a Set Variable Value to establish them. The script then runs a series of scripts that use those variables to create and distribute month end reports and Excel exports.

Opening up the Automation script and editing the Set Variable Value each time is really not too painful, however I am wondering if there is a way to collect those variables much like the : convention used in SQL scripts and the Query Builder.

This question is really about other user run Automation Scripts that would benefit from this.

I suggest you’re assigning new values to those two variables (#month# and #year#) and then run the series of scripts using those two particular values. If this is the case then why not to add series of values for those two variables somwhere (say, Excel spreadsheet) and then loop through this spreadsheet using Loop Dataset activity? E.g., such spreadsheet might contain those values:

#month# #year#

Jan 2012

Feb 2012

Mar 2013

Apr 2013

May 2013

etc.

Isn’t it what you actually need?

Igor.

Well, not really. What I would like to do is have the Set Variable Value somehow interactively prompt for the values on #Month# and #Year#, much like using the Where convention found in the Query Builder where you assign the value as something like '=:Year" and ‘=:Month’ . When you execute, a window like the following pops. That would be ideal.

8625.prompt.png

How do you suggest it should be working if Automation script is scheduled? Any popup dialog obviously freezes script execution so we’re making specific efforts to get rid of situations like this.

Igor.

Hi Igor,

Yes, this is true. The idea would be to run the Automation script interactively.

I was really just curious if the functionality I described was in any way available. I am aware that Automation scripts, by their very nature, are designed to be run as scheduled processes.

If the answer on the interactively prompted variables is no, I can accept that. No sense in burning any more time on it.

As always, thanks for all your help.

Hi, I saw this discussion and am running into the same issue with getting variable prompts to work on SQL through automation. My idea would be to allow users with TDP to run the automation and hopefully have them prompt the values they need into the queries, without specifically updating the SQL to the query. Essentially can variable prompts be set up similar to how they are used in the query editor in automation? Thanks!

Bind variable prompts are support for Query Builder, SQL Editor, Data Reports, Pivot Grid and Export Wizard. The purpose of an automation script is to be run without a user. Can you provide more detail about your usecase of what an end user would need to use an automation script and be prompted for bind values?

Sure thing Debbie. Those of us who write SQL queries often have to give our query/report processes over to others who don’t necessarily know how to code SQL. With that being said, the use of variable prompts can be important for them to run queries, but change criteria on them without having to actually update each SQL file for them. A good scenario is say about 5 different queries that need to run and export reports for a process. Instead of them going to each of them individually, even using prompts, but running and exporting them in a specific format, they can use the automation task which will have the queries pre-stored, and the export specifications already set. My idea in a specific case is all the reports go to tables in an access database for further reconciliation against large data sets. With that being said, this prompt in the automation will allow them to accomplish many reports and automatic exports while only hitting the run button of the automation and entering the variable prompts and the reports generate as needed automatically, allowing in increase efficiency, plus allowing users who have little to no experience to run the same things we run on a day to day basis or for backup purposes. I saw another post from another user, who I believe was trying to accomplish the same thing with his company with other employees with less query knowledge in mind.

Lastly, I will say, that the workaround at this point is to have the variable set action at the beginning of the automation task and basically instruct the users to input each variable on that variable action before running the automation. This is fairly basic and will suffice for now, but if prompted, it could surely make it just a tad bit easier, especially for users with no toad data point experience at all.

Please let me know if you have other questions.

Thanks!

What is your end user using to request the run of the automation script? Are you saying he has Toad Data Point installed and he runs the script from TDP? Or are you wanting to publish the automation script to Toad Intelligence Central and be able to enter bind values and run the script from the Web Portal? The latter seems the most elegant and useful.

The end users do have Toad Data Point installed as well. We do not use Toad Intelligence Central at this point, nor the Web portal.

I entered QAT-7672 for this enhancement. It is too late for the TDP 4.0 release but will see what I can do for 4.1

Thank you so much Debbie.

Chad

This is wonderful. I have the exact same problem.

I need to have "Less Technical: staff run automation that controls the ins and outs but they have to be able to control the bind variables for the date search on the query.

Is this on the list to be done in 4.1 and is there an estimated timeline?

Thanks,

Judy

I had to move this to TDP 4.2. TDP 4.1 will be a 64-bit version of TDP and we hope to have that by the end of the year.

I have some good news. The latest Beta (Toad Data Point Beta 4.2.0.659) has this new feature. Any time you define a variable or email activity you will see a check box labeled “Prompt?”. If you check this the user will be prompted for the values of the variable and email prior to running. Whatever variable values they enter will be used instead of what is in the file. We had expanded this feature to include this type of support when published to Toad Intelligence Central and end users running automation scripts from Web Portal. Enjoy[;)]

Woo Hoo! This is great news. Thanks for listening to us and getting this done.

From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]

Sent: Tuesday, March 14, 2017 2:48 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Interactive input of bind variables in Automation

RE: Interactive input of bind variables in Automation

Reply by Debbie Peabody

I have some good news. The latest Beta (Toad Data Point Beta 4.2.0.659) has this new feature. Any time you define a variable or email activity you will see a check box labeled “Prompt?”. If you check this the user will be prompted for the values of the variable and email prior to running. Whatever variable values they enter will be used instead of what is in the file. We had expanded this feature to include this type of support when published to Toad Intelligence Central and end users running automation scripts from Web Portal. Enjoy[;)]

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

Hi there,
Can anyone provide and example of how to configure the PROMPT discussed above now available; with syntax and whatever else is required for Toad to cut up data by date ranges?
Thanks

Hi,

I believe you are looking for use of bind variables in Toad. This post is more specific to Variable activity which we have in our Automation module.
You can take a look on this post:
http://dev.toadfordataanalyst.com/webhelp/Content/Editor/Set_Bind_Variables.htm

Every time you run your query you will see a dialog where you can put your specific value (like start/end date).

Filip