When running SQL Workbench/J in batch mode, you can define the connection using a profile name or specifying the connection properties directly .

The script that should be run is specified with the parameter -script=<filename> Multiple scripts can be specified by separating them with a comma. The scripts will then be executed in the order in which they appear in the commandline. If the filenames contain spaces or dashes (i.e. test-1.sql) the names have to be quoted.

You can also execute several scripts by using the WbInclude command inside a script.

If you do not want to create an extra SQL script just to run one or more short SQL commands, you can specify the commands to be executed directly with the -command parameter. To specifiy more than on SQL statement use the standard delimiter to delimit them, e.g. -command='delete from person; commit;'

If a script has been specified using the -script parameter, the -command parameter is ignored.

When using Linux (or Unix-Based operating systems) the command can also be passed using a "Here Document". In this case the -command parameter has to be used without a value:

$ java -jar sqlworkbench.jar -profile=PostgresProduction -command <<SQLCMD
insert into some_table values (42);
delete from other_table where id = 42;
commit;
SQLCMD

The position of the -command parameter does not matter. The following will also work:

$ java -jar sqlworkbench.jar \
     -profile=PostgresProduction \
     -command \
     -displayResult=true \
     -showTiming=true <<SQLCMD
select *
from person;
SQLCMD

If your script files use a non-standard delimiter for the statements, you can specify an alternate delimiter through the profile or through the -altDelimiter parameter. The alternate delimiter should be used if you have several scripts that use the regular semicolon and the alternate delimiter. If your scripts exceed a certain size, they won't be processed in memory and detecting the alternate delimiter does not work in that case. If this is the case you can use the -delimiter switch to change the default delimiter for all scripts. The usage of the alternate delimiter will be disabled if this parameter is specified.

In case your script files are not using the default encoding, you can specify the encoding of your script files with the -encoding parameter. Note that this will set for all script files passed on the command line. If you need to run several script files with different encodings, you have to create one "master" file, which calls the individual files using the WbInclude command together with its -encoding parameter.

If you don't want to write the messages to the default logfile which is defined in workbench.settings an alternate logfile can be specified with -logfile

To control the behavior when errors occur during script execution, you can use the parameter -abortOnError=[true|false]. If any error occurs, and -abortOnError is true, script processing is completely stopped (i.e. SQL Workbench/J will be stopped). The only script which will be executed after that point is the script specified with the parameter -cleanupError.

If -abortOnError is false all statements in all scripts are executed regardless of any errors. As no error information is evaluated the script specified in -cleanupSuccess will be executed at the end.

If this parameter is not supplied it defaults to true, meaning that the script will be aborted when an error occurs.

If a script resulted in an error from the database, the last error message, error code and "SQL state" will be available in the special variables:

Those variables can also be used for conditional aggregation in subsequent commands.

You can also specify whether errors from DROP commands should be ignored. To enable this, pass the parameter -ignoreDropErrors=true on the command line. This works when connecting through a profile or through a full connection specification. If this parameter is set to true only a warning will be issued, but any error reported from the DBMS when executing a DROP command will be ignored.

Note that this will not always have the desired effect. When using e.g. PostgreSQL with autocommit off, the current transaction will be aborted by PostgreSQL until a COMMIT or ROLLBACK is issued. So even if the error during the DROP is ignored, subsequent statements will fail nevertheless.

The script specified with the parameter -cleanupSuccess=<filename> is executed as the last script if either no error occurred or AbortOnError is set to false.

If you update data in the database, this script usually contains a COMMIT command to make all changes permanent.

If the filename is specified as a relative file, it is assumed to be in the current working directory.

The script specified with the parameter -cleanupError=<filename> is executed as the last script if AbortOnError is set to true and an error occurred during script execution.

The failure script usually contains a ROLLBACK command to undo any changes to the database in case an error occured.

If the filename is specified as a relative file, it is assumed to be in the current working directory.

When connecting without a profile, you can use the switch -ignoreDropErrors=[true|false] to ignore errors that are reported from DROP statements. This has the same effect as connecting with a profile where the Ignore DROP errors property is enabled.

You can change the current connection inside a script using the command WbConnect.

Any output generated by SQL Workbench/J during batch execution is sent to the standard output (stdout, System.out) and can be redirected if desired.

SELECT executed successfully

By default neither parameter prompts nor execution confirmations ("Confirm Updates") are processed when running in batch mode. If you have batch scripts that contain parameter prompts and you want to enter values for the parameters while running the batch file, you have to start SQL Workbench/J using the parameter -interactive=true.

The definition of variables can be read from a properties file, either by specifying -file=filename for the WbVarDef command, or by passing the -varFile or -variable parameter when starting SQL Workbench/J. Please see the description for the command line parameters for details.

When running SQL Workbench/J in batch mode, with no workbench.settings file, you can set any property by passing the property as a system property when starting the JVM. To change the loglevel to DEBUG you need to pass -Dworkbench.log.level=DEBUG when starting the application:

java -Dworkbench.log.level=DEBUG -jar sqlworkbench.jar 

	For readability the examples in this section are displayed on several lines. If you enter them manually on the command line you will need to put everything in one line, or use the escape character for your operating system to extend a single command over more then one input line.

Connect to the database without specifying a connection profile:

java -jar sqlworkbench.jar -url=jdbc:postgresql:/dbserver/mydb
     -driver=org.postgresql.Driver
     -username=zaphod
     -password=vogsphere
     -driverjar=C:/Programme/pgsql/pg73jdbc3.jar
     -script='test-script.sql'

This will start SQL Workbench/J, connect to the database server as specified in the connection parameters and execute the script test-script.sql. As the script's filename contains a dash, it has to be quoted. This is also necessary when the filename contains spaces.

Executing several scripts with a cleanup and failure script:

java -jar sqlworkbench.jar
     -script='c:/scripts/script-1.sql','c:/scripts/script-2.sql',c:/scripts/script3.sql
     -profile=PostgreSQL
     -abortOnError=false
     -cleanupSuccess=commit.sql
     -cleanupError=rollback.sql

Note that you need to quote each file individually (where it's needed) and not the value for the -script parameter

Run a SQL command in batch mode without using a script file

The following example exports the table "person" without using the -script parameter:

java -jar sqlworkbench.jar
     -profile='TestData'
     -command='WbExport -file=person.txt -type=text -sourceTable=person'

The following example shows how to run two different SQL statements without using the -script parameter:

java -jar sqlworkbench.jar
     -profile='TestData'
     -command='delete from person; commit;'