Automic Workload Automation

I am trying to create a dynamic prompt set wherein the third control element is a combo box whose data source depends on the values of the previous two fields.

Unfortunately, I have encountered an obstacle: When an EXEC VARA is used as the data source of a prompt set combo box, the child SQL job of the EXEC VARA does not inherit variables from the prompt set. In the course of troubleshooting this issue, I have identified at least two separate problems:

1. EXEC VARAs do not reliably pass parameter values to child objects.(Fixed)
   
2. SEC SQLI VARA objects do not work with object variables in the SQL statement.(Fixed)
   

Here is the prompt set I am using. The first field is an ordinary text box. The second is a combo box whose data source is a static VARA object.
https://us.v-cdn.net/5019921/uploads/editor/mo/lvnqwmrkhkvf.png" width="1105">

The third input field, Source Profile, is a combo box with an EXEC VARA as its data source. The EXEC VARA in turn runs an SQL job and creates a new data sequence with the results*. The two previous input fields in the prompt set provide pieces of information that required by the EXEC VARA and the SQL job it calls. In particular, the value of Project Prefix is used as part of a predicate in the SQL query, and the value of Source Environment determines which database is used.

These variables are already be set in the prompt set, but they were not automatically being passed to the child SQL job, so I added them as parameters to the EXEC VARA. It did not work. I even made the names of the variables the same between the prompt set and the SQL job, but this still didn’t work.
https://us.v-cdn.net/5019921/uploads/editor/2d/fqx1k4hbk2j6.png" width="516">

This brings me to the first problem:

1. EXEC VARAs do not reliably pass parameter values to child objects.

Update 2016.08.04 16:07 CEST: Reading parameters in the child object must be done using a prompt set or :READ statements.

Even hard-coding the values of these two parameters did not work.

https://us.v-cdn.net/5019921/uploads/editor/0g/t1zcqnbowabk.png" width="516">
I added :PRINT statements to the pre-process of the SQL job, so I could see what values were being passed. They’re always null.

Here is an even simpler example demonstrating this problem. I created a new EXEC VARA that sets three parameters:
https://us.v-cdn.net/5019921/uploads/editor/yu/vfg16tcdzhd3.png" width="446">

In the SCRI object, I returned no result but instead just printed the values of the three variables:

:PRINT Variable 1: &VAR1#
:PRINT Variable 2: &VAR2#
:PRINT Variable 3: &VAR3#

When l clicked the Preview button in the VARA, the SCRI ran. Here is the report:

2016-07-25 13:56:49 - U00020206 Variable '&VAR1#' was stored with value ''.
2016-07-25 13:56:49 - U00020206 Variable '&VAR2#' was stored with value ''.
2016-07-25 13:56:49 - U00020206 Variable '&VAR3#' was stored with value ''.
2016-07-25 13:56:49 - U00020408 Variable 1:
2016-07-25 13:56:49 - U00020408 Variable 2:
2016-07-25 13:56:49 - U00020408 Variable 3:

If an EXEC VARA cannot be relied upon to pass values to the child object, then the usefulness of this type of object will be quite limited.

Update 2016.08.04 16:07 CEST: When an EXEC VARA executes the specified executable object, any parameters that have been specified are passed to the child object using something equivalent to :PUT_PROMPT_BUFFER. For this reason, it is necessary to either create a prompt set on the child object, or insert :READ statements into that object. The names of the variables in the prompt set, or the variables read using :READ, must match the names of the parameters specified in the EXEC VARA. See [DEAD LINK https://community.automic.com/discussion/comment/23540/#Comment_23540]this comment on the VARA documentation page for more details.

2. SQLI and SEC_SQLI VARA objects do not work with object variables in the SQL statement

Update 2016.07.25 16:00 CEST: The reason for this behavior was that VAR_SECURITY_LEVEL was set to 0.

To eliminate the possibility that the problem might be limited EXEC VARA objects, I tried using a SQLI or SEC_SQLI VARA object as the data source for the prompt set combo box. I discovered that when one uses an object variable directly in the SQL, the value of the variable is not correctly inserted in the SQL statement. So this does not work:

https://us.v-cdn.net/5019921/uploads/editor/m0/lg9vgdueqt1e.png" width="709">
If I use an SEC_SQLI VARA object and pass the object variable value via a bind parameter, it works fine. E.g.,

https://us.v-cdn.net/5019921/uploads/editor/h9/rz9y31z67ydu.png" width="875">

Update 2016.07.25 16:00 CEST: The documentation page on Dynamic PromptSet Dialogs clearly mentions VAR_SECURITY_LEVEL, but somehow I overlooked it. As soon as I set it to 3, resolution of object/script varaibles in SQL started working. Since we have this set to 0 in our main systems, I set it back to 0 and switched to the secure (SEC) types of SQL VARA objects where possible.

Setting VAR_SECURITY_LEVEL to 3 did not fix the problem with parameters not being passed by EXEC VARAs.

* The reason I am using an EXEC VARA to call an SQL job, instead of just using an SQL VARA, is that the database, login, and password must be set dynamically based on the previous user input.

Automic Support replied to the incident I opened about the variable passing problem. It turns out that the variables are passed to the child object using something like :PUT_PROMPT_BUFFER. For this reason, the variables must be explicitly read in the child object, either using a prompt set, or using :READ.