Upsert: Insert or Update Record with Auditing

Insert record or update field values in table with record locks and audit record creation.

Warning: For experienced developers only. Some referential integrity enforced at application level not database. Updating arbitrary fields can have unforeseen consequences.

Note: Upsert should almost never be used to create new records. Use in this order:

1. Catch-e APP (safest)

2. Import Table Data (UI)

3. Dedicated Token Authenticated APIs

4. Change Request to Catch-e

5. Only then consider upsert

Can run for authorised tables. Run getTablesAuthorisedForImport to get list.

Audit records marked as external.

Multiple records can update in single JSON payload (same table). All individually locked.

Supports Dependent Records using UUID placeholder for child records with key field dependency.

Supplementary SWAGGER documentation: api.catch-e.com/docs/#/Import%20%2F%20Export/upsert

Requires Record:Upsert permission.

Go to Roles / APIs and check on Record:Upsert.

HTTP Method

POST

URL Examples

https://api.catch-e.com/gb/record/upsert/{table_name} 

Parameters

Headers

Body (JSON Payload)

API Examples

Update Multiple Records

https://api.catch-e.com/gb/record/upsert/fm_contracts 

[ { "contract_id": 100000, "colour": "Blue" }, { "contract_id": 100001, "colour": "White", "radio_pin": "777" } ] 

Insert New Record

https://api.catch-e.com/gb/record/upsert/fm_contracts 

[ { "contract_id": "", "colour": "Blue" } ] 

Creates new contract with colour set to blue. Other fields use defaults or blank.

Response - 204 No Content

Request successful. No response required.

Response - 400 Bad Request

"detail": "JSON decoding error: Underflow or the modes mismatch" "title": "Bad Request" 

JSON misconfigured. Example: missing right brace.

Response - 403 Forbidden

"detail": "Forbidden" 

You do not have permissions.

Response - 422 Unprocessable Entity

Missing Unique Key fields:

{ "detail": "Failed Validation", "records": { "missingMandatoryColumns": "Missing mandatory columns: supplier_id, card_number" } } 

Table uses Unique Key. Required fields not all passed. See Table Data for documented tables/required fields.

Table not authorised:

{ "detail": "Failed Validation", "table_name": { "noRecordFound": "Table is not authorised for import or is invalid" } } 

Table not authorised or invalid. Run getTablesAuthorisedForImport for available tables.

Response - 423 Locked

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Locked", "status": 423, "detail": "Locked", "Record already locked by user": "{user_login}" } 

Record being updated was locked. Entire batch fails if one record locked. Try later.

Web Service GB Queries exportQuery (Deprecated)

Execute query by query_id and return resultset as XML. Executes query stored in gb_queries, can include derived fields.

Note: If no derived fields needed, use Print Report Query API instead.

Input Fields

Warning: LIMIT works only with simple queries, not UNION queries.

URL Example

https://yourname.catch-e.net.au/services/gb/queries/exportQuery?query_id=100001 

Output Fields

Output Example

<root> <row> <contract_id>100000</contract_id> <make>Holden</make> ... </row> <row> <contract_id>100001</contract_id> <make>Ford</make> ... </row> </root> 

Error Codes

• CATCH_E_ERROR_BAD_QUERY

• CATCH_E_ERROR_BAD_QUERY_TYPE

• CATCH_E_ERROR_UNKNOWN_QUERY

• CATCH_E_ERROR_TOO_MANY_REQUESTS

• 403 Forbidden

Error Output

CATCH_E_ERROR_UNKNOWN_QUERY 

403 Error Troubleshooting

If using exportQuery on website, Catch-e must whitelist IP address.

GetCountries

Extract list of countries for use in APIs that use country_id in payload (CreateDrivers, UpdateDriversList, UpdateDriver). Use of country_id optional.

Requires Countries:Get permission.

Go to System Roles, select 'web_services' role, navigate to Roles / APIs and check Countries:Get.

HTTP Method

GET

URL Examples

https://api.demo.catch-e.com/gb/countries 

Input Fields

Wildcard % Operator Notes

In Postman, wildcard searches (%ab% or %ba%) may return "isEmpty" error.

Use %25ab%25 or %25ba%25 instead for proper encoding.

Input Fields (JSON)

https://api.catch-e.com/gb/countries [ { "name": "Australia", "iso_code_2": "", "iso_code_3": "", "page": "1", "page_size": "25" } ] 

Successful Response

{ "_links": { "self": { "href": "https://api.catch-e.com/gb/countries/?name=Australia&page=1&page_size=25" }, "first": { "href": "https://api.catch-e.com/gb/countries/?name=Australia&page_size=25" }, "last": { "href": "https://api.catch-e.com/gb/countries/?name=Australia&page=1&page_size=25" } }, "_embedded": { "gb_countries": [ { "country_id": "7015503456999836108", "name": "Australia", "iso_code_2": "AU", "iso_code_3": "AUS", "_links": { "self": { "href": "https://api.catch-e.com/gb/countries/7015503456999836108" } } } ] }, "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

No validations in place due to wildcard option.