Clarity

Expand all | Collapse all

Obtaining NSQL Query Output via XOG

Jump to Best Answer
  • 1.  Obtaining NSQL Query Output via XOG

    Posted 04-08-2011 03:17 PM
    I’m trying to devise how to XOG out the results of a custom NSQL query from Clarity. I started with a simple SQL query:

    SELECT DEPARTCODE AS DEPARTMENT, LOCATIONID AS LOCATION
    FROM CLARADM.LOCATIONDEPT

    Then turned that into NSQL easily enough:

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:CC:DEPARTCODE:DEPARTMENT@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:CC:LOCATIONID:LOCATION@
    FROM LOCATIONDEPT
    WHERE @FILTER@

    Now, I wish to be able to extract the results of this NSQL query through Clarity’s XOG capability. Browsing through the various posts on the Clarity XOG/GEL/WSDL message board, I came up with the following XOG input file:

    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_user.xsd">
    <Header version="12.0.5.5350" object="query" action="read" externalSource="NIKU"/>
    <Query xmlns:quer="http://www.niku.com/xog/Query">
    <quer:Code>locationdept</quer:Code>
    </Query>
    </NikuDataBus>

    When I run this using Clarity’s XOG client for Windows (which I use regularly to XOG various objects from our DEV copy of Clarity over to our Production copy), I get the following error output:

    <XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd">
    <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: Missing action and/or objectType in NikuDataBus request

    Can anyone help me correct what I’m doing wrong here?

    Thanks in advance for any assistance.


  • 2.  RE: Obtaining NSQL Query Output via XOG

    Posted 04-08-2011 04:03 PM
    Hello,

    The XOG client itself only supports XOGing out the actual NSQL query (not the data) if you use the following filter in the content_pack_read.xml:

    <QueryQuery>
    <Filter name="code" criteria="EQUALS">my_nsql</Filter>
    </QueryQuery>
    <QueryQuery>

    You may however be able to create a WSDL request to bring the data out. Maybe someone on this board has done that before?

    Hope this helps,
    --Kathy


  • 3.  RE: Obtaining NSQL Query Output via XOG
    Best Answer

    Posted 04-09-2011 06:54 AM
    You can run queries over the XOG client; there was a subtle trick to it though - you had to be running the XOG client in the "right" way; offhand I can't recall the trick (it might just have been running it via xog.bat instead of run.bat :unsure: or "how" you are running your PC based XOG client???) but I will dig it out of my documentation next week for you.

    As Kathy implies; its all just the web-services stuff really, but using the "Query API" instead of the "Object API"

    --

    EDIT : OK here is how I can do it;

    I've created a query in studio called "simple_query" (it just contains the default NSQL that Clarity puts in when you create a query)

    I have a properties file for my DEV environment called "Q_DEV.properties";:
    # server host name you want to test against
    servername=***.***.***.***
    portnumber=yyyy
    
    #set to true if running against a SSL enabled server
    sslenabled=false
    
    output=outputQDEV.xml
    
    username=AAAAAA
    password=BBBBBB
    
    input=../xml/QUERY_RUN.xml
    (obviously I've obscured my server and user details!)

    then I use the following XML in a file called "QUERY_RUN.xml";
    <?xml version="1.0" encoding="UTF-8"?>
    <Query xmlns="http://www.niku.com/xog/Query">
    <Code>simple_query</Code>
    </Query>
    (the difference here between my XML and yours is that I just don't have all the NikuDataBus stuff?)

    And finally this is my "BAT" file that I use to run XOG;
    @echo off
    PATH="C:\Program Files\Java\jre1.5.0_12\bin"
    JAVA_HOME=C:\Program Files\Java\jre1.5.0_12\bin
    if exist outputQDEV.xml erase outputQDEV.xml
    call xog -propertyfile QDEV.properties
    @echo
    pause
    I think the important bit here is that I am using "call xog" rather than "call run"?

    --

    So when I run my BAT file, I just get an "outputQDEV.xml" that contains the output of the NSQL statement.

    ?


  • 4.  RE: Obtaining NSQL Query Output via XOG

    Posted 04-11-2011 01:52 PM
    Thank you Dave.
    That is definitely much more convenient than to use the query API from command line as the Integration guide suggests.
    The benefit of this is that you are not limited to what the XOG Object API can do but there is a supported way of querying anything in the database you can think of and getting the results out without any direct access to the database. Like if you want to query the groups with members only without the access rights or vice versa.
    The downside with this is the output format: it is xml but not the the format you can write back to Clarity.
    Like if you want to get the user data without OBS and FOS and groups for creating them in a new system.
    If you take the effort you can format your query so that the output will require only minimal search and replace for rewrite.

    Then on the other hand, for that purpose why bother, because you can export to Excel and use Excel to add the xml tags back and also reassociate with the OBS and FOS and groups of the new system.

    Martti K.


  • 5.  RE: Obtaining NSQL Query Output via XOG

    Posted 04-11-2011 02:56 PM
    Thanks, Dave ... your guidance did the trick for me! The key was simply NOT including all that NikuDataBus stuff.

    My XOG input file is now simply this:

    <Query xmlns="http://www.niku.com/xog/Query">
    <Code>locationdept</Code>
    </Query>

    ... and I get my query output in an xml file nicely as you please!

    Much appreciated!!!


  • 6.  RE: Obtaining NSQL Query Output via XOG

    Posted 04-12-2011 04:17 PM
    Would you like to share where you use the xml file in that
    <Record>
    </Record>
    <Record>
    </Record>
    <Record>
    </Record>

    format that comes out.

    Martti K.


  • 7.  RE: Obtaining NSQL Query Output via XOG

    Posted 07-19-2012 04:29 AM
    Do you have the
    <?xml version="1.0" encoding="UTF-8"?>
    code at the top of your XML?

    You also have a typo (hthttp) in your xmlns and you don't need to change that xmlns line at all (the original niku reference is fine)


  • 8.  RE: Obtaining NSQL Query Output via XOG

    Posted 07-20-2012 01:45 AM
    Hi Dave,

    Thanks for the quick reply.

    I tried to include that line in the top of that XML.And now i have tried with both of the xml below:

    <?xml version="1.0" encoding="UTF-8"?>
    <Query xmlns="http://serverAddress:port/niku/wsdl/Query">
    <Code>xid_qry_prjInfo</Code>
    </Query>

    And

    <?xml version="1.0" encoding="UTF-8"?>
    <Query xmlns="http://www.niku.com/xog/query">
    <Code>xid_qry_prjInfo</Code>
    </Query>

    but it still gives me the same error saying "Empty SOAP Request".
    But according the document attached in one of your previous posts,the person is able to fetch the data.

    Please help.

    Regards,
    Divyang Agrawal


  • 9.  RE: Obtaining NSQL Query Output via XOG

    Posted 07-20-2012 03:53 AM

    divyang_agrawal wrote:

    Please help.]l
    OK, clearly I can not debug your system remotely ; my best advice is for you to follow EXACTLY the steps in my earlier post ; i.e. set up a QUERY like I have posted, use a PROPERTIES file like I have posted, use the XML like I have posted and use the BAT FILE like I have posted (only changing the server details in the PROPERTIES file to match your system).

    So that should work?

    Then once you have it working, you can try YOUR query etc.


  • 10.  RE: Obtaining NSQL Query Output via XOG

    Posted 04-12-2011 04:21 PM
    Very cool info Dave - thanks for sharing!

    --Kathy


  • 11.  RE: Obtaining NSQL Query Output via XOG

    Posted 04-14-2011 03:54 AM
    Martti has written a "how to" in a nice PDF - attached to THIS THREAD

    +1 to Martti! B)


  • 12.  RE: Obtaining NSQL Query Output via XOG

    Posted 04-08-2014 10:11 AM

    Thanks Dave! Exactly the information we were looking for.  Would you know if parameters can be passed from XOG to NSQL.  So, for example, the same query - select employee_name, mgr_name from emplyee_table where manager = ? - would work for different manager sent at run time.

    If someone has done something similar, please share how you did it.

     

    Thanks and Regards,

    Ashutosh

     



  • 13.  RE: Obtaining NSQL Query Output via XOG

    Posted 04-08-2014 10:38 AM

    Yes parameters work just the same way as if you were building a portlet using the NSQL.

    You would just call it over XOG like this;

    <Query xmlns="http://www.niku.com/xog/Query">
    <Code>simple_query</Code>
    <Filter>
    <param_my_param1>TEST1</param_my_param1>
    </Filter>
    </Query>


    etc (where the NSQL contains the parameter @WHERE:PARAM:USER_DEF:STRING:my_param1@ - note that the NSQL parameter does not need the "param_" bit added to it)

    You can use the built-in parameters as well, such as @WHERE:PARAM:LANGUAGE@ or @WHERE:PARAM:USER_NAME@ which will pick up the relevant values from the user that you are calling the XOG with (i.e. exactly the same as if the NSQL was used for a portlet)