Execute Query

This topic describes how to run a query using the query web service.

The following is required:

Your application must have obtained a session ID. If not, review the User Sessions topic before continuing with this section.
The query you are calling has already been defined using the Cantara Console. See Managing Queries, for more details.

You can execute a query by calling the service below with a payload that follows the Query Request definition (at a minimum the payload requires a query name, start index and record count). If the execution is successful, Cantara returns a response that follows the Query Response definition. If there is a failure, Cantara returns an error. See the Error Messages topic for more information about errors.

Jump To

Info

The recordCount parameter defines how many records you want Cantara to return in the result set. This is defined to prevent excessively large payloads being returned, which could cause performance issues. For example, if a query request is accidentally sent to fetch all F4801 records, this would cause a severe performance issue. By defining a record count limit you can prevent this.

The startIndex parameter is used in scenarios when the returning set of records is greater than the defined record count. For example, if a query request is executed on the F4311 to get the lines of a purchase order and the recordCount is only set to 10 but the PO we are trying to retrieve has 15 lines. In this scenario Cantara will return the first 10 records along with the size (15), startIndex (1), and EndIndex (10) of the result set in the payload. Once the resulting 10 rows are processed, you need to fetch the 5 that were not received from the previous request. You do this by setting startIndex to 10 on the next request. (See Get UDC List for an example).

Each query requires one or more parameters based on their definition on the Cantara server (see Managing Queries). Although there multiple parameters defined for queries, only the ones marked as Required need to be included in the payload.

For information about how to obtain the definition for a query, see the Obtaining Request Definitions topic.

Service Definition: Execute Query

Endpoint: http://hostname:port/cantara/service/version/query/environment

Request-Method: POST

Note:

Environment refers to the Cantara Access Server environment you are trying to log into. For example, E1DEV. You would have previously configured this when you set up your environment. See the Managing Environments topic for more information.

You can obtain a list of configured environments by using the console to Search for an Environment.

Example - GetJobInformation

In this example we will fetch the job information for the UBE we submitted in the Execute Report Example.

Before building our payload we need to view the required parameters, as well as the criteria parameters for the GetJobInformation query. By looking at the queries definition in the Cantara console we can see that one of the criteria parameters is the jobNo. From the execute report example we know the jobNo is 152.

With this information we can now call the query service.

Endpoint: http://hostname:port/cantara/serivce/version/query/E1DEV

Request-Method: POST

Content-Type: application/json

Request Payload

JS

{
   "CriteriaParameter": [
      {
         "CriteriaLiteral": [
            "152"
         ],
         "name": "jobNo"
      }
   ],
   "name": "GetJobInformation",
   "startIndex": 1,
   "recordCount": 10,
   "tenantId": "123456",
   "apiKey": "AAA000-0000-0000-AAAA-A0A0A0A0A0",
   "cantaraVersion":"5.0"
}

Query Response

JS

{
  "Query": {
    "QueryRow": [
      {
        "Parameter": [
          {
            "value": "MSJENT91",
            "name": "executionHostName"
          },
          {
            "value": "152",
            "name": "jobNo"
          },
          {
            "value": "QBATCH",
            "name": "jobQueue"
          },
          {
            "value": "5",
            "name": "jobPriority"
          },
          {
            "value": "D",
            "name": "jobstatus"
          },
          {
            "value": "DV910",
            "name": "environmentName"
          },
          {
            "value": "JOE",
            "name": "userId"
          },
          {
            "value": "01",
            "name": "jobType"
          },
          {
            "value": "2014/04/06",
            "name": "dateJobSubmitted"
          },
          {
            "value": "205705",
            "name": "timeJobSubmitted"
          },
          {
            "value": "MSJENT91",
            "name": "originationHostName"
          },
          {
            "value": "296",
            "name": "serverProcessID"
          },
          {
            "value": "2014/04/06",
            "name": "dateLastActivity"
          },
          {
            "value": "205706",
            "name": "timeLastActivity"
          },
          {
            "value": "UBE",
            "name": "functionCodeOpenSystems"
          },
          {
            "value": "6016",
            "name": "printQueue"
          },
          {
            "value": "Printer in BLOB",
            "name": "foundationFutureUse1"
          },
          {
            "value": "R43500_XJDE0001_152_PDF",
            "name": "foundationFutureUse2"
          },
          {
            "value": "QBATCH",
            "name": "jobQueueDescription"
          },
          {
            "value": "Done",
            "name": "jobstatusDescription"
          }
        ]
      }
    ],
    "handle": "lrj5345288f0206-0000048c-00000009-00000000000000000000ffff0a230149",
    "size": 1,
    "startIndex": 1,
    "endIndex": 1,
    "xmlList": false
  },
  "Message": {
    "value": "Transaction Successful",
    "type": 0
  },
  "name": "GetJobInformation"
}

Example - GetUDCList

This example demonstrates how to us the startIndex and endIndex parameters in a query request.

Endpoint: http://hostname:port/cantara/serivce/version/query/E1DEV

Request-Method: POST

Content-Type: application/json

Request Payload

JS

{
   "CriteriaParameter": [
      {
         "CriteriaLiteral": [
            "98"
         ],
         "name": "productCode"
      },
      {
         "CriteriaLiteral": [
            "SY"
         ],
         "name": "recordType"
      }
   ],
   "name": "GetUDCList",
   "startIndex": 1,
   "recordCount": 5
}

Query Response

JS

{
   "name": "GetUDCList",
   "Message": "Transaction Successful",
   "Query": {
      "handle": "lrj53546572000c-0000048c-0000001e-00000000000000000000ffff0a230149",
      "size": "877",
      "startIndex": "1",
      "endIndex": "5",
      "xmlList": "false",
      "QueryRow": ...}
}

In the query response we can see that the size is 877 and endIndex is 5. This means there are more records to fetch. In our next request startIndex will be 6.

Request Payload

JS

{
   "CriteriaParameter": [
      {
         "CriteriaLiteral": [
            "98"
         ],
         "name": "productCode"
      },
      {
         "CriteriaLiteral": [
            "SY"
         ],
         "name": "recordType"
      }
   ],
   "name": "GetUDCList",
   "startIndex": 6,
   "recordCount": 5,
   "handle": "lrj53546572000c-0000048c-0000001e-00000000000000000000ffff0a230149"
}

Query Response

JS

{
   "name": "GetUDCList",
   "Message": "Transaction Successful",
   "Query": {
      "handle": "lrj535467fd0112-0000048c-0000001f-00000000000000000000ffff0a230149",
      "size": "877",
      "startIndex": "6",
      "endIndex": "10",
      "xmlList": "false",
      "QueryRow": ...}
}