BroadSQL Download
Documentation / Scripting: Examples

Examples

Complete Scripts and what they print. The first example runs on the WORLD sample; its output is illustrative, built from WORLD's data and the output format of this release. The second example uses no table, and its output is the real output of a run.

A complete example

This Script uses most of the scripting features. Save it as country-report.sql in the Scripts Library:

-- @description: Cities of at least 500000 inhabitants of one country
-- @params: country

ON ERROR STOP;
OUTPUT QUIET;

ECHO 'Preparing report for ${country}';

LET country_name =
    SELECT TRIM(NAME)
    FROM COUNTRY
    WHERE CODE = ${country};

ECHO 'Largest cities of ${country_name}';

SELECT ID, NAME, POPULATION
FROM CITY
WHERE COUNTRYCODE = ${country}
  AND POPULATION >= 500000
ORDER BY POPULATION DESC;

ECHO 'Report completed';

Connected to WORLD, run it:

@country-report.sql country='FRA'

BroadSQL prints:

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
WORLD> Found 7 queries
WORLD> ON ERROR STOP
WORLD> OUTPUT QUIET
Preparing report for FRA
Largest cities of France
|-----------|-----------------------------------|-----------|
|ID         |NAME                               |POPULATION |
|-----------|-----------------------------------|-----------|
|2974       |Paris                              |2125246    |
|2975       |Marseille                          |798430     |
|-----------|-----------------------------------|-----------|

2 rows fetched in 1 ms.

Report completed
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.

What happened:

  • -- @params: country makes country='FRA' mandatory on every call, and the call assigns it before the first statement.
  • ON ERROR STOP ends the run at the first failure, so a broken query never produces a partial report.
  • OUTPUT QUIET hides the echo of the following statements, the LET confirmation and the final SUCCESS line; the lines printed before it stay. The ECHO messages and the result table are always shown.
  • LET country_name = SELECT ... stores one value, which is never displayed or exported; ECHO prints it.
  • ${country} is passed to the database as a typed value: no quotes around ${country}.

Export the same report to a spreadsheet, france_report.xlsx, worksheet DATA:

DUMP LIB country-report.sql country='FRA' TO france_report AS XLSX;

The Script runs exactly as above, and the file is written only because the run ends SUCCESS.

Calling Scripts from a Script

monthly.bsql sets a variable and passes it to two library Scripts:

LET region = 'EU';
@prepare-data.bsql region=${region};
@create-report.bsql region=${region};

prepare-data.bsql:

-- @params: region
LET prepared_for = ${region};
ECHO 'prepared ${region}';

create-report.bsql:

-- @params: region
ECHO 'report for ${region}, prepared for ${prepared_for}';

Run it with @monthly.bsql:

BroadSQL> Found 3 queries
BroadSQL> LET region = 'EU'
BroadSQL> region = 'EU' (VARCHAR)
BroadSQL> @prepare-data.bsql region=${region}
BroadSQL> Found 2 queries
BroadSQL> LET prepared_for = ${region}
BroadSQL> prepared_for = 'EU' (VARCHAR)
BroadSQL> ECHO 'prepared ${region}'
prepared EU
BroadSQL> @create-report.bsql region=${region}
BroadSQL> ECHO 'report for ${region}, prepared for ${prepared_for}'
report for EU, prepared for EU
BroadSQL> monthly.bsql: SUCCESS (6 statements, 0 failed) [run 3FX975RY]
  • create-report.bsql reads prepared_for, set by prepare-data.bsql: called Scripts share one variable namespace, and an assignment survives the end of the Script that made it.
  • Only monthly.bsql prints a status line. Its counts include the statements of both called Scripts, and the three Scripts share one Run ID.
  • After the run, SHOW SCRIPT VARIABLES at the prompt lists both region and prepared_for.

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