GEL Script in Process Not Showing Messages or Committing Update Query

[ABT Product Support]

Hi all,

 

I'm working with CA PPM 14.3, trying to write a custom script to use within a workflow process that will allow me to update the Investment's "Status Indicator" field based on a custom "Lookup - Number" attribute on the Status Report subobject. I have the process set up, and it appears to trigger correctly from the Status Report update. However, the script is not outputting any of my messages, and does not appear to commit the UPDATE statement.

 

Pretty much flying blind into GEL scripting, I have no experience other than using a few examples found here.

 

Here's the full script:

 

<gel:script xmlns:core="jelly.core"

xmlns:gel="jelly.com.niku.union.gel.GELTagLibrary"

xmlns:sql="jelly.sql">

 

<gel:setDataSource dbId="niku"/>

 

<core:catch var="sqlException">

<sql:update>

 

<![CDATA[

UPDATE inv

set inv.status_indicator = sr.abt_advert_status

FROM   inv_investments inv

       INNER JOIN odf_ca_cop_prj_statusrpt sr

         ON inv.id = sr.odf_parent_id

       INNER JOIN odf_ca_project prj

         ON inv.id = prj.id

       INNER JOIN cop_prj_statusrpt_latest_v late

         ON late.investment_id = inv.id

WHERE  inv.id = ?

       AND report_order = 1

]]>

 

<sql:param value="${gel_objectInstanceId}" />

 

<gel:log category="XML" level="INFO">Object Instance Id: "${gel_objectInstanceId}"</gel:log>

 

</sql:update>

</core:catch>

 

<core:if test="${!empty sqlException}">

  <gel:log category="XML" level="INFO">SQL Exception: "${sqlException}"</gel:log>

</core:if>

 

</gel:script>

 

 

Any guidance is extremely appreciated!

 

Thanks,

Melissa

[Sridhar Bandaru]

Assuming that your query was executed successfully from SQL client, please try this and let us know.

 

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

 

  <gel:setDataSource dbId="niku"/>

 

  <gel:log category="XML" level="INFO">Object Instance Id: ${gel_objectInstanceId}</gel:log>

 

  <core:catch var="sqlException">

<sql:update var="rowsUpdated"><![CDATA[

  UPDATE inv

  set inv.status_indicator = sr.abt_advert_status

  FROM   inv_investments inv

    INNER JOIN odf_ca_cop_prj_statusrpt sr

  ON inv.id = sr.odf_parent_id

    INNER JOIN odf_ca_project prj

  ON inv.id = prj.id

    INNER JOIN cop_prj_statusrpt_latest_v late

  ON late.investment_id = inv.id

  WHERE  inv.id = ?

    AND report_order = 1

  ]]>

  <sql:param value="${gel_objectInstanceId}" />

  </sql:update>

  </core:catch>

 

  <gel:log category="XML" level="INFO">Number of rows updated: ${rowsUpdated}</gel:log>

  <core:if test="${!empty sqlException}">

   <gel:log category="XML" level="INFO">SQL Exception: ${sqlException}</gel:log>

  </core:if>

 

</gel:script>

[Nick Darlington]

Please note that your namespace declarations in the <gel:script> tag should be jelly:core and jelly:sql not jelly.core and jelly.sql

 

The same applies in changing jelly.com.niku.union.gel.GELTagLibrary to jelly:com.niku.union.gel.GELTagLibrary

[ABT Product Support]

Thanks for catching the colons I missed - that got me further along with the messages outputting correctly.

 

Additional follow-up: If I need to run two update queries inside of the process, should they exist inside of a single script or separated into two scripts, each with its own step in the process?

[Nick Darlington]

A script can contain several actions, multiple actions that are the same type (e.g. queries) or a mix (e.q. queries and notifications and XOG requests).

 

Mostly it's down to keeping the script so that it performs what is necessary, without lingering unnecessarily (e.g. if an action like a XOG or query in your script takes time to execute that's fine, but don't for example inject any delays inside the script itself, such as waiting time in a loop inbetween different actions).

 

The rest is just making the script do whatever seems most logical for the task at hand.  If it's updating data AND notifying a team of the results for example, then it makes sense to do that in a single script and have the one piece and place to maintain these related items, no need to put them in separate (process step) actions or scripts unless you are just wanting to have step actions of different types together (e.g. a custom script for notifying a non-Clarity user or team, and a System Action for changing the values of attributes on the object), or it just seems to make more sense to you to separate them.

 

There's no absolutely right or wrong answer though (so long as you don't inject the delays into scripts between activities as mentioned, for the reason that there are a limited number of custom script threads that may execute concurrently and could result in bottlenecks).

[ABT Product Support]

Excellent, thank you for the guidance.

 

While I have my messages returning now, it appears the query itself is not being committed. The script still runs to completion and throws no errors that I can tell. Beyond the <sql:update> tags, are any additional steps required to commit the result of a query?

[Nick Darlington]

No there should be nothing needed; the tag encloses the transaction and the commit will be automatic.

 

I would confirm your rowsUpdated result or run a <sql:query> prior to the update to see and confirm the expectations of the execution, or even (whilst testing) take out the <core:catch> just in case you happen to be suppressing a problem that would otherwise normally halt the script and report/log the reason itself.

 

Exception handling is (of course) a great thing to implement, but sometimes whilst developing/testing, the raw and unhandled outputs can be useful enough.

 

Making changes to Clarity schema data using a <sql:update> tag isn't something we would actually support (needing the changes to be made some other way that is supported such as XOG), but it might be an idea to enable Clarity's Audit on the attribute in question to help see what is going on as it would still catch changes made at this level.  Sometimes we've seen processes that do actually apply the value change, only to see them immediately be reverted back again, and the audit would help show that.  Then you would know it's not necessarily the query not committing that's the issue but somewhere else to consider.

[ABT Product Support]

I'll look into the audit, exception handling, and updated rows suggestions.

 

Is utilizing a GEL script to run a XOG process to update an underlying value common practice? If so, is there any good documentation or an example script I could model after?

[David Morton]

ABT_Project_Support wrote:

Is utilizing a GEL script to run a XOG process to update an underlying value common practice? If so, is there any good documentation or an example script I could model after?

Yes absolutely ; using GEL-to-call-XOG in order to programatically manipulate any Clarity data would be very common in any custom solution which does not fit "stock Clarity behaviour".

 

There are some GEL-calling-XOG examples in the documents contained in the Gel.zip file attached to the FAQ thread ; CA Clarity General Discussion - FAQs