Using DbVisualizer Variables

Variables can be used to build parameterized SQL statements and let DbVisualizer prompt you for the values when the SQL is executed. This is handy if you are executing the same SQL repetitively, just wanting to pass new data in the same SQL statement.

Variable Syntax

The variable format supports setting a default value, data type and a few options as in the following example:

${FullName||Andersson||String||where pk}$

Here is the complete variable syntax:

${name || value || type || options}$

• name Required. This is the name that appear in the substitution dialog. If multiple variables in a script have the same name, the substitution dialog shows only one and the entered value will be applied to all variables of that name.

• value The default value for the variable

• type The type of variable: String, Boolean, Integer, Float, Long, Double, BigDecimal, Date, Time and Timestamp. In addition DbVisualizer defines: BinaryData and TextData (for CLOB). This is used to determine how the data should be passed between DbVisualizer and the database server. If no type is specified, it is treated as an Integer.

• options The options part is used to express certain conditions:

• pk Indicates that the variable is part of the primary key in the final SQL. Represented with a key icon
• where Defines that the variable is part of the WHERE clause. The green star icon further illustrate this condition
• noshow This option define that the variable should not appear in the substitution dialog. A proper value must be set when using this option, unless it is an output variable (see dir below)
• nobind Specifies that the value should be replaced as text in the final statement instead of being replaced as a parameter marker
• dir=in | out | inout The direction for a variable used with the @call command (it is ignored for other uses). A variable assigned the return value for a function must be declared as dir=out, and a variable used for a procedure parameter must use a dir type matching the procedure parameter direction declaration. in is the default.

Pre-defined Variables

A few pre-defined DbVisualizer variables can be used anywhere in the SQL. These are replaced with actual values just before the SQL is sent to the DB server. The final value for these variables are self explanatory.

${dbvis-date}$${dbvis-time}$${dbvis-timestamp}$ ${dbvis-connection}$<br></br>${dbvis-database-type}$

By default, date/time variable values are formatted as defined in Tool Properties->Data Formats, but you can also specify a custom format for a single use of the variable, e.g.

${dbvis-date||||||format=[yyyyMMdd]}$

The following variables can be used only when monitoring a SQL statement that produce a result set and the Allowed Row Count for the monitor is > 0. The output format is seconds and milliseconds. Ex: 2.018

${dbvis-exec-time}$${dbvis-fetch-time}$

Variable Substitution in SQL statements

A simple variable may look like this:

${FullName}$

A variable is identified by the start and end sequences, ${ ... }$. (These can be re-defined in Tool Properties). During execution, the SQL Commander searches for variables and displays a window with the name of each variable and an input (value) field. Enter the value for each variable and then press Execute. This will then replace the variable with the value and finally let the database execute the statement.

Consider the following SQL statement with variables. It is the simplest use of variables as it only contains the variable names. In this case it is also necessary to enclose text values within quotes since the substitution window cannot determine the actual data type from these variable expressions.

INSERT
INTO
   "SCOTT"."EMP"
VALUES
(
   ${EMPNO}$,
   '${ENAME}$',
   '${JOB}$',
   ${MGR}$,
   '${HIREDATE}$',
   ${SAL}$,
   ${COMM}$,
   ${DEPTNO}$
)

Executing the above SQL will result in the following window being displayed:

The substitution window have the same look and functionality as the Form Data Editor i.e. you can sort, filter, insert pre-defined data, copy, paste and edit cells in the multi line editor, plus a lot of other things. In addition the substitution window adds two new commands (leftmost in the toolbar and in the form right-click menu) specifically for the substitution window:

• Set Default Values This will set the value to the default value for the variable. If a default value was not specified in the variable, (null) will appear
• Set Previously Used Values Set the value for each variable to the values used in the previous run (if there is no values from a previous run, this button is disabled).

The SQL Preview area shows the statement with all variables substituted with the values.

Here is an example of a more complex use of variables.

update SCOTT.EMP set 
  EMPNO = ${EMPNO||7698||BigDecimal||pk ds=22 dt=NUMERIC }$, 
  ENAME = ${ENAME||BLAKE||String||nullable ds=10 dt=VARCHAR }$, 
  JOB = ${JOB||MANAGER||String||nullable ds=9 dt=VARCHAR }$, 
  MGR = ${MGR||7839||BigDecimal||nullable ds=22 dt=NUMERIC }$, 
  HIREDATE = ${HIREDATE||1981-05-01 00:00:00.0||Timestamp||nullable ds=7 dt=TIMESTAMP }$, 
  SAL = ${SAL||2850||BigDecimal||nullable ds=22 dt=NUMERIC }$, 
  COMM = ${COMM||(null)||BigDecimal||nullable ds=22 dt=NUMERIC }$, 
  DEPTNO = ${DEPTNO||30||BigDecimal||nullable ds=22 dt=NUMERIC }$ 
where EMPNO = ${EMPNO (where)||7698||BigDecimal||where pk ds=22 dt=NUMERIC }$

This example use the full capabilities of variables. It is in fact generated by the Script to SQL Commander->INSERT COPY INTO TABLE right click menu choice in the Data tab grid.

To highlight that a variable is part of the WHERE clause in the final SQL it is represented with a green icon in front of the name.

When executing a statement that consist of variables, DbVisualizer replaces each variable with either the value as inline text or as a parameter marker. Using parameter markers to pass data with a statement is more reliable and safe than inline values. It is also the recommended technique to set values as the database engine may then pre-compile these statements properly. DbVisualizer will automatically generate a parameter marker if the variable have the type section set and if there is no nobind option specified.

The following will be replaced with a parameter marker:

${Name||rolle||String}$

These will be replaced with the variable value:

${Name||rolle}$${Name||rolle||String||nobind}$

Variables in DbVisualizer may be used anywhere in a statement. However, there may be problems once the final statement is passed to the database for execution if it contains parameter markers in non supported places. A simple example is Oracle that does not accept parameter markers for a table name. To solve this problem either clear the type part of the variable expression or add the option nobind (see above).

Changing the Delimiter Characters

You can change which character sequences should be used as the prefix, suffix and part delimiter in a variable expression in Tools->Tool Properties, in the Variables category under the General tab.