GetQueries

GetQueries

Get list of queries stored in gb_queries table.

Requires Queries:Get permission.

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

HTTP Method

GET

URL Examples

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

Parameters

Name

Format

Notes

Mandatory

name

string

Filter by partial or full name

No

description

string

Filter by partial or full description

No

query

string

Filter by partial query

No

status_flag

string

Filter by status. Values: active, inactive

No

last_edit_by

string

Filter by last edit user_id

No

page

number

Page number (default: 1). Returns all pages by default.

No

page_size

number

Results per page (default: 25)

No

Headers

Key

Format

Notes

Mandatory

Time-Zone

string

Timezone for contract events. Named zones (Europe/Helsinki, US/Eastern) or offset (+10:00, -6:00, +05:30). Default: UTC

No

Successful Output

{ "_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 - 401 Unauthorized

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

You have not authenticated or token_timeout has passed. Authenticate again.

Response - 403 Forbidden

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

You do not have permissions. Go to System Roles, enter 'web_services', navigate to Roles / APIs and check permissions.

Response - 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 request content before resubmitting.

Response - 429 Too Many Requests

Rate limit exceeded. Response contains seconds to wait before retry. Rate limit includes all legacy web services and APIs. Whitelisted IP addresses excluded (includes localhost). Catch-e uses sliding window rate limiting.

Reference: https://blog.cloudflare.com/counting-things-a-lot-of-different-things/

Response - 500 Unexpected Error

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

Temporary outage or bug. If consistent, raise ticket with Account Manager. Supply complete Request & Response payloads.

Warning: Never send user login credentials in tickets. Catch-e staff have own credentials for debugging.

GetQuery

Get single query stored in gb_queries table by query_id.

Requires Queries:Get permission.

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

HTTP Method

GET

URL Examples

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

Successful Output

{ "_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 - 401 Unauthorized

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

You have not authenticated or token_timeout has passed. Authenticate again.

Response - 403 Forbidden

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

You do not have permissions. Go to System Roles, enter 'web_services', navigate to Roles / APIs and check permissions.

Response - 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 request content before resubmitting.

Response - 429 Too Many Requests

Rate limit exceeded. Response contains seconds to wait before retry. Rate limit includes all legacy web services and APIs. Whitelisted IP addresses excluded (includes localhost). Catch-e uses sliding window rate limiting.

Reference: https://blog.cloudflare.com/counting-things-a-lot-of-different-things/

Response - 500 Unexpected Error

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

Temporary outage or bug. If consistent, raise ticket with Account Manager. Supply complete Request & Response payloads.

Warning: Never send user login credentials in tickets. Catch-e staff have own credentials for debugging.

CreateQuery

Create new query saved in gb_queries table.

Requires Queries:Create permission.

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

Queries validated prior to being saved.

HTTP Method

POST

URL Examples

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

Headers

Key

Format

Notes

Mandatory

Audit-User-Id

string

Pass user_id to create audit records. Only if authenticated user has Audit:UserIdOverride permission.

No

Body (JSON)

{ "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" }

Successful Output

HTTP 201 (Created) 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 - 401 Unauthorized

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

You have not authenticated or token_timeout has passed. Authenticate again.

Response - 403 Forbidden

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

You do not have permissions. Go to System Roles, enter 'web_services', navigate to Roles / APIs and check permissions.

Response - 422 Unprocessable Entity

Invalid SQL syntax:

{ "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" }

Invalid query detected. Correct SQL syntax before resubmitting.

Invalid status_flag:

{ "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" }

Invalid status_flag value detected. Correct before resubmitting.

Response - 429 Too Many Requests

Rate limit exceeded. Response contains seconds to wait before retry. Rate limit includes all legacy web services and APIs. Whitelisted IP addresses excluded (includes localhost). Catch-e uses sliding window rate limiting.

Reference: https://blog.cloudflare.com/counting-things-a-lot-of-different-things/

Response - 500 Unexpected Error

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

Temporary outage or bug. If consistent, raise ticket with Account Manager. Supply complete Request & Response payloads.

Warning: Never send user login credentials in tickets. Catch-e staff have own credentials for debugging.

DeleteQuery

Update status of query stored in gb_queries table to 'deleted'.

Requires Queries:Delete permission.

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

HTTP Method

DELETE

URL Examples

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

Successful Output

204 No Content

Response - 401 Unauthorized

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

You have not authenticated or token_timeout has passed. Authenticate again.

Response - 403 Forbidden

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

You do not have permissions. Go to System Roles, enter 'web_services', navigate to Roles / APIs and check permissions.

Response - 422 Unprocessable Entity

{ "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 request content before resubmitting.

Response - 429 Too Many Requests

Rate limit exceeded. Response contains seconds to wait before retry. Rate limit includes all legacy web services and APIs. Whitelisted IP addresses excluded (includes localhost). Catch-e uses sliding window rate limiting.

Reference: https://blog.cloudflare.com/counting-things-a-lot-of-different-things/

Response - 500 Unexpected Error

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

Temporary outage or bug. If consistent, raise ticket with Account Manager. Supply complete Request & Response payloads.

Warning: Never send user login credentials in tickets. Catch-e staff have own credentials for debugging.