Query Services

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 Login Services 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.

Query

Endpoint: http(s)://[hostname]:[port]/[context]/service/[version]/query/[environment]

Method: POST

Content-Type: application/json or application/xml

Accept: application/json or application/xml

Path Parameters

Parameter	Description
hostname	Cantara worker hostname
port	Cantara worker listening port (only required if non-standard HTTP/s port
context	Cantara worker web application context
version	Cantara worker API version
environment	Cantara access environment. Refer to the Managing Environments topic for more information.(Example - E1DEV)

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 Message topic for more information about errors.

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.

Note:

For improved performance, the Cantara Integration Platform supports simple query results paging. In the Query Request you can specify the start index (indexed from 0) and the number of query results to return. If you return the last result from the query the result set is automatically deleted by Cantara. However, if you do not retrieve the last row, you must ensure that you run the Delete Query operation to remove the results from persistent storage on the JD Edwards Enterprise Server.

On This Page

Request Body

JS

{
	"name": "GetUDCList",
	"cantaraVersion": "5.1",
	"tenantId": "0",
	"apiKey": "",
	"criteriaParameter": [
		{
			"criteriaLiteral": [
				"00"
			],
			"name": "productCode"
		},
		{
			"criteriaLiteral": [
				"ST"
			],
			"name": "recordType"
		}
	],
	"handle": "",
	"startIndex": 0,
	"recordCount": 100
}

Request Body Parameters

Path	Description
name	Query service name
cantaraVersion	Cantara platform version
tenantId	Cantara Tenant ID (The Tenant ID and API Key can be retrieved from the customer portal.)
apiKey	Cantara Tenant API Key (The Tenant ID and API Key can be retrieved from the customer portal.)
criteriaParameter	Query request parameters
handle	Reference for query
startIndex	which record to start from
recordCount	paging size

Response Body

JS

{
	"Message": {
		"value": "Transaction Successful",
		"origin": "Worker7@5bbdad89215b",
		"type": 0,
		"cantaraSid": "1b2732bd-1afb-45b4-9385-729c5f532cf9"
	},
	"name": "GetUDCList",
	"Query": {
		"QueryRow": [
			{
				"Parameter": [
					{
						"value": "00",
						"name": "productCode"
					},
					{
						"value": "ST",
						"name": "recordType"
					},
					{
						"name": "key"
					},
					{
						"value": "Blank",
						"name": "description1"
					},
					{
						"name": "description2"
					},
					{
						"name": "specialHandling"
					},
					{
						"name": "udcOwnership"
					},
					{
						"value": "N",
						"name": "hardCoded"
					}
				]
			}
		],
		"handle": "lrj5dbf9d08029f-000017c4-00000005-00000000000000000000ffff0a230134",
		"size": 18,
		"startIndex": 1,
		"endIndex": 18,
		"xmlList": true
	}
}

Response Body Parameters

Path	Description
Message	Response message information
Message.value	Message description
Message.origin	origin host
Message.type	Message type identifier
Message.cantaraSid	Web application server session ID
name	Function service name
Parameter	Query response parameters
handle	Reference detail for query
size	Total paging size received
startIndex	which record to start from
endIndex	gives the total number of results
xmlList	Query Response list as XML

Execute Query

Example for executing Query services

Example Endpoint: https://us.cantara.cloud/cantara/service/50/query/E1DEV

Example - GetUDCList

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

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

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 18 lines. In this scenario Cantara will return the first 10 records along with the size (18), startIndex (1), and EndIndex (10) of the result set in the payload. Once the resulting 10 rows are processed, you need to fetch the 8 that were not received from the previous request. You do this by setting startIndex to 11 on the next request. (See Get UDC List for an example).

Query Request

JS

{
	"name": "GetUDCList",
	"cantaraVersion": "5.1",
	"tenantId": "0",
	"apiKey": "",
	"criteriaParameter": [
		{
			"criteriaLiteral": [
				"00"
			],
			"name": "productCode"
		},
		{
			"criteriaLiteral": [
				"ST"
			],
			"name": "recordType"
		}
	],
	"handle": "",
	"startIndex": 0,
	"recordCount": 10
}

Query Response

JS

{
	"Message": {
		"value": "Transaction Successful",
		"origin": "Worker7@5bbdad89215b",
		"type": 0,
		"cantaraSid": "1b2732bd-1afb-45b4-9385-729c5f532cf9"
	},
	"name": "GetUDCList",
	"Query": {
		"QueryRow": [
			{
				"Parameter": [
					{
						"value": "00",
						"name": "productCode"
					},
					{
						"value": "ST",
						"name": "recordType"
					},
					{
						"name": "key"
					},
					{
						"value": "Blank",
						"name": "description1"
					},
					{
						"name": "description2"
					},
					{
						"name": "specialHandling"
					},
					{
						"name": "udcOwnership"
					},
					{
						"value": "N",
						"name": "hardCoded"
					}
				]
			}
		],
		"handle": "lrj5dbf9d08029f-000017c4-00000005-00000000000000000000ffff0a230134",
		"size": 18,
		"startIndex": 1,
		"endIndex": 10,
		"xmlList": true
	}
}

Delete Query

Endpoint: http(s)://[hostname]:[port]/[context]/service/[version]/query/[environment]/[handle]

Method: DELETE

Content-Type: application/json or application/xml

Accept: application/json or application/xml

Path Parameters

Parameter	Description
hostname	Cantara worker hostname
port	Cantara worker listening port (only required if non-standard HTTP/s port
context	Cantara worker web application context
version	Cantara worker API version
environment	Cantara access environment. Refer to the Managing Environments topic for more information.(Example - E1DEV)
handle	Cantara reference for query

The delete query operation allows you to remove an existing query data set from the JD Edwards Enterprise Server. The input payload should be the handle of the query result set which is provided as part of the Query Response that is returned when a query is executed. The operation will return a Message JSON string if the service executes correctly, otherwise it will throw TransactionException.

Execute Delete Query

Example for executing Delete Query service

Example Endpoint: https://us.cantara.cloud/cantara/service/50/query/E1DEV/lrj5dbf9d08029f-000017c4-00000005-00000000000000000000ffff0a230134

Example - GetUDCList