Execute Query
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/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/query/E1DEV
Request-Method: POST
Content-Type: application/json
Request Payload
{
"CriteriaParameter": [
{
"CriteriaLiteral": [
"152"
],
"name": "jobNo"
}
],
"name": "GetJobInformation",
"startIndex": 1,
"recordCount": 10
}
Query Response
{
"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/query/E1DEV
Request-Method: POST
Content-Type: application/json
Request Payload
{
"CriteriaParameter": [
{
"CriteriaLiteral": [
"98"
],
"name": "productCode"
},
{
"CriteriaLiteral": [
"SY"
],
"name": "recordType"
}
],
"name": "GetUDCList",
"startIndex": 1,
"recordCount": 5
}
Query Response
{
"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
{
"CriteriaParameter": [
{
"CriteriaLiteral": [
"98"
],
"name": "productCode"
},
{
"CriteriaLiteral": [
"SY"
],
"name": "recordType"
}
],
"name": "GetUDCList",
"startIndex": 6,
"recordCount": 5,
"handle": "lrj53546572000c-0000048c-0000001e-00000000000000000000ffff0a230149"
}
Query Response
{
"name": "GetUDCList",
"Message": "Transaction Successful",
"Query": {
"handle": "lrj535467fd0112-0000048c-0000001f-00000000000000000000ffff0a230149",
"size": "877",
"startIndex": "6",
"endIndex": "10",
"xmlList": "false",
"QueryRow": ...}
}