Post-processing of arrays returned by REST jobs

[Michael A. Lowry]

I want to do some post-processing of the output of a REST job. The rest job returns several arrays stored in object variables. I want to return the values from these arrays in a data sequence so that I can wrap the REST jobs into EXEC VARA objects.

However, I don't want to have to write customized post-processing for each REST job. I want one JOBI I can include in the Post-Process tab of every similar job.

Is there a way to get a list of the variables and/or arrays set in the Definition & Parsing tab of a REST job? Is there a way to automatically process these variables?

Failing that, is there a way to fill an array by indirect reference?

By this I mean something like:

:DEFINE &varValues1#,STRING,100
:FILL &varValues1#[] = GET_SCRIPT_VAR(&varName1#) 

Unfortunately, the :FILL command does not currently work with the GET_SCRIPT_VAR function. If you try it, you’ll get the following error:

U00020264 Runtime error in object 'SYSTEM.GLOBAL.CREATE_DATA_SEQUENCE_FROM_REST_JOB_RESPONSE.JOBI', line '00009': Invalid function for FILL command.

It would be great if there were a function, e.g., PREP_PROCESS_RESPONSEVARS, that provides a data sequence with the names and types of the variables generated in the Response / Definition & Parsing tab of REST job. This could be combined with a new PREP_PROCESS_ARRAY function that could be used to parse the response variables that are arrays.

This would make it relatively straightforward to write the general purpose REST output parsing JOBI that I have in mind.

[kay.koll]

I do not think so that you can implement a fully custom Post Process function which deals with these Web Service generated variables.

However you can create a SQLI VARA which reads the list of variables from a REST Job. You cannot use the variables names in GET_SCRIPT_VARS but you can use them within your Post Process implementation in order to run only these code elements which using directly the variable names.
Here is an example:

:SET &HND#=PREP_PROCESS_VAR("VARA.SQLI.GET.REST.OUTPUT.VARIABLE")
:PROCESS &HND#
:   SET &VarName# = GET_PROCESS_LINE(&HND#,1)
:   SET &ColumName# = GET_PROCESS_LINE(&HND#,3)
:   PRINT "&VarName# &ColumName#"
:   IF &VarName# = "DATES#"
:       P "&DATES#[1]"
:   ENDIF
:ENDPROCESS​

Here is the SQL statement, tested with a Postgre DB.

Select OCV_Value, OCV_VName from OCV where OCV_OH_idnr IN (SELECT OH_Idnr FROM OH where OH_Name = ? and OH_Client = ? ) and (ocv_vname like 'response_part_%_useArray' OR ocv_vname like 'response_part_0_script_%_resourceName')

------------------------------
Sr. Solution Architect
Broadcom
------------------------------

[Michael A. Lowry]

Thanks, @kay.koll. I came up with something similar. I found the data in OCV, and with the help of GMB on StackOverflow, composed a query that returns the variable details in separate columns.

SQL query:

select
 oh_idnr, oh_name
 ocv_vname_common,
 max(case when ocv_var_name = 'code_0' then ocv_value end) code_0,
 max(case when ocv_var_name = 'queryType' then ocv_value end) queryType,
 max(case when ocv_var_name = 'resourceName' then ocv_value end) resourceName,
 max(case when ocv_var_name = 'saveTo' then ocv_value end) saveTo,
 max(case when ocv_var_name = 'useArray' then ocv_value end) useArray
from (
 select
 oh_idnr, oh_name,
 regexp_substr(ocv_vname, 'response_part_0_script_\d_') ocv_vname_common,
 regexp_replace(ocv_vname, 'response_part_0_script_\d_', '') ocv_var_name,
 CAST(ocv_value as VARCHAR(200)) as OCV_Value
 from OH
left join OCV on OH.OH_Idnr = OCV_OH_Idnr
where 1=1
and OH_Client = ?
and OH_DeleteFlag = 0
and OH_Name = 'UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST'
and OCV_VName like ?
) t
group by oh_idnr, oh_name, ocv_vname_common

​Example output:

OH_IDNR	OCV_VNAME_COMMON	CODE_0	QUERYTYPE	RESOURCENAME	SAVETO	USEARRAY
8420518	UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST	$[*].name	JSONPath	profileNames#	variable	TRUE
8420518	UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST	$[*].id	JSONPath	profileIds#	variable	TRUE
8420518	UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST	$[*].description	JSONPath	profileDescriptions#	variable	TRUE
8420518	UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST	$[*].environment	JSONPath	profileEnvironments#	variable	TRUE

This works only for REST jobs that return & parse single-part responses. It could potentially be expanded to support multi-part responses, but this would be a bit of work.

This addresses the first obstacle: finding the names of the variables.

Now the second obstacle remains: parsing the content of these variables when they are arrays whose names are not known ahead of time.

[Michael A. Lowry]

FWIW, here's what I have so far:

:PRINT "--- BEGIN SYSTEM.GLOBAL.CREATE_DATA_SEQUENCE_FROM_REST_JOB_RESPONSE.JOBI ---"
:SET &NAME# = &$NAME#
:SET &Hnd1# = PREP_PROCESS_VAR(SYSTEM.GLOBAL.LIST_REST_JOB_RESPONSE_VARIABLES.VARA_EXEC)
:SET &PPV_Counter# = 0
:PROCESS &Hnd1#
: SET &PPV_Counter# = &PPV_Counter# + 1
: IF &PPV_Counter# = 1
!: SET &Result# = CREATE_PROCESS(NEW) ! -- To be continued
: PRINT "REST job response variables:"
: ENDIF
: SET &VarName# = GET_PROCESS_LINE(&Hnd1#,5)
: SET &VarArray# = GET_PROCESS_LINE(&Hnd1#,7)
: IF &VarArray# = 'false' OR 'FALSE'
: SET &VarValue# = GET_SCRIPT_VAR(&VarName#)
: PRINT "&&&VarName# : &VarValue#"
: ELSE
: PRINT "Arrays not supported yet."
: ENDIF
:ENDPROCESS
:CLOSE_PROCESS &Hnd1#
:PRINT "--- END SYSTEM.GLOBAL.CREATE_DATA_SEQUENCE_FROM_REST_JOB_RESPONSE.JOBI ---"

Come to think of it, even if there were a way to parse the arrays, I'm not sure there’s a truly elegant way to present multiple values from multiple arrays in a single output data sequence. Perhaps something like this:

Variable_Name	Array	Value1	Value2	Value3	Value4	Value5
profileNames#	TRUE	DEV	ITE	PROD	EXP2	EXP
profileIds#	TRUE	1	2	3	4	5
profileDescriptions#	TRUE	Development	Testing	Production	Experimental 2	Experimental
profileEnvironments#	TRUE	DEV	ITE	PROD	EXP2	EXP

If Array is FALSE, then only Value1 will be populated. It could work I guess. One challenge will be finding out already when writing the header row how many columns are needed. It would be necessary to check the length of every array before even building the data sequence.

[Michael A. Lowry]

Oh, and for what it's worth, we are moving away from SQLIs. Long-running SQLIs can hang WPs and cause problems with the AE server itself. We have moved to SQL jobs with EXEC VARA wrappers.

[Michael A. Lowry]

@kay.koll: Is it possible to use GENERATE_SCRIPT_VARS to generate array variables?
​

[kay.koll]

I do not think so that the GENERATE_SCRIPT_VARS can be used to create arrays. I do not see an option to define whether a variable is an array or not.

I do not know any solution to generate Array variables as runtime.
My proposal is to retrieve all variables definitions from the REST Job definition and use for the non-array variables the 'GET_SCRIPT_VAR() .
You have to create in your code a list of predefined Array variable names of all array variables used in your REST jobs. 
See my example script above.

------------------------------
Sr. Solution Architect
Broadcom
------------------------------

[Michael A. Lowry]

Yeah that's what I thought. Dynamic processing of arrays does not seem possible in the current version. The names simply must be known ahead of time. This somewhat reduces the utility of arrays as a way of passing data from the REST response to the AE. I prefer not to hard-code names any more than necessary.

[Michael A. Lowry]

I submitted a new enhancement request:

Fill array by indirect reference using GET_SCRIPT_VAR function in :FILL command

[Michael A. Lowry]

Here's what I have in mind:

:SET &HND1# = PREP_PROCESS_RESPONSEVARS(&$RUNUD#)
:PROCESS &HND1#
:  SET &ResponseVarName# = GET_PROCESS_LINE(&HND1#,1)
:  SET &ResponseVarType# = GET_PROCESS_LINE(&HND1#,2)
:  IF &ResponseVarType# <> "ARRAY"
:    SET &ResponseVarValue# = GET_SCRIPT_VAR(&ResponseVarName#)
:    PRINT "&&&ResponseVarName# : &ResponseVarValue#"
:  ELSE
:   SET &ResponseVarLength# = GET_PROCESS_LINE(&HND1#,3)
:    DEFINE &MyArray#,STRING,&ResponseVarLength#
:    FILL &MyArray#[] = GET_SCRIPT_VAR(&ResponseVarName#)
:    SET &HND2# = PREP_PROCESS_ARRAY(&MyArray#)
:    SET &ArrayCounter# = 0
:    PROCESS &HND2#
:      SET &ArrayCounter# = &ArrayCounter# + 1
:      SET &ResponseVarValue# = GET_PROCESS_LINE(&HND2#,1)
:      PRINT "&&&ResponseVarName#[&ArrayCounter#] : &ResponseVarValue#"
:    ENDPROCESS
:    CLOSE_PROCESS &HND2#
:  ENDIF
:ENDPROCESS
:CLOSE_PROCESS &HND1#

This requires delivery of three enhancement requests:

1. PREP_PROCESS_RESPONSEVARS to provide list of REST job response variables
2. PREP_PROCESS_ARRAY() to process elements of an array

3. Fill array by indirect reference using GET_SCRIPT_VAR function in :FILL command

[Michael A. Lowry]

Ok, I figured out a very roundabout way of doing what I had in mind. Attached are a bunch of JOBI, JOBS_SQL, and VARA_EXEC objects. The main JOBI is SYSTEM.GLOBAL.OUTPUT_HANDLING_POSTPROC.JOBI. It belongs in the post-process tab of the JOBS object whose output/response needs to be processed. To use the JOBI, you have to first set a few variables to tell it how to behave. Here's an example.

:SET &Create_Ouput_DS# = "YES"
:SET &Verbose_Logging# = "YES"
:SET &Output_Delimiter# = ";"
:SET &Job_Type# = "REST"
:INC SYSTEM.GLOBAL.OUTPUT_HANDLING_POSTPROC.JOBI
:SET &ResponseHnd# = LOAD_PROCESS(&$RunID#,&Results#)
:SET &NumColumns# = GET_PROCESS_INFO(&ResponseHnd#,COLUMNS)
:SET &NumRows# = GET_PROCESS_INFO(&ResponseHnd#,ROWS)
:IF &NumRows# > 0
: SET &NumColumns# = GET_PROCESS_INFO(&ResponseHnd#,COLUMNS)
:ELSE
: SET &NumColumns# = 0
:ENDIF
:SET &NumColumns# = FORMAT(&NumColumns#)
:SET &NumRows# = FORMAT(&NumRows#)
:PROCESS &ResponseHnd#
: SET &VarName# = GET_PROCESS_LINE(&ResponseHnd#,1)
: SET &VarType# = GET_PROCESS_LINE(&ResponseHnd#,2)
: IF &VarName# = "profileNames#"
: PRINT "Found profile names."
: ENDIF
: IF &VarType# = "STANDARD"
: SET &VarValue# = GET_PROCESS_LINE(&ResponseHnd#,3)
: ELSE
: SET &ColumnCounter# = 3
: WHILE &ColumnCounter# < &NumColumns#
: SET &ColumnCounter# = &ColumnCounter# + 1
: SET &ColumnCounter# = FORMAT(&ColumnCounter#)
: SET &CurrentValue# = GET_PROCESS_LINE(&ResponseHnd#,&ColumnCounter#)
! Insert here handling of array variable values.
: IF &VarName# = "profileNames#"
: PRINT "Profile: &CurrentValue#"
: ENDIF
: ENDWHILE
: ENDIF
:ENDPROCESS
:CLOSE_PROCESS &ResponseHnd#

The JOBI uses several SQL queries (via EXEC VARAs) to do its magic:

1. Look up the names of the REST response variables created by the job.
2. Find out the maximum array length of these variables.
3. One by one, read the values of these variables.
4. Create an output data sequence that contains all the response variable names, their types (STANDARD/ARRAY), and their values.

This was almost certainly more trouble than it's worth, but it was an interesting exercise. I think it might be more interesting to rework this into a more targeted tool that creates a single output data sequence based on the values of a specific REST response array variable.

Anyway, enjoy!

Attachment(s)

AE job output handling.xml   34 KB 1 version

[Michael A. Lowry]

Attachment(s)

AE job output handling.xml   34 KB 1 version

[Michael A. Lowry]

Attachment(s)

AE job output handling.xml   34 KB 1 version

[kay.koll]

This is an impressive solution. 
However I will support you feature request to extend the ':FILL' / GET_SCRIPT_VAR.  
Thanks for sharing the solution

------------------------------
Sr. Solution Architect
Broadcom
------------------------------

[Michael A. Lowry]

Thanks. Come to think of it, there ought to be a straightforward way to copy an existing array to a new one.

[Michael A. Lowry]

Here's the XML file containing the AE objects.

Attachment(s)

AE job output handling.xml   34 KB 1 version

[Michael A. Lowry]

I've tried four times to attach the XML, without success. Contact me if you want it.

[Antoine Sauteron]

Hi Michael,

Thanks for sharing this. Not sure if something has changed but the xml is visible (in every post where you attached it)​:

Best regards,
Antoine

[Michael A. Lowry]

Antoine, this is what I see:

​​​​