You can define variables within SQL Workbench/J that can be referenced in your
SQL statements. This is done through the internal command
WbVarDef myvar=42 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
WbVarDef myvar=. Alternatevily you can use the command
WbVarDelete myvar to remove a variable definition.
Variable substitution is also done within Macros. If your macro definition contains a reference to a SQL Workbench/J variable, this will be treated the same way as in regular statements.
The definition of variables can also be read from a properties file. This can be done by specifying
-file=filename for the
or by passing the
-vardef parameter when starting SQL Workbench/J.
Please see the description for the command line parameters
This file has to be a standard Java "properties" file. Each variable
is listed on a single line in the format
Lines starting with a
# character are ignored (comments). Assuming
myvars.def had the following content:
#Define the ID that we need later var_id=42 person_name=Dent another_variable=24
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. The query can be defined
through the parameter
-query. Alternatively this can be done by
@ as the first character after the equal sign. The query needs to be enclosed in double quotes.
WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"
or the old short syntax using
WbVarDef myvar=@"SELECT id FROM person WHERE name='Dent'"
SELECT returns more than one column, multiple variables can be defined
by specifying a comma separated list of variable names. The following statement will define the
name based on the values returned from the
WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"
When executing the statement, SQL Workbench/J only retrieves the first row of the result set. Subsequent rows are ignored. If the select returns more columns than variable names, the additional values are ignored. If more variables are listed than columns are present in the result set, the additional variables will be undefined.
-nullHandling can be used to control the result if the value retrieved through
the query is
A variable can also be defined by reading the content of a file (this is different from reading the variable definition from a file).
WbVarDef -variable=somevar -contentFile=/temp/mydata.txt
When executing the statement, SQL Workbench/J will read the content of the file
and use that as the value for the variable
If the file contents contains references to variables, these are replaced after the content as been loaded.
To disable replacement, use the parameter
Consider the following sequence of statements, where the file
contains the statement
SELECT * FROM person WHERE id = $[person_id]
WbVarDef person_id=42; WbVarDef -variable=my_select -contentFile=select.txt; $[my_select];
After running the above script, the variable
my_select, will have the value of
SELECT * FROM person WHERE id = 42.
$[my_select], the row with id=42 will be retrieved.
To view a list of currently defined variables execute the command
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
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
], 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
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.
or you put the quotes around the variable's placeholder, e.g.:
Variables will be replaced inside string literals (e.g.
If you are using values in your regular statements that actually need the prefix (
]) characters, please make sure that you have no variables defined.
Otherwise you will have 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 prefix e.g. to
the suffix (i.e end of the variable name) to
#, add the following lines to
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, reference the value with a question 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 implicitly 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 question 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
WBVARDEF or on the command line) you will be prompted for a value for
search_name. Any subsequent execution of the statement (or any other
$[&search_name]) will re-use the value
When defining a variable, you can specify a list of values that should be entered in the dialog.
WbVardef -variable=status -values='active,pending,closed';
By default the variables shown in the prompt dialog are sorted alphabetically. This behavior can be
changed by setting the configuration property
workbench.sql.parameter.prompt.sort to true,
e.g. using WbSetConfig
If the property is set to
false, the variables are shown in the order they were
WbVarDef zzz=''; WbVarDef vvv=''; WbVarDef aaa=''; select * from foobar where col1 = $[?aaa] and col2 = $[?vvv] and col3 > $[?zzz]
The dialog to enter the variables will show them in the order
By default all windows opened through→ will share the same set of variables. If you change a variable in the first window, the new value is also immediately visible in all other windows.
The default behaviour can be changed in the "General" section of the options dialog. If enabled, each window will have its own set of variables. Setting or changing a variable in one window will not affect variables in the other windows.