Scripting overview

This section explains how to write, run and troubleshoot BroadSQL Scripts: variables, named arguments, messages, output and error control, nested Scripts and exporting a Script's result. This page gives the mental model; each topic has its own detailed page, listed in The Scripting pages. The exact syntax of each command is in the command reference.

What is a BroadSQL Script?

A Script is a text file of SQL statements, BroadSQL commands, or both, separated by ;. Each statement runs exactly as if you had typed it at the prompt.

CONNECT WORLD;

SELECT CODE, NAME, POPULATION FROM COUNTRY WHERE CONTINENT = 'Oceania';

DUMP / TO oceania AS XLSX;

A file with a single SELECT is a Script too: there is no separate kind of file for a saved query.

The Scripts Library is the folder where you keep reusable Scripts: scripts/ by default, set by the ScriptsLibrary key of BroadSQL.ini (see Application settings). It is a place, not a different kind of Script. A Script inside the library and a Script stored anywhere else behave identically.

Run a Script with @, the preferred short form, or with LIB RUN, which only runs Scripts of the library. Both use the same execution engine, and so does Run in the BroadSQL Editor.

SQL scripting is intentionally lightweight. A Script is a list of statements with a few helpers: variables, named arguments, messages, and output and error control. It is not a programming language: there are no loops, conditions or functions (see What SQL Scripts do not do).

One library, no separate SQL Library

Releases before 5.3.0 had a separate SQL Library for saved queries next to a scripts catalog. Both were merged into the Scripts Library: every saved query is now simply a Script, run with @ or LIB RUN and managed with the LIB commands. The former SqlLib and Scripts settings no longer exist.

JavaScript files are not Scripts. They are run by the JS commands, a separate developer capability described in JavaScript scripting, and never appear in the Scripts Library or in the Editor.

Extensions carry no meaning

A Script's file extension does not matter to BroadSQL. countries.bsql, countries.sql, countries.txt and a file simply called countries are all Scripts if they are text files of valid BroadSQL input, read by the same parser.

.bsql is the default extension of a new Script created in the BroadSQL Editor, and the recommended one for a Script that contains BroadSQL commands. .sql remains fully supported and suits SQL oriented Scripts.

BroadSQL never guesses an extension: @foo runs the file named foo, never foo.bsql or foo.sql.

Your first Script

Save this as reports/country-count.bsql in the Scripts Library:

-- @description: Number of countries per region of Europe
ECHO 'Counting the countries of each region of Europe';

SELECT REGION, COUNT(*) AS COUNTRIES
FROM COUNTRY
WHERE CONTINENT = 'Europe'
GROUP BY REGION
ORDER BY REGION;

Connect to the WORLD sample (CONNECT WORLD;), then run it with either form:

@reports/country-count.bsql
LIB RUN reports/country-count.bsql

BroadSQL echoes each statement before running it, prints the ECHO message and the query result, and ends with one status line:

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints

WORLD> Found 2 queries
WORLD> ECHO 'Counting the countries of each region of Europe'
Counting the countries of each region of Europe
WORLD> SELECT REGION, COUNT(*) AS COUNTRIES FROM COUNTRY WHERE CONTINENT = 'Europe' GROUP BY REGION ORDER BY REGION
|--------------------------|--------------------|
|REGION                    |COUNTRIES           |
|--------------------------|--------------------|
|Baltic Countries          |3                   |
|British Islands           |2                   |
|Eastern Europe            |11                  |
|Nordic Countries          |11                  |
|Southern Europe           |16                  |
|Western Europe            |9                   |
|--------------------------|--------------------|

6 rows fetched in 4 ms.

WORLD>
WORLD> reports/country-count.bsql: SUCCESS (2 statements, 0 failed) [run 5O42HI6T]

Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

The first line is a comment. Comments starting with -- @ form the Script's metadata header and never interfere with execution.

How Scripts work

A Script runs its statements one after the other, exactly as if you had typed them, and a few helpers make that list of statements reusable and safe:

Running. @ runs any Script by a library path, a ./ path or an absolute path; LIB RUN runs a Script of the library only. A reference is never searched for: the path you write is the path used.
Variables. LET stores one typed value, written as a literal, copied from another variable or read with a one-row, one-column query. ${name} uses it in SQL as a bound value, never as pasted text. Variables belong to the session: the prompt and every Script share them.
Arguments. @report.sql country='FR' passes named values, which are ordinary variables assigned just before the Script starts. -- @param declares a parameter with its type (TEXT, INTEGER, DATE, a list...), an optional default and a description; the older -- @params: lists the names every call must pass. The former positional parameters %1 to %9 were removed.
Output and errors. ECHO prints a message, OUTPUT QUIET hides routine output, and ON ERROR STOP ends a Script at its first failure. A failed SQL statement rolls back the pending transaction, even when the Script continues.
Nested Scripts and the run. A Script can call Scripts; they all share the variables and form one run, with one Run ID and one final status: SUCCESS, COMPLETED_WITH_ERRORS, FAILED or CANCELLED. CTRL+C cancels the whole run.
Export. DUMP LIB and DUMP @ run a Script and write its tabular result to a file, only when the run ends SUCCESS.
The Scripts Library. A folder of reusable Scripts with an optional metadata header, listed, searched, edited and safely archived with the LIB commands and the BroadSQL Editor.

The Scripting pages

Page	What it covers
Running Scripts	`@` and `LIB RUN`, how a reference is resolved, statement splitting, comments, and the checks made before a Script starts.
Variables	`LET`, `${name}` as a typed bound value, `SHOW SCRIPT VARIABLES`, and variables at the prompt.
Arguments and parameters	Named arguments, typed, optional and list parameters (`-- @param`), `-- @params:`, and the migration from `%1..%9`.
Output and error handling	`ECHO`, `OUTPUT QUIET` and `OUTPUT NORMAL`, `ON ERROR`, and transactions.
Nested Scripts and run lifecycle	Scripts calling Scripts, the final status, the Run ID, and cancelling with CTRL+C.
Exporting Script results	`DUMP LIB` and `DUMP @`.
Scripts Library	The metadata header, browsing, grouping and searching, the folder tree, editing, deleting and restoring, `LIB LINT`, and encoding.
BroadSQL Editor	The Scripts Library as a folder tree: editing, running and saving Scripts, with automatic version history.
Examples	Complete Scripts with their real output.

What SQL Scripts do not do

SQL Scripts deliberately stay a list of statements with a few helpers. They do not provide, and are not meant to grow into:

loops;
IF/ELSE or any other condition;
user-defined functions or procedures;
GOTO or jumps;
local variable scopes: every variable is a session variable;
a general expression language: compute a value with SQL in a LET query;
a general-purpose programming runtime.

Other limits follow from the same model:

What	Why
Positional parameters `%1` to `%9`	Removed: arguments are named, `name=value`.
`${name}` as a table or column name	A variable is always passed as a value.
Procedural SQL blocks	A `;` inside a block still ends a statement.
A shared or team library	The Scripts Library is a personal, single installation folder.
A dry run that shows the statements with their values	Each statement is echoed as it runs, followed by the values of its variables.

When a task needs real programming logic, such as loops, conditions or several connections open at the same time, use JavaScript scripting, the advanced capability for developers.

Examples: complete Scripts with their output.
Command reference: exact syntax of @, LIB RUN, LET, ECHO, ON ERROR, OUTPUT, SHOW SCRIPT VARIABLES, DUMP and PULL, generated from each command's own code.
Export: every DUMP source, format and option.
BroadSQL Editor: browsing, editing, running and versioning Scripts.
Application settings: the ScriptsLibrary key.
Glossary: short definitions of the scripting terms.
JavaScript scripting: the developer capability for loops, conditions and several connections at once.
The file macro <@file> is documented on the Features page.

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