You can define variables within SQL Workbench/J that can be referenced in your
SQL statements. This is done through the internal command WbVarDef
,
e.g.: wbvardef myvar=42
This example defines a variable with the name myvar
and the value
42
. If the variable does not exist, it will be created. If it exists
its value will be overwritten with the new value. To remove a variable simply set its value
to nothing: wbvardef myvar=
. Alternatevily you can use the command
wbvardelete myvar
to remove a variable definition.
![]() | |
Variables are case sensitive. |
Variables can also be read from a properties file, either by specifying
-file=filename
for the WbVarDef
command,
or by passing the -vardef
parameter when starting SQL Workbench/J.
Please see the description for the command line parameters
for details.
wbvardef -file=/temp/myvars.def
This file has to be a standard Java "properties" file. Each variable
is listed on a single line in the format variable=value
.
Lines starting with a #
character are ignored (comments). Assuming
the file myvars.def
had the following content:
#Define the ID that we need later var_id=42 person_name=Dent another_variable=24
After executing wbvardef -file=/temp/myvars.def
there would be
three variables available in the system: var_id, person_name, another_variable
, that
could be used e.g. in a SELECT query:
SELECT * FROM person where name='$[person_name]' or id=$[var_id];
SQL Workbench/J would expand the variables and send the following statement to the server:
SELECT * FROM person where name='Dent' or id=42;
A variable can also be defined as the result of a SELECT
statement. This indicated
by using @ as the first character after the equal sign. The SELECT
needs
to be enclosed in double quotes, if you are using single quotes e.g. in the where clause:
wbvardef myvar=@"SELECT id FROM person WHERE name='Dent'"
When executing the statement, SQL Workbench/J uses the first column of the first row of the result set for retrieving the value for the variable. Everything else (additional columns, additional rows) will be ignored.
You can also use PreparedStatements in the SQL editor. In this case the parameters are denoted by quotation marks and you will be prompted for a value each time you run the statement (which is different to using SQL Workbench/J variables. For details on how to use prepared statements refer to support for prepared statements
To view a list of currently defined variables execute the command WBVARLIST
.
This will display a list of currently defined variables and their values. You can edit
the resulting list similar to editing the result of a SELECT
statement.
You can add new variables by adding a row to the result, remove existing variables by deleting
rows from the result, or edit the value of a variable.
If you change the name of a variable, this is the same as removing the old, and
creating a new one.
The defined variables can be used by enclosing them in special characters inside the SQL statement. The default is set to $[ and ] thus you can use a variable this way:
SELECT firstname, lastname FROM person WHERE id=$[id_variable];
If you have a variable with the name id_variable
defined, the sequence
$[id_variable]
will be replaced with the current value of the
variable.
![]() | |
Variables will be replaced after replacing macro parameters. |
If the SQL statement requires quotes for the SQL literal, you can either put
the quotes into the value of the variable (e.g. wbvardef name="'Arthur'"
)
or you put the quotes around the variable's placeholder, e.g.: WHERE name='$[name]';
![]() | |
As you can see the variable substitution is also done inside quoted literals. |
If you are using values in your regualar statements that actually need the prefix ($[
or
suffix (]
) characters, please make sure that you have no variables defined.
Otherwise you will unpredictable results. If you want to use variables but need to use
the default prefix for marking variables in your statements, you can configure a different
prefix and suffix for flagging variables. To change the the prefix e.g. to %#
and
the suffix (i.e end of the variable name) to #
, add the following lines to
your workbench.settings
file:
workbench.sql.parameter.prefix=%# workbench.sql.parameter.suffix=#
You may leave the suffix empty, but the prefix definition may not be empty.
You can also use variables in a way that SQL Workbench/J will prompt you during execution of a SQL statement that contains a variable.
If you want to be prompted for a value, simply reference the value with a quotation mark in front of its name:
SELECT id FROM person WHERE name like '$[?search_name]%'
If you execute this statement, SQL Workbench/J will prompt you for the value
of the variable search_name
. If the variable is already defined
you will see the current value of the variable. If the variable is not yet defined
it will be implicitely defined with an empty value.
If you use a variable more then once in your statement it is sufficient to define it once as a prompt variable. Prompting for a variable value is especially useful inside a macro definition.
You can also define a conditional prompt with using an ampersand instead of a quotation mark. In this case you will only be prompted if no value is assigned for the variable:
SELECT id FROM person WHERE name like '$[&search_name]%'
The first time you execute this statement (and no value has been assigned to search_name
before using WBVARDEF
or on the commandline) you will be prompted for a value for
search_name
. Any subsequent execution of the statement (or any other
statement referencing $[&search_name]
) will re-use the value
you entered.