2. Installing and starting SQL Workbench/J

2.1. Starting the program
2.2. Displaying help
2.3. Increasing the memory available to the application
2.4. Display Problems, Crashes, Bluescreens when running under Windows®
2.5. High CPU usage when executing statements
2.6. Using the Windows launcher
2.7. Command line parameters

[Note]
Starting with build 99 the jar file has been renamed to sqlworkbench.jar. Please delete the old file Workbench.jar. Windows user should also delete the old JWorkbench.exe and use the most recent SQLWorkbench.exe

To run SQL Workbench/J a Java runtime environment version 1.5 (or above) is required. You can either use a JRE ("Runtime") or a JDK ("Development Kit") to run SQL Workbench/J. Once you have downloaded the application, unzip the SQL Workbench/J archive into a directory of your choice. Apart from that, no special installation procedure is needed. When upgrading to a newer version of SQL Workbench/J simply overwrite the old sqlworkbench.jar and the exe launcher and shell scripts that start the application.

2.1. Starting the program

sqlworkbench.jar is a self executing JAR file. This means, that if your JDK is installed properly, a double click (on the Windows® platform) on sqlworkbench.jar will execute the application. To run the application manually use the command:

java -jar sqlworkbench.jar

If you want to create a shortcut on your desktop use the above line. On Windows® systems - if you don't use the native launcher- it is recommended to use javaw instead of java, so that the console window is not displayed.

javaw -jar sqlworkbench.jar

2.1.1. Specifying the location of configuration files

If no configuration directory has been specified on the commandline, SQL Workbench/J will check the current directory for a workbench.settings file. If no file is present in the current directory, SQL Workbench/J will check the directory where sqlworkbench.jar is located. If no workbench.settings is found in that directory, SQL Workbench/J creates a sub-directory called .sqlworkbench in the user's home folder (This is $HOME on a Linux or Unix based system, or %HOMEPATH% on a Windows system.

[Note]
Before Build 98 the default configuration directory was the program's directory and not a directory in the user's home directory.

The configuration files are:

  • General configuration settings (workbench.settings)
  • Connection profiles (WbProfiles.xml)
  • JDBC Driver definitions (WbDrivers.xml)
  • Customized shortcut definitions (WbShortcuts.xml)
  • Macro definitions (WbMacros.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.

If you want to control the location where 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.

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 filename of the workspace file is usually stored with a placeholder for the configuration directory (%configDir%) so that the profiles don't need to be adjusted.

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.

2.2. Displaying help

You have two options to display help for SQL Workbench/J. Either the built-in help file, which is accessible through HelpContents or the PDF manual by selecting HelpManual. In order to be able to display the PDF manual, you need to define the path to the executable for the PDF reader in the General options and the file SQLWorkbench-Manual.pdf must be available in the directory where sqlworkbench.jar is located.

2.3. Increasing the memory available to the application

SQL Workbench/J is a Java application and thus runs inside a so-called virtual machine (JVM). The virtual machine limits the memory 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).

When you use the Windows® Launcher to start SQL Workbench/J you need to pass the parameter -J-Xmx512m to the executable:

SQLWorkbench.exe -J-Xmx512m
This example will increase the maximum memory to 512MB. The recommended way is to create Windows® shortcut (e.g. on the desktop) and add the above parameter to the shortcut definition. The launcher sets the available heap size for SQL Workbench/J to 256MB.

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

The default heap size for your Java environment depends on your operating system and your JDK implementation. Most JDKs use a default of 96MB as the default maximum heap size.

[Note]

The -Xmx parameter increases the maximum memory to the given value. This does not mean that the application will use that much memory

2.4. Display Problems, Crashes, Bluescreens when running under Windows®

If you experience problems when running SQL Workbench/J (or other Java/Swing based applications) on the Windows® platform, this might be due to problems with the graphics driver and/or the DirectDraw installation. If upgrading the graphics driver or the DirectDraw/DirectX version is not an option (or does not solve the problem), try to run SQL Workbench with the direct draw feature turned off:

java -Dsun.java2d.noddraw=true -jar sqlworkbench.jar

When using the exe launcher, you can use the following syntax:

SQLWorkbench -noddraw

If you run SQL Workbench/J through a program that enables remote access to a Windows® workstations (PC-Duo, VNC, NetMeeting, etc), you may need to disable the use of DirectDraw for Java as well.

2.5. High CPU usage when executing statements

If you experience a high CPU usage when running a SQL statement, this might be caused by a combination of the graphics driver, the JDK and the Windows® version you are using. This is usually caused by the animated icon which indicates a running statement (the yellow smiley). This animation can be turned off in ToolsOptions See Enable animated icons for details. A different icon (not animated) will be used if that option is disabled.

2.6. Using the Windows launcher

On the Windows® platform the supplied SQLWorkbench.exe can be used to start the program when using a Sun JDK. The native launcher searches for an installed JDK (querying the registry) and then starts the SQL Workbench/J. The file sqlworkbench.jar has to be located in the same directory as the SQLWorkbench.exe, otherwise it doesn't work.

[Note]

The launcher only works with JDK's from Sun, as it directly calls the JDK dll's to start the virtual machine. If you are using a different JDK, you cannot use the launcher to start SQL Workbench/J (unless it uses the same directory layout and filenames as the Sun JDK).

By default the launcher increases the maximum JVM heap size to 128 MB. If you need more heap memory, you need to pass the appropriate JVM parameter to the launcher.

2.6.1. How the Windows launcher searches for a Sun JDK

First the launcher checks for a system variable WORKBENCH_JDK. If that is defined, the JDK specified by that directory is used. If WORKBENCH_JDK is not found, JAVA_HOME is used. If JAVA_HOME is not defined, then the launcher checks if a sub-directory JRE exists in the folder where SQLWorkbench.exe is located. If that sub-directory exists, it is assumed that it contains a valid JRE. If the sub-directory does not exist, or if it is not a JRE installation, then the registry key HKLM\Software\JavaSoft\Java Runtime Environment is queried. If that is not defined, HKLM\Software\JavaSoft\Java Development Kit is queried.

In the registry key, a subkey for the version 1.5 is retrieved, and the directory specified by that key is used as the base JDK directory.

If your JDK/JRE installation cannot be found by the launcher, but you do have a JDK available, you can specify the location of the JDK with the -jdk parameter

The launcher assumes the layout of the Sun jdk in the specified directory. If you specify c:\jdk as the JDK directory, the launcher looks for the file c:\jdk\bin\client\jvm.dll (the specified directory would actually be a JRE then). If that is not found, it looks for c:\jdk\jre\bin\client\jvm.dll (that would be a "true" JRE installation). If the -sever parameter is specified, it will look for a sub-directory server instead of client. If your non-Sun JDK/JRE follows the same directory layout and filename conventions, you can use the launcher for that JDK as well.

2.6.2. Parameters for the Windows launcher

To distinguish parameters for the launcher and parameters to the JVM, JVM parameter need to be prefixed with -J. If you want to pass the parameter -Xmx256m to the JVM, pass the parameter -J-Xmx256m to the launcher. To define a system property you need to pass the parameter -J-Dproperty.name=property_value.

The following parameters are recognized:

ParameterDescription
-jdkSpecify the installation directory of the JDK e.g.: -jdk=c:\jdk1.5 When this parameter is specified the launcher will not look for a JDK installation as described here
-JPass a parameter to the JVM e.g: -J-Xms128m
-serverSelect the server JVM (instead of the default client JVM). This switch only works with the Sun JVM.
-clientSelect the client JVM. This switch only works with the Sun JVM.
-jvmtypeSelect the JVM type to be loaded. For the Sun JVM this may be either client or server (equivalent to the -server or -client switches). If the JDK identified with the -jdk switch points to BEA's JRockit JVM, this should be jrockit (i.e. -jvmtype=jrockit). Basically the value of this switch is used to locate the jvm.dll in the base directory specified with the -jdk switch.
-noddrawDisable the use of DirectDraw routines for the JVM. Use this parameter when you are running SQL Workbench/J through PC-Duo or a similar program, or if you are experiencing crashes when starting SQL Workbench/J
-debugWrite debug information to the file workbench.dbg to identify problems when using the launcher
-helpDisplay a message with the list of parameters

All other parameters are passed unchanged to the program. See command line parameters for details.

The following call to the launcher:

SQLWorkbench -noddraw -configDir=c:\MyConf

is the same as directly starting sqlworkbench.jar with these parameters:

java -Dsun.java2d.noddraw=true -jar sqlworkbench.jar -configDir=c:\MyConf

2.6.3. Windows Vista

With Windows Vista, Microsoft changed the way needed DLLs are searched when an executable is loaded. This affects the SQL Workbench/J launcher due to the (new?) Microsoft C runtime distribution model. If you want to run SQL Workbench/J under Windows Vista, please copy the file msvcr71.dll into the directory where SQLWorkbench.exe is located.

This file can be found at %SystemRoot%\System32\msvcr71.dll (usually this is c:\Windows\System32\msvcr71.dll)

Thanks to Jon for this tip.

2.7. 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.

2.7.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.

java -jar sqlworkbench.jar -configDir=${user.home}/wbconfig
SQLWorkbench -configDir='c:\Configurations\SQLWorkbench'

Note that even on the Windows platform you can use a forward slash to separate directory names in the parameter.

2.7.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.

2.7.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.

2.7.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

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

2.7.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]

Note that if this parameter is supplied, the workspace will not be saved automatically as well!

2.7.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='Oracle - 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='Oracle - Test' 
     -profilegroup='Local' 
     -script='test.sql'

2.7.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.
-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.
-emptyStringIsNullThis parameter corresponds to the Empty String is NULL setting of the connection profile. This will only be used in GUI mode.
-removeCommentsThis parameter corresponds to the Remove comments setting of the connection profile.
-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.
-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.
-trimCharData Turns on right-trimming of values retrieved from CHAR columns. See the description of the profile properties for details.
-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.