Using script variables as bind variable equivalents in SQL Jobs

Products

• Automic SaaS
• CA Automic One Automation
• CA Automic Workload Automation - Automation Engine

Issue/Introduction

In SEC_SQL variables, there is the option to use bind variables as variables with a VARA object and pass values in via Automic scripting at runtime of other tasks.  An example could be:

where the ? in the query is replaced by the value in bind parameter 1 on the right.  Or:

where a script passes in a value for &phrase_to_select# and then reads the result, for example in a prep_process_var statement like:

:set &var_name# = 'vara.sec_sql.select_phrase'
:set &phrase_to_select# = 'hello world'
:set &hnd# = prep_process_var(&var_name#)
:  set &line# = get_process_line(&hnd#, 1)
:  print &line#
:endprocess

What does the equivalent of bind variables look like in SQL job?

Environment

AE: Any version

Resolution

The Process tab can be used to set variables as you would with an OS job or script object within Automic and these can then be used in SQL statements.  An example would be the following:

1) Click on the script button (rather than form) in the Process tab:

2) Add something like the following:

!set the bind_var1 variable to hello world - note that the external doublequotes show this is a string in Automic and single quotes make this a string in SQL Server
:set &bind_var1# = " 'hello world' "
!select bind_var1, effectively printing it
select &bind_var1#