4. Installing and starting SQL Workbench/J

4.1. Pre-requisites
4.2. First time installation
4.3. Upgrade installation
4.4. Starting the program from the commandline
4.5. Starting the program using the shell script
4.6. Starting the program using the Windows® launcher
4.7. Configuration directory
4.8. Increasing the memory available to the application
4.9. Command line parameters

4.1. Pre-requisites

To run SQL Workbench/J a Java 7 runtime environment or higher is required. You can either use a JRE ("Runtime") or a JDK ("Development Kit") to run SQL Workbench/J.

A Java 6 runtime will work as well, but because Java 6 is no longer supported it is not recommended to use it.

SQL Workbench/J does not need a "fully installed" runtime environment, you can also copy the jre directory from an existing Java installation or use the no-installer packages from the Oracle homepage

The "local" Java installation in the jre subdirectory will not be used by the Windows® launcher if a Java runtime has been installed and is registered in the system (i.e. the Windows® registry)

If you cannot (or do not want to) do a regular installation of a Java 7 runtime, you can download a ZIP distribution from Oracle's homepage Under "JRE Download" you can download tar.gz archives for Windows® and Linux (32bit and 64bit versions are available).

The archive just needs to be unpacked. Inside the archive the actual JRE is stored in a directory named e.g. jre1.7.0_xx where xx is the build number of the Java runtime. When moving this directory to the installation directory of SQL Workbench/J you have to rename it to jre in order for the Windows® launcher or the batch files to recognize it.

4.2. First time installation

Once you have downloaded the application's distribution package, unzip the archive into a directory of your choice. Apart from that, no special installation procedure is needed.

You will need to configure the necessary JDBC driver(s) for your database before you can connect to a database. Please refer to the chapter JDBC Drivers for details on how to make the JDBC driver available to SQL Workbench/J

When starting SQL Workbench/J for the first time, it will create a directory called .sqlworkbench in the current user's home folder to store all its configuration information.

The "user's home directory" is $HOME on a Linux or Unix based system, and %HOMEPATH% on a Windows® system. (Technically speaking it is using the contents of Java system property user.home to find the user's home directory)

4.3. Upgrade installation

When upgrading to a newer version of SQL Workbench/J simply overwrite the old sqlworkbench.jar, the exe files and shell scripts that start the application. If you are using the bundle that includes the libraries for reading and writing OpenOffice and Microsoft Office files, replace all existing jar files with those from the distribution archive as well.

4.4. Starting the program from the commandline

sqlworkbench.jar is a self executing JAR file. This means, that if your Java runtime is installed and registered with the system, a double click on sqlworkbench.jar will execute the application. To run the application manually use the command:

java -jar sqlworkbench.jar

Native executables for Windows® and Mac OSX are supplied that start SQL Workbench/J by using the default Java runtime installed on your system. Details on using the Windows® launcher can be found here.

4.5. Starting the program using the shell script

To run SQL Workbench/J under an Unix-type operating system, the supplied shell script sqlworkbench.sh can be used. For Linux desktops a sample ".desktop" file is available.

4.5.1. Specifying the Java runtime for the shell script

The shell scripts (and the batch files) first check if a Java runtime is available in the sub-directory jre. If that is available it will be used.

If no "local" Java runtime is found, the environment variable WORKBENCH_JDK is checked. If that variable is defined and points to a Java runtime installation, the shell script will use $WORKBENCH_JDK/bin/java to run the application.

If WORKBENCH_JDK is not defined, the shell script will check for the environment variable JAVA_HOME. If that is defined, the script will use $JAVA_HOME/bin/java to run the application.

If neither WORKBENCH_JDK nor JAVA_HOME is defined, the shell script will simply use java to start the application, assuming that a valid Java runtime is available on the path.

All parameters that are passed to the shell scripts are passed to the application, not to the Java runtime. If you want to change the memory or other system settings for the JVM, you need to edit the shell script.

4.6. Starting the program using the Windows® launcher

To start SQL Workbench/J on the Windows® platform, the supplied SQLWorkbench.exe (32bit Windows) or SQLWorkbench64.exe (64bit Windows) can be used to start the program when using an installed Oracle Java runtime. The file sqlworkbench.jar has to be located in the same directory as the exe files, otherwise it does not work.

SQL Workbench/J does not need a "fully installed" runtime environment, you can also copy the jre directory from an existing Java installation. Note that the "local" Java installation in the jre subdirectory will not be used by the Windows® launcher if a Java runtime has been installed and registered in the system.

If you cannot (or don't want to) do a regular installation of a Java 7 runtime, you can download a ZIP distribution for Windows® from Oracle's homepage: http://www.oracle.com/technetwork/java/javase/downloads/index.html. Under "JRE Download" there is also an option to download a no-installer version. These downloads are offered as tar.gz archives and they only need to be unpacked with a packer being able to handle the Unix tar format (e.g. 7-Zip or TotalCommander).

By default memory available to SQL Workbench/J is determined by the Java runtime. If you need more heap memory (e.g. because you have to load large result sets), you need to pass the appropriate JVM parameter to the launcher. Please refer to Increasing the memory for details on how to increase the memory that is available to SQL Workbench/J

4.6.1. Parameters for the Windows® launcher

The launcher executables are based on WinRun4J, further documentation on the format of the configuration file and parameters can also be found there.

If the launcher cannot find your installed Java runtime, you can specify the location of the JRE by creating a text file named SQLWorkbench.ini (or SQLWorkbench64.ini when using the 64bit version) with the following content:

vm.location=c:\Program Files\Java\jdk16\jre\bin\client\jvm.dll

Note that you need to specify the full path to the jvm.dll, not the directory where the Java runtime is installed.

4.7. Configuration directory

The configuration directory is the directory where all config (workbench.settings, WbProfiles.xml, WbDrivers.xml) files are stored.

If no configuration directory has been specified on the commandline, SQL Workbench/J will identify the configuration directory by looking at the following places

  1. The current directory
  2. The directory where sqlworkbench.jar is located
  3. In the user's home direcotry (e.g. $HOME/.sqlworkbench on Unix based systems or %HOMEPATH%\.sqlworkbench on Windows® systems)

If the file workbench.settings is found in one of those directories, that directory is considered the configuration directory.

If no configuration directory can be identified, it will be created in the user's home directory (as .sqlworkbench).

The above mentioned search can be overridden by supplying the configuration directory on the commandline when starting the application.

The following files are stored in the configuration directory:

  • General configuration settings (workbench.settings)
  • Connection profiles (WbProfiles.xml)
  • JDBC Driver definitions (WbDrivers.xml)
  • Customized shortcut definitions (WbShortcuts.xml). If you did not customize any of the shortcuts, this file does not exist
  • Macro definitions (WbMacros.xml)
  • Saved column orders from the DbExplorer(WbColumnOrder.xml)
  • Log file (workbench.log)
  • Workspace files (*.wksp)

If you want to use a different file for the connection profile than WbProfiles.xml then you can specify the location of the profiles with the -profilestorage parameter on the commandline. Thus you can create different shortcuts on your desktop pointing to different sets of profiles. The different shortcuts can still use the same main configuration file.

4.7.1. Copying an installation

To copy an installation to a different computer, simply copy all the above files to the other computer (the log file does not need to be copied). When a profile is connected to a workspace, the workspace file should be specified without a directory name (or using the %ConfigDir% placeholder). In that case it is always loaded from the configuration directory. If the workspace file location is given with an absolute directory, this needs to be adjusted after the copying the files.

You will need to edit the driver definitions (stored in WbDrivers.xml) as the full path to the driver's jar file(s) is stored in the file (unless you define the location of the drivers using the libdir variable.

4.8. Increasing the memory available to the application

SQL Workbench/J is a Java application and thus runs inside a virtual machine (JVM). The virtual machine limits the memory of the application independently from the installed memory that is available to the operating system.

SQL Workbench/J reads the data that is returned by a SELECT statement into the main memory. When retrieving large result sets, you might get an error message, indicating that not enough memory is available. In this case you need to increase the memory that the JVM requests from the operating system (or change your statement to return fewer rows).

To increase the memory that is available to the application, you can pass the parameter -Xmx to the Windows® launcher, specifying the maximum memory the application should use. To increase the memory to 1GB, you can use: -Xmx1024m. This can e.g. be added to the shortcut you use to start SQL Workbench/J

As an alternative you can create a configuration file named SQLWorkbench.ini (or SQLWorkbench64.ini when using the 64bit launcher) with the following content:

vm.heapsize.preferred=1024

This will increase the memory for the application to 512MB. For more options to configure the JVM, please refer to the documentation of WinRun4J. The configuration file has to be located in the same directory where the .exe is located.

If you are running SQL Workbench/J on a non-Windows® operating system or do not want to use the launcher, then you need to pass this parameter directly to the JVM

java -Xmx512m -jar sqlworkbench.jar

If you are using the supplied shell scripts to start SQL Workbench/J, you can edit the scripts and change the value for the -Xmx parameter in there.

4.9. Command line parameters

Command line parameters are not case sensitive. The parameters -PROFILE or -profile are identical. The usage of the command line parameters is identical between the launcher or starting SQL Workbench/J using the java command itself.

[Note]

When quoting parameters on the commandline (especially in a Windows® environment) you have to use single quotes, as the double quotes won't be passed to the application.

4.9.1. Specify the directory for configuration settings

The parameter -configDir specifies the directory where SQL Workbench/J will store all its settings. If this parameter is not supplied, the directory where the default location is used. The placeholder ${user.home} will be replaced with the current user's home directory (as returned by the Operating System). If the specified directory does not exist, it will be created.

If you want to control the location where SQL Workbench/J stores the configuration files, you have to start the application with the parameter -configDir to specify an alternate directory:

java -jar sqlworkbench.jar -configDir=/export/configs/SQLWorkbench

or if you are using the Windows® launcher:

SQLWorkbench -configDir=c:\ConfigData\SQLWorkbench

The placeholder ${user.home} will be replaced with the current user's home directory (as returned by the Operating System), e.g.:

java -jar sqlworkbench.jar -configDir=${user.home}/.sqlworkbench

If the specified directory does not exist, it will be created.

On the Windows® platform you can use a forward slash to separate directory names in the parameter.

4.9.2. Specify a base directory for JDBC driver libraries

The -libdir parameter defines the base directory for your JDBC drivers. The value of this parameter can be referenced when defining a driver library using the placholder %LibDir% The value for this parameter can also be set in the file workbench.settings.

4.9.3. Specify the file containing connection profiles

SQL Workbench/J stores the connection profiles in a file called WbProfiles.xml. If you want to use a different filename, or use different set of profiles for different purposes you can define the file where the profiles are stored with the -profilestorage parameter.

If the value of the parameter does not contain a path, the file will be expected (and stored) in the configuration directory.

4.9.4. Defining variables

With the -vardef parameter a definition file for internal variables can be specified. Each variable has to be listed on a single line in the format variable=value. Lines starting with a # character are ignored (comments). the file can contain unicode sequences (e.g. \u00fc. Values spanning multiple lines are not supported. When reading a file during startup the default encoding is used. If you need to read the file in a specific encoding please use the WbVarDef command with the -file and -encoding parameter.

#Define some values
var_id=42
person_name=Dent
another_variable=24

If the above file was saved under the name vars.txt, you can use those variables by starting SQL Workbench/J using the following commandline:

java -jar sqlworkbench.jar -vardef=vars.txt

You can also define a list of variables with this parameter. In this case, the first character after the = sign, has to be # (hash sign) to flag the value as a variable list:

java -jar sqlworkbench.jar -vardef=#var_id=42,person_name=Dent

The -vardef parameter can be used multiple times on the commandline.

Defining variable values in this way can also be used when running in batch mode.

4.9.5. Prevent updating the .settings file

If the -nosettings parameter is specified, SQL Workbench/J will not write its settings to the file workbench.settings when it's beeing closed. Note that in batch mode, this file is never written.

[Note]

If this parameter is supplied, the workspace will not be saved automatically as well!

4.9.6. Connect using a pre-defined connection profile

You can specify the name of an already created connection profile on the commandline with the -profile=<profile name> parameter. The name has to be passed exactly like it appears in the profile dialog (case sensitiv!). If the name contains spaces or dashes, it has to be enclosed in quotations marks. If you have more than one profile with the same name but in different profile groups, you have to specify the desired profile group using the -profilegroup parameter, otherwise the first profile matching the passed name will be selected.

Example (on one line):

java -jar sqlworkbench.jar
     -profile='PostgreSQL - Test'
     -script='test.sql'

In this case the file WbProfiles.xml must be in the current (working) directory of the application. If this is not the case, please specify the location of the profile using either the -profilestorage or -configDir parameter.

If you have two profiles with the names "Oracle - Test" you will need to specify the profile group as well (in one line):

java -jar sqlworkbench.jar
     -profile='PostgreSQL - Test'
     -profilegroup='Local'
     -script='test.sql'

4.9.7. Connect without a profile

You can also specify the full connection parameters on the commandline, if you don't want to create a profile only for executing a batch file. The advantage of this method is, that SQL Workbench/J does not need the files WbProfiles.xml, WbDrivers.xml to be able to connect to the database.

The connection can be specified with the following parameters:

ParameterDescription
-urlThe JDBC connection URL
-usernameSpecify the username for the DBMS
-passwordSpecify the password for the user
-driverSpecify the full class name of the JDBC driver
-driverJarSpecify the full pathname to the .jar file containing the JDBC driver
-autocommitSet the autocommit property for this connection. You can also control the autocommit mode from within your script by using the SET AUTOCOMMIT command.
-rollbackOnDisconnectIf this parameter is set to true, a ROLLBACK will be sent to the DBMS before the connection is closed. This setting is also available in the connection profile.
-checkUncommitted If this parameter is set to true, SQL Workbench/J will try to detect uncommitted changes in the current transaction when the main window (or an editor panel) is closed. If the DBMS does not support this, this argument is ignored. It also has no effect when running in batch or console mode.
-trimCharData Turns on right-trimming of values retrieved from CHAR columns. See the description of the profile properties for details.
-removeCommentsThis parameter corresponds to the Remove comments setting of the connection profile.
-fetchSizeThis parameter corresponds to the Fetch size setting of the connection profile.
-ignoreDropErrorThis parameter corresponds to the Ignore DROP errors setting of the connection profile.
-emptyStringIsNullThis parameter corresponds to the Empty String is NULL setting of the connection profile. This will only be needed when editing a result set in GUI mode.
-connectionProperties

This parameter can be used to pass extended connection properties if the driver does not support them e.g. in the JDBC URL. The values are passed as key=value pairs, e.g. -connectionProperties=someProp=42

If either a comma or an equal sign occurs in a parameter's value, it must be quoted. This means, when passing multiple properties the whole expression needs to be quoted: -connectionProperties='someProp=42,otherProp=24'.

As an alternative, a colon can be used instead of the equals sign, e.g -connectionProperties=someProp:42,otherProp:24. In this case no quoting is needed (because no delimiter is part of the parameters value).

If any of the property values contain a comma or an equal sign, then the whole parameter value needs to be quoted again, even when using a colon. -connectionProperties='someProp:"answer=42",otherProp:"2,4"' will define the value answer=42 for the property someProp and the value 2,4 for the property otherProp.

-altDelim The alternate delimiter to be used for this connection. To define a single line delimiter append the characters :nl to the parameter value: e.g. -altDelimiter=GO:nl to define a SQL Server like GO as the alternate delimiter. Note that when running in batchmode you can also override the default delimiter by specifying the -delimiter parameter.
-separateConnectionIf this parameter is set to true, and SQL Workbench/J is run in GUI mode, each SQL tab will use it's own connection to the database server. This setting is also available in the connection profile. The default is true.
-connectionName When specifying a connection without a profile (only using -username, -password and so on) then the name of the connection can be defined using this parameter. The connection name will be shown in the title of the main window if SQL Workbench/J is started in GUI mode. The parameter does not have any visible effect when running in batch or console mode.
-workspaceThe workspace file to be loaded. If the file specification does not include a directory, the workspace will be loaded from the configuration directory. If this parameter is not specified, the default workspace (Default.wksp) will be loaded.
-readOnly Puts the connection into read-only mode.

If a value for one of the parameters contains a dash or a space, you will need to quote the parameter value.

A disadvantage of this method is, that the password is displayed in plain text on the command line. If this is used in a batch file, the password will be stored in plain text in the batch file. If you don't want to expose the password, you can use a connection profile and enable password encryption for connection profiles.