YoannD

How to use a NSQL query as DataExport or DataSync

Blog Post created by YoannD Employee on Jun 7, 2018

One thing that I found very useful in Clarity, but somewhat not really known is using native NSQL query SOAP Interface to make a output data connection with external system.

For example imagine the client asks you to create data export for the MySystemB. Usually we do a kind of process that embed a gelScript, that produce a CVS file and a kind of FTP transport this file to the target system. Of course other efficient way can be done, like a ETL task between database etc...

 

Here I will explain how we can easily do (with no code from Clarity).

 

First : Create your own NSQL Query

So the first thing to do is to create an NSQL Query, for example we can take the following one:

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:I.CODE:PRJ_CODE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:I.NAME:PRJ_NAME@
FROM     INV_INVESTMENTS I
WHERE   
(@WHERE:PARAM:USER_DEF:INTEGER:IS_ACTIVE@ IS NULL) OR
(@WHERE:PARAM:USER_DEF:INTEGER:IS_ACTIVE@ = I.IS_ACTIVE)
AND      @FILTER@

Please notice that we have added a parameter (here to filter on active project).

 

Second: SOAP Template calling the NSQL query

From documentation an other post to query a NSQL query we need to use this form:

<q:Query xmlns:q="http://www.niku.com/xog/Query">
     <q:Code>xqMyQueryCode</q:Code>
     <q:Filter>
          <param_is_active>0</param_is_active>
     </q:Filter>
</q:Query>

Where xqMyQueryCode is the unique name of the NSQLQuery. Please note that we can add filter in the element <Filter/>

Third: Try it with your preferred client

Here as I'm familiar with gelScript, I will present a test script in Gel, but we can use any language like PHP, Python, C++ or whatever that can make a SOAP call over HTTP[S].

At this point we can imagine that the client will be our MySystemB client server where need to get information from Clarity.

<gel:script  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:xog="http://www.niku.com/xog">
     <!-- Structure d'appel de la requête -->
     <gel:parse var="xmlQuery">
          <q:Query xmlns:q="http://www.niku.com/xog/Query">
               <q:Code>xqMyQueryCode</q:Code>
               <q:Filter>
                    <param_is_active>0</param_is_active>
               </q:Filter>
          </q:Query>  </gel:parse>
     <!-- Generation de l'appel -->
     <soap:invoke endpoint="http://MyFavoriteClarityServer/niku/xog" var="xmlReturn">
          <soap:message>
               <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog" >
                    <soapenv:Header>
                         <xog:Auth>
                              <xog:Username>Me</xog:Username>
                              <xog:Password>AndMyPassWord;</xog:Password>
                         </xog:Auth>
                    </soapenv:Header>
                    <soapenv:Body>
                         <gel:include select="$xmlQuery"/>
                    </soapenv:Body>
               </soapenv:Envelope>
          </soap:message>
     </soap:invoke>
     <gel:out>
          <gel:expr select="$xmlReturn"/>
     </gel:out>
</gel:script>

 

Enjoy ;-) !

Outcomes