Skip to content

Using Variables and Parameter Markers

If you run the same SQL many times with different values, put variables or parameter markers in it instead of the values. When you execute the SQL, DbVisualizer asks for the values in a window and uses them in the statement. For example, a script that creates a user and grants permissions can ask for the user name.

Variables and parameter markers are only handled when SQL Commander → SQL Commander Options → Parameterized SQL is selected.

Choosing a syntax

DbVisualizer supports its own variables and two kinds of parameter markers:

SyntaxExampleUse
DbVisualizer variable${Age||20}$Anywhere in the SQL, also for table and column names. Can have a default value, a data type and options.
Named parameter marker:Age, &Age, :{Age}, :'Age'For values, where the database supports it. Has only a name.
Unnamed parameter marker?For values. Supported by most databases. Shown as Parameter1, Parameter2 and so on.

DbVisualizer variables are the most flexible. Parameter markers are useful when you want to run SQL from an application or another tool in DbVisualizer as it is. Check that the database and driver support the syntax.

Use only one kind of variable or marker in a script. If you mix DbVisualizer variables and parameter markers, or named and unnamed markers, you are only asked for the values of one kind, and the execution fails.

Example

SELECT *
FROM   EMPLOYEE
WHERE  FIRST_NAME LIKE '${First Name||Phil}$'
AND    AGE > ${Age||20}$;

When you execute this, DbVisualizer asks for First Name and Age, with Phil and 20 as the default values.