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.

Was this helpful?

Still need help? Ask the team