Clarity PPM

Expand all | Collapse all

Error with Gel Script to update the As of Date field

Jump to Best Answer
  • 1.  Error with Gel Script to update the As of Date field

    Posted 01-30-2018 01:30 PM

    I'm giving it my first shot at modifying a gel script to update the As of Date field on a project to today's date.  I thought what I did made sense but I keep getting an error.  Any help with this issue and helping me understand what the error is telling me would be great!  Let me know if you need anything further info from me.

     

    Here's the gel script:

    <gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <gel:setDataSource dbId="niku"/>
    <!-- SET AS OF DATE TO TODAY -->
    <sql:update escapeText="0"
    var="results"><![CDATA[
    UPDATE inv_projects SET prasof = sysdate
    WHERE id = ?
    ]]><sql:param value="${gel_objectInstanceId}"/>
    </sql:update>
    </gel:script>

     

    The field 'As of Date' (found on the Schedule and Performance project subpage)  that I want to update on a project instance.

     

    'As of Date' info

     

    Error:

    BPM-0704: An error occurred while executing custom script: org.apache.commons.jelly.JellyTagException: null:5:44: <sql:update> UPDATE inv_projects SET prasof = sysdate WHERE id = ?: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "ID": invalid identifier at org.apache.commons.jelly.tags.sql.UpdateTag.doTag(UpdateTag.java:116) at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:247) at org.apache.commons.jelly.impl.ScriptBlock.run(ScriptBlock.java:95) at org.apache.commons.jelly.TagSupport.invokeBody(TagSupport.java:186) at com.niku.union.gel.tags.ScriptTag.doTag(ScriptTag.java:20) at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:247) at com.niku.union.gel.GELScript.run(GELScript.java:67) at com.niku.union.gel.GELController.invoke(GELController.java:74) at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:207) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLSyntaxErrorException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "ID": invalid identifier at com.ca.clarity.jdbc.oraclebase.ddcr.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcr.a(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.a(Unknown Source) at com.ca.clarity.jdbc.oracle.ddam.t(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.y(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3._(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dddz.executeUpdate(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddd0.executeUpdate(Unknown Source) at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:68) at org.logicalcobwebs.cglib.proxy.Proxy$ProxyImpl$$EnhancerByCGLIB$$b4aace17.executeUpdate(<generated>) at org.apache.commons.jelly.tags.sql.UpdateTag.doTag(UpdateTag.java:99) ... 11 more Root cause java.sql.SQLSyntaxErrorException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "ID": invalid identifier at com.ca.clarity.jdbc.oraclebase.ddcr.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcr.a(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.a(Unknown Source) at com.ca.clarity.jdbc.oracle.ddam.t(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.y(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3._(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dddz.executeUpdate(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddd0.executeUpdate(Unknown Source) at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:68) at org.logicalcobwebs.cglib.proxy.Proxy$ProxyImpl$$EnhancerByCGLIB$$b4aace17.executeUpdate(<generated>) at org.apache.commons.jelly.tags.sql.UpdateTag.doTag(UpdateTag.java:99) at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:247) at org.apache.commons.jelly.impl.ScriptBlock.run(ScriptBlock.java:95) at org.apache.commons.jelly.TagSupport.invokeBody(TagSupport.java:186) at com.niku.union.gel.tags.ScriptTag.doTag(ScriptTag.java:20) at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:247) at com.niku.union.gel.GELScript.run(GELScript.java:67) at com.niku.union.gel.GELController.invoke(GELController.java:74) at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:207) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExe...



  • 2.  Re: Error with Gel Script to update the As of Date field

    Posted 01-30-2018 02:04 PM

    Well what is wrong with your GEL script is that it is doing an unsupported direct SQL update to the database ; you should not be doing that, you should be updating the project via XOG (called from GEL).

     

    (the actual error is just in the SQL)



  • 3.  Re: Error with Gel Script to update the As of Date field

    Posted 01-30-2018 02:15 PM

    Thanks for the response David.  That's interesting that it's unsupported because I took the script from a process that a pretty big PPM consulting company gave us.  So can you help me understand what a supported script would look like using GEL to XOG?

     

    Just wondering what is the error stating anyway? 

     

    Garrett



  • 4.  Re: Error with Gel Script to update the As of Date field

    Posted 01-30-2018 02:49 PM

    There are threads with examples how to build XML and XOG it in with GEL

    My favorite is
    https://communities.ca.com/message/101798519?commentID=101798519#comment-101798519 



  • 5.  Re: Error with Gel Script to update the As of Date field
    Best Answer

    Posted 01-30-2018 02:51 PM

    The error is stating that the ID field  is not ID but PRID



  • 6.  Re: Error with Gel Script to update the As of Date field

    Posted 01-30-2018 02:58 PM

    Thank you Urmas for both posts.  I changed the id to prid and it worked.  However I will take it back to my team that what I have is unsupported.

     

    Garett



  • 7.  Re: Error with Gel Script to update the As of Date field

    Posted 01-30-2018 03:09 PM

    Size does not matter when you talk about unsupported.

    Unsupported solutions are a fact of life.

    When deciding to go unsupported people consider things like

    - just once or repeatedly

    - will it break or can it corrupt the system

    - will it invalidate support

    - will it prevent upgrades

    - is in critical area or something with little effect and hardly ever used

     

    Many organizations prefer to play safe as the upgrades have become more and more picky.

    The release information even lists the types of customizations that have to be removed prior to upgrades.



  • 8.  Re: Error with Gel Script to update the As of Date field

    Posted 01-30-2018 04:11 PM

    Somewhat surprising that a consultancy would implement that sort of update (against a core PPM table, with a field that would likely form part of some complex internal processing).

    SQL updates against custom fields (i.e. on odf_ca_xxxxx tables) are generally considered less troublesome since those fields would not form part of "stock" functionality (and would likely not "break the system" as per urmas's list).

     

    If you are on a on-demand system, then CA would likely object to your process, if you are on-premise then the risk is your own to some extent but it will cause issues if you are requiring support from CA (you should read and understand the documentation* around such unsupported solutions before you take that risk - * there used to be a dedicated "policy" document around customization, but must admit I can't immediately find it in the current documentation sets?).



  • 9.  Re: Error with Gel Script to update the As of Date field

    Posted 01-30-2018 04:17 PM

    Thanks for your feedback David.  And we are a SaaS environment.



  • 10.  Re: Error with Gel Script to update the As of Date field

    Posted 01-31-2018 04:35 AM

    The more systems you see the less surprised you are seeing things that you are not supposed to do. ;-)



  • 11.  Re: Error with Gel Script to update the As of Date field

    Posted 02-01-2018 12:36 PM

    We, too, are on-demand and have many processes created by a major PPM consultancy that perform direct database updates - on both custom and OOTB objects.  One of the sad side effects of direct database updates we've seen is that the audit trail does not get updated.   We are updating these processes to perform XOG updates as our time allows.



  • 12.  Re: Error with Gel Script to update the As of Date field

    Posted 02-02-2018 03:27 AM

    Audit trail is populated by database triggers not by "business logic" - so you should still see audit information when you do direct database updates...

     

    HOWEVER the trick is that when you do the direct update you also need to update the last_updated_by and last_updated_date on the relevant table because the audit trigger picks those values up to place in the audit history table (CMN_AUDITS). If you don't do that, then the audit record "looks like" it happened at the previous update to the record (i.e. matching the unchanged last_update_ values).



  • 13.  Re: Error with Gel Script to update the As of Date field

    Posted 02-02-2018 09:29 AM

    Ah, okay.  That’s good to know!  No, the processes provided by our consulting company are not updating those fields.  However, we’re still switching to the supported method of inserting/updating data for on-demand.

     

    Thanks!

     

    Tanessa Richardson

    Banking Officer | M&T Bank

    Technology Governance and Shared Services | Senior Software Engineer

    717-391-8006 |  610-675-5199

    trichardson@mtb.com<mailto:trichardson@mtb.com>



  • 14.  Re: Error with Gel Script to update the As of Date field

    Posted 02-02-2018 11:59 AM

    Sorry to say going the supported way is not going to be much better.

    That is the last updated by will the user whose ID is used to log in to the XOG client.



  • 15.  Re: Error with Gel Script to update the As of Date field

    Posted 02-02-2018 12:07 PM

    ^ true that it will be the user running the XOG that would appear in the audit log, but at least the date/time would be correct.

    (in the unsupported version you could find the user triggering the process from the database process table(s) and use that in the SQL, and if you were trying to be really clever you might be able to pull the user's sessionid from the database too and use that in the XOG for the supported version)



  • 16.  Re: Error with Gel Script to update the As of Date field

    Posted 02-02-2018 10:04 AM

    Thanks for this good feedback David.  We will have to take a look at this during our testing and evaluate it.



  • 17.  Re: Error with Gel Script to update the As of Date field

    Posted 02-02-2018 10:01 AM

    Thanks for letting me know this.