Output and error handling

Printing messages: ECHO

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

With a variable country_code set to 'FRA' (LET country_code = 'FRA';):

ECHO 'Starting country check';
ECHO 'Country ${country_code}; step 2 -- loading';
ECHO 'It''s done';
ECHO 'Write $${country_code} to use the variable';

prints:

Starting country check
Country FRA; step 2 -- loading
It's done
Write ${country_code} 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.

The examples of this page write into a practice table of the WORLD sample, never into its own tables. Create it empty first with CREATE TABLE MY_CITY_NOTE (CITY_ID INTEGER, NOTE VARCHAR(100));, and remove it afterwards with DROP TABLE MY_CITY_NOTE;. This Script adds two notes, the second one quietly:

INSERT INTO MY_CITY_NOTE VALUES (2974, 'Paris office');
OUTPUT QUIET;
INSERT INTO MY_CITY_NOTE VALUES (3068, 'Berlin visit');
LET n = SELECT COUNT(*) FROM MY_CITY_NOTE;
ECHO '${n} notes';
SELECT COUNT(*) AS TOTAL FROM MY_CITY_NOTE;

prints:

WORLD> Found 6 queries
WORLD> INSERT INTO MY_CITY_NOTE VALUES (2974, 'Paris office')
1 row(s) created.
WORLD>
WORLD> OUTPUT QUIET
2 notes
|--------------------|
|TOTAL               |
|--------------------|
|2                   |
|--------------------|

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

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 MY_CITY_NOTE VALUES (2974, 'Paris office');
INSERT INTO MY_CITY_NOTES VALUES (3068, 'Berlin visit');
INSERT INTO MY_CITY_NOTE VALUES (1464, 'Roma workshop');
COMMIT;

The second statement fails (the table name is misspelt), which also rolls back the note on Paris:

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

The Script continues, so the note on Roma is inserted and committed. The end result is the note on Roma without the note on Paris, 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.

Related pages

• Scripting overview
• Nested Scripts and run lifecycle: how failures add up across called Scripts, the final status, and CTRL+C.
• Variables: the values ECHO prints.
• Command activity log: where hidden output still goes.
• Command reference: ECHO, OUTPUT, ON ERROR.

Found something wrong or unclear on this page? Tell us, with the page address.