14. Using SQL Workbench/J in batch files

14.1. Specifying the connection
14.2. Specifying the script file(s)
14.3. Specifying a SQL command directly
14.4. Specifying a delimiter
14.5. Specifying an encoding for the file(s)
14.6. Specifying a logfile
14.7. Handling errors
14.8. Specify a script to be executed on successful completion
14.9. Specify a script to be executed after an error
14.10. Ignoring errors from DROP statements
14.11. Changing the connection
14.12. Controlling console output during batch execution
14.13. Running batch scripts interactively
14.14. Defining variables
14.15. Setting configuration properties
14.16. Examples

SQL Workbench/J can also be used from batch files to execute SQL scripts. This can be used to e.g. automatically extract data from a database or run other SQL queries or statements.

To start SQL Workbench/J in batch mode, either the -script or -command must be passed as an argument on the commandline.

If neither of these parameters is present, SQL Workbench/J will run in GUI mode.

[Important]

When running SQL Workbench/J on Windows, you either need to use sqlwbconsole or start SQL Workbench/J using the Java command. You cannot use the Windows launcher SQLWorkbench.exe, as it will run in the background without a console window, and thus you will not see any output from the batch run.

Please refer to Starting SQL Workbench/J for details on how to start SQL Workbench/J with the java command.

When you need to quote parameters inside batch or shell scripts, you have to use single quotes ('test-script.sql') to quote these values. Most command line shells (including Windows®) do not pass double quotes to the application and thus the parameters would not be evaluated correctly by SQL Workbench/J

If you want to start the application from within another program (e.g. an Ant script or your own program), you will need to start SQL Workbench/J's main class directly.

java -cp sqlworkbench.jar workbench.WbStarter

Inside an Ant build script this would need to be done like this:

<java classname="workbench.WbStarter" classpath="sqlworkbench.jar" fork="true">
  <arg value="-profile='my profile'"/>
  <arg value="-script=load_data.sql"/>
</java>

The parameters to specifiy the connection and the SQL script to be executed have to be passed on the commandline.

14.1. Specifying the connection

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

14.2. Specifying the script file(s)

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.

14.3. Specifying a SQL command directly

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.

14.4. Specifying a delimiter

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.

14.5. Specifying an encoding for the file(s)

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 commandline. If you need to run several scriptfiles with different encodings, you have to create one "master" file, which calls the individual files using the WbInclude command together with its -encoding parameter.

14.6. Specifying a logfile

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

14.7. Handling errors

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.

You can also specify whether errors from DROP commands should be ignored. To enable this, pass the parameter -ignoreDropErrors=true on the commandline. 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.

14.8. Specify a script to be executed on successful completion

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.

14.9. Specify a script to be executed after an error

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.

14.10. Ignoring errors from DROP statements

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.

14.11. Changing the connection

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

14.12. Controlling console output during batch execution

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.

14.12.1. Displaying result sets

If you are running SELECT statements in your script without "consuming" the data through an WbExport, you can optionally display the results to the console using the parameter -displayResult=true. If this parameter is not passed or set to false, results sets will not be visible (for a SELECT statement you will simply see the message 'SELECT executed successfully'.

14.12.2. Controlling execution feedback

When running statements, SQL Workbench/J reports success or failure of each statement. Inside a SQL script the WbFeedback command can be used to control this feedback. If you don't want to add a WbFeedback command to your scripts, you can control the feedback using the -feedback switch on the command line. Passing -feedback=false has the same effect as putting a WbFeedback off in your script.

As displaying the feedback can be quite some overhead especially when executing thousands of statements in a script file, it is recommended to turn off the result logging using WbFeedback off or -feedback=false

To only log a summary of the script execution (per script file), specify the parameter -consolidateMessages=true. This will then display the number of statements executed, the number of failed statements and the total number of rows affected (updated, deleted or inserted).

When using -feedback=false, informational messages like the total number of statements executed, or a successful connection are not logged either.

14.12.3. Controlling statement progress information

Several commands (like WbExport) show progress information in the statusbar. When running in batch mode, this information is usually not shown. When you specifiy -showProgress=true these messages will also be displayed on the console.

14.13. Running batch scripts interactively

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.

14.14. Defining variables

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

14.15. Setting configuration properties

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 

14.16. Examples

[Note]

For readability the examples in this section are displayed on several lines. If you enter them manually on the commandline 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;'