Skip to content

Command-Line Interface (dbviscmd)

Only in DbVisualizer Pro

This feature is only available in the DbVisualizer Pro edition.

In addition to the DbVisualizer application, there is a command-line interface, dbviscmd, for running queries and scripts without the GUI. To start the DbVisualizer GUI from a terminal instead, see Starting the GUI from the Command Line (dbvisgui). Use it to automate database tasks, for example:

  • scheduled jobs, such as nightly exports or cleanup scripts, run by cron or Windows Task Scheduler,
  • steps in a CI/CD pipeline, a deployment script or the setup of integration tests, such as running migration scripts or loading test data. The exit code tells the pipeline whether the script succeeded,
  • database tasks in a larger shell script,
  • large scripts, such as a script generated by the Export Schema feature.

On a build server, dbviscmd needs a DbVisualizer installation. Connect with -url and a driver if there is no workspace with your connections, and pass passwords with environment variables, see Keeping passwords off the command line.

Running dbviscmd

You find dbviscmd in the DbVisualizer installation folder:

  • Windows: dbviscmd.bat
  • Linux: dbviscmd.sh
  • macOS: dbviscmd.sh in /Applications/DbVisualizer.app/Contents/Resources/app

To choose which Java to use, or to pass extra arguments to the Java VM, use the JAVA_EXEC and VM_ARGS environment variables, see Starting the GUI from the Command Line (dbvisgui).

When you have installed a new DbVisualizer version, start DbVisualizer once and let it migrate your settings before you use dbviscmd.

What you can do with dbviscmd

TaskOptions
Run queriesA connection, and -sql or -sqlfile
Create a connection-createconnection
Change the user ID or password of a connection-modifyconnection
List your connections-listconnections
Show help or the version-help or -version

The last two tasks need no other options. For a complete list of options, see All options.

Running queries

To run queries, you specify a connection, and the queries to run.

Choosing the connection

The easiest way is to use a connection that you have created in DbVisualizer, using the -connection option:

./dbviscmd.sh -connection "Sakila" -sql "select * from sakila.actor"

If you don't remember the name, list your connections with -listconnections.

You can also connect without a connection in DbVisualizer, by giving the JDBC URL with -url, and the driver to use:

  • -drivername uses a driver from the Driver Manager in DbVisualizer, see Managing Database Drivers. If the driver files have not been downloaded yet, they are downloaded when you run dbviscmd.
  • -driverclass and -driverpath specify the driver class and the driver's JAR files yourself. Separate several JAR files with :, or with ; on Windows.
./dbviscmd.sh -url jdbc:mysql://localhost:3306/ \
   -drivername "MySQL 8+" \
   -userid root \
   -sql "select * from sakila.actor"
./dbviscmd.sh -url jdbc:oracle:thin:@localhost:1521/ORCL \
   -driverclass oracle.jdbc.OracleDriver \
   -driverpath /drivers/ojdbc11.jar \
   -userid system \
   -passwordfile /secure/oracle.pw \
   -sql "select * from hr.countries"

For more about JDBC URLs, see Creating a Connection. For how to give the password, see Keeping passwords off the command line.

Running statements

To run one or more statements, use -sql. Separate several statements with ;:

> ./dbviscmd.sh -connection "Sakila" -sql "select * from sakila.actor; select * from sakila.category"
22:05:36  START Executing Command Line for: 'Sakila' [H2], Schema: PUBLIC
22:05:36  INFO  Physical database connection acquired for: Sakila
22:05:36  SUCCESS  [SELECT - 5 rows, 0.004 secs]  Result set fetched
select * from sakila.actor;
ACTOR_ID  FIRST_NAME  LAST_NAME
--------  ----------  ------------
1         PENELOPE    GUINESS
2         NICK        WAHLBERG
3         ED          CHASE
4         JENNIFER    DAVIS
5         JOHNNY      LOLLOBRIGIDA
22:05:36  SUCCESS  [SELECT - 5 rows, 0.001 secs]  Result set fetched
select * from sakila.category;
CATEGORY_ID  NAME
-----------  ---------
1            Action
2            Animation
3            Children
4            Classics
5            Comedy
22:05:36  END Execution 2 statement(s) executed, 10 row(s) affected, exec/fetch time: 0.005/0.000 secs   [2 successful, 0 errors]

Running a script

If you run the same statements often, put them in a script file, and run it with -sqlfile. To specify the encoding of the file, use -encoding.

./dbviscmd.sh -connection "Sakila" -sqlfile myscript.sql

The output is the same as when you give the statements with -sql. Scripts can also contain client-side commands, such as @run and @export.

Controlling the execution

OptionDescription
-catalog, -schemaThe database (catalog) and schema to use for table names without a database or schema, see Choosing the Connection, Database and Schema.
-stoponerrorStop when a statement fails.
-stoponsqlwarningStop when a statement gives an SQL warning.
-stoponnorowsStop when a statement returns no rows, or affects no rows.
-maxrows, -maxcharsThe maximum number of rows to show for a result set, and of characters to show for a column.
-stripcomments true, -stripcomments falseWhether to remove comments before the statements are sent to the database. By default, the setting in DbVisualizer is used.
-reconnectpolicyWhat to do if the connection is lost: noreconnect handles it as an error, reconnect reconnects, and reconnectandexecute reconnects and runs the failed statement again. Not all statements can be run again.

Controlling the output

By default, dbviscmd shows both log messages and result sets. To show only the result sets, use -output result:

> ./dbviscmd.sh -connection "Sakila" -sqlfile myscript.sql -output result
ACTOR_ID  FIRST_NAME  LAST_NAME
--------  ----------  ------------
1         PENELOPE    GUINESS
2         NICK        WAHLBERG
3         ED          CHASE
4         JENNIFER    DAVIS
5         JOHNNY      LOLLOBRIGIDA
CATEGORY_ID  NAME
-----------  ---------
1            Action
2            Animation
3            Children
4            Classics
5            Comedy

To show only the log messages, for example for a script with INSERT statements, use -output log:

> ./dbviscmd.sh -connection "Sakila" -sqlfile myscript.sql -output log
22:05:43  START Executing Command Line for: 'Sakila' [H2], Schema: PUBLIC
22:05:43  INFO  Physical database connection acquired for: Sakila
22:05:43  SUCCESS  [SELECT - 5 rows, 0.005 secs]  Result set fetched
select * from sakila.actor;
22:05:43  SUCCESS  [SELECT - 5 rows, 0.001 secs]  Result set fetched
select * from sakila.category;
22:05:43  END Execution 2 statement(s) executed, 10 row(s) affected, exec/fetch time: 0.006/0.000 secs   [2 successful, 0 errors]

To show nothing, use -output none. To write the output to a file instead of the console, use -outputfile.

Using variables

To use DbVisualizer variables in the statements, add -processvariables. dbviscmd prompts for the value of each variable, and shows the default value in brackets. In this example, J% is entered:

> ./dbviscmd.sh -connection "Sakila" -processvariables \
     -sql 'select first_name, last_name from sakila.actor where first_name like ${Name||P%||String||}$'
dbviscmd.sh: Valid inputs: Enter '_B_' for empty, '(null)' for null
dbviscmd.sh: Variable 'Name' (Literal) [P%]: J%
22:05:52  START Executing Command Line for: 'Sakila' [H2], Schema: PUBLIC
22:05:52  INFO  Physical database connection acquired for: Sakila
22:05:52  SUCCESS  [SELECT - 2 rows, 0.015 secs]  Result set fetched
select first_name, last_name from sakila.actor where first_name like J%;
FIRST_NAME  LAST_NAME
----------  ------------
JENNIFER    DAVIS
JOHNNY      LOLLOBRIGIDA
22:05:52  END Execution 1 statement(s) executed, 2 row(s) affected, exec/fetch time: 0.015/0.001 secs   [1 successful, 0 errors]

To specify what an empty entry means, use -emptypromptvalue.

Calling dbviscmd from a shell script

For more complex tasks, you can call dbviscmd from a shell script or a BAT file, and use DbVisualizer variables to pass values from the shell script to an SQL script.

In this example, the SQL script cmdtest.sql selects from a table given by a variable:

select * from ${table}$

The text file tables.txt lists the tables to select from:

sakila.actor
sakila.category

This Bash loop runs dbviscmd once for each table:

for name in `cat tables.txt`;
  do ./dbviscmd.sh -connection "Sakila" -sql "@run cmdtest.sql \${table||$name||||nobind}\$; ";
done

Each run uses the client-side command @run to run the script, with the table variable set to the table name from the shell. Note that the dollar signs of the variable are escaped, so that the shell doesn't interpret them. The output of the first run:

22:05:55  START Executing Command Line for: 'Sakila' [H2], Schema: PUBLIC
22:05:55  INFO  Physical database connection acquired for: Sakila
22:05:55  RUNNING  [@run ....actor||||nobind}$ - - secs]
@run cmdtest.sql ${table||sakila.actor||||nobind}$;
22:05:55  SUCCESS  [SELECT - 5 rows, 0.005 secs]  Result set fetched
select * from sakila.actor;
ACTOR_ID  FIRST_NAME  LAST_NAME
--------  ----------  ------------
1         PENELOPE    GUINESS
2         NICK        WAHLBERG
3         ED          CHASE
4         JENNIFER    DAVIS
5         JOHNNY      LOLLOBRIGIDA
22:05:55  SUCCESS  [@run ....actor||||nobind}$ - 0.005 secs]  Script processed
@run cmdtest.sql ${table||sakila.actor||||nobind}$;
22:05:55  END Execution 1 statement(s) executed, 5 row(s) affected, exec/fetch time: 0.005/0.000 secs   [1 successful, 0 errors]

If the saved password for the connection is protected by a master password, each run of dbviscmd prompts for it. To run the loop without prompts, see Keeping passwords off the command line.

Creating and changing connections

To create a connection in DbVisualizer, use -createconnection with a name for the new connection, its JDBC URL and driver, and optionally a user ID and password. dbviscmd connects to the database to check the connection before it is saved:

./dbviscmd.sh -createconnection -connection "Sakila" \
   -url jdbc:h2:/data/sakila \
   -drivername "H2 2.x Embedded" \
   -userid sa

To change the user ID or password of a connection, use -modifyconnection. Give at least one of -userid, -password, -passwordfile and -passwordenv:

./dbviscmd.sh -modifyconnection -connection "Sakila" -userid newuser

Keeping passwords off the command line

A password given with -password or -masterpw is visible to other users on the machine in the process list (for example, with ps) while dbviscmd runs. To avoid this, read the password from a file or an environment variable instead:

Connection passwordMaster password
From a file-passwordfile <filename>-masterpwfile <filename>
From an environment variable-passwordenv <variable>-masterpwenv <variable>
  • A password file is read as UTF-8, and only its first line is used. Restrict access to the file so that only you can read it.
  • Specify the name of the environment variable, not its value.
  • Use only one of -password, -passwordfile and -passwordenv, and only one of -masterpw, -masterpwfile and -masterpwenv.
  • The options work with -sql, -sqlfile, -createconnection and -modifyconnection.

Updating a connection password from a script

./dbviscmd.sh -connection "MyDatabase" -modifyconnection \
   -passwordfile /secure/mydb.pw \
   -masterpwenv DBVIS_MASTER_PW

If your workspace has a master password and you don't give one using an option, dbviscmd prompts for it. If the password vault is unlocked with Touch ID or Windows Hello, dbviscmd asks for the recovery code instead, and the options take the recovery code. When no console is available, for example when dbviscmd runs from a scheduler, use -masterpwfile or -masterpwenv.

Other options

OptionDescription
-workspaceUse the settings and connections of another workspace.
-debug, -debugfileWrite debug messages, by default to the console.
-errordirWrite error logs to another folder.

Exit codes

CodeMeaning
0OK
1Other error
2Connect error
3Script execution resulted in an error. Execution stopped
4Script execution resulted in errors
5Script execution failed unexpectedly

Generating a command from SQL Commander

From time to time, you may want to use some SQL that you have tested in the GUI and run it in the command-line interface. You can generate the string with all parameters and paste that string on the command line or a script. Use menu option SQL Commander → Generate Command for dbviscmd to open a dialog where you can specify the options. Some options are selected and some are disabled depending on the editor contents.

If you generate a command from an anonymous script (a script that has not been saved to a file) with more than one line, you will be prompted to save the file or generate a temporary one. If you generate the command from a file that is modified, you will be prompted to save the file before generating the command.

In addition to the options, there are a few settings in the dialog:

  • You can use a predefined (named) or an anonymous connection; this will enable and disable connection options accordingly.
  • You can choose whether or not to Include SQL Commander Options; this is a convenient way to enable or disable all corresponding options. You can still enable or disable individual options as desired.
  • You can choose whether or not to Include Output Options; this is a convenient way to enable or disable all corresponding options. You can still enable or disable individual options as desired.

The Generate Command for dbviscmd dialog

All options

This is the help text that dbviscmd -help shows:

Usage: dbviscmd     -connection <name> |
                    -url <URL> -drivername <name> |
                    -url <URL> -driverclass <name> -driverpath <p1:p2...> )
                   [-userid <userid>]
                   [-password <password> | -passwordfile <filename> |
                    -passwordenv <variable>]
                   [-createconnection]
                   [-modifyconnection]
                   [-masterpw <password> | -masterpwfile <filename> |
                    -masterpwenv <variable>]
                   (-sql <statements> |
                    -sqlfile <filename> [-encoding <encoding>])
                   [-catalog <catalog>] [-schema <schema>]
                   [-maxrows <max>] [-maxchars <max>]
                   [-stoponerror] [-stoponsqlwarning] [-stoponnorows]
                   [-stripcomments (true | false)]
                   [-processvariables]
                   [-emptypromptvalue <string>]
                   [-listconnections]
                   [-output (all | none | log | result)] [-outputfile <filename>]
                   [-debug [-debugfile <filename>]]
                   [-errordir <directory>]
                   [-workspace <directory>]
                   [-reconnectpolicy (reconnectandexecute | noreconnect | reconnect)]
                   [-help] [-version]

Options:
 -connection <name>                  Database connection name
 -url <URL>                          Database URL
 -drivername <name>                  Database driver name
 -driverclass <name>                 Full name of the JDBC driver class name
 -driverpath <p1:p2...>              Paths to the jar files constituting the JDBC driver.
                                     Each path separated by a ":"
 -userid <userid>                    Userid to connect as
 -password <password>                Password for userid
 -passwordfile <filename>            Read the password for userid from the first line of a file
 -passwordenv <variable>             Read the password for userid from an environment variable
 -createconnection                   Creates a new database connection in the workspace using the specified parameters.
                                     Required options:
                                     -connection <name>       Name of the connection to be created.
                                     -url <URL>               JDBC URL for the target database.
                                     -drivername <name>       (OR)
                                     -driverclass <name> -driverpath <path1[:path2...]>  JDBC driver details for the database.
                                     -userid <userid>         Database userid for the connection.
                                     -password <password>     Database password, if needed. Or use -passwordfile or -passwordenv.
                                     The command connects to the specified database after creating the connection to validate credentials.
                                     Example:
                                     -connection "H2 1.x Embedded with password" -createconnection -url "jdbc:h2:/path/to/db" -drivername "H2 1.x Embedded" -userid "sa" -password "secret"
 -modifyconnection                   Modifies an existing database connection by updating its user ID and/or password.
                                     Required options:
                                     -connection <name>       Name of the existing connection to modify.
                                     Optional options:
                                     -userid <userid>         New userid for the connection.
                                     -password <password>     New password for the connection.
                                     At least one of -userid, -password, -passwordfile or -passwordenv must be provided.
                                     Example:
                                     -connection "MyDatabase" -modifyconnection -userid "newuser" -password "newpass"
 -masterpw <password>                Master Password for encrypted database passwords.
                                     If Touch ID or Windows Hello protects them, the recovery code
 -masterpwfile <filename>            Read the Master Password from the first line of a file
 -masterpwenv <variable>             Read the Master Password from an environment variable
 -sql <statements>                   One or more delimited SQL statements
 -sqlfile <filename>                 SQL script file to execute
 -encoding <encoding>                Encoding for the SQL script file
 -catalog <catalog>                  Catalog to use for unqualified identifiers
 -schema <schema>                    Schema to use for unqualified identifiers
 -maxrows <max>                      Maximum number of rows to display for a result set
 -maxchars <max>                     Maximum number of characters to display for a column
 -reconnectpolicy <reconnect policy> Specify how to handle connection lost when executing scripts
 -stoponerror                        Stop execution when getting an error
 -stoponsqlwarning                   Stop execution when getting an SQL warning
 -stoponnorows                       Stop execution when an empty result set or no affected rows
 -stripcomments <true/false>         Strip comments before sending to database.
                                     Default is the setting made in the GUI
 -processvariables                   Process variables
 -emptypromptvalue <string>          String to use when entering an empty value when
                                     prompted for variable(s)
 -listconnections                    Lists all database connections
 -output <out>                       "all" (default), output both log messages and result sets
                                     "none", suppress both log messages and result sets
                                     "log", output only log messages
                                     "result", output only result sets
 -outputfile <filename>              Script execution output file. Default is stdout
 -debug                              Write debug messages
 -debugfile <filename>               File for debug messages. Default is stderr
 -errordir <directory>               Use an alternate location for error logs
 -workspace <directory>              Load preferences from an alternate workspace
 -help                               Display this help
 -version                            Show version info