BroadSQL BroadSQL Downloads
On this page
Documentation / Scripting: Output and error handling

Output and error handling

Printing messages: ECHO

ECHO prints one message, at the prompt or in a Script:

ECHO 'Starting customer check';
ECHO 'Customer ${customer_id}; step 2 -- loading';
ECHO 'It''s done';
ECHO 'Write $${customer_id} to use the variable';

prints:

Starting customer check
Customer 42; step 2 -- loading
It's done
Write ${customer_id} to use the variable
  • The message is exactly one single-quoted string. ECHO Hello; is refused: without quotes, an apostrophe or -- in the text would change where the statement ends.
  • Inside the quotes, ${name} prints a variable's value (NULL for a NULL value), '' prints one apostrophe, and $${ prints ${ without replacing anything. Spaces, line breaks, ; and -- are printed as written.
  • An undefined variable makes the statement fail and nothing is printed.
  • ECHO is never hidden by OUTPUT QUIET. Control characters coming from variable values are shown as ?.

Controlling output: OUTPUT QUIET and OUTPUT NORMAL

By default a Script shows everything it does. OUTPUT QUIET; hides its routine output **from that statement on**; OUTPUT NORMAL; shows everything again.

INSERT INTO CUSTOMER VALUES (10, 'Dan', 'FR');
OUTPUT QUIET;
INSERT INTO CUSTOMER VALUES (11, 'Eve', 'FR');
LET n = SELECT COUNT(*) FROM CUSTOMER;
ECHO '${n} customers';
SELECT COUNT(*) AS TOTAL FROM CUSTOMER;

prints:

BroadSQL> Found 6 queries
BroadSQL> INSERT INTO CUSTOMER VALUES (10, 'Dan', 'FR')
1 row(s) created.
BroadSQL> 
BroadSQL> OUTPUT QUIET
6 customers
|--------------------|
|TOTAL               |
|--------------------|
|6                   |
|--------------------|

1 rows fetched in 0 ms.

OUTPUT QUIET is not retroactive: the lines printed before it ran stay on screen. After it, it hides:

  • the statement echo and the Found N queries line;
  • the variable value lines after the echo;
  • the row counts of inserts, updates and deletes, and the blank separator lines;
  • the LET confirmations;
  • the final status line, only when the status is SUCCESS.

It never hides query result tables, ECHO, SHOW SCRIPT VARIABLES, the output of other commands, warnings, errors, or a final status other than SUCCESS. A quiet Script that goes wrong still tells you so.

The activity log always receives everything, hidden lines included. A called Script starts with the caller's setting; a change it makes ends when it returns. Every top-level run starts in NORMAL.

Error handling: ON ERROR

ON ERROR CONTINUE;
ON ERROR STOP;
  • CONTINUE is the default. A failed statement is reported and counted, the next statement runs, and the Script ends COMPLETED_WITH_ERRORS.
  • STOP ends the Script at its first failed statement. Nothing after it runs, not even a COMMIT; a message gives the statement number and line, and the status is FAILED.
ERROR: Script stop.bsql stopped by ON ERROR STOP at statement 3 (line 3)
stop.bsql: FAILED (3 statements, 1 failed) [run NSM038PL], stopped at statement 3 (line 3)

A statement fails when SQL returns an error, a BroadSQL command reports an error, a LET or ECHO fails, a variable is undefined, or a called Script fails before starting or ends FAILED. A warning is not a failure. A called Script starts with the caller's policy; a change it makes ends when it returns. ON ERROR is refused at the prompt, where a typed line already stops at its first failure. CTRL+C is never continued.

Continuing is not keeping the transaction

ON ERROR CONTINUE means continue executing the Script. It does not mean preserve the current transaction.

When a SQL statement fails, BroadSQL rolls back the pending transaction, as it always has (some databases, such as PostgreSQL, cannot continue a failed transaction). With Autocommit off, this also discards the earlier uncommitted changes of the same Script, and a warning says so. The Script then goes on in a new transaction. With autocommit off:

INSERT INTO CUSTOMER VALUES (10, 'Dan', 'FR');
INSERT INTO CUSTOMR VALUES (11, 'Eve', 'FR');
INSERT INTO CUSTOMER VALUES (12, 'Fay', 'DE');
COMMIT;

The second statement fails (the table name is misspelt), which also rolls back customer 10:

WARNING: Pending changes since the last COMMIT on SALES_QA were rolled back.

The Script continues, so customer 12 is inserted and committed. The end result is customer 12 without customer 10, and the status is COMPLETED_WITH_ERRORS. When every change must be kept or lost together, use ON ERROR STOP: the Script ends at the failure, and nothing after it, including the COMMIT, runs.

Scripts create no transaction boundary of their own: nothing is committed or rolled back when a Script starts or ends, and COMMIT and ROLLBACK work as at the prompt. A failing BroadSQL command and ON ERROR STOP never roll back. When a Script ends with uncommitted changes, a notice after the status line says that changes are pending.