Scripts and the Scripts Library

BroadSQL executes Scripts. A Script is a text file containing one or more commands that BroadSQL can execute. It may contain SQL statements, BroadSQL commands, or a combination of both.

SELECT * FROM CUSTOMER;

is a Script. So is:

CONNECT PROD;

SELECT * FROM CUSTOMER;

EXPORT RESULT customers.xlsx;

There is no separate kind of file for a single saved query: a file with one SQL statement is simply a Script with one statement.

The Scripts Library is BroadSQL's default managed location for reusable Scripts: a folder, scripts/ by default, set by the ScriptsLibrary key in 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 when run.

Use @script-name as the preferred concise way to run a library Script. LIB RUN script-name does the same thing and is available when you are working through the LIB command family. Both go through one and the same execution pipeline, and so do runs started from the BroadSQL Editor.

JavaScript files are not Scripts in this sense. They belong to the separate, experimental JS commands and never appear in the Scripts Library or in the Editor.

Extensions carry no meaning

Which file extension a Script has does not matter to BroadSQL. customer.bsql, customer.sql, customer.txt, customer.foo and a file simply called customer are all Scripts if they are text files whose content is valid BroadSQL input. A .sql file may hold several statements and BroadSQL commands; a .bsql file uses the same parser as a .sql file.

.bsql is the default extension for a new Script created in the BroadSQL Editor, because a Script may contain BroadSQL commands as well as SQL. It is the recommended extension for Scripts that contain BroadSQL commands or a mix. .sql remains fully supported and suits SQL oriented Scripts.

BroadSQL never guesses an extension. @foo runs the file literally named foo; it does not try foo.bsql or foo.sql. Likewise LIB RUN foo means a library file named foo.

Running a Script

@maintenance/cleanup.bsql
LIB RUN maintenance/cleanup.bsql
@./helper.bsql
@C:\temp\foo.bsql
@"C:\My Scripts\foo.bsql" value1 "value 2"
LIB RUN "folder/my script.bsql" value1 "value 2"

How a reference is resolved never depends on cleverness, only on its form:

Points that follow from those rules:

• A plain reference such as @common/util.bsql always starts at the library root, even when it is written inside another Script. @./helper.bsql is the form that means "next to this Script", so a folder of Scripts can be moved as a bundle.
• LIB RUN runs Scripts of the library and nothing else. It rejects an absolute path, a ./ path and any path that would leave the library, such as ../elsewhere/x.bsql. Use @ with an explicit path for a Script stored elsewhere. Plain references made with @ are held inside the library in the same way.
• Nothing is searched: there is no file name search across subfolders, no shorthand and no fallback folder. The name you write is the path that is used.
• Path forms follow your operating system. On Windows both \ and / separate folders and .\ is an explicit relative reference. On Linux and macOS a backslash is an ordinary file name character.
• archives/ at the top of the library is reserved for archived Scripts and cannot be run through a reference.
• Quote a reference or a value that contains spaces, as in the examples above.

If a Script cannot be run, the message names the path that was actually used and says why: not found, a directory instead of a file, unreadable, not a text file, a path that leaves the library, or a library folder that is missing or is a file.

What happens while it runs

A Script is a series of statements separated by ;. A statement may span several lines. Line comments (--, anywhere on a line) and block comments (/* ... */) are ignored; a ; or a comment marker inside a quoted string is ordinary text. Each statement runs exactly as if you had typed it at the prompt. A statement that fails is reported and the rest of the Script still runs.

A Script that cannot be run as a whole is refused before anything executes: unreadable content, a quote or block comment that is never closed (the message gives the line and column), or missing parameters.

A statement that starts with @ runs another Script, so Scripts can call Scripts. A Script that calls itself, directly or through other Scripts, is refused immediately with the chain of Scripts involved, and nesting is limited to 32 levels. The execution context is restored after each called Script, whether it succeeds or fails, so the calling Script carries on.

BroadSQL splits statements only at ;. Procedural SQL blocks that contain ; inside them are therefore still split at each inner ;, as they always have been.

Parameters

Positional placeholders %1 to %9 in a Script are replaced by the values given after the reference, in order:

-- uses %1 and %2
SELECT COUNT(*) FROM customer WHERE country = '%1' AND agent = %2;

@customers.bsql CH 800
LIB RUN customers.bsql CH 800

The same rules apply to @ and LIB RUN, and to every statement in the Script, including INSERT/UPDATE/DELETE. %10 is not a placeholder, and a value that itself contains %2 is inserted as written. If a placeholder has no value, the Script is refused before it starts. If more values are given than the Script uses, that is reported and the Script still runs.

Encoding

New Scripts are written as UTF-8 without a byte order mark. BroadSQL reads a Script with a byte order mark using that encoding, otherwise as UTF-8 if the whole file is valid UTF-8, and otherwise with the default character set of the Java process. BroadSQL does not attempt to detect other legacy encodings. When you save an existing file in the Editor, it is written back in the encoding it was read in; if the text cannot be represented in that encoding, the save is refused and the file is not changed.

A file whose beginning contains a NUL byte or mostly control characters is treated as not being text: it is not listed, cannot be opened in the Editor, and cannot be run.

Metadata header

Any Script can start with a short header of metadata lines. A file with none of these lines is simply untagged, so nothing already in the library needs to change to keep working.

-- @description: Monthly revenue by country
-- @instance: MYSAP
-- @environment: PROD
-- @tags: finance, monthly, revenue
-- @status: stable
select country, sum(amount) from revenue where year = %1 group by country;

Both -- @key value and -- @key: value are recognized, in -- line comments or /* ... */ block comments. A directive is only ever recognized inside a valid SQL comment, so select '@instance WRONG'; is never mistaken for metadata. If the same key is declared more than once with conflicting values, the first occurrence wins; @instance, @environment and @tags may instead be declared more than once on purpose to accumulate several values.

The @instance tag and the grid's Group column predate the Database Group rename and keep that name in the file format: what they compare against is the current connection's Database Group.

Metadata describes a Script. It is never a way to find one: nothing is looked up by description, tag or status, and an old @alias line is treated as an ordinary comment with no effect.

The header is skipped automatically, since it consists of comments, so it never interferes with execution.

What @instance and @environment do and do not enforce

Both tags are safeguards, not access control, and they apply to every Script that runs, including a Script called from another Script and a Script run through an explicit path. When a Script starts, its tags are compared with the connection that is current at that moment. A mismatch prints one warning line for each mismatched dimension, then the Script runs anyway. With no connection open there is nothing to compare against and nothing is printed.

• Nothing is blocked by a mismatch, whatever the connection.
• The Production flag of an environment has no connection to these tags.
• Browsing uses them too: LIB LIST without ALL hides a Script that does not match both the current Database Group and environment (or is tagged ALL, or untagged).
• Only the current connection matters; other connections are never opened or compared.

Browsing and searching

LIB LIST shows every Script in the library, including Scripts in subfolders, which appear under their library path such as maintenance/cleanup.bsql. There is no setting for this; the former ListSubfolders setting no longer exists. The grid has one row per Script: File, Group, Environment, Status and Modified.

By default the list is scoped to both the current connection's Database Group and environment, as described above. LIB LIST ALL lifts both dimensions. A search term filters by a case insensitive part of the path.

LIB LIST;
LIB LIST revenue;
LIB LIST ALL;

LIB FIND <term> searches the full text of every Script (the header included, so a description or tag matches too) and shows the matching Scripts in the same grid. It always searches the whole library. Search and listing are the only places a partial name means anything; running, showing, editing and deleting always use the exact path.

LIB SHOW <script> prints a Script's full content. Only text files are listed anywhere; a binary file in the folder is ignored.

Editing

LIB EDIT <script> and EDIT <script> open the Script in the BroadSQL Editor, the one window that shows the Scripts Library as a folder tree. No external editor is started and opening does not save anything. If the Script does not exist, the Editor offers to create it; a new Script that you name without an extension gets .bsql, and it starts with -- @status: draft.

Deleting safely

LIB DEL <script> asks for a y/n confirmation, then moves the Script to the archives/ folder of the library instead of deleting it. The BroadSQL Editor's Delete does exactly the same thing, and its Recently Deleted window lists the same archive. Nothing is purged automatically. Two ways to bring a Script back:

• LIB UNDO restores whichever Script was archived most recently.
• LIB RESTORE <script> restores a specific archived Script by the path it had. The path must match exactly.

Both refuse, rather than overwrite, when a Script already exists at that path again. When a Script is restored, its revision history in the Editor continues; a brand new file created at the same path after a deletion starts a new history. Browse the archive with LIB LIST ARCHIVES.

Checking consistency

LIB LINT (optionally followed by one Script's path) reports, without changing anything: an @instance id that matches no Database Group in the connections definition file, an @environment id that matches no environment, and a %N sequence with a gap (for example %1 and %3 but no %2). This applies to every Script.

Not supported

Related pages

• Command reference, generated from each command's own code and always up to date.
• Application settings: the ScriptsLibrary key.
• BroadSQL Editor: browsing, editing, running and versioning Scripts.
• Light scripting with JS: the separate, experimental JavaScript commands.
• The file macro <@file> is documented on the Features page.