How To Batch NSQL Query SOAP Calls

Products

• Clarity PPM On Premise

Issue/Introduction

We want to be able to filter records as the result set is large. using SOAP.
Is there a batching mechanism to retrieve only x amount of records at a time or make the result set smaller?

Environment

Release : All

Resolution

The following steps can be performed.

A. Ensure Clarity app service is started.

Create NSQL Query example:


SELECT
  @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:inv.id:KEY_ID@, 
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:inv.name:PROJECT_NAME@ 
FROM 
  inv_investments inv 
WHERE 
  inv.is_active=1 
AND @FILTER@

B. Download/Use application such as SoapUI to make SOAP calls.

1. Get Session ID

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:obj="http://www.niku.com/xog/Object">
   <soapenv:Header/>
   <soapenv:Body>
      <obj:Login>
         <obj:Username>your_admin</obj:Username>
         <obj:Password>your_pwd</obj:Password>
      </obj:Login>
   </soapenv:Body>
</soapenv:Envelope>

2. Place the session id in the following read XML 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
 xmlns:quer="http://www.niku.com/xog/Query"> 
    <soapenv:Header> 
       <quer:Auth> 
  
 <quer:SessionID>ENTER_SESSION</quer:SessionID> 
       </quer:Auth> 
    </soapenv:Header> 
    <soapenv:Body> 
       <quer:Query> 
          <quer:Code>aaa_test</quer:Code> 
       </quer:Query> 
    </soapenv:Body> 
 </soapenv:Envelope> 

C. Review the XML output/file.

Additional Information

To filter results on Oracle db:
Modify the query by adding the row_num to the NSQL query to filter out the records as required in SOAP calls

XML Open Gateway (XOG) Development