Custom Gel Script Parameters

[Anon Anon]

We maintain three environment levels for our Clarity implementation (PROD, QA, DEV). From time to time, the development and testing environments are overwritten with the prod environment.

We have about a dozen or so processes containing custom gel scripting that utilize XOG. The environment URL, the XOG username and its associated password are written as custom script parameters rather than being hardcoded into the script in order to make them easier to update.

Whenever we refresh our environments with PROD, the DEV and QA receive versions of the processes that reference the PROD environment URL, which means they act on PROD when run. Currently, I have to manually update the three parameters in each process in order to ensure that the appropriate environment URL, username, and password are employed in each version of each process.

I'd like to be able to run a simply update SQL in my DB to take care of all of these at once, but I can't seem to find them. I can find entries that look like they should match up in the bpm_custom_script_props table but there aren't nearly enough entries to account for all of my processes. In what table can I find entries for custom script parameters?

[Anon Anon]

Hi,

You can fetch the clarity application URL from properties.config file. when custom script refer the prod, test and dev server, the URL is dynamically fetched from the properties.cofig file whatever configured from NSA --> Application settings.


Thanks
Senthil.

[Anon Anon]

Hi Senthil, Is there some identifier for fetching the URL from properties.config file, or we have to write the code to read the file from hard disk?

[Anon Anon]

Hi Vivek,

Yes, You have to make some script, to fetch the application URL from properties.config file where located to clarity application server. Please use the below script , to fetch the application URL from properties.config file.


<gel:script xmlns:x="jelly:xml"
xmlns:core="jelly:core"
xmlns:sql="jelly:sql"
xmlns:html="jelly:html"
xmlns:e="jelly:email"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xog="http://www.niku.com/xog"
xmlns:f="jelly:com.niku.union.gel.FileTagLibrary">


<!-- end get URL -->

<gel:setDataSource dbId="niku" var="connect"/>

<!-- Non Based Windows -->
<!-- The XOG URL is taken from the properties file-->
<gel:parse file="../config/properties.xml" var="properties"/>

<!-- Windows Based-->
<gel:parse file="C:\niku\clarity\config" var="properties"/>


<gel:set select="$properties//mailServer/@host" var="sn" asString="true"/>
${sn}
<gel:log>Server's name is ${sn}</gel:log>

</gel:script>



Thanks
Senthil.

[Anon Anon]

We are generating customized Emails within our workflows using a GEL script to call a stored procedure. We tested the SQL code successfully but something is wrong with our GEL Script Code. Can someone tell us what is causing the error:

gel:script xmlns:em="jelly:email"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:j="jelly:core" xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<gel:persist scope="INSTANCE" value="${gel_objectInstanceId}" var="ObjectID"/>
<gel:setDataSource dbId="Niku" var="cn_Clarity"/>
<sql:update dataSource="${cn_Clarity}">
exec NsNotifyMessage(1, 1, ${gel_objectInstanceId})
</sql:update>
</gel:script>

BPM-0704: An error occurred while executing custom script: org.apache.commons.jelly.JellyTagException: null:6:40: <sql:update> exec NsNotifyMessage(1, 1, 5085993): [CA Clarity][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '1'. 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:80) at com.niku.union.gel.GELController.invoke(GELController.java:20) at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:180) at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:727) at java.lang.Thread.run(Thread.java:595) Caused by: java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '1'. at com.ca.clarity.jdbc.base.BaseExceptions.createException(Unknown Source) at com.ca.clarity.jdbc.base.BaseExceptions.getException(Unknown Source) at com.ca.clarity.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source) at com.ca.clarity.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source) at com.ca.clarity.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source) at com.ca.clarity.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown

[Anon Anon]

Hi,

I'm trying to create a Custom Script that calls a Stored Procedure from SQL. I was provided this code but I'm not sure what goes into the "Prior"/ parameter. Can you provide me with a quality check on this and some guidance? (The 2,2 is a message)

<gel:script
xmlns:core="jelly:core"
xmlns:sql="jelly:sql"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">
<sql:setDataSource
url="jdbc:clarity:oracle://localhost:1521;SID=niku"
driver="com.ca.clarity.jdbc.oracle.OracleDriver"
user="niku"
password="niku"/>
<sql:update dataSource="${cn_Clarity}">
<gel:persist scope="INSTANCE" value="${gel_objectInstanceId}" var="ObjectID"/>
<gel:setDataSource dbId="Niku" var="cn_Clarity"/>
exec NsNotifyMessage(2, 2, ${gel_objectInstanceId})
<sql:param value="prior"/>
</sql:update>
</gel:script>

[Legacy User]

[color=#121E9C][size=4][font=Arial]hi Ed,

The sql:param tag is not required in this case since your SQL statement as written does not use any replaceable parameters (indicated by a ?). Additionally, the two statements following the sql:update tag are not needed or out of sequence. The datasource had already been set and the gel_objectinstanceid parameter is already a persistent parameter and will retain it's value in all GEL scripts run from an internal process.

Hope this helps.

Patrick
[font][size][color]

[Legacy User]

Another option is to use a sql command to look at the server. Its quick and dirty but its an alternative to looking at the properties file.

<sql:query var="qry_host">
     Select substr(Host_Name,1,8) FROM v$instance
</sql:query>

<core:forEach items="${qry_host.rowsByIndex}" var="row">
     <core:set  var="V_HOSTNAME">${row[0]}</core:set>
</core:forEach>

<core:choose>

     <core:when test="${V_HOSTNAME == 'clarpxdb'}">    
          -- Production Variable Set
     </core:when>

     <core:when test="${V_HOSTNAME == 'clartxdb'}">    
          -- Test Variable Set
     </core:when>

     <core:when test="${V_HOSTNAME == 'clardxdb'}">    
          -- Dev Variable Set  
     </core:when>

</core:choose> 

[Legacy User]

you can also use a java call to get the environment. This is more useful than the Properties if you have a clustered environment.

<!-- ****************************************GET THE XOG URL FROM THE APPLICATION **********************************************-->
<core:invokeStatic
className="com.niku.union.config.ConfigurationManager"
method="getInstance" var="config"/>
<core:set var="v_IsLocal">0</core:set>
<core:set
value="${config.getProperties().getWebServer().getWebServerInstance(0).getId()}" var="WebServerid"/>
<core:if test="${WebServerid == 'app'}">
<core:set
value="${config.getProperties().getWebServer().getWebServerInstance(0).getAddress()}" var="AddressIp"/>
<core:set
value="${config.getProperties().getWebServer().getWebServerInstance(0).getEntryUrl()}" var="EntryURL"/>
<core:set
value="${config.getProperties().getWebServer().getWebServerInstance(0).getPort()}" var="Port"/>
<core:if test="${EntryURL == 'http://localhost' }">
<gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
<core:set var="v_IsLocal">1</core:set>
<core:set var="AddressIp">${EntryURL}</core:set>
</core:if>
</core:if>
<core:if test="${WebServerid == 'nsa'}">
<core:set
value="${config.getProperties().getWebServer().getWebServerInstance(1).getAddress()}" var="AddressIp"/>
<core:set
value="${config.getProperties().getWebServer().getWebServerInstance(1).getEntryUrl()}" var="EntryURL"/>
<core:set
value="${config.getProperties().getWebServer().getWebServerInstance(1).getPort()}" var="Port"/>
<core:if test="${EntryURL == 'http://localhost' }">
<gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
<core:set var="v_IsLocal">1</core:set>
<core:set var="AddressIp">${EntryURL}</core:set>
</core:if>
</core:if>
<core:if test="${Port != null }">
<core:set value="${EntryURL}:${Port}" var="EntryURL"/>
</core:if>
<core:if test="${v_IsLocal == 0 }">
<core:if test="${AddressIp != '' }">
<core:set value="http://${AddressIp}" var="EntryURL"/>
</core:if>
</core:if>
<core:set
value="${config.getProperties().getDirectories().getInstallDir()}" var="NIKUHOME"/>
<core:set value="${EntryURL}/niku/xog" var="XOGURL"/>
<!-- ***************************************END GET XOG URL ***********************************-->

[Anon Anon]

All good suggestions, and I'll have to consider them in future process implementations.

That only partially takes care of the difficulty I initially presented, however.

The user password is an equally thorny issue. For security reasons we don't provide XOG access to all users. Thus any process that incorporates XOG utilizes a generic XOG user that has all XOG accesses. This user does not have the same password in all three environments, which is one way we maintain segregation of duties (i.e., developers have access to the generic XOG user in the DEV environment, testers in the TEST environment, admin only in PROD). I can't query the DB for the user password, because that field is encrypted.

If I still have to manually go into each process to alter the parameter password (as opposed to the URL and the password), I'm not saving a tremendous amount of time.

I still think this should be an easy SQL update statement, especially since the updates would always be taking place in our DEV/TEST environments, but I simply can't find the tables for the parameters.

Further suggestions?

[Anon Anon]

Have you tried "CMN_CUSTOM_SCRIPT_PARAMS" table?

If you have defined same name for the variable in all processes, you can create another process, which would update that parameter in the above mentioned table with the updated URL.

Example - The following update script would be housed in the custom process.





UPDATE CMN_CUSTOM_SCRIPT_PARAMS




SET PARAM_VALUE = '<URL>'




WHERE PARAM_NAME = '<Variable_Name>'

For reset of the XOG User password, you can define a dummy process that would have a parameter - XOG User Password. Mark it as Secure in the GEL Script. The current XOG User's password would be updated in this dummy process.

Another process containing the below code would be used to update the XOG User password across all the processes (provided the variable name is same across processes)





UPDATE CMN_CUSTOM_SCRIPT_PARAMS




SET





is_secure=1,





PARAM_VALUE =








(SELECT PARAM_VALUE








FROM CMN_CUSTOM_SCRIPT_PARAMS








WHERE PARAM_NAME = <user_name_parameter from dummy process>)




WHERE
PARAM_NAME = <password_variable>

Hope this helps.

Regards
~D

[Anon Anon]

Awesome! This was the table I was looking for. I was searching in vain through all the BPM_ tables, since most of the process oriented information seems to be contained there.

Everyone's suggestions for automating this rather than using parameters are appreciated and will be taken into advisement.

[Legacy User]

Try this.
if you want the complete example of how to get all the variables from the eenvironment except the username (althoug i have a script that also gets the person that ran the process if you like) send me an email.
cheers,


<!-- ***************************************Get the Password from the system ***********************************-->
<core:new
className="com.niku.union.security.DefaultSecurityIdentifier" var="secId"/>
<core:invokeStatic
className="com.niku.union.security.UserSessionControllerFactory"
method="getInstance" var="userSessionCtrl"/>
<core:set value="${userSessionCtrl.init(username, secId)}" var="secId"/>
<core:set value="${secId.getUserName()}" var="XOGUsername"/>
<core:set value="${secId.getPWD()}" var="XOGPassword"/>
<core:set value="${secId.getSessionId()}" var="sessionID"/>





<!-- ***************************************END Get the Password from the system ***********************************-->

[System]

Moving to Clarity XOG/GEL/WSDL board

[Anon Anon]

I know this is late but...

Consider using the -D parameters in the app and bg JVM settings. in NSA append a string like -DXOGUser=someuser -DXOGPass=somepass

Then, just use {XOGUser} and {XOGPass} as you would any user defined variable in your GEL script. (You can extend this to any system dependent parameters, e.g. if Dev connects to a dev remedy instance, push the remedy instance name up into the VM).

Good Luck