Parameterized SQL - Variables and Parameter Markers

A useful feature in the SQL Commander is to use variables or parameter markers in SQL scripts. Variables are used to express that certain parts of the SQL should be replaced with values when the SQL is executed. If you use a script to perform repetitive tasks, such as creating a user and granting permissions, just insert variables for the user name and permissions to grant in the script and DbVisualizer will prompt for the values at execution.

In addition to DbVisualizer's own variable syntax, two parameter marker syntaxes supported natively by some databases/drivers can also be used. This makes it easier to use SQL statements from other tools or code as-is in DbVisualizer, but you need to be aware of the limitations in how they are used compared to DbVisualizer variables.

The following gives an overview of the different formats and how they can be used.

The following variable syntaxes are supported by DbVisualizer:

DbVisualizer Variables

This is the syntax for a DbVisualizer variable:

${variable||value||type||options}$

This is the most flexible syntax as it supports setting a name, default value, data type, and other options. Check the DbVisualizer Variables section for details. A DbVisualizer variable can be used anywhere in the SQL as the specified value replaces the variable definition as a literal (unless a data type is specified; with a data type, its behavior is exactly the same as for Named Parameter Markers.

Example

select * 
from   EMPLOYEE 
where  FIRST_NAME like '${First Name||Phil}$'
and    AGE > ${Age||20}$

The variable identifiers, ${...}$ can be modified in Tools → Tool Properties and in the General / Variables category.

Named Parameter Markers

These are the parameter marker syntaxes supported by DbVisualizer.

&name
:name
:{name}
:'name'

This format allows only a name for the parameter and no other settings, such as type or default value. The parameter name is the name DbVisualizer shows in the prompt window.

Named parameter values are bound at runtime with the markers in the SQL statement. Some JDBC drivers/databases require that the proper data type is set while some are more relaxed. For named (and unnamed) parameter markers, you may choose data type in the prompt window. The data type Literal means that the specified value will replace the variable as a literal in the final SQL statement. I.e., the value is not bound at runtime.

Named parameter markers should only be used in contexts supported by the actual database, usually for column values. For example, as opposed to a DbVisualizer variable, a parameter marker cannot be used for a table or column name. The only difference between &name, :name, :{name}, and :'name' is that the latter two, :{name} and :'name', allows white spaces in the name.

Example

insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, ADDRESS, AGE)
   values (null, &FirstName, &LastName, &Address, &Age);

insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, ADDRESS, AGE)
   values (null, :FirstName, :LastName, :Address, :Age);

insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, ADDRESS, AGE)
   values (null, :{FirstName}, :{LastName}, :{Address}, :{Age});

insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, ADDRESS, AGE)
   values (null, :'FirstName', :'LastName', :'Address', :'Age');

Read more about named parameter markers.

Unnamed Parameter Markers

The question marker symbol is probably the most supported parameter marker among the supported databases.

?

It is also the most unintuitive marker since the user has to count the number of question marks to set the corresponding value.

Since there is no name associated with it, DbVisualizer shows these as the following:

• Parameter 1
• Parameter 2
• Parameter 3
• ...

For unnamed parameters, you can set a data type for the parameter when prompted for the value.

There is no technical difference between how unnamed and named parameter markers are handled internally in DbVisualizer or when processed by the database. All are bound with a prepared SQL statement.

Example

insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, ADDRESS, AGE)
   values (null, ?, ?, ?, ?);

In DbVisualizer it is recommended to use named parameter in favor of unnamed parameter markers for easier usability during execution. If you intend to run the SQL in application code, check the support for named parameters in the target database.

Read more about unnamed parameter markers.

For more information about the different syntaxes check Using DbVisualizer Variables and Using Parameter markers.