Clarity

Hi,

I'm new to gel scripts and xog.
There was a requirement from our client to import data from excel to clarity database using xog.
I was able to write a gel script for that and the data was imported successfully.
But now they want us to create a xog file so that they can run it in xog.client from the clarity front end.

I wrote the gel script in a soap message and tried to run it in xog.client.
But i got below error message:

<?xml version="1.0" encoding="UTF-8"?>
<XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd">
<Object type="unknown"/>
<Status state="FAILURE"/>
<Statistics failureRecords="0" insertedRecords="0"
totalNumberOfRecords="0" updatedRecords="0"/>
<ErrorInformation>
<Severity>FATAL</Severity>
<Description>Exception while processing client request</Description>
<Exception type="com.niku.xog.XOGException"><![CDATA[
com.niku.xog.XOGException: Empty SOAP Request

at com.niku.xog.util.ObjectRequestContext.extractInfo(ObjectRequestContext.java:148)

at com.niku.xog.util.ObjectRequestContext.<init>(ObjectRequestContext.java:127)

at com.niku.xog.handler.XOGProcessHandler.postProcess(XOGProcessHandler.java:43)

at com.niku.xql2.XQLVisitor.postProcess(XQLVisitor.java:1355)

at com.niku.union.xml.dom.DOMWalker.postProcess(DOMWalker.java:210)

at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:94)

at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)

at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)

at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:36)

at com.niku.xql2.XQLProcessor.process(XQLProcessor.java:239)

at com.niku.xql2.XQLProcessor.process(XQLProcessor.java:280)

at com.niku.xql2.binding.ServiceControlBindingXql.processFile(ServiceControlBindingXql.java:393)

at com.niku.xql2.binding.ServiceControlBindingXql.getDocument(ServiceControlBindingXql.java:258)

at com.niku.xql2.binding.ServiceControlBindingXql.processRequest(ServiceControlBindingXql.java:142)

at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:759)

at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:213)

at com.niku.union.web.WebServiceAdapter.processRequest(WebServiceAdapter.java:195)

at com.niku.union.web.PortletController.processServiceRequest(PortletController.java:1341)

at com.niku.union.web.PortletController.processAuthorizedPortlet(PortletController.java:861)

at com.niku.union.web.PortletController.processPageActionRequest(PortletController.java:381)

at com.niku.union.web.PortletController.processRequest(PortletController.java:160)

at com.niku.union.web.WebActionController.processAction(WebActionController.java:1090)

at com.niku.union.web.WebActionController.processRequest(WebActionController.java:170)

at com.niku.union.web.WebControlServlet.service(WebControlServlet.java:319)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at com.niku.union.web.filter.GZIPResponseFilter.doFilter(GZIPResponseFilter.java:121)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at com.niku.union.web.filter.CharsetFilter.doFilter(CharsetFilter.java:29)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)

at org.apache.catalina.valves.FastCommonAccessLogValve.invoke(FastCommonAccessLogValve.java:495)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)

at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)

at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)

at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)

at java.lang.Thread.run(Thread.java:595)
]]></Exception>
</ErrorInformation>
</XOGOutput>


Is there any workaround?

Below is my xml:

<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"

xmlns:core="jelly:core"

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

xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"

xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"

xmlns:sql="jelly:sql"

xmlns:xog="http://allscripts-qa.axisgroup.com/niku/xog"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:cats="jelly:com.clarity.CATS.customtag.CATSTagLibrary"

xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"

xmlns:u="jelly:util">

<gel:parameter default="http://allscripts-qa.axisgroup.com/niku/xog" var="XOGURL"/>
<gel:parameter default="svong" var="XOGUsername"/>
<gel:parameter default="svong" secure="true" var="XOGPassword"/>


<!-- Log into XOG and get a session ID -->
<soap:invoke endpoint="${XOGURL}/niku/xog" var="auth">
<soap:message>
<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xog="http://allscripts-qa.axisgroup.com/niku/xog">
<soapenv:Header/>
<soapenv:Body>
<xog:Login>
<xog:Username>${XOGUsername}</xog:Username>
<xog:Password>${XOGPassword}</xog:Password>
</xog:Login>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>

<!-- Checking whether a sessionID is returned. If not, it means that Login was unsuccessful -->
<gel:set asString="true" select="$auth/SOAP-ENV:Envelope/SOAP-ENV:Body/xog:SessionID/text()" var="sessionID"/>
<core:choose>
<core:when test="${sessionID == null}">
<gel:out>Couldn't Log in. Check the username/password.</gel:out>
</core:when>
<core:otherwise></core:otherwise>
</core:choose>

<!--Run XOG and attach an input file...alternatively the Body section can be the NikuDatabus section of an input file-->
<soap:invoke endpoint="${XOGURL}/niku/xog" var="runresult">
<soap:message>
<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xog="http://allscripts-qa.axisgroup.com/niku/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<core:set var="DB_Server_Name_or_IP" value="206.188.13.38" />
<core:set var="DB_Database_Name" value="eclipsys_qa"/>
<core:set var="DB_user" value="skatiyar" />
<core:set var="DB_password" value="sbsEl543" />

<core:set var="Clarity_DB" value="jdbc:sqlserver://${DB_Server_Name_or_IP}:1433;databaseName=${DB_Database_Name};user=${DB_user};password=${DB_password};"/>
<sql:setDataSource url="${Clarity_DB}" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" var="ClarityDS"/>


<file:readFile delimiter="," embedded="false" fileName="C:\My Docs\2012 PTO and FH Hours.csv" var="input"/>



<!--<sql:query var="SDD_Instance">SELECT * FROM [${worksheet}$$]</sql:query>-->

<core:forEach trim="false" begin="1" items="${input.rows}" step="1" var="instanceSDD" indexVar="i">
<core:choose>

<sql:update escapeText="false" dataSource="${ClarityDS}" >
Update odf_ca_resource
set
pto_2012 = ${instanceSDD[3]},
fltg_hlday_2012 = ${instanceSDD[4]}
where
id = (select id from srm_resources where unique_name = '${instanceSDD[2]}')
</sql:update>

</core:choose>
</core:forEach>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>


<!-- Read the output and extract some information from it -->
<gel:set asString="true" select="$runresult/SOAP-ENV:Envelope/SOAP-ENV:Body/NikuDataBus/XOGOutput/Status/@state"
var="XOGoutcome"/>
<core:switch on="${XOGoutcome}">
<core:case value="SUCCESS">



<gel:set asString="false" select="$runresult/SOAP-ENV:Envelope/SOAP-ENV:Body/NikuDataBus/XOGOutput/Statistics" var="stats"/>
<gel:out>
Success!
</gel:out>
</core:case>
<core:case value="FAILURE">

<gel:out>
XOG failed.
</gel:out>
</core:case>
<core:default>
<gel:out>Couldn't find XOG output summary. Please check the output file manually.</gel:out>
</core:default>
</core:switch>


<!-- Log out of the XOG -->
<soap:invoke endpoint="${XOGURL}/niku/xog" var="logoutresult">
<soap:message>
<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xog="http://allscripts-qa.axisgroup.com/niku/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>


<xog:Logout/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>

</gel:script>


Please help me correct the issues.

I don't understand what exactly you are pasting into the "xog.client"?

Hi

i'm pasting the below script



<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql"
xmlns:xog="http://allscripts-qa.axisgroup.com/niku/xog"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:cats="jelly:com.clarity.CATS.customtag.CATSTagLibrary"

xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"

xmlns:u="jelly:util">

<gel:parameter default="http://allscripts-qa.axisgroup.com/niku/xog" var="XOGURL"/>
<gel:parameter default="svong" var="XOGUsername"/>
<gel:parameter default="svong" secure="true" var="XOGPassword"/>


<!-- Log into XOG and get a session ID -->
<soap:invoke endpoint="${XOGURL}/niku/xog" var="auth">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xog="http://allscripts-qa.axisgroup.com/niku/xog">
<soapenv:Header/>
<soapenv:Body>
<xog:Login>
<xog:Username>${XOGUsername}</xog:Username>
<xog:Password>${XOGPassword}</xog:Password>
</xog:Login>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>

<!-- Checking whether a sessionID is returned. If not, it means that Login was unsuccessful -->
<gel:set asString="true" select="$auth/SOAP-ENV:Envelope/SOAP-ENV:Body/xog:SessionID/text()" var="sessionID"/>
<core:choose>
<core:when test="${sessionID == null}">
<gel:out>Couldn't Log in. Check the username/password.</gel:out>
</core:when>
<core:otherwise></core:otherwise>
</core:choose>

<!--Run XOG and attach an input file...alternatively the Body section can be the NikuDatabus section of an input file-->
<soap:invoke endpoint="${XOGURL}/niku/xog" var="runresult">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xog="http://allscripts-qa.axisgroup.com/niku/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<core:set var="DB_Server_Name_or_IP" value="206.188.13.38" />
<core:set var="DB_Database_Name" value="eclipsys_qa"/>
<core:set var="DB_user" value="skatiyar" />
<core:set var="DB_password" value="sbsEl543" />

<core:set var="Clarity_DB" value="jdbc:sqlserver://${DB_Server_Name_or_IP}:1433;databaseName=${DB_Database_Name};user=${DB_user};password=${DB_password};"/>
<sql:setDataSource url="${Clarity_DB}" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" var="ClarityDS"/>


<file:readFile delimiter="," embedded="false" fileName="C:\My Docs\2012 PTO and FH Hours.csv" var="input"/>



<!--<sql:query var="SDD_Instance">SELECT * FROM [${worksheet}$$]</sql:query>-->

<core:forEach trim="false" begin="1" items="${input.rows}" step="1" var="instanceSDD" indexVar="i">
<core:choose>

<sql:update escapeText="false" dataSource="${ClarityDS}" >
Update odf_ca_resource
set
pto_2012 = ${instanceSDD[3]},
fltg_hlday_2012 = ${instanceSDD[4]}
where
id = (select id from srm_resources where unique_name = '${instanceSDD[2]}')
</sql:update>

</core:choose>
</core:forEach>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>


<!-- Read the output and extract some information from it -->
<gel:set asString="true" select="$runresult/SOAP-ENV:Envelope/SOAP-ENV:Body/NikuDataBus/XOGOutput/Status/@state"
var="XOGoutcome"/>
<core:switch on="${XOGoutcome}">
<core:case value="SUCCESS">



<gel:set asString="false" select="$runresult/SOAP-ENV:Envelope/SOAP-ENV:Body/NikuDataBus/XOGOutput/Statistics" var="stats"/>
<gel:out>
Success!
</gel:out>
</core:case>
<core:case value="FAILURE">

<gel:out>
XOG failed.
</gel:out>
</core:case>
<core:default>
<gel:out>Couldn't find XOG output summary. Please check the output file manually.</gel:out>
</core:default>
</core:switch>


<!-- Log out of the XOG -->
<soap:invoke endpoint="${XOGURL}/niku/xog" var="logoutresult">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xog="http://allscripts-qa.axisgroup.com/niku/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>


<xog:Logout/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>

</gel:script>

So you are pasteing a GEL script into the XOG window - that is not going to work. :O

You need to paste XOG data into the XOG client window.

In a GEL script this would be what is in the <BODY> section of the call to the XOG web-service - it will start with a <NIikuDataBus> tag.

( Looking at your GEL script it appears you are actually updating data through direct SQL UPDATES of the database in the GEL and not through XOG at all ???? :wacko: )

--

You should also be aware that the xog.client window in the application is not officially supported* so you should not really be building solutions based on this.

* - even though it seems to work OK

thanks!
I'm totally new to xog and gel scripts -_-

All we need is to prepare some sort of xog script that can be run on xog.client and that does the same thing as the gel script is doing i.e update data based on excel file.
Can you please help me

Thanks again

As I said, your GEL script is actually just running a SQL update statement against the database - it is not XOG-ing in the data changes.

--

You appear to be trying to update a resource attribute based on a some external data in a CSV file...

So you need to understand what a resource XOG file looks like; so read out a resource using XOG (you can use the CA provided "rsm_resources_read.xml" to do this). Make sure you include the custom information in that read.
You will see in your XML somewhere the custom attribute you want to change.

So what you need to do is create a XML file that looks like that XML file you have read out, but with a different value in that custom attribute field. You then XOG that file into Clarity (through a web-service call or using that unsupported xog.client screen - I am stressing again that using the xog.client screen is unsupported here).

In practice your "input XOG" file does not actually need ALL that data that will have been produced in the file you have read out; it will only require the mandatory values and then the custom attribute value you want to update - you should experiment to work out what exactly you need to provide.

--

When you have got your head around all that, then you should realise that you should be able to construct the required XOG input file directly from your input CSV file, just with some text manipulation etc.

You NEED to understand how XOG works to get all this working; you need to read the integration manuals for that.

thanks a lot :)
you actually cleared some of my concepts