BroadSQL BroadSQL Downloads
Documentation / Scripting: Examples

Examples

Complete Scripts, run against BroadSQL; the output shown is the real output.

A complete example

This Script uses most of the scripting features. Save it as customer-report.sql:

-- @description: Customers of one country, up to its highest customer ID
-- @params: country

ON ERROR STOP;
OUTPUT QUIET;

ECHO 'Preparing report for ${country}';

LET max_id =
    SELECT MAX(ID)
    FROM CUSTOMER
    WHERE COUNTRY = ${country};

ECHO 'Maximum customer ID is ${max_id}';

SELECT ID, NAME, COUNTRY
FROM CUSTOMER
WHERE COUNTRY = ${country}
  AND ID <= ${max_id};

ECHO 'Report completed';

Run it:

@customer-report.sql country='FR'
BroadSQL> Found 7 queries
BroadSQL> ON ERROR STOP
BroadSQL> OUTPUT QUIET
Preparing report for FR
Maximum customer ID is 3
|-----------|-------------------------|----------|
|ID         |NAME                     |COUNTRY   |
|-----------|-------------------------|----------|
|1          |Ann                      |FR        |
|3          |Chloe                    |FR        |
|-----------|-------------------------|----------|

2 rows fetched in 1 ms.

Report completed

What happened:

  • -- @params: country makes country='FR' 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 max_id = SELECT ... stores one value, which is never displayed or exported.
  • ${country} and ${max_id} are passed to the database as typed values: no quotes around ${country}.

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

DUMP LIB customer-report.sql country='FR' TO customers_fr 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.