Skip to content

Using Client-Side Commands

Only in DbVisualizer Pro

This feature is only available in the DbVisualizer Pro edition.

A client-side command is a DbVisualizer-specific command that starts with @, such as @echo or @export. You use them in SQL scripts, together with SQL statements, to control how the script runs and to do things like exporting results, importing data and sending emails. DbVisualizer runs the commands itself; they are never sent to the database.

You can use client-side commands in SQL Commander and in the command-line interface.

@stop on error;
@set maxrows -1;
@export on;
@export set Filename="/tmp/actor.csv";
SELECT * FROM actor;
@export off;
@echo Export done;

Writing client-side commands

  • Write a client-side command on its own, and end it with the statement delimiter, like an SQL statement. A command can span several lines.
  • Command names and parameter names are case-insensitive.
  • Parameters with values are written as Name="value". To use a double quote in a value, double it: "".
  • You can use DbVisualizer variables, such as ${dbvis-date}$, in client-side commands.
  • If a parameter is unknown or has an invalid value, the command fails, and the Log tab shows the error.
  • In SQL Commander, auto-completion suggests the commands and their parameters.

Controlling the script

CommandDescription
@stop on errorStop the script if a statement fails. See Stopping on errors.
@stop on norowsStop the script if a query returns no rows.
@stop on sqlwarningStop the script if a statement returns an SQL warning.
@continue on errorContinue the script if a statement fails.
@continue on norowsContinue the script if a query returns no rows.
@continue on sqlwarningContinue the script if a statement returns an SQL warning.
@stop nowStop the script immediately.
@set dryrun on, @set dryrun offTurn dry run on or off. See Testing a script with dry run.
@command ignore, @command resumeSkip the client-side commands that follow, until @command resume. SQL statements still run. See Skipping client-side commands.
@sleep <milliseconds>Pause the script, for example @sleep 2000; for two seconds.
@delimiter <delimiter>Change the statement delimiter, for example for a complex statement. Change it back with @delimiter ;. The short form @delim also works.
@run <file> [<variables>]Run the SQL script in a file. See Executing a Script File.
@cd <folder>Set the folder for relative file paths in the commands that follow, such as @run, @export, @import, @mail and @log.

When a script stops because of an error, no rows or an SQL warning, the following client-side commands still run, so that you can, for example, send a mail about the result: @mail, @log <file> spool, @log close, @open <file> RunOnStop and @command. After @stop now, nothing runs.

Transactions

CommandDescription
@set autocommit on, @set autocommit offTurn auto commit on or off.
@commitCommit the current transaction.
@rollbackRoll back the current transaction.

Queries and results

CommandDescription
@set maxrows <number>Set the maximum number of rows to fetch for the queries that follow, instead of the Max Rows field in SQL Commander. Use -1 for all rows. See Setting Max Rows and Max Chars.
@set maxchars <number>Set the maximum number of characters to fetch for text values, instead of the Max Chars field in SQL Commander. Use -1 for no limit.
@set resultset name <name>Name the result tab of the next query, for example @set resultset name MyActors;. See Naming result tabs.
@set serveroutput on, @set serveroutput offTurn output to the DBMS Output tab on or off, for databases that support it. Not supported in the command-line interface.
@describe <table>Show the columns of a table. The table name can be qualified with a schema and database. The short form @desc also works.
@ddl <type>="<name>"Show the DDL for an object, for example @ddl table="SAKILA.ACTOR";. See Getting the DDL of an Object (@ddl).
@call <function_or_procedure>Run a function or procedure. See Executing a Procedure or Function.

Exporting, importing and copying data

CommandDescription
@exportExport the results of queries to files. See Exporting Results (@export).
@importImport data from CSV, Excel, TXT, XML and JSON files. See Importing Data (@import).
@copy table <parameters>Copy a table, including its data, to another connection. See Copying a Table.
@compare schema <parameters>Compare the structure of two schemas, databases or tables, and generate a migration script. See Comparing Schemas.
@snapshot schema <parameters>Save the structure of a schema, database or table to a file, to compare against later. See Comparing Schemas.

Messages, logs and notifications

CommandDescription
@echo <text>Write text to the Log tab. See Writing to the Log Tab.
@log <file>, @log <file> spool, @log closeWrite the log to a file. See Writing the log to a file.
@notify level="<level>" message="<text>"Show a notification, for example when a script that takes a long time has finished. The level is success, info (default), warning or error.
@beepPlay a sound.
@mail <parameters>Send an email, for example with exported results. See Sending Email (@mail).
@open <file_or_url> [RunOnStop]Open a file in its default application, or a URL in the browser. With RunOnStop, it also runs when the script has stopped. Not supported in the command-line interface.
@window iconify, @window restoreMinimize or restore the DbVisualizer window. Not supported in the command-line interface.

Examples

Running several scripts and getting a notification

This script runs two script files from a folder, and shows a notification when they are done. Because of @stop on error, the script stops if a statement fails, and no notification is shown.

@stop on error;
@cd /scripts/nightly;
@run 01_load.sql;
@run 02_transform.sql;
@notify level="success" message="Nightly scripts finished";

Naming result tabs

When a script returns several results, you can give each result tab a name:

@set resultset name Actors;
SELECT * FROM actor;
@set resultset name Films;
SELECT * FROM film;

Skipping client-side commands

To test the SQL in a script without, for example, exporting or sending mail, you can skip the client-side commands using @command ignore. The SQL statements still run:

@echo This is shown;
@command ignore;
@echo This is not shown;
SELECT * FROM actor;
@command resume;
@echo This is also shown;

In this example, the query runs, but the second @echo doesn't. To skip the SQL statements as well, use dry run.

Testing a script with dry run

With dry run, you can check the client-side commands in a script before you run it for real. The parameters of each command are checked, for example that the files to attach using @mail exist, but the commands don't do anything: no files are exported, no mails are sent, and no SQL statements are sent to the database. The Log tab shows each skipped statement and command as Dry Run: Command not run, or an error if a command is wrong.

A few commands still run in dry run, such as @echo and @run, so that you can follow the script and check the scripts it runs. @import checks the data against the table without inserting it. SQL statements are not checked, so errors in them only show up when you run the script without dry run.

@set dryrun on;
@sleep 2000;
SELECT * FROM actor;
@set dryrun off;

In this example, neither the pause nor the query is run.

Writing the log to a file

With @log, you can save the messages from the Log tab to a file:

  • @log <file>; writes the log entries of the commands that follow to the file. If another log file is open, it is closed first.
  • @log close; closes the log file.
  • @log <file> spool; writes all log entries so far to the file, and closes it.
@log /tmp/mylog.txt;
SELECT * FROM actor;
@log close;