BroadSQL BroadSQL Downloads
On this page
Documentation / Scripting: Variables

Variables

A variable holds one typed value. Assign it with LET, then use it as ${name}:

LET country = 'FR';
LET max_id = SELECT MAX(ID) FROM CUSTOMER;
LET saved_id = ${max_id};

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

Three ways to assign a value

  • Literal assignment: a number (42, -1.5), a single-quoted string ('O''Brien'), TRUE, FALSE or NULL. There are no expressions: LET n = 1 + 1 is refused.
  • Scalar query assignment: a query that starts with SELECT, WITH or VALUES and returns exactly one row and one column. It runs on the current connection, in the current transaction, so it sees your uncommitted changes. No row, several rows or several columns are errors: the first row is never taken silently. A NULL value is assigned as NULL, so SELECT MAX(ID) on an empty table gives NULL, not an error. Large objects, binary and structural columns cannot be held by a variable. The query's result is never displayed and never becomes the "last result" used by /, DUMP / or <@last:...>.
  • Exact copy: LET saved_id = ${max_id}; copies the value and its type unchanged. Use it to keep a value before a Script or a later LET changes the original.

Names are made of letters, digits and _, start with a letter or _, have at most 64 characters, and are case-insensitive. ENV, NULL, TRUE and FALSE are reserved. A failed assignment leaves the variable unchanged. Each successful LET prints a confirmation, such as max_id = 4 (INTEGER).

${name} is a typed bound value, not text

${name} is never pasted into the SQL text. BroadSQL sends the statement with a placeholder and passes the value to the database separately, through JDBC, with its type.

This has practical consequences:

  • A string needs no quotes and no escaping. With LET name = 'O''Brien';, WHERE NAME = ${name} finds O'Brien. Do not write '${name}': inside quotes, ${name} is ordinary text.
  • Numbers, dates and booleans keep their type.
  • A value can never change the statement, so a variable read from one database is safe to use in another.
  • ${name} stands for a value only. It cannot be a table name, a column name or a piece of SQL: SELECT * FROM ${table} is a SQL syntax error. Variables are not a way to build dynamic SQL.
  • A NULL value behaves as SQL NULL: WHERE col = ${x} matches no row when x is NULL; write col IS NULL.
  • An undefined or misspelt variable fails the statement before anything is sent to the database: ERROR: Variable nope is not defined.

${name} is replaced in SQL statements and in the query of DUMP (...) and PULL (...). It is not replaced in other command arguments such as CONNECT, ENV or TO <file>, nor inside quotes and comments. ECHO is the one exception: it prints variable values inside its quoted message.

On PostgreSQL, the ?, ?| and ?& operators keep working in a statement that also uses ${name}. On the other databases, a statement cannot mix a ? placeholder with ${name}.

In a Script, each statement that uses variables is echoed with one line per variable giving the value used:

SELECT ID, NAME FROM CUSTOMER WHERE ID >= ${id} AND COUNTRY = ${country}
-- id = 1
-- country = 'FR'

Listing variables: SHOW SCRIPT VARIABLES

SHOW SCRIPT VARIABLES lists every variable with its type and value. Strings appear between quotes, so the string 'NULL' is told apart from a NULL value:

|--------|-------|----------|
|Name    |Type   |Value     |
|--------|-------|----------|
|country |VARCHAR|'FR'      |
|max_id  |INTEGER|4         |
|name    |VARCHAR|'O''Brien'|
|saved_id|INTEGER|4         |
|--------|-------|----------|

Script variables are unrelated to the API variables set with VAR, which only API requests use. Neither command lists or changes the other's variables.

Variables at the prompt

Variables are not limited to Scripts. They are just as useful when you work interactively:

LET cutoff = SELECT MAX(ID) FROM ORDERS;

SELECT *
FROM ORDERS
WHERE ID >= ${cutoff};

SHOW SCRIPT VARIABLES;

Variables belong to the BroadSQL session, not to a connection or a Script:

  • The prompt, every Script and every Script it calls share one namespace. A Script sees the variables set at the prompt, and the variables a Script sets remain at the prompt after it ends.
  • They survive CONNECT, ENV, DISCONNECT and reconnections, so a value read on one database can be used on another. A LET query always runs on the connection that is current at that moment.
  • They are lost when BroadSQL exits.
  • On a line of several statements, such as LET x = 1; SELECT * FROM t WHERE id = ${x};, each statement sees the variables as they are when it runs.
  • / runs the last query again with the variables' current values.

ON ERROR and OUTPUT are refused at the prompt; everything else in the Scripting pages works there too.