Automic Workload Automation

 View Only
  • 1.  SQL_SEC Parameter

    Posted Jul 02, 2020 01:50 PM
    Hi guys,

    I have a query that involves subquery needs to be executed in an SQL_SEC object.

    My full query is: Select organization_id from hr_operating_units where name in (Select OU from hr_Header where ID = 123)

    Since my subquery needs to be generated based on certain conditions in another object, hence I will pass this subquery as a parameter
    into SQL_SEC object.


    My ultimate purpose is to execute a query (Constructed dynamically) and get the result.


    What I did below actually not working, can anyone suggest how can I do this in Automic, please? Thank you! :)




    ------------------------------
    Ken
    TX, Houston
    ------------------------------


  • 2.  RE: SQL_SEC Parameter

    Posted Jul 02, 2020 02:01 PM
    Interesting problem, and actually makes a lot of sense to me from a database behavior standpoint.

    I am guessing that what is happening here is;
    (1) the database engine binds the original SQL statement, and tells it there is to be one bound variable.
    (2) The bound variable is applied at run time, *after* the SQL binding process.
    (3) Therefore, it is too late for the injected SQL to perform as you would like.

    So you'll need to see if you can compose the whole SQL statement prior to the binding process.

    I have not tested this, but I wonder if you could replace this:
         MySql where name in(?)
    with;
         MySql where name in (&sub_query#)
    I suspect this idea will not work, because I don't think the variable object SQL container will resolve UC4 variables.  But it might?

    Another workaround that might be possible, is to do this with a batch SQL job which can modify the SQL statement prior to binding, and then invoke it from an VAR.EXEC.

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 3.  RE: SQL_SEC Parameter

    Posted Jul 06, 2020 06:18 PM
    Thank you  Pete! :)

    ------------------------------
    Ken
    TX, Houston
    ------------------------------



  • 4.  RE: SQL_SEC Parameter
    Best Answer

    Posted Jul 03, 2020 12:16 PM
    Hi Ken,

    as Pete explained, it's too late for binding the variable on the same level. But this should work:

    1) create your VARA.SQL as shown
    2) create a "parent" object, i.e. a SCRIPT object with the following content

    :set &sub_query# = 'Select OU from hr_Header where ID = 123'
    :set &hnd# = prep_process_var (VARA.SEC_SQL)
    :process &hnd#
    : set &line# = get_process_line (&hnd#)
    : print &line#
    :endprocess

    3) start the SCRIPT object and then open the report - it should display the result of your query.

    regards,
    Peter


  • 5.  RE: SQL_SEC Parameter

    Posted Jul 06, 2020 06:19 PM
    Thank you Peter! :)

    ------------------------------
    Ken
    TX, Houston
    ------------------------------