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:
| Syntax | Example | Use |
|---|---|---|
| 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.