System Records API: Gb Queries executeScript

Gb_* API endpoints for system record operations

Gb Queries executeScript

Scripts are groups of queries that modify data but don't return anything. You can include as many SQL statements you like within a script. A script can include any number of UPDATE / INSERT / SET / LOCK TABLES / UNLOCK TABLES statements. There are many possible uses for these e.g. set the status to 'expired' for all quotes older than 30 days.

Visit UserDefinedQueries for instructions on writing scripts for scheduler jobs.

Before this job can be set up, you need to save your script in the table.

To protect your data, make sure you use transaction safe queries in these jobs. Transaction safe queries will roll back changes if if any of the query updates fail.

If the script affects records used in the billing process, use stored procedures to lock and unlock the billing tables.

Warning: Contact Catch-e Support if you want an executeScript job set-up for you.

Parameters

These are the different parameter settings that you can set up on the Job Screen screen:

Parameter	Notes
Query ID	queryid for script in gbqueries to run. Includes a search icon to search queries by Name or Description.

Run Now

**Job failed! *Error:* CATCHEAPIEXECUTESCRIPT_ERROR Error message: (see below list)** — The executeScript job started, but could not be completed. The script number is shown in the message.

BILLING LOCK

Failure executing query #. Message: Statement could not be executed (45000 - 1644 - Billing already locked!) — A billing lock could not be secured for one of the scripts in the query. Try again later.

START TRANSACTION / COMMIT

A START TRANSACTION / COMMIT or SET autocommit=0 / COMMIT block is required. — Scripts must contain an appropriate START TRANSACTION / COMMIT block to be transaction safe.
No COMMIT statement — No closing COMMIT command detected.
not allowed with START TRANSACTION / COMMIT block — LOCK TABLES, UNLOCK TABLES, SET autocommit = 0 These commands automatically commit any active transactions before attempting to lock the tables. This means that any changes made within a transaction will be committed, and the transaction will end before the tables are locked.
CREATE TEMPORARY TABLE … LIKE not supported — This syntax was allowed in MySQL 5.7 but is no longer supported. Use this alternative syntax, CREATE TEMPORARY TABLE SELECT * FROM yourtablename LIMIT 0; Note: No keys or indexes will be created so add those with ALTER TABLE if required.
Multiple START TRANSACTIONs not allowed — Only one START TRANSACTION / COMMIT block is allowed.
not allowed before START TRANSACTION — COMMIT, INSERT, UPDATE, REPLACE, DELETE, MERGE This is to ensure all data manipulation commands are safely inside the START TRANSACTION / COMMIT block ensuring a clean ROLLBACK if the transaction cannot complete for any reason. Exceptions are INSERT INTO sch… or tmp…, and DELETE FROM sch… or tmp…
Multiple COMMITs not allowed — Only one START TRANSACTION / COMMIT block is allowed.
not allowed within START TRANSACTION / COMMIT block without the TEMPORARY keyword — CREATE TABLE, DROP TABLE These commands cause an implicit commit and are therefore not transaction-safe. If you want to create a permanent table, place the command outside of the START TRANSACTION / COMMIT block. To create a table within the START TRANSACTION / COMMIT block, use the transaction-safe TEMPORARY keyword. E.g. CREATE TEMPORARY TABLE…
not allowed within START TRANSACTION / COMMIT block — ALTER, RENAME, TRUNCATE, INSTALL, GRANT, REVOKE, SET PASSWORD, BEGIN, LOAD, ANALYZE, CACHE, CHECK, FLUSH, OPTIMIZE, REPAIR, RESET, START, STOP, CHANGE
CALL is not transaction safe so cannot be used in a START TRANSACTION / COMMIT block — must exist in the gbtransactionsafe_calls table.
not allowed after COMMIT — INSERT, UPDATE, REPLACE, DELETE, MERGE This is to ensure all data manipulation commands are safely inside the START TRANSACTION / COMMIT block ensuring a clean ROLLBACK if the transaction cannot complete for any reason. Exceptions are INSERT INTO sch… or tmp…, and DELETE FROM sch… or tmp…
**Must use SELECT … *FOR UPDATE* when doing UPDATE or REPLACE INTO gbuniqueids / gl_controls.** — The SELECT FOR UPDATE statement in MySQL is used to lock the rows returned by a SELECT query until the transaction is committed or rolled back. This ensures that other transactions cannot modify or lock these rows, maintaining data consistency during the transaction.
**Must use SELECT … *FOR UPDATE* when doing UPDATE or REPLACE INTO gbuniqueids / gl_controls.** — The SELECT FOR UPDATE statement in MySQL is used to lock the rows returned by a SELECT query until the transaction is committed or rolled back. This ensures that other transactions cannot modify or lock these rows, maintaining data consistency during the transaction.
Job failed… Error: CATCHEAPIEXECUTESCRIPT_ERROR Error Message: Unable to perform theis action! Please try again. If problem persists please contact your adminstrator. — The executeScript job started, but could not be completed. The error is generated if the related query is incorrectly syntax like with ';'..

Gb Record deleteLock

Synopsis

Allows you to delete to a single record from table gb_locks, includes auditing.

Input Fields

	Query string key	Format	Notes	Mandatory
1	lockkey_	string	Value of Lock Key to be deleted e.g. '04354cb861aade428f723857ce2fb6fa'	yes

Input URL Example

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

Output Fields

	XML element	Format	Notes	Mandatory
1	return_status	bool	TRUE on success	yes

Output Example

true

Error Codes

	Error Codes	Notes
1	CATCHEERRORAUTHENTICATIONFAILED	Your login credentials don't belong to the 'web_services' role.
2	CATCHEERRORMISSINGLOCK_KEY	The mandatory parameter lockkey_ is missing.
3	CATCHEERRORDELETEFAILED	The delete and/or audit process was unsuccessful. Consult Catch-e.
4	CATCHEERRORCANNOTUNLOCK	Some tables may not be unlocked : gbuniqueids, gl_controls. Consult Catch-e if you think you really need to.
5	CATCHEERRORLOCKNOT_FOUND	No Lock Key exists in table gblocks for the value provided in parameter lockkey .
6	CATCHEERRORTOOMANY_REQUESTS	Rest call requests exceeded the limit set in .

Error Output

CATCHEERRORCANNOTUNLOCK

Gb Record lockRecord

Synopsis

Allows you to lock a record in a specified table.

This is useful in cases where an upsert is being performed on a record that the application locks a higher level e.g. fmcontractbudgets. When the system updates entries in fmcontractsbudgets, it locks the parent fm_contracts record.

Warning: You should always use the same locking strategy as the underlying application. If in any doubt, please contact Catch-e Support.

Input Fields

	Query string key	Format	Notes	Mandatory
1	tablename_	string	Table name	yes
1	uniqueid_	string	Record id to lock	yes

Input 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
1	return_status	bool	TRUE on success	yes

Output Example

true

Error Codes

	Error Codes	Notes
1	CATCHEERRORAUTHENTICATIONFAILED	Your login credentials don't belong to the 'web_services' role.
2	CATCHEERRORMISSINGTABLE_NAME	The mandatory parameter tablename_ is missing.
3	CATCHEERRORMISSINGUNIQUE_ID	The mandatory parameter uniqueid_ is missing.
4	CATCHEERRORRECORDLOCKED	A lock could not be obtained because an existing lock already exists
5	CATCHEERRORBADRECORD_LOCK	A lock could not be obtained. Consult Catch-e if you think you really need to.
6	CATCHEERRORTOOMANY_REQUESTS	Rest call requests exceeded the limit set in .

Error Output

CATCHEERRORBADRECORD_LOCK

Gb Record readRecord

Synopsis

Warning: This web service is in the process of being deprecated and is no longer supported by Catch-e.
You must use readRecord instead and contact Catch-e Support if you need assistance with the set-up.

Allows you to read values for a single record.

Input Fields

	Query string key	Format	Notes	Mandatory
1	tablename_	string	Name of table to read e.g. fm_contracts	yes
2	tablekeyfield	string	Name of table key field(s) to use in read query. Multiple key names are seperated by a comma delimeter, e.g. &tablekeyfield=receiptid,receiptitemno,billingid	yes
3	tablekeyvalue	string	Value of table key field(s) to use in read query. Multiple key values are seperated by a comma delimeter, e.g. tablekeyvalue=100002,1,102295	yes
4	[field names]	string	Specific field(s) you want returned. If you request no fields, then all fields are returned. Any field(s) that don't exist will cause an error to be returned.	no

Input URL Example

https://yourname.catch-e.net.au/services/gb/record/readRecord?_table_name_=fm_contracts&_table_key_field_=contract_id&_table_key_value_=101939&contract_id=

Output Fields

	XML element	Format	Notes	Mandatory
1	tablename_	array	Result data contained in a response element whose name matches the tablename_ request parameter	yes

Output Example

101939

Error Codes

1 — Your login credentials don't belong to the 'webservices' role. CATCHEERRORAUTHENTICATION_FAILED
2 — The mandatory Input Field tablename is missing. CATCHEERRORMISSINGTABLENAME
3 — The mandatory Input Field tablekeyfield is missing, e.g. contractid. CATCHEERRORMISSINGTABLEKEY_FIELD
4 — The mandatory Input Field tablekeyvalue is missing, e.g. 101939. CATCHEERRORMISSINGTABLEKEYVALUE
5 — Unknown filter fields specified in request. Unknown fields are included with the error message. CATCHEERRORUNKNOWNOPTIONAL_FIELDS
6 — No records found for specified tablekeyfield and tablekeyvalue combination. CATCHEERRORRECORDNOT_FOUND
7 — Invalid tablekeyfield data specified. Any unknown key fields are listed, or if there is a discrepancy between the number of tablekeyfield keys and tablekeyvalue values, then you get the following error message 'key / value mismatch!'. CATCHEERRORBADTABLE_KEY
8 — Rest call requests exceeded the limit set in . CATCHEERRORTOOMANY_REQUESTS

Error Output

CATCHEERRORMISSINGTABLE_NAME 2

Gb Record unlockRecord

Synopsis

Allows you to unlock a record in a specified table.

Input Fields

	Query string key	Format	Notes	Mandatory
1	tablename_	string	Table name	yes
1	uniqueid_	string	Record id to unlock	yes

Input 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
1	return_status	bool	TRUE on success	yes

Output Example

true

Error Codes

	Error Codes	Notes
1	CATCHEERRORAUTHENTICATIONFAILED	Your login credentials don't belong to the 'web_services' role.
2	CATCHEERRORMISSINGTABLE_NAME	The mandatory parameter tablename_ is missing.
3	CATCHEERRORMISSINGUNIQUE_ID	The mandatory parameter uniqueid_ is missing.
4	CATCHEERRORBADRECORD_UNLOCK	Unlock could not be performed. Consult Catch-e if you think you really need to.

Error Output

CATCHEERRORBADRECORD_UNLOCK

Gb Record update

Synopsis

Allows you to write values to a single record, includes locking and auditing.

Warning: This service is to be used by experienced developers with a very good understanding of the data schema and business rules. If in any doubt, please contact Catch-e Support.

Input Fields

	Query string key	Format	Notes	Mandatory
1	tablename_	string	Name of target table e.g. fm_contracts	yes
2	uniqueid_	string	Unique ID (otherwise known as record_id) of the target record	yes
3	*[field names]	string	Field name and value pairs for fields to be updated	no

Any field names that do not match field names in the target table will be ignored.
The unique_id field itself cannot be updated.

Input 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
1	return_status	bool	TRUE on success	yes

Output Example

true

Error Codes

	Error Codes	Notes
1	CATCHEERRORPERMISSIONDENIED	Your login credentials don't belong to the 'web_services' role.
2	CATCHEERRORMISSINGTABLE_NAME	The mandatory Input Field tablename_ is missing.
3	CATCHEERRORMISSINGUNIQUE_ID	The value of the Input Field uniqueid_ is missing.
4	CATCHEERRORBADTABLE_NAME	The table name cannot be found in the list of tables held in the gbuniqueids table.
5	CATCHERECORDDOESNOT_EXIST	The record does not exist.
6	CATCHEERRORRECORDLOCKED	The record is already locked. The user_login locking the record is also given.
7	CATCHEERRORBADRECORD_LOCK	The record could not be locked. Catch-e support will be informed.
8	CATCHEERRORMISSINGFIELD_NAMES	None of the Input Fields contained field names from the target table.

Error Output

CATCHEERRORBADQUERY_3

Gb Record upsert

Warning: This web service has been deprecated and is no longer supported by Catch-e.
You must use the API Insert or update a record with auditing.

Gb SystemMaintenance checkSPFRecordForGbMailSenderDomains

This job checks and verifies the status of SPF records of domains stored in the table if the status_flag of the record is 'enrolled' or 'failed'. All other statuses are ignored.

If the verification fails then the field statusflag is changed to 'failed' and information on why it failed is added into the errordata field.

If the verification succeeds for a 'failed' record, the statusflag is updated to 'enrolled' and data in the errordata field is cleared..

If the ) is set to 'yes' and the domain record is not 'enrolled', your emails will either not send, or use the fall_back email if you have one. Visit the Domain Setup page for full details of how emailing is affected by these status changes.

This job is run frequently and a child job executeQueries) is set to run immediately after which notifies [mailto:support@catch-e.com.au] of domains that fails the verification check.

Parameters

No run time parameters are required.

Gb SystemMaintenance rebuildCostCentreAllocations

This job type allows you to rebuild the fmcontractcostcentreallocations table. You might want to run this after importing 'CC' type contract events if you don't want to wait for the scheduled daily rebuild.

Parameters

No run time parameters are required.

Gb SystemMaintenance rebuildDivisionAllocations

This job type allows you to rebuild the fmcontractdivision_allocations table. You might want to run this after importing 'DIV' type contract events if you don't want to wait for the scheduled daily rebuild.

Parameters

No run time parameters are required.

Gb SystemMaintenance rebuildDriverAllocations

This job type allows you to rebuild the fmcontractdriver_allocations table. You might want to run this after importing Driver Change contract events if you don't want to wait for the scheduled daily rebuild.

Parameters

Typically set up to run at 10:00PM.