Clarity

  • 1.  NSQL query with parameters from within a GEL script

    Broadcom Employee
    Posted Jan 25, 2019 10:57 AM

    Hi All.

    Do someone have a working example on how to execute an NSQL query with parameters from within a GEL script (V15.x)?

    Thanks in advance

    Fabio



  • 2.  Re: NSQL query with parameters from within a GEL script

    Posted Jan 28, 2019 07:56 AM

    GEL script just calls SQL (rather than NSQL format)

     

    You can embed parameters in the SQL string that you build up (just by referencing the GEL variable) or (better practice, especially if you are going to call the same SQL statement multiple times) use a ? in the SQL text and add a SQL:PARAM to the SQL tag.

     

    Some code examples can be found in the Gel.zip file attached to the old FAQ thread CA Clarity General Discussion - FAQs 

     

    and some example of the ? stuff here ; https://communities.ca.com/message/9368156?commentID=9368156#comment-9368156 



  • 3.  Re: NSQL query with parameters from within a GEL script



  • 4.  Re: NSQL query with parameters from within a GEL script

    Broadcom Employee
    Posted Jan 30, 2019 03:38 AM

    Hi Fabio,

     

    The following gel script is sample.

    It will get projects name from SRM_PROJECTS table and write them into bg-system.log.

     

    If there is "gel:parameter" statement in gel script, the parameter will be appeared in "Custom Script Parameters" tab like as below. You will be able to pass the value to script via parameter.

    I run it in PPM15.5.1 and work fine.

     

     

    -------- Sample Gel script -------------

     

    <gel:script xmlns:core="jelly:core"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:sql="jelly:sql">

    <gel:parameter default="svong" var="ClarityUser"/>
    <gel:parameter default="svong" secure="true" var="ClarityPassword"/>

    <sql:setDataSource url="jdbc:clarity:oracle://localhost:1521;SID=niku" driver="com.ca.clarity.jdbc.oracle.OracleDriver" user="${ClarityUser}" password="${ClarityPassword}"/>
    <sql:query var="result">
          select name, unique_name from srm_projects
    </sql:query>
    <core:forEach trim="true" items="${result.rowsByIndex}" var="row">
          <core:forEach var="field" items="${row}">
                 <gel:out>${field}</gel:out>
          </core:forEach>
    </core:forEach>

    </gel:script>

    -------------------------------------------------

     

    Regards,
    Shoichi



  • 5.  Re: NSQL query with parameters from within a GEL script

    Posted Jan 31, 2019 04:28 AM

    Don't think that that example shows parameters in the SQL, it shows parameters to the GEL script.

     

    "inline";

    <sql:query var="my_sql_var">
    SELECT
    foo
    ,bar
    FROM somewhere
    WHERE something = ${my_gel_variable}
    </sql:query>

     

    With parameter (better);

    <sql:query var="my_sql_var">
    SELECT
    foo
    ,bar
    FROM somewhere
    WHERE something = ?
    <sql:param value="${my_gel_variable}"/>
    </sql:query>


  • 6.  Re: NSQL query with parameters from within a GEL script

    Broadcom Employee
    Posted Jan 31, 2019 08:10 PM

    Hi David,

     

    Thank you for update.

    As you mentioned , my example does not use parameter in SQL blocks.

    So, I defined parameter "param01" and I used it in SQL blocks like as below.

    It works fine.

     

    <gel:script xmlns:core="jelly:core"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:sql="jelly:sql">

    <gel:parameter default="svong" var="ClarityUser"/>
    <gel:parameter default="svong" secure="true" var="ClarityPassword"/>
    <gel:parameter default="svong" var="param01"/>

     

    <sql:setDataSource url="jdbc:clarity:oracle://localhost:1521;SID=niku" driver="com.ca.clarity.jdbc.oracle.OracleDriver" user="${ClarityUser}" password="${ClarityPassword}"/>

     

    <sql:query var="result">
    select name from srm_projects
    where id = ?
    <sql:param value="${param01}"/>
    </sql:query>

     

    <core:forEach trim="true" items="${result.rowsByIndex}" var="row">
       <core:forEach var="field" items="${row}">
       <gel:out>${field}</gel:out>
       </core:forEach>
    </core:forEach>

    </gel:script>

     

    Regards,

    Shoichi



  • 7.  Re: NSQL query with parameters from within a GEL script

    Posted Feb 11, 2019 11:52 PM

    Why would you have the user password as a parameter?  Everytime the password gets updated, you will have to modify all the Process steps that use it.  Off topic from the original request, just an observation.