Automic Workload Automation

Is there a way to retrieve all used/set variables in a workflow.
I want to write all the used variables into an external file with the format <Var_name>:<Var_Value>
pe.
:PSET &VAR1 = "Var one"
:PSET &VAR2 = "Var two" 
:PSET etc ...

Before writing I do not know which vars are all set.
Sometime I have &VAR1# .. &VAR4# and an other time I have &VAR2# .. &VAR8#

So the question is not how to write them to a file, but the question is how to determine which vars are used/set.  

Thanks in advance

That info is stored in the EV Table

https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11.2/DB%20Schema/db/_structure/HTML/EV.html

you can use this select (TSQL)
1346008 is the current Run Id of the object

select EV_Vname as 'Scriptvariable Name', EV_Value as 'Value' from EV
where EV_AH_IDNR = 1346008

Thanks for the replay
Looking at the structure thats indeed where I'm looking for.

I'm quitte a new user of Automic
Is it possible to access the EV Table from an AE-object (pe SCRI) ?
Or which ways are possible to access the EV Table.
Can you guide me on how to ... . 

Again thanks in advance

Yes, you can access the EV table via SQL Internal (SQLI) or SQL Internal Secure (SEC_SQLI)  or SQL Secure (SEC_SQL) variable object.

For SQLI:
Use this if you don't need any dynamic values in your query.
No Connection object, and you can't bind variables.
See https://docs.automic.com/documentation/webhelp/english/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaabq.htm#02

For SEC_SQLI:
You'll need a CONN (Connection) object that connects to your Automation Engine (AE) database. This will be used by this VARA object to connect to the AE database.
You can also bind variables. 
This does *not* require a database agent.
Please  see  https://docs.automic.com/documentation/webhelp/english/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaabq.htm#08

For SEC_SQL:
Requires a database agent that is started in Database Service mode.
Almost similar as SQL Internal Secure.
See https://docs.automic.com/documentation/webhelp/english/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaabq.htm#07

Note: Difference between Database Agent and Database Service Agent, see 
https://community.automic.com/discussion/8949/database-service-agent-vs-database-agent-mssql-oracle-etc

you can use an VARA.SQLI object or an SQL job
to extract the information you can use the script command prep_process_var
or for the SQL job prep_process_report

in any process tab (preproc, porc or postproc).

I personally would prefer the sqli vara...

but that depends on your requirements and how you wish to use this information.

Docu:
https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaasc.htm%3FTocPath%3DAutomation%2520Engine%2520Script%2520Guide%7COrdered%2520by%2520Function%7CData%2520Sequences%7C_____16

https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaavf.htm%3FTocPath%3DAutomation%2520Engine%2520Script%2520Guide%7COrdered%2520by%2520Function%7CData%2520Sequences%7C_____14

It's also good to mention that the EV values are only available while the objects are active (i.e. are also in EH), so you can use these values within the running objects scripts.

If you need to check variables and values that were used on previous executions, then AV can be used.

@P Jansen  as a general Hint for debugging purposes I would recommend printing the value of Variables at least once in the Job report.

To check if they are set ot not you can use following code construct:

:SET &VAR# = "&VAR#"
:PRINT "&&VAR# = &VAR#"

this does not throw an error if &VAR# is not set - instead the value of &VAR# will be ""

If you do not want that to be printed to the job Report put it in an IF clause and set e.g. the scriptvar &EXTENDED_LOG# Value 1 -- or 0  in Variables and Prompts

:IF &EXTENDED_LOG# = 1
:  SET &VAR# = "&VAR#"
:  PRINT "&&VAR# = &VAR#"
:  SET &VAR33# = "&VAR33#"
:  PRINT "&&VAR33# = &VAR33#"
...
...
...
:ENDIF


For access the EV table I can use the SQL Internal (SQLI)
I did a quick test, creating a new VARA and was able to get the info I need 

I have one additional question.
Is it also possible to use a variable in the query to decrease the count of records?!
For example 
"select EV_Vname, EV_Value from EV where EV_AH_IDNR = &$RUNID#"
update: the query gives me an error  :s

Yes this is possible but not in a standalone SQLI object -- this is not able to resolve the variable &$RUNID#

=> works only if you use it in combination with prep_process_var (in a script, workflow  or job) - then the variable &$RUNID# can be resolved within the VARA.

Hi,

I created a SQLI object (VARA.SQLI_TEST_EV) 
Query used is : select EV_AH_IDNR, EV_Vname, EV_Value from EV

In a testflow I include a script-object with the following process

I suggest you to put an  :Print "test" before the IF statement for the first time and after the IF statement in a second time, to see if condition is satisfied or not.

With Karims hint you would have found the error after a bit testing ;.)

&$RUNID# returns the RUN ID with leading zeros
the DB query returns it without leading zeros.

I put a PRINT instead of the IF clause - just for now & for testin  purposes:

: PRINT "own: &MY_RUN_ID# -- RUN# &RUN_ID# -- VAR Name: &VAR_NAME# -- Val: &VAR_VALUE#"

REPORT:
2017-03-27 21:13:30 - U00020408 own: 0001351022 -- RUN# 1351022 -- VAR Name: &TEST2123# -- Val: 55

so basically your script is fine - we just have to remove the leading Zeros using script command FORMAT:

:SET  &MY_RUN_ID# = &$RUNID#
!remove leadig Zeros
:SET &MY_RUN_ID# = FORMAT(&MY_RUN_ID#)

:SET &HND#=PREP_PROCESS_VAR(VARA.SQLI.COMMUNITY1)
:PROCESS &HND#
:   SET &RUN_ID# = GET_PROCESS_LINE(&HND#,1)
:   IF &RUN_ID# = &MY_RUN_ID#
:     SET &VAR_NAME# = GET_PROCESS_LINE(&HND#,3)
:     SET &VAR_VALUE# = GET_PROCESS_LINE(&HND#,4)
:     PRINT "own: &MY_RUN_ID# -- RUN# &RUN_ID# -- VAR Name: &VAR_NAME# -- Val: &VAR_VALUE#"
:   ENDIF
:ENDPROCESS
:CLOSE_PROCESS &HND#

REPORT:
2017-03-27 21:14:45 - U00020206 Variable '&TEST2123#' was stored with value '55'.
2017-03-27 21:14:45 - U00020408 own: 1351024 -- RUN# 1351024 -- VAR Name: &TEST2123# -- Val: 55


During job development some more prints are better than fewer.
thats it :-)

Again thanks for the tips.

Finally I got the expected results, but first I needed to handle a SQLVAR_MAX_ROWS  limitation.
See below 

Made a new script;

#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+

The output is giving me the expected result  :)

Found this 'limitation' is set in SQLVAR_MAX_ROWS (default 200)