Gb Queries executeScript

Gb Queries executeScript

Scripts are groups of queries that modify data but return nothing. Can include unlimited SQL statements: UPDATE, INSERT, SET, LOCK TABLES, UNLOCK TABLES. Examples: set status to 'expired' for quotes older than 30 days.

Refer to User Defined Queries for script writing instructions.

Save script in gb_queries table before setup.

Important: Use transaction safe queries. Transaction safe queries rollback changes if any updates fail.

If script affects billing records, use stored procedures to lock/unlock billing tables.

Warning: Raise request via Lily to setup executeScript job.

Parameters

Parameter

Notes

Query ID

Query_id for script in gb_queries. Search by Name/Description.

Run Now

Job failed! Error: CATCH_E_API_EXECUTE_SCRIPT_ERROR

Message shows script number. Script started but couldn't complete.

Billing Lock Error

Failure executing query #. Message: Statement could not be executed (45000 - 1644 - Billing already locked!)

Billing lock couldn't be secured. Try later.

Validation Errors

Error

Notes

START TRANSACTION / COMMIT block required

Scripts must contain appropriate block for transaction safety

No COMMIT statement

No closing COMMIT detected

LOCK TABLES, UNLOCK TABLES, SET autocommit not allowed with START TRANSACTION / COMMIT

Automatically commit before locking tables

CREATE TEMPORARY TABLE...LIKE not supported

MySQL 5.7 syntax no longer supported. Use: CREATE TEMPORARY TABLE SELECT * FROM tablename LIMIT 0; Add keys/indexes with ALTER TABLE.

Multiple START TRANSACTIONs not allowed

Only one block allowed

COMMIT, INSERT, UPDATE, REPLACE, DELETE, MERGE not allowed before START TRANSACTION

Ensure inside START TRANSACTION / COMMIT block for clean ROLLBACK. Exceptions: INSERT INTO sch... or tmp..., DELETE FROM sch... or tmp...

Multiple COMMITs not allowed

Only one START TRANSACTION / COMMIT block

CREATE TABLE, DROP TABLE not allowed within START TRANSACTION / COMMIT without TEMPORARY

Cause implicit commit. Place outside block or use TEMPORARY keyword. Example: CREATE TEMPORARY TABLE...

ALTER, RENAME, TRUNCATE, INSTALL, GRANT, REVOKE, SET PASSWORD, BEGIN, LOAD, ANALYZE, CACHE, CHECK, FLUSH, OPTIMIZE, REPAIR, RESET, START, STOP, CHANGE not allowed

CALL not transaction safe, cannot use in START TRANSACTION / COMMIT block

Must exist in gb_transaction_safe_calls table

INSERT, UPDATE, REPLACE, DELETE, MERGE not allowed after COMMIT

Ensure inside START TRANSACTION / COMMIT block. Exceptions: INSERT INTO sch... or tmp..., DELETE FROM sch... or tmp...

Must use SELECT...FOR UPDATE when UPDATE/REPLACE INTO gb_unique_ids / gl_controls

SELECT FOR UPDATE locks rows until transaction committed/rolled back. Ensures other transactions cannot modify, maintaining consistency.

Gb Record deleteLock

Delete single record from gb_locks table with auditing.

Input Fields

Query String

Key

Format Notes

Mandatory

lock_key

string

Lock Key value to delete. Example: 04354cb861aade428f723857ce2fb6fa

Yes

URL Example

https://yourname.catch-e.net.au/services/gb/record/deleteLock?_lock_key_=04354cb861aade428f723857ce2fb6fa

Output Fields

XML

Element

Format Notes

Mandatory

return_status

bool

TRUE on success

Yes

Output Example

true

Error Codes

Code

Notes

CATCH_E_ERROR_AUTHENTICATION_FAILED

Login credentials don't belong to web_services role

CATCH_E_ERROR_MISSING_LOCK_KEY

Mandatory parameter lock_key missing

CATCH_E_ERROR_DELETE_FAILED

Delete and/or audit process unsuccessful. Consult Catch-e.

CATCH_E_ERROR_CANNOT_UNLOCK

Some tables may not unlock: gb_unique_ids, gl_controls. Consult Catch-e.

CATCH_E_ERROR_LOCK_NOT_FOUND

No Lock Key exists in gb_locks for provided value

CATCH_E_ERROR_TOO_MANY_REQUESTS

REST request limit exceeded

Error Output

CATCH_E_ERROR_CANNOT_UNLOCK

Gb Record lockRecord

Lock record in specified table. Useful when upsert performed on record that application locks at higher level (e.g. fm_contract_budgets). When system updates fm_contract_budgets, it locks parent fm_contracts record.

Warning: Always use same locking strategy as underlying application. If uncertain, raise request via Lily.

Input Fields

Query String

Key

Format Notes

Mandatory

table_name

string

Table name

Yes

unique_id

string

Record ID to lock

Yes

URL Example

https://yourname.catch-e.net.au/services/gb/record/lockRecord?_table_name_=fm_clients&_unique_id_=102130

Output Fields

XML

Element

Format Notes

Mandatory

return_status

bool

TRUE on success

Yes

Output Example

true

Error Codes

Code

Notes

CATCH_E_ERROR_AUTHENTICATION_FAILED

Login credentials don't belong to web_services role

CATCH_E_ERROR_MISSING_TABLE_NAME

Mandatory parameter table_name missing

CATCH_E_ERROR_MISSING_UNIQUE_ID

Mandatory parameter unique_id missing

CATCH_E_ERROR_RECORD_LOCKED

Lock couldn't be obtained; existing lock exists

CATCH_E_ERROR_BAD_RECORD_LOCK

Lock couldn't be obtained. Consult Catch-e.

CATCH_E_ERROR_TOO_MANY_REQUESTS

REST request limit exceeded

Error Output

CATCH_E_ERROR_BAD_RECORD_LOCK

Gb Record unlockRecord

Unlock record in specified table.

Input Fields

Query String

Key

Format Notes

Mandatory

table_name

string

Table name

Yes

unique_id

string

Record ID to unlock

Yes

URL Example

https://yourname.catch-e.net.au/services/gb/record/unlockRecord?_table_name_=fm_clients&_unique_id_=102134

Output Fields

XML

Element

Format Notes

Mandatory

return_status

bool

TRUE on success

Yes

Output Example

true

Error Codes

Code

Notes

CATCH_E_ERROR_AUTHENTICATION_FAILED

Login credentials don't belong to web_services role

CATCH_E_ERROR_MISSING_TABLE_NAME

Mandatory parameter table_name missing

CATCH_E_ERROR_MISSING_UNIQUE_ID

Mandatory parameter unique_id missing

CATCH_E_ERROR_BAD_RECORD_UNLOCK

Unlock couldn't be performed. Consult Catch-e.

Error Output

CATCH_E_ERROR_BAD_RECORD_UNLOCK

Gb Record update

Write values to single record with locking and auditing.

Warning: For experienced developers only with good understanding of data schema and business rules. If uncertain, raise request via Lily.

Input Fields

Query String

Key

Format Notes

Mandatory

table_name

string

Target table name. Example: fm_contracts

Yes

unique_id

string

Unique ID (record_id) of target record

Yes

*[field names]

string

Field name/value pairs for update

No

Field names not matching target table fields ignored. unique_id field cannot be updated.

URL Example

https://yourname.catch-e.net.au/services/gb/record/update?_table_name_=fm_contracts&_unique_id_=101939&colour=blue&state=NSW

Output Fields

XML

Element

Format Notes

Mandatory

return_status

bool

TRUE on success

Yes

Output Example

true

Error Codes

Code

Notes

CATCH_E_ERROR_PERMISSION_DENIED

Login credentials don't belong to web_services role

CATCH_E_ERROR_MISSING_TABLE_NAME

Mandatory parameter table_name missing

CATCH_E_ERROR_MISSING_UNIQUE_ID

Parameter unique_id value missing

CATCH_E_ERROR_BAD_TABLE_NAME

Table name not in gb_unique_ids table

CATCH_E_ERROR_RECORD_DOES_NOT_EXIST

Record doesn't exist

CATCH_E_ERROR_RECORD_LOCKED

Record already locked. User_login locking record shown.

CATCH_E_ERROR_BAD_RECORD_LOCK

Record couldn't be locked. Catch-e support will be informed.

CATCH_E_ERROR_MISSING_FIELD_NAMES

None of Input Fields contained field names from target table

Error Output

CATCH_E_ERROR_BAD_QUERY_3

Gb SystemMaintenance checkSPFRecordForGbMailSenderDomains

Check and verify SPF record status of domains in gb_mail_sender_domains table. Checks status_flag 'enrolled' or 'failed'; all other statuses ignored.

If verification fails: status_flag changed to 'failed', failure info added to error_data field.

If verification succeeds for 'failed' record: status_flag updated to 'enrolled', error_data cleared.

If require_spf_check_flag set to 'yes' and domain not 'enrolled': emails won't send or use fall_back email. Refer Domain Setup page for full email impact details.

Job runs frequently with child job executeQueries immediately after, notifying Catch-e Support of failed verification domains.

Parameters

No run time parameters required.

Gb SystemMaintenance rebuildCostCentreAllocations

Rebuild fm_contract_cost_centre_allocations table. Run after importing 'CC' type contract events if don't want to wait for scheduled daily rebuild.

Parameters

No run time parameters required.

Gb SystemMaintenance rebuildDivisionAllocations

Rebuild fm_contract_division_allocations table. Run after importing 'DIV' type contract events if don't want to wait for scheduled daily rebuild.

Parameters

No run time parameters required.

Gb SystemMaintenance rebuildDriverAllocations

Rebuild fm_contract_driver_allocations table. Run after importing Driver Change contract events if don't want to wait for scheduled daily rebuild.

Parameters

No run time parameters required. Typically setup to run at 10:00 PM.

System Controls

quotes_search_results_limit

Quotes shown on Quotes / Search screen limited to 1,000 records by default to optimise page loading.

Setting

Value

Module

qt

Control

quotes_search_results_limit

Default Value

1000

Description

Quote search results limit. Set blank or '0' to remove limit entirely.

Users can update limit in Pagebar. Default limit reapplied when next navigating into Quotes module.