Automic Workload Automation

Hi All

Thanks to your help I'm now talking to our SQL DB & running a stored procedure which is returning values based on the parameters I enter, so I'm pleased about that.

The values that are returned are shown in the Object's report but I would like to store them in a variable so I can act on them.

I'm getting back (among other things) a file name & a path name which I need to output to.

My process is as follows.  As you can see at step 4 I need to use the output values rather than just acting on them.

1. Fetch a file from an SFTP Site

2. Pass details to SQL

3. Receive approve or reject message (This I can deal with via Output scan)

4. If approved save the file to the returned path with the new filename.

5.If rejected move the file to rejected folder.

I've not done much scripting yet, that courses come later.

Update

After doing more research on here, I've found the SQL VARA Object.  Currently this is just capturing the first returned value.

Cheers Mick

Mick Moses said:

After doing more research on here, I've found the SQL VARA Object.  Currently this is just capturing the first returned value.

how do you gatehr the content of this VARA?

I would recommend using PREP_PROCESS_VAR - thats a loop for every line of the VARA
-as there is a little docu bug in 11.2 DOCU I´ll post the V11.1 Docu (same content)
https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11.1/All%20Guides/help.htm#ucaasc.htm%3FTocPath%3DAutomation%2520Engine%2520Script%2520Guide%7COrdered%2520by%2520Function%7CData%2520Sequences%7C_____16

with a JOBS.WIN or JOBS.UNIX you cann pass this line to a new file

good luck
Wolfgang

Just to save you a big amount of time (if you do not already know) during JOB generation Automic Script is always processed first, then comes JCL (OS commands) so within Pre_process & process you are not able to react on OS returncodes

BTW - same topic here

https://community.automic.com/discussion/8784/sql-to-file-automation#latest

here you have a short example:

pls adjust the column (number) Parameter in every GET_PROCESS_LINE command to your needs.

The PREP_PROCESS_VAR iterates through every line of the result ot the SQL and echos it to the new file
you should take care, that the file is not being overwritten if the job is executed several times

WIN Job Process:

:SET &FILENAME# = "C:\Temp\output.csv"
:SET &SEPERATOR# = ";"

:SET &HND#=PREP_PROCESS_VAR(VARA.SQLI.GET_EH_STATUS)
:PROCESS &HND#
:   SET &RUNID# = GET_PROCESS_LINE(&HND#,2)
:   SET &OBJ_NAME# = GET_PROCESS_LINE(&HND#,3)
:   SET &STATUS# = GET_PROCESS_LINE(&HND#,4)
:   PRINT "&RUNID#&SEPERATOR#&OBJ_NAME#&SEPERATOR#&STATUS#&SEPERATOR#"
echo &RUNID#&SEPERATOR#&OBJ_NAME#&SEPERATOR#&STATUS#&SEPERATOR# >> &FILENAME#
@set retcode=%errorlevel%
@if NOT %ERRORLEVEL% == 0 goto :retcode
:ENDPROCESS
:CLOSE_PROCESS &HND#

MuckMoses if you wnt the 2 objects as xml export for a better understanding pls let me know.

Thanks for all this Wolgang!

I'm on another job today (our first LIVE Automic job went parrellel early this AM some bits to change) so I just thought I'd let you know I've seen your comments and will update you tomorrow.

NP :)

 :)

That is brilliant!!!!!!!!!!

I have amended SET to PSET and can now access all the return values throughout my workflow.

Thank you very much Wolfgang.

The other Automic process I was working on the other day is now live, we're just waiting on an error in the process that Automic calls before we can sign it off!!  That is our first Automic process, so it's been a good week.

If we ever meet I owe you a pint (Beer).

 

Mick Moses said:

That is brilliant!!!!!!!!!!

I know  B)
No its just coding basics..

Hmmm unfortunately I don´t like beer (as the only one of all other IT guys around the world).
If you grant me a like I would also be happy :-)

THX & great that it worked fine for you!

cheers Wolfgang

Other beverages are available.......

I've ticked like on your responses is that they way to do it?  I'm new here.

Oh thanks a lot - the one with the code example would be enough :-)