Queries & Reports API: GetQueries

API endpoints for queries, reports, and record operations

GetQueries

Get a list of Queries stored in the table.

Permissions

To run this API, the nominated 'web-services' role needs to be given permission.

If you are not actively using the API, leave the permission off for better security.

Go to Roles / Apis and check on Queries:Get.

URL Examples

https://api.catch-e.com/gb/queries

HTTP Method

Use the HTTP Method 'GET' for consuming this web service.

Parameters

Name	Format	Notes	Mandatory
Query
name	string	Filter by partial or full name of the query.	No
description	string	Filter by partial or full description of the query.	No
query	string	Filter by partial query.	No
status_flag	string	Filter by status of the query. Available values: active, inactive.	No
lasteditby	string	Filter by last edit user_id.	No
page	number	Returns all pages by default. You can limit the results with this input value (default = 1).	no
page_size	number	25 results per page are returned by default. You can limit the results with this input value (default = 25).	no

Headers

Key	Format	Notes	Mandatory
Time-Zone	string	Contract events will be created in the specified timezone. The timezone value can be given in several formats, none of which are case sensitive: As a named time zone, such as 'Europe/Helsinki', 'US/Eastern', or 'MET'. As a string indicating an offset from UTC of the form :MM, prefixed with a + or -, such as '+10:00', '-6:00', or '+05:30' A leading zero can optionally be used for hours values less than 10; Default value : UTC	No

(Successful) Output Example

Example response.

{ "_links": { "self": { "href": "https://api.test.catch-e.com/gb/queries?status_flag=active&page=1&page_size=25" } }, "_embedded": { "queries": [ { "query_id": "123456", "name": "List of contacts", "description": "List of contacts", "query": "SELECT * FROM fm_contacts;", "super_flag": "yes", "status_flag": "active", "maximum_execution_time": 300, "_embedded": { "scheduler_jobs": [ { "scheduler_job_id": "123456", "method": "executeQuery", "status_flag": "active", "last_run": "2020-02-21 10:05:00", "average_run_time": 300 } ] }, "_link": { "self": { "href": "https://api.test.catch-e.com/gb/queries/100001" } } } ] }, "page_count": 1, "page_size": 25, "total_items": 1, "page": 1}

Response Details

Validation Messages	Comments
401 - Unauthorized

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unauthorized", "status": 401, "detail": "Unauthorized"}

| You have not authenticated before running this API or The token_timeout of the current session has passed. You need to authenticate again. |
| 403 - Forbidden | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Forbidden", "status": 403, "detail": "Forbidden"}

| You do not have permissions for this request. Go to System Roles and enter 'web_services' Navigate to the Roles / APIs tab to make sure the permission you need to run this API is checked. |
| 422 - Unprocessable Entity | | |

{ "warning_messages": { "fieldName": { "warningExceptionCode": "A textual description of the warning exception" } }, "validation_messages": { "fieldName": { "validationExceptionCode": "A textual description of the validation exception" } }, "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unprocessable Entity", "status": 422, "detail": "Failed Validation"}

| Correct your request content before submitting your request again. |
| 429 - Too Many Requests | | |

"title": "To Many Requests"

| The rate limit of your system has been exceeded and the response contains the number of seconds you should wait until trying again. Your rates limit includes all legacy web services and current token-based APIs. Whitelisted IP Addresses are excluded, the whitelist includes the localhost IP so that internal APIs are excluded from rate limiting counts. Catch-e's rate limiting uses the sliding window concept. See the following discussion piece about this: https://blog.cloudflare.com/counting-things-a-lot-of-different-things/ |
| 500 - Unexpected error | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "string", "status": 599, "detail": "string"}

| This response could be a temporary outage or potentially a bug. If you are consistently receiving this error, please raise a ticket with your Account Manager. Please supply the complete Request & Response payloads. [Warning:] Please remember to NEVER send user login credentials when sending a ticket request. Catch-e staff have our own credentials we can use to de-bug these tickets. |

GetQuery

Get a Queries stored in the table.

Permissions

To run this API, the nominated 'web-services' role needs to be given permission.

If you are not actively using the API, leave the permission off for better security.

Go to Roles / Apis and check on Queries:Get.

URL Examples

https://api.catch-e.com/gb/queries/{query_id}

HTTP Method

Use the HTTP Method 'GET' for consuming this web service.

(Successful) Output Example

Example response.

{ "_links": { "self": { "href": "https://api.test.catch-e.com/gb/queries?status_flag=active&page=1&page_size=25" } }, "_embedded": { "queries": [ { "query_id": "123456", "name": "List of contacts", "description": "List of contacts", "query": "SELECT * FROM fm_contacts;", "super_flag": "yes", "status_flag": "active", "maximum_execution_time": 300, "_embedded": { "scheduler_jobs": [ { "scheduler_job_id": "123456", "method": "executeQuery", "status_flag": "active", "last_run": "2020-02-21 10:05:00", "average_run_time": 300 } ] }, "_link": { "self": { "href": "https://api.test.catch-e.com/gb/queries/100001" } } } ] }, "page_count": 1, "page_size": 25, "total_items": 1, "page": 1}

Response Details

Validation Messages	Comments
401 - Unauthorized

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unauthorized", "status": 401, "detail": "Unauthorized"}

| You have not authenticated before running this API or The token_timeout of the current session has passed. You need to authenticate again. |
| 403 - Forbidden | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Forbidden", "status": 403, "detail": "Forbidden"}

{ "warning_messages": { "fieldName": { "warningExceptionCode": "A textual description of the warning exception" } }, "validation_messages": { "fieldName": { "validationExceptionCode": "A textual description of the validation exception" } }, "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unprocessable Entity", "status": 422, "detail": "Failed Validation"}

| Correct your request content before submitting your request again. |
| 429 - Too Many Requests | | |

"title": "To Many Requests"

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "string", "status": 599, "detail": "string"}

CreateQuery

Allows you to create a new query that is saved in the table.

Permissions

To run this API, the nominated 'web-services' role needs to be given permission.

If you are not actively using the API, leave the permission off for better security.

Go to Roles / Apis and check on Queries:Create.

URL Examples

https://api.catch-e.com/gb/queries

HTTP Method

Use the HTTP Method 'POST' for consuming this web service.

Headers

Key	Format	Notes	Mandatory
Audit-User-Id	string	Pass a user_id here to create audit records with this user's details. This can only be done if the authenticated API user has Roles / Apis checked for the permission Audit:UserIdOverride	No

Body (JSON)

Example Value.

{ "name": "COUNT of contracts with Rego", "description": "Count of Active Contracts with Rego", "query": "SELECT COUNT(*) FROM fm_contracts WHERE suspend_date = '0000-00-00' AND reg_no != 'ORDER';", "super_flag": "no", "status_flag": "active"}

Queries are validated prior to being saved in the table.

(Successful) Output Example

Successfully validated Queries are saved in the table. An HTTP 201 (Created) response will be returned from the API request.

Example response.

{ "query": "SELECT COUNT(*) FROM fm_contracts WHERE suspend_date = '0000-00-00' AND reg_no != 'ORDER';", "query_id": "100000", "name": "COUNT of contracts with Rego", "description": "Count of Active Contracts with Rego", "super_flag": "no", "max_execution_time": 300, "user_id_last_edit": "10000", "last_edit": "2023-12-05 08:49:08", "status_flag": "active", "_links": { "self": { "href": "https://api.test.catch-e.com/gb/queries/100000" } }, "_embedded": { "scheduler_jobs": [] }}

Response Details

Validation Messages	Comments
401 - Unauthorized

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unauthorized", "status": 401, "detail": "Unauthorized"}

| You have not authenticated before running this API or The token_timeout of the current session has passed. You need to authenticate again. |
| 403 - Forbidden | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Forbidden", "status": 403, "detail": "Forbidden"}

{ "validation_messages": { "query": { "invalidQuerySyntax": " 'You have an error in your SQL syntax'" } }, "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unprocessable Entity", "status": 422, "detail": "Failed Validation"}

| An invalid Query has been detected. Correct your Query syntax before submitting your request again. | |

{ "validation_messages": { "query": { "invalidQuery": "Query validation failure 'An expression was expected.'" }, "status_flag": { "notInArray": "Value must be either 'active' or 'inactive'" } }, "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unprocessable Entity", "status": 422, "detail": "Failed Validation"}

| In this example, an invalid status_flag value has been detected. Correct your Query syntax before submitting your request again. |
| 429 - Too Many Requests | | |

"title": "To Many Requests"

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "string", "status": 599, "detail": "string"}

DeleteQuery

Update the status of a Query stored in the table to 'deleted'.

Permissions

To run this API, the nominated 'web-services' role needs to be given permission.

If you are not actively using the API, leave the permission off for better security.

Go to Roles / Apis and check on Queries:Delete.

URL Examples

https://api.catch-e.com/gb/queries/{query_id}

HTTP Method

Use the HTTP Method 'DELETE' for consuming this web service.

(Successful) Output Example

Example response.

204 No Content

Response Details

Validation Messages	Comments
401 - Unauthorized

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unauthorized", "status": 401, "detail": "Unauthorized"}

| You have not authenticated before running this API or The token_timeout of the current session has passed. You need to authenticate again. |
| 403 - Forbidden | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Forbidden", "status": 403, "detail": "Forbidden"}

{ "validation_messages": { "query_id": { "noRecordFound": "No record matching the input was found" } }, "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unprocessable Entity", "status": 422, "detail": "Failed Validation"}

| Correct your request content before submitting your request again. |
| 429 - Too Many Requests | | |

"title": "To Many Requests"

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "string", "status": 599, "detail": "string"}