28. Manage variables with SQL Workbench/J

28.1. Define a script variable - WbVarDef
28.2. Delete a script variable - WbVarDelete
28.3. Show defined script variables - WbVarList
[Important]

All SQL Workbench/J specific command can only be used from within SQL Workbench/J

More details about using variables can be found in the chapter Variable substitution.

28.1. Define a script variable - WbVarDef

This defines an internal variable which is used for variable substitution during SQL execution.

There are two possibilities to define a variable.

The short syntax is: WbVarDef variable=value

The long syntax allows to define variables in a different way:

ParameterDescription
-variableThe name of the variable to be defined.
-valueThe value of the variable.
-file Read the variable definitions from the specified file.
-contentFile Read the contents of the variable from a the specified file.
-encoding The encoding of the file specified with -contentFile.
-query Define a SQL query to be used as the source for the variable(s).
-replaceVars

Valid values are: true, false

If the variable content is defined through -contentFile, this parameter controls if variable references in the the content should be replaced.

The default for this parameter is: true.

-cleanupValue

Valid values are: true, false

If this is set to true, the value is "cleaned" up such that newlines (linefeeds) and SQL style comments are removed.

The default for this parameter is: false.

-removeUndefined

Valid values are: true, false

If this is set to true, any reference to non-existing variables inside the value to be defined through -value, -query or -contentFile) is removed.

The default for this parameter is: false.

-nullHandling Define how NULL values returned from a SQL query should be handled.
empty - if the value from the query is NULL, the variable be set to an empty string.
ignore - the value is ignored. An existing variable will not be changed. If the variable does not exist, it will not be created.
remove - if the value from the query is NULL, the variable will be removed.
-values Define a comma separated list of values that are used in the dialog that is shown when prompting for variable values.
-silent If the option -silent is specified, no feedback about the variable definition will be shown.

More details and examples can be found in the chapter: Variable substitution

28.2. Delete a script variable - WbVarDelete

This removes a variable.

The syntax is WbVarDelete variable

variable is the name of the variable to be removed. The name can contain wildcards. WbVarDelete * will remove all defined variables. WbVarDelete id* will remove all variables starting with id.

28.3. Show defined script variables - WbVarList

This shows all defined variables when invoked without parameters. There are two ways to filter the result:

Supplying a simple wildcard value, e.g. WbVarList foo* to display variables where the name starts with foo

Using the parameter -match to define a regular expression only show variables where the name matches the regular expression, e.g. WbVarList -match="^(foo|bar).*" to list all variables where the name starts with foo or bar.