This document describes build 119 of SQL Workbench/J

Feedback regarding this program is more then welcome. Please report any problems you find, or send your ideas to improve the usability to: <support@sql-workbench.net>

SQL Workbench/J can be downloaded from http://www.sql-workbench.net

If you want to contact other users of SQL Workbench/J you can do this using an online forum at Google Groups: http://groups.google.com/group/sql-workbench

Thanks to Christian (and his team) for his thorough testing, his patience and his continuous ideas to improve this tool. His input has influenced and driven a lot of features and has helped reduce the number of bugs drastically!

"License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document.

"Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License.

"Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity.

"You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License.

"Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files.

"Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types.

"Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below).

"Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof.

"Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution."

"Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work.

Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form.

The right to use this software is explicitely NOT granted to the governments of the following countries or organizations directly related to them:

Members of the above mentioned governments or any of its organizations (especially, but not limited to the so called "intelligence" agencies) are NOT ALLOWED to download or use this software.

Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed.

You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions:

Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions.

This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file.

Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License.

In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages.

While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability.

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

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 home page

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 8 runtime, you can download a ZIP distribution from Oracle's home page. 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.8.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.

Maven central also offers ZIP archives of the Java runtime: http://maven.nuiton.org/nexus/content/repositories/jvm/com/oracle/jre/

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)

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.

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.

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.

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 8 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, so a tool that can handle Unix/Linux that format is needed for unpacking the archive (e.g. TotalCommander or 7-Zip).

When using a 32bit Java runtime the default memory available to the application is set to 1GB. When using a 64bit Java runtime the default is set to 65% of the available physical memory.

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

vm.heapsize.preferred=12000

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

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:

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 command line. 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.

To copy an installation to a different computer, simply copy all the files from the configuration directory 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 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) because the full path to the driver's jar file(s) is stored in the file.

If you store all JDBC drivers in a common directory (or below a common root directory) you can define the libdir variable. In that case the paths to the driver's jar file are stored relative to the %LibDir% directory. After copying the installation you only need to adjust the %LibDir% variable on the other computer.

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 all the data that is returned by a SQL statement into 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 using the Windows launcher (e.g. SQLWorkbench64.exe), the available memory is defined in the INI file.

When using the shell or batch scripts, the available memory is defined through the -Xmx parameter for the java command. In the following example, the parameter -Xmx4g sets the available memory to 4GB

java -Xmx4g -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.

	With a 32bit Java runtime, you can not use (or assign) more than approx. 1.5GB for the application. If you need to process results that require more memory that that, you will have to use a 64bit Java runtime.

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.

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 placeholder %LibDir% The value for this parameter can also be set in the file workbench.settings.

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.

The default XML format of the WbProfiles.xml file is not intended to be edited manually. To manage pre-defined profiles for console mode or batch mode, it's easier to use a properties file containing the profiles.

When specifying a properties file with -profileStorage the file extension must be .properties

You can define variables when starting SQL Workbench/J by either passing the variable definition directly or by passing a file that contains the variable definitions.

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

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

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

java -jar sqlworkbench.jar -variable=foo=42 -variable=bar='xyz'

java -jar sqlworkbench.jar -variable="foo=hello world"

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.

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

You can specify the name of an already created connection profile on the command line with the -profile=<profile name> parameter. The name has to be passed exactly like it appears in the profile dialog (case sensitive!). 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'

You can also store the connection profiles in a properties file and specify this file using the -profileStorage parameter.

You can also specify the full connection parameters on the command line, 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.

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.

Before you can connect to a DBMS you have to configure the JDBC driver to be used. The driver configuration is available in the connection dialog or through File → Manage Drivers

The JDBC driver is a file with the extension .jar (some drivers need more than one file). See the end of this section for a list of download locations. Once you have downloaded the driver you can store the driver's .jar file anywhere you like.

To register a driver with SQL Workbench/J you need to specify the following details:

After you have selected the .jar file(s) for a driver (by clicking on the button), SQL Workbench/J will scan the jar file looking for a JDBC driver. If only a single driver is found, the class name is automatically put into the entry field for the class name. If more than one JDBC driver implementation is found, you will be prompted to select one. In that case, please refer to the manual of your driver or database to choose the correct one.

	SQL Workbench/J is not using the system's CLASSPATH definition (i.e. the environment variable named CLASSPATH) to load the driver classes. Changing the CLASSPATH environment variable to include your driver's library will have no effect.

If you enter the class name of the driver manually, remember that it's case-sensitive: org.postgresql.driver is something different than org.postgresql.Driver

Files that are not found are displayed in red and italics.

The name of the library has to contain the full path to the driver's jar file, so that SQL Workbench/J can find it. Some drivers are distributed in several jar files. In that case, select all necessary files in the file open dialog, or add them one after the other. If an entry is selected in the list of defined jar files when adding a new jar file, the selected entry will be overwritten.

For drivers that require a license file, you have to include the license jar to the list of files for that driver.

If the driver requires files that are not contained in the jar library, you have to include the directory containing those files as part of the library definition (e.g: "c:\etc\TheDriver\jdbcDriver.jar;c:\etc\TheDriver").

You can assign a sample URL to each driver, which will be put into the URL property of the profile, when the driver class is selected.

SQL Workbench/J comes with some sample URLs pre-configured. Some of these sample URLs use brackets to indicate a parameters that need to be replaced with the actual value for your connection: (servername) In this case the entire sequence including the brackets need to be replaced with the actual value.

	The JDBC/ODBC bridge is no longer available in Java 8 and therefor it is not possible to connect through an ODBC data source when using SQL Workbench/J.

When defining the location of the driver's .jar file, you can use the placeholder %LibDir% instead of the using the directory's name directly. This way your WbDrivers.xml is portable across installations. To specify the library directory, either set it in the workbench.settings file, or specify the directory using the -libdir switch when starting the application.

Here is an overview of common JDBC drivers, and the class name that need to be used. SQL Workbench/J contains predefined JDBC drivers with sample URLs for connecting to the database.

Most drivers accept additional configuration parameters either in the URL or through the extended properties. Please consult the manual of your driver for more detailed information on these additional parameters.

SQL Workbench/J uses the concept of profiles to store connection information. A connection profile stores two different types of settings:

After the program is started, you are prompted to choose a connection profile to connect to a database. The dialog will display a list of available profiles on the left side. When selecting a profile, its details (JDBC and SQL Workbench/J settings) are displayed on the right side of the window.

To create a new profile click on the New Profile button (). This will create a new profile with the name "New Profile". The new profile will be created in the currently active group. The other properties will be empty. To create a copy of the currently selected profile click on the Copy Profile button (). The copy will be created in the current group. If you want to place the copy into a different group, you can either choose to Copy & Paste a copy of the profile into that group, or move the copied profile, once it is created.

To delete an existing profile, select the profile in the list and click on the Delete Profile button ()

Profiles can be organized in groups, so you can group them by type (test, integration, production) or customer or database system. When you start SQL Workbench/J for the first time, no groups are created and the tree will only display the default group node. To add a new group click on the Add profile group () button. The new group will be appended at the end of the tree. If you create a new profile, it will be created in the currently selected group. If a profile is selected in the tree and not a group node, the new profile will be created in the group of the currently selected profile.

	Empty groups are discarded (i.e. not saved) when you restart SQL Workbench/J

You can move profiles from one group to another but right clicking on the profile, then choose Cut. Then right-click on the target group and select Paste from the popup menu. If you want to put the profile into a new group that is not yet created, you can choose Paste to new folder. You will be prompted to enter the new group name.

If you choose Copy instead of Cut, a copy of the selected profile will be pasted into the target group. This is similar to copying the currently selected profile.

To rename a group, select the node in the tree, then press the F2 key. You can now edit the group name.

To delete a group, simply remove all profiles from that group. The group will then automatically be removed.

When connecting to a PostgreSQL database it's not necessary to specify username and password. Username and password will then be resolved according to the rules as psql or any other libpq application would do:

JDBC drivers support additional connection properties where you can fine tune the behavior of the driver or enable special features that are not switched on by default. Most drivers support passing properties as part of the URL, but sometimes they need to be passed to the driver using a different method called extended properties.

If you need to pass an additional parameter to your driver you can do that with the Extended Properties button. After clicking that button, a dialog will appear with a table that has two columns. The first column is the name of the property, the second column the value that you want to pass to the driver.

To create a new property click on the new button. A new row will be inserted into the table, where you can define the property. To edit an existing property, simply double click in the table cell that you want to edit. To delete an existing property click on the Delete button ().

Some driver require those properties to be so called "System properties" (see the manual of your driver for details). If this is the case for your driver, check the option Copy to system properties before connecting.

Connecting to Oracle with SYSDBA privilege can be done by checking the option as SYSDBA next to the username. when using this option, you have to use an Oracle user account that is allowed to connect as SYSDBA (e.g. SYS).

The behaviour of the quick filter depends on whether tags are defined or not.

If no tags are defined at all, the quick filter will only search in the profile name. The search is done case-insensitive. Search for prod will match any profile that has PROD or prod anywhere in the profile's name

If tags are defined, the input is first checked if its one or more tags. If that is the case, the profiles are only filtered based on the tags defined. If there is a tag named prod, and the filter value is prod, only profiles with that tag are displayed. The profile name is not taken into account then. If the value in the filter field is not a tag, then the profiles are filtered based on name.

Multiple tags are separated by a comma. To see a list of defined tags, press the Ctrl-Space key.

A workspace is a collection of editor tabs that group scripts or statement together. A workspace stores the name of each editor tab, the cursor position for each editor, the selection and the statement history.

Each connection profile is assigned a workspace. If no workspace is explicitely chosen for a connection profile, a workspace with the name Default is used. If not specified otherwise, workspaces are stored in the configuration directory.

A workspace file has the extension .wksp and is a regular ZIP archive that can be opened with any ZIP tool. It contains one text file for each editor in the workspace and some property files that store additional settings like the divider location, the Max. Rows value or the selected catalog and schema of the DbExplorer.

	It is recommended to use a different workspace for each connection profile.

Workspaces can be used to reduce the number of editor tabs being used. You can create different workspaces for different topics you work on. One workspace that contains queries to monitor a database. One workspace that contains everything related to a specific feature you are working on. One workspace to initialize a new environment and so on.

To create a copy of the current workspace, use Workspace → Save workspace as After saving the workspace, the new workspace becomes the current workspace (the old one will not be changed). You will be asked if the new workspace should be the default profile's workspace, so that if you connect using that connection profile the new workspace will be loaded automatically.

If the new workspace is not made the profile's workspace, the next time you connect using that connection profile, the old workspace file will be loaded.

If you chose not to assign the new workspace right after saving it, you can later assign the currently loaded workspace to be used by the current connection profile using: Workspace → Assign Workspace.

This feature can be used if you have a workspace that contains statements that you want to use for a new topic, but you don't want to lose the original set of statements (that were used for a previous work).

If you want to load an existing workspace e.g. because you want to work on a different topic, you can use Workspace → Load Workspace Again you are asked if you want to use the newly loaded workspace as the default workspace.

Workspaces loaded through this will be put into the Workspace → Recent Workspaces menu so that you can quickly switch between workspaces you often use.

If you have a workspace loaded other than the default workspace of the current connection profile, you can quickly re-load the default workspace through Workspace → Re-Load Profile Workspace If you do that, the current workspace will be saved and the workspace assigned to the current connection profile will be loaded.

By default a workspace "remembers" the external files that were loaded. The content of the loaded file will also be stored in the workspace file. This can be configured in the Options dialog.

You can load and save the editor's content into external files (e.g. for re-using) them in other SQL tools.

To load a file use File → Open... or right click on the tab's label and choose Open... from the popup menu.

The association between an editor tab and the external file will be saved in the workspace that is used for the current connection. When opening the workspace (e.g. by connecting using a profile that is linked to that workspace) the external file will be loaded as well.

	If you want to run very large SQL scripts (e.g. over 15MB) it is recommended to execute them using WbInclude rather than loading them completely into the editor. WbInclude will not load the script into memory, thus you can even run scripts that would not fit into memory.

The editor can show a popup window with a list of available tables (and views) or a list of available columns for a table. Which list is displayed depends on the position of the cursor inside the statement.

If the cursor is located in the column list of a SELECT statement and the FROM part already contains the necessary tables, the window will show the columns available in the table. Assuming you are editing the following statement ( the | indicating the position of the caret):

SELECT p.|, p.firstname, a.zip, a.city
FROM person p
  JOIN address a ON p.id = a.person_id;

then pressing the Ctrl-Space key will show a list of columns available in the PERSON table (because the cursor is located after the p. alias). If you put the cursor after the a.city column and press the Ctrl-Space the popup window will list the two tables that are referenced in the FROM part of the statement. The behavior when editing the WHERE part of an statement is similar.

When editing the list of tables in the FROM part of the statement, pressing the Ctrl-Space will pop up a list of available tables.

If the cursor is located inside the assignment of an UPDATE statement (set foo = |, ) or in the VALUES part of an INSERT statement, the popup will contain an item (Select FK value). When selecting this item the dialog to select a value from a referenced table will be displayed if the current column is referencing another table. For performance reasons the check if the current column is referencing another table is only done after the item has been selected. If no foreign key could be found, a message is displayed in the status bar.

The editor assumes that the standard semicolon is used to separate statements when doing auto-completion or using the "Execute current" function. This can be changed to a non-standard behaviour through the options dialog so that the editor also recognizes empty lines as a statement delimiter.

Parameters for SQL Workbench/J specific commands are also supported by the command completion. The parameters will only be shown, if you have already typed the leading dash, e.g. WbImport -. If you press the shortcut for the command completion while the cursor is located after the dash, a list of available options for the current comand is shown. Once the parameter has been added, you can display a list of possible values for the parameter if the cursor is located after the equals sign. for WbImport -mode= will display a list of allowed values for the -mode parameter. For parameters where table names can be supplied the usual table list will be shown.

When writing (long) INSERT statements it is often helpful to check if a specific value is actually written into the intended column. To check the column a value corresponds to (or the vice versa), press Ctrl-# while in the column or values list. A tool tip will appear to show the corresponding element from the "other" part of the statement. Consider the following statement:

INSERT INTO some_table (column1, column2, column3)
VALUES
('hello', 'world', 42, 'foobar');

When the cursor is located at column1, pressing Ctrl-# will show a tool tip containing the text 'hello' as that is the value that corresponds to column1. When the cursor is located at the number 42 pressing Ctrl-# will show the text column3 in the tool tip.

When no matching column or value can be found, the tool tip will contain a hint that the "other" element is missing.

If the values inserted are the result of a SELECT statement, the tool tip in the insert column list will show the corresponding column name from the SELECT statement.

The keywords that the editor can highlight are based on an internal list of keywords and information obtained from the JDBC driver. You can extend the list of known keywords using text files located in the config directory.

SQL Workbench/J reads four different types of keywords: regular keywords (e.g. SELECT), data types (e.g. VARCHAR), functions (e.g. upper()) and operators (e.g. JOIN). Each keyword type is read from a separate file: keywords.wb, datatypes.wb, functions.wb and operators.wb.

The files contain one keyword per line. Case does not matter (SELECT and select are treated identically). If you want to add a specific word to the list of global keywords, simply create a plain text file keywords.wb in the config directory and put one keyword per line into the file, e.g:

ALIAS
ADD
ALTER

If you want to define keywords specific for a DBMS, you need to add the DBID as a prefix to the filename, e.g. oracle.datatypes.wb.

To add the word geometry as a datatype for the editor when connected to a PostgreSQL database, create the file postgresql.datatypes.wb in the config directory with the following contents:

geometry

The words defined for a specific database are added to the globally recognized keywords, so you don't need to repeat all existing words in the file.

The color for each type of keyword can be changed in the options dialog.

When you analyze statements from e.g. a log file, they are not necessarily formatted in a way that can be easily read, let alone understood. The editor of the SQL Workbench/J can reformat SQL statements into a format that's easier to read and understand for a human being. This feature is often called pretty-printing. Suppose you have the following statement (pasted from a log file)

select user.* from user, user_profile, user_data
where user.user_id = user_profile.user_id and
user_profile.user_id = uprof.user_id and user_data.user_role = 1
and user_data.delete_flag = 'F' and not exists
(select 1 from data_detail where data_detail.id = user_data.id and
data_detail.flag = 'X' and data_detail.value > 42)

this will be reformatted to look like this:

SELECT user.*
FROM user,
     user_profile,
     user_data
WHERE user.user_id = user_profile.user_id
AND   user_profile.user_id = uprof.user_id
AND   user_data.user_role = 1
AND   user_data.delete_flag = 'F'
AND   NOT EXISTS (SELECT 1
                  FROM data_detail
                  WHERE data_detail.id = user_data.id
                  AND   data_detail.flag = 'x'
                  AND   data_detail.value > 42)

You can configure a threshold up to which sub-SELECTs will not be reformatted but put into one single line. The default for this threshold is 80 characters. Meaning that any subselect that is shorter than 80 characters will not be reformatted as the sub-SELECT in the above example. Please refer to Formatting options for details.

Sometimes when you Copy & Paste lines of text from e.g. a spreadsheet, you might want to use those values as a condition for a SQL IN expression. Suppose you a have a list of ID's in your spreadsheet each in one row of the same column. If you copy and paste this into the editor, each ID will be put on a separate line. If you select the text, and then choose SQL → Code tools → Create SQL List the selected text will be converted into a format that can be used as an expression for an IN condition:

Dent
Beeblebrox
Prefect
Trillian
Marvin

will be converted to:

('Dent',
 'Beeblebrox',
 'Trillian',
 'Prefect',
 'Marvin')

The function SQL → Code tools → Create non-char SQL List is basically the same. The only difference is, that it assumes that each item in the list is a numeric value, and no single quotes are placed around the values.

The following list:

42
43
44
45

will be converted to:

(42, 43, 44, 45)

These two functions will only be available when text is selected which spans more then one line.

The editor of the SQL Workbench/J offers two functions to aid in developing SQL statements which should be used inside your programming language (e.g. for SQL statements inside a Java program).

SELECT p.name,
       p.firstname,
       a.street,
       a.zipcode,
       a.phone
FROM person p,
     address a
WHERE p.person_id = a.person_id;

String sql="SELECT p.name, \n" +
"       p.firstname, \n" +
"       a.street, \n" +
"       a.zipcode, \n" +
"       a.phone \n" +
"FROM person p, \n" +
"     address a \n" +
"WHERE p.person_id = a.person_id; \n";

String sql="SELECT p.name, \n" +
"       p.firstname, \n" +
"       a.street, \n" +
//"       a.county, \n" +
"       a.zipcode, \n" +
"       a.phone \n" +
"FROM person p, \n" +
"     address a \n" +
"WHERE p.person_id = a.person_id; \n"

SELECT p.name,
       p.firstname,
       a.street,
--"       a.county, " +
       a.zipcode,
       a.phone
FROM person p,
     address a
WHERE p.person_id = a.person_id;

9.7.3. Support for prepared statements

For better performance Java applications usually make use of prepared statements. The SQL for a prepared statement does not contain the actual values that should be used e.g. in the WHERE clause, but uses quotation marks instead. Let's assume the above example should be enhanced to retrieve the person information for a specific ID. The code could look like this:

String sql="SELECT p.name, \n" +
"       p.firstname, \n" +
"       a.street, \n" +
"       a.zipcode, \n" +
"       a.phone \n" +
"FROM person p, \n" +
"     address a \n" +
"WHERE p.person_id = a.person_id; \n" +
"  AND p.person_id = ?";

You can copy and clean the SQL statement but you will not be able to execute it, because there is no value available for the parameter denoted by the question mark. To run this kind of statements, you need to enable the prepared statement detection using SQL → Settings → Detect prepared statements

Once the prepared statement detection is enabled, SQL Workbench/J will examine each statement to check whether it is a prepared statement. This examination is delegated to the JDBC driver and does cause some overhead when running the statement. For performance reasons you should disable the detection, if you are not using prepared statements in the editor (especially when running large scripts).

If a prepared statement is detected, you will be prompted to enter a value for each defined parameter. The dialog will list all parameters of the statement together with their type as returned by the JDBC driver. Once you have entered a value for each parameter, clicking OK will execute the statement using those values. When you execute the SQL statement the next time, the old values will be preserved, and you can either use them again or modify them before running the statement.

Once you are satisfied with your SQL statement, you can copy the statement and paste the Java code into your program.

Prepared statements are supported for SELECT, INSERT, UPDATE and DELETE statements.

	This feature requires that the getParameterCount() and getParameterType() methods of the ParameterMetaData class are implemented by the JDBC driver and return the correct information about the available parameters.

The following drivers have been found to support (at least partially) this feature:

• PostgreSQL, driver version 8.1-build 405
• H2 Database Engine, Version 1.0.73
• Apache Derby, Version 10.2
• Firebird SQL, Jaybird 2.0 driver
• HSQLDB, version 1.8.0

Drivers known to not support this feature:

• Oracle 11g driver (ojdbc6.jar, ojdbc7.jar)
• Microsoft SQL Server 2000/2005 driver (sqljdbc4.jar)

A bookmark inside the editor is defined by adding the keyword @WbTag followed by the name of the bookmark into a SQL comment:

-- @WbTag delete everything
truncate table orders,order_line,customers;
commit;
    

The keyword is not case sensitive, @wbtag will work just as wel as @WBTAG, or @WbTag. A multiline comment can be used as well as a single line comment.

The annotations for naming a result can additionally be included in the bookmark list. This is enabled in the options panel for the editor.

The names of procedures and functions can also be used as bookmarks if enabled in the bookmark options

To jump to a bookmark select Tools → Bookmarks. A dialog box with all defined bookmarks will be displayed. You can filter the list of displayed bookmarks by entering a value in the input field. Depending on the option Filter on name only the value will either be compared only against the bookmark name. If that option is disabled then the bookmark name and the name of the SQL tab will be checked for the entered value.

	The selection in the bookmark list can be moved with the UP/DOWN keys even when the cursor is located in the filter input field.

If the option Only for current tab is enabled, then the dialog will open showing only bookmarks for the current tab.

There are two options to influence how the list of bookmarks is displayed. Both options are available when displaying the context menu for the list header (usually through a click with the right mouse button):

The body of a function in Postgres is a character literal. Because a delimiter inside a character literal does not define the end of the statement, no special treatment is needed for Postgres.

This is an example of a CREATE PROCEDURE which will NOT work due to the embedded semicolon in the procedure source itself.

CREATE OR REPLACE FUNCTION proc_sample RETURN INTEGER
IS
  l_result INTEGER;
BEGIN
  SELECT max(col1) INTO l_result FROM sometable;
  RETURN l_result;
END;

When executing this script, Oracle would return an error because SQL Workbench/J will send everything up to the keyword INTEGER to the database. Obviously that fragment would not be correct.

The solution is to terminate the script with a character sequence that is called the "alternate delimiter" which can be defined in the connection profile. To be compatible with SQL Developer and SQL*Plus it is recommended to set the alternate delimiter to a forward slash (/).

The script needs to be written like this:

CREATE OR REPLACE FUNCTION proc_sample RETURN INTEGER
IS
  l_result INTEGER;
BEGIN
  SELECT max(col1) INTO l_result FROM sometable;
  RETURN l_result;
END;
/

Note the trailing forward slash (/) at the end in order to "turn on" the use of the alternate delimiter. If you run scripts with embedded semicolons and you get an error, please verify the setting for your alternate delimiter.

The standard delimiter (the semicolon) and the alternate delimiter can be mixed in a single script. Whenever a PL/SQL block (either a stored procedure or an anonymous block) is encountered, SQL Workbench/J expects the alternated delimiter to terminate that block. This follows the same rules as used in SQL*Plus.

The following script will therefore work when connected to an Oracle database:

drop table sometable cascade constraints;
create table sometable
(
  col1 integer not null
);

create or replace function proc_sample return integer
is
  l_result integer;
begin
  select max(col1) into l_result from sometable;
  return l_result;
end;
/

When is the alternate delimiter used?

For all other DBMS, the use of the alternate delimiter is defined by the last delimiter used in the script.

As soon as the statement (or script) that you execute ends with the alternate delimiter, the alternate delimiter is used to separate all SQL statements. When you execute selected text from the editor, be sure to select the alternate delimiter as well, otherwise it will not be recognized (if the alternate delimiter is not selected, the statement to be executed does not end with the alternate delimiter).

This means a script must use the alternate delimiter for all statements in the script. The following script will not work, because the last statement is terminated with the alternate delimiter and thus SQL Workbench/J assumes all statements are delimited with that. As the CREATE TABLES statements are delimited with the standard delimiter, they are not recognized as a separate statement and thus the script is sent as a single statement to the server.

create table orders
(
  order_id    integer not null primary key,
  customer_id integer not null,
  product_id  integer not null,
  pieces      integer not null,
  order_date  date    not null
);

create table orders_audit_log
(
  order_id    integer not null,
  delete_date timestamp not null
);

create trigger orders_audit_log
  for orders
  before delete
as
begin
  insert into audit_log (id, delete_date) values (old.order_id, current_timestamp);
end;
/

The solution is to terminate every statement with the alternate delimiter:

create table orders
(
  order_id    integer not null primary key,
  customer_id integer not null,
  product_id  integer not null,
  pieces      integer not null,
  order_date  date    not null
)
/

create table orders_audit_log
(
  order_id    integer not null,
  delete_date timestamp not null
)
/

create trigger orders_audit_log
  for orders
  before delete
as
begin
  insert into audit_log (id, delete_date) values (old.order_id, current_timestamp);
end;
/

You have two possibilities to display help for SQL Workbench/J: a HTML a PDF version of the manual.

The HTML help is available through the menu item Help → Contents It is expected that the HTML manual is stored in a directory called manual in the same directory where sqlworkbench.jar is located. This is automatically the case when you extract the distribution archive with sub-directories.

You can choose to display a single-page version of the HTML help (easier to search) or a multi-page version of the help that is easier to navigate. This can be changed in the options dialog, that is accessible from Tools → Option.

The PDF manual can be displayed by selecting Help → Manual. 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 section of the options dialog.

The file SQLWorkbench-Manual.pdf must be available in the directory where sqlworkbench.jar is located.

When connected to a database, the menu item Help → DBMS Manual will display the online manual for the current DBMS (if there is one). Where possible the link will display the manual that corresponds to the version of the current connection.

The URL that is used to display the manual can be changed in the configuration file workbench.settings.

Every window that is opened by SQL Workbench/J for the first time is displayed with a default size. In certain cases it can happen that not all labels are readable or all controls are visible on the window. This can happen, e.g. when a large default font is selected (or defined through the look and feel).

Every window in SQL Workbench/J can be resized and will remember its size. So in case not everything is readable on a dialog, just resize the window so that the missing parts become visible, and that size will be kept for the future.

When you run SQL statements that produce a result (such as a SELECT statement) these results will be displayed in the lower pane of the window, next to the message panel. For each result that is returned from the server, one tab (labeled "Result") will be created. If you select and execute three SELECT statements, the lower pane will show three result tabs and the message tab. If your statement(s) did not produce any result, only the messages tab will be displayed.

	SQL Workbench/J will read all rows returned by your statement into memory. When retrieving large results you might run out of memory. To adjust the memory available to SQL Workbench/J please refer to this chapter.

When you run a SQL statement, the current results will be cleared and replaced by the new results. You can turn this off by selecting SQL → Settings → Append new results. Every result that is retrieved while this option is turned on, will be added to the set of result tabs, until you de-select this option. This can also be toggled using the button () on the toolbar. Additional result tabs can be closed using Data → Close result. You can configure the default behavior for new editor tabs in the options dialog.

You can also run stored procedures that return result sets. These result will be displayed in the same way. For DBMS's that support multiple result sets from a single stored procedure (e.g. Microsoft SQL Server), one result tab will be displayed for each result returned.

SQL Workbench/J supports reading and writing BLOB (Binary Large OBject) or CLOB (Character Large OBject) columns from and to external files. BLOB clumns are sometimes also referred to as binary data. CLOB columns are sometimes also referred to as LONG VARCHAR. The exact data type depends on the DBMS used.

To insert and update LOB columns the usual INSERT and UPDATE statements can be used by using a special placeholder to define the source for the LOB data. When updating the LOB column, a different placeholder for BLOB and CLOB columns has to be used as the process of reading and sending the data is different for binary and character data.

	When working with Oracle, only the 10g driver supports the standard JDBC calls used by SQL Workbench/J to read and write the LOB data. Earlier drivers will not work as described in this chapter.

UPDATE theTable
  SET blob_col = {$blobfile=c:/data/image.bmp}
WHERE id=24;

INSERT INTO theTable
(id, blob_col)
VALUES
(42,{$blobfile=c:/data/image.bmp});

12.5.2. Updating CLOB data through SQL

The process of updating or inserting CLOB data is identical to the process for BLOB data. The only difference is in the syntax of the placeholder used to specify the source file. Firstly, the placeholder has to start with {$clobfile= and can optionally contain a parameter to define the encoding of the source file.

UPDATE theTable
  SET clob_col = {$clobfile=c:/data/manual.html encoding=utf8}
WHERE id=42;

If you ommit the encoding parameter, SQL Workbench/J will leave the data conversion to the JDBC driver (technically, it will use the PreapredStatement.setAsciiStream() method whereas with an encoding it will use the PreparedStatement.setCharacterStream() method).

	The format of the {$clobfile=} or {$blobfile=} parameter has to be entered exactly as described here. You may not put e.g. spaces before or after the equal sign or the braces. If you do this, SQL Workbench/J will not recognize the parameter and will pass the statement "as is" to the JDBC driver.

12.5.4. BLOB data in the result set

When the result of your SELECT query contains BLOB columns, they will be displayed as (BLOB) together with a button. When you click on the button a dialog will be displayed allowing you to save the data to a file, view the data as text (using the selected encoding), display the blob as an image or display a hex view of the blob.

When displaying the BLOB content as a text, you can edit the text. When saving the data, the entered text will be converted to raw data using the selected encoding.

The window will also let you open the contents of the BLOB data with a predefined external tool. The tools that are defined in the options dialog can be selected from a drop down. To open the BLOB content with one of the tools, select the tool from the drop down list, then click on the button Open with next to the external tools drop down. SQL Workbench/J will then retrieve the BLOB data from the server, store it in a temporary file on your hard disk, and run the selected application, passing the temporary file as a parameter.

From within this information dialog, you can also upload a file to be stored in that BLOB column. The file contents will not be sent to the database server until you actually save the changes to your result set (this is the same for all changes you make directly in the result set, for details please refer to Editing the data)

	When using the upload function in the BLOB info dialog, SQL Workbench/J will use the file content for any subsequent display of the binary data or the the size information in the information dialog. You will need to re-retrieve the data, in order to use the blob data from the server.

There are some configuration settings that affect the performance of SQL Workbench/J. On slow computers it is recommended to turn off the usage of the animated icon as the indicator for a running statement.

When running large scripts, the feedback which statement is executed can also slow down the execution. It is recommended to either turn off the feedback using WBFEEDBACK OFF or by consolidating the script log

When running imports or exports it is recommended to turn off the progress display in the statusbar that shows the current row that is imported/exported because this will slow down the process as well. In both cases you can use -showProgress to turn off the display (or set it to a high number such as 1000) in order to reduce the overhead caused by updating the screen.

The complete history for all editor tabs is saved and loaded into one file, called a workspace. These workspaces can be saved and loaded to restore a specific editing context. You can assign a saved workspace to a connection profile. When the connection is established, the workspace is loaded into SQL Workbench/J. Using this feature you can maintain a completely different set of statements for different connections.

If you do not assign a workspace to a connection profile, a workspace with the name Default.wksp will be used for storing the statement history. This default workspace is shared between all profiles that have no workspace assigned.

To save the current SQL statement history and the visible tabs into a new workspace, select Workspace → Save Workspace as....

The default file extension for workspaces is wksp.

Once you have loaded a workspace, you can save it with Workspace → Save Workspace. The current workspace is automatically saved, when you exit SQL Workbench/J.

An existing workspace can be loaded with Workspace → Load Workspace

If you have an external file open in one of the editor tabs, the filename itself will be stored in workspace. When loading the workspace SQL Workbench/J will try to load the external file again. If the file does not exist, the last history entry from the saved history for that tab will be displayed.

The workspace file itself is a normal ZIP file, which contains one file with the statement history for each tab. The individual files can be extracted from the workspace using your favorite UNZIP tool.

The text from the current editor can be saved to an external file, by choosing File → Save or File → Save as. The filename for the current editor will be remembered. To close the current file, select File → Discard file (Ctrl-F4) or use the context menu on the tab label itself.

	Detaching a file from the editor will remove the text from editor as well. If you only want to detach the filename from the editor but keep the text, then press Ctrl-Shift-F4 or hold down the Shift key while selecting the Discard menu item.

When you load a SQL script and execute the statements, be aware that due to the history management in SQL Workbench/J the content of the external file will be placed into the history buffer. If you load large files, this might lead to massive memory consumption. Currently only the number of statements put into the history can be controlled, but not the total size of the history itself. You can prevent files from being put into the history by unchecking the option "Files in history" in the Editor section of the options dialog.

The command describe can be used to display the structure of a view or table. You can also display information about the database object at the cursor by using SQL → Object info. This function is also available in the context menu of the editor.

When the menu item is invoked using the mouse, holding down the CTRL key will return dependent object information as well (e.g. indexes, foreign keys).

You can configure this function to always include dependent objects by adding a configuration property.

Once the data has been retrieved from the database, it can be edited directly in the result set. SQL Workbench/J assumes that enough columns have been retrieved from the table so that at a unique identifier is available to identify the rows to be updated.

If you have primary keys defined for the underlying tables, those primary key columns will be used for the WHERE statements for UPDATE and DELETE. If no primary key is found, the unique indexes for the table will be retrieved. The first unique index found that only consists of columns defined as NOT NULL will be used.

If no PK or unique index can be found, the custom PK Mapping will be checked. If still no PK columns can be found, you will be prompted to select the key columns based on the current result set.

	The changes (modified, new or deleted rows) will not be saved to the database until you choose Data → Save Changes to Database.

If the update is successful (no database errors) a COMMIT will automatically be sent to the database (if autocommit is turned off).

If your SELECT was based on more than one table, you will be prompted to specify which table should be updated. It cannot be detected reliably which column belongs to which of the tables from the select statement. When updating a result from multiple tables, only columns from the chose update table should be changed, otherwise incorrect SQL statements will be generated.

If no primary (or unique) key can be found for the update table, you will be prompted to select the columns that should be used to uniquely identify a row in the update table.

If an error is reported during the update, a ROLLBACK will automatically be sent to the database. The COMMIT or ROLLBACK will only be sent if autocommit is turned off.

Columns containing BLOB data will be displayed with a ... button. By clicking on that button, you can view the blob data, save it to a file or upload the content of a file to the DBMS. Please refer to BLOB support for details.

When editing, SQL Workbench/J will highlight columns that are defined as NOT NULL in the database. You can turn this feature off, or change the color that is used in the options dialog.

	When editing date, timestamp or time fields, the format specified in the options dialog is used for parsing the entered value and converting that into the internal representation of a date. The value entered must match the format defined there.

If you want to input the current date and time you can use now, today, sysdate, current_timestamp, current_date instead. This will then use the current date & time and will convert this to the approriate data type for that column. e.g. now will be converted to the current time for a time column, the current date for a date column and the current date/time for a timestamp column. These keywords also work when importing text files using WbImport or importing a text file into the result set. The exact keywords that are recognized can be configure in the settings file

If the option Empty String is NULL is disabled for the current connection profile, you can still set a column's value to null when editing it. To do this, double click the current value, so that you can edit it. In the context menu (right mouse button) the option "Set to NULL" is available. This will clear the value and set it to NULL. You can assign a shortcut to this action, but the shortcut will only be active when editing a value inside a column.

To delete a row from the result, select Data → Delete Row from the menu. This will remove the currently selected row(s) from the result and will mark them for deletion once the changes are saved. No foreign key checks will be done when using this option.

The generated DELETE statements will fail if the deleted row(s) are still referenced by another table. In that case, you can use Delete With Dependencies.

The result will be displayed in the order returned by the DBMS (i.e. if you use an ORDER BY in your SELECT the display will be displayed as sorted by the DBMS).

You can change the sorting of the displayed data by clicking on the header of the column that should be used for sorting. After the first click the data will be sorted ascending (lower values at the top). If you click on the column again the sort order will be reversed. The sort order will be indicated by a little triangle in the column header. If the triangle points upward the data is sorted ascending, if it points downward the data is sorted descending. Clicking on a column will remove any previous sorting (including the secondary columns) and apply the new sorting.

If you want to sort by more than one column, hold down the Ctrl key will clicking on the (second) header. The initial sort order is ascending for that additional column. To switch the sort order hold down the Ctrl key and click on the column header again. The sort order for all "secondary" sort columns will be indicated with a slightly smaller triangle than the one for the primary sort column.

To define a different secondary sort column, you first have to remove the current secondary column. This can be done by holding down the Shift key and clicking on the secondary column again. Note that the data will not be resorted. Once you have removed the secondary column, you can define a different secondary sort column.

By default SQL Workbench/J will use "ASCII" sorting which is case-sensitive and will not sort special characters according to your language. You can change the locale that is used for sorting data in the options dialog under the category "Data Display". Sorting using a locale is a bit slower than "ASCII" sorting.

Once the data has been retrieved from the server it can be filtered without re-retrieving it. You can define the filter in two ways: either select the filter columns and their filter values manually, or create a filter from the currently selected values in the result set.

	The filter is applied on the data that is retrieved from the database. The data will not be reloaded from the database when you define a filter.

12.14.1. Defining a filter manually

To define a filter, click on the Filter button () in the toolbar or select Data → Filter data. A dialog will appear where you can define a filter for the current result set. Each line in the filter dialog defines an expression that will be applied to the column selected in the first drop down. If you select * for the column, the filter condition will be applied to all columns of the result set.

	The value expression for a column does not accept SQL expressions! You can only compare the column to a constant, not to the result of a SQL function (such as CURRENT_DATE or now()) If you need this kind of filter, you have to use a SQL statement with the approriate WHERE condition.

To add a multi-column expression, press the More button, to create a new line. To remove a column expression from the filter, click the Remove () button. For character based column data, you can select to ignore the case of the column's data when applying the expression, i.e. when Ignore case is selected, the expression 'NAME = arthur' will match the column value 'Arthur', and 'ARTHUR'.

By default, the column expressions are combined with an OR, i.e. that a row will be displayed if at least one of the column expressions evaluates to true. If you want to view only rows where all column expressions must match, select the AND radio button at the top of the dialog.

Once you have saved a filter to an external file, this filter will be available in the pick list, next to the filter icon. The list will show the last filters that were saved. The number of items displayed in this drop down can be controlled in the settings file.

Stored procedures can be executed by using the SQL Workbench/J command WbCall which replaces the standard commands available for the DBMS (e.g. CALL or EXECUTE). By using a special command, additional checks can be carried out by SQL Workbench/J. This is especially necessary when dealing with OUT parameters or REF CURSORS.

The simplest way to run a stored procedure is:

WbCall my_proc();

When using Microsoft SQL Server, WbCall is not necessary as long as the stored procedure does not have OUT or REF CURSOR parameters. So with SQL Server you can simply write:

sp_who2;

To run the stored procedure sp_who2 and to display it's results.

For more details on running a stored procedure with OUT parameters or REF CURSORS please refer to the description of the WbCall command.

You can export the data of the result set into local files of the following formats:

In order to write the proprietary Microsoft Excel format, additional libraries are needed. Please refer to Exporting Excel files for details.

To save the data from the current result set into an external file, choose Data → Save Data as You will be prompted for the filename. On the right side of the file dialog you will have the possibility to define the type of the export. The export parameters on the right side of the dialog are split into two parts. The upper part defines parameters that are available for all export types. These are the encoding for the file, the format for date and date/time data and the columns that should be exported.

All format specific options that are available in the lower part, are also available when using the WbExport command. For a detailed discussion of the individual options please refer to that section.

The options SQL UPDATE and SQL DELETE/INSERT are only available when the current result has a single table that can be updated, and the primary key columns for that table could be retrieved. If the current result does not have key columns defined, you can select the key columns that should be used when creating the file. If the current result is retrieved from multiple tables, you have to supply a table name to be used for the SQL statements.

Please keep in mind that exporting the data from the result set requires you to load everything into memory. If you need to export data sets which are too big to fit into memory, you should use the WbExport command to either create SQL scripts or to save the data as text or XML files that can be imported into the database using the WbImport command. You can also use SQL → Export query result to export the result of the currently selected SQL statement.

You can also copy the data from the result into the system clipboard in four different formats.

As with the Save Data as command, the options SQL UPDATE and SQL DELETE/INSERT are only available when the current result set is updateable. If no key columns could be retrieved for the current result, you can manually define the key columns to be used, using Data → Define key columns

	If you do not want to copy all columns to the clipboard, hold down the the CTRL key while selecting one of the menu items related to the clipboard. A dialog will then let you select the columns that you want to copy.

Alternatively you can hold down the Alt key while selecting rows/columns in the result set. This will allow you to select only the columns and rows that you want to copy. If you then use one of the formats available in the Copy selected submenu, only the selected cells will be copied. If you choose to copy the data as UPDATE or DELETE/INSERT statements, the generated SQL statements will not be correct if you did not select the primary key of the underlying update table.

You can change the name of the result tab associated with a statement. To give a result set a name, use the annotation @WbResult followed by the name that should appear as the result's name.

The following examples executes two statements. The result for the first will be labelled "List of contacts" and the second will be labelled "List of companies":

-- @WbResult List of contacts
SELECT * FROM person;

/*
 @WbResult List of companies
 this will retrieve all companies from the database
*/
SELECT * FROM company;

The result name that is used, will be everything after the annotation's keyword until the end of the line.

For the second select (with the multi-line comment), the name of the result tab will be List of companies, the comment on the second line will not be considered.

The annotation @WbMacro can be used to add macros to the context menu of the result.

When such a macro is executed, the values of all columns of the currently selected row will be defined as variables that are usable in the macro. The result of the macro will always be appended to the current result regardless of the setting in the macro definition.

	Variables which are defined by invocation of a macro from this menu item will not be deleted after the macro has been executed.

Assume the following macro with the name "Person Address":

select *
from address
where person_id = $[id];

and the following SQL query:

-- @WbMacro name="Person Address"
select id, firstname, lastname
from person;

The context menu of the result will then contain a new submenu: Macros → Person Address. The variables $[id], $[firstname] and $[lastname] will contain the values of the currently selected row when the macro is executed.

It is also possible to re-map the column names to different variable names.

-- @WbMacro name="Person Address" map="id:PersonID"
select id, firstname, lastname
from person;

In this case a variable named PersonID will be created with the value of the id column from the selected row.

The map parameter can be repeated several times to re-map multiple columns, e.g. map=p_id:PersonID map=o_id:OrderID

It is possible to specify more than one macro for the context menu:

-- @WbMacro name="Person Address" map="id:PersonID"
-- @WbMacro name="Customer Orders" map="id:PersonOrderID"
select id, firstname, lastname
from person;

A macro can only be executed from the menu when exactly one row is selected in the result.

You can assign a title to the result by using the @WbResult annotation based on a variable in the macro:

-- @WbResult Addresses for $[firstname] $[lastname]
select *
from address
where person_id = $[PersonID];

If the result of a query should be displayed in an existing result tab, the annotation @WbUseTab together with a tab name can be used. If this annotation is present and a result tab with that name already exists, the existing result will be replaced with the new result. If no result tab with that name exists, a new tab (with the supplied name) will be created.

	Re-using a result tab only works if SQL → Append new results is enabled. You can combine @WbUseTab with the @WbAppendResult annotation to force re-using an existing result even though the option is turned off.

If the following query is run for the second time, the existing data will be replaced with the newly retrieved data:

-- @WbUseTab List of contacts
SELECT * FROM person;

The annotation @WbScrollTo can be used to automatically scroll a result set after it has been retrieved to a specific row number. The row number has to be supplied using a # sign:

-- @WbScrollTo #100
SELECT *
FROM person;

In addition to a row number, the special values end or last (without a #) are also recognized. When they are supplied, the result is automatically scrolled to the last row. This is useful when displaying the contents of log tables.

-- @WbScrollTo end
SELECT *
FROM activity_log;

The annotation @WbAppendResult can be used to always append the result of the associated query regardless of the current setting of SQL → Append new results.

To suppress an empty result, the annotation @WbRemoveEmpty can be used. If a query returns no rows and contains this annotation, no result tab will be created. No warning or message will be shown if this happens!

To automatically refresh a result in a defined interval, the @WbRefresh annotation can be used. The interval is specified as a parameter to the annotation:

-- @WbRefresh 15s
SELECT *
FROM pg_stat_activity;

The automatic refresh can also be enabled through the context menu of the result tab.

By default SQL Workbench/J will use a file with the name WbMacros.xml stored in the configuration directory to save and load the macros.

To create a copy of the currently loaded macros, use Macros → Save Macros as.... To load previously saved macros, use Macros → Load Macros....

The currently loaded file is displayed as a tool tip of the Save Macros as... menu item and and the bottom of the Manage Macros dialog.

	A set of macros is always loaded globally, not just for the current window. If you have more than one window open, the newly loaded macros will also be active in all the other windows.

There are three ways to define a SQL macro.

If the current statement in the editor should be defined as a macro, select (highlight) the statement's text and select Macros → Add SQL macro from the main menu. You will be prompted to supply a name for the new macro. If you supply the name of an existing macro, the existing macro will be overwritten.

Alternatively you can add a new macro through Macros → Manage Macros.... This dialog can also be used to delete and and edit existing macros. You can put macros into separate groups (e.g. one for PostgreSQL macros, one for Oracle etc). If you have only one group defined (or only one visible group), all macros of that group will be listed in the menu directly. If you define more than one group, each group will appear as a separate sub-menu.

Macros can also be defined using the command WbDefineMacro.

When the dialog is closed using the OK button the macros are automatically saved to the current file.

The order in which the macros (or groups) appear in the menu can be changed by dragging them to the desired position in the manage macro dialog.

There are two ways to run an executable macro: use it's name as a SQL command by typing it into the editor and executing it like any other SQL statement. Or by selecting the corresponding menu entry from the Macros menu.

Note that the macro name needs to be unique to be used as a "SQL Statement". If you have two different macros in two different macro groups with the same name, it is undefined (i.e. "random") which of them will be executed.

To view the complete list of macros select Macros → Manage Macros... After selecting a macro, it can be executed by clicking on the Run Run button. If you check the option "Replace current SQL", then the text in the editor will be replaced with the text from the macro when you click on the run button.

In console mode you can use the command WbListMacros to show the complete list of macros (of course this can also be used in GUI mode as well.

	Macros will not be evaluated when running in batch mode.

Apart from the SQL Workbench/J script variables for SQL Statements, additional "parameters" can be used inside a macro definition. These parameters will be replaced before replacing the script variables.

The SQL statement that is eventually executed will be logged into the message panel when invoking the macro from the menu. Macros that use the above parameters cannot correctly be executed by entering the macro alias in the SQL editor (and then executing the "statement").

	The parameter keywords are case sensitive, i.e. the text ${SELECTION}$ will not be replaced!

This feature can be used to create SQL scripts that work only with with an additional statement. e.g. for Oracle you could define a macro to run an explain plan for the current statement:

explain plan
for
${current_statement}$
;

-- @wbResult Execution plan
select plan_table_output
from table(dbms_xplan.display(format => 'ALL'));

When you run this macro, it will run an EXPLAIN PLAN for the statement in which the cursor is currently located, and will immediately display the results for the explain. Note that the ${current_statement}$ keyword is terminated with a semicolon, as the replacement for ${current_statement}$ will never add the semicolon. If you use ${selection}$ instead, you have to pay attention to not select the semicolon in the editor before running this macro.

For PostgreSQL you can define a similar macro that will automatically run the EXPLAIN command for a statemet:

explain (analyze true, verbose true, buffers true) ${current_statement}$;

Another usage of the parameter replacement could be a SQL Statement that retrieves the rowcount that would be returned by the current statement:

SELECT count(*) FROM
(
${current_statement}$
)

Expandable macros are not intended to be run directly. They serve as code templates for writing statements.

When typing the name of the macro in the editor and completing this name with the "Macro expansion key", the typed word will be replaced with the macro's text. The name of a such a macro is not case sensitive. So slt and SLT are detected as the same macro name.

The macro expansion is only triggered if the macro expansion key is typed quickly after the word. If there is a longer pause between typing the last character of the macro's name and typing the expansion key, the macro will not be expanded.

For expandable macros, two special place holders in the macro text are supported. Both place holders are deleting when the macro text is inserted.

Once you have retrieved data from a table that has foreign key relations to other tables, you can navigate the relationship for specific rows in the result set. Select the rows for which you want to find the data in the related tables, then right click inside the result set. In the context menu two items are available:

Consider the following tables:

The context menu for the selected rows will give you the choice in which SQL tab you want the generated SELECT to be pasted. This is similar to the Put SELECT into feature in the table list of the DbExplorer.

Once you have obtained a result set from the table BASE, select (mark) the rows for which you want to retrieve the related rows, e.g. the one where id=1. Using Referencing rows → DETAIL SQL Workbench/J will create the following statement:

SELECT *
FROM DETAIL
WHERE base_id = 1;

The result of the generated statement will always be added to the existing results of the chosen SQL panel. By default the generated SQL statement will be appended to the text editor. If you don't want the generated statement to be appended to the editor, hold down the Ctrl key while selecting the desired menu item. In that case, the generated statement will only be written to the messages panel of the SQL tab. If the target tab contains an external file, the statement will never be appended to the editor's text.

To navigate from the child data to the "parent" data, use Referenced rows

The additional result tabs can be closed using Data → Close result

When using ANSI JOIN syntax to create table joins with tables linked by foreign keys in the database, the command JOIN completion can be used to automatically generate the necessary join condition. Consider the following statement

SELECT ord.amount, ord.order_date, prod.name
FROM orders ord
  JOIN product prod ON |

(the | denoting the location of the cursor).

When the cursor is located behind the ON keyword and you select SQL → JOIN completion, SQL Workbench/J will retrieve the foreign key and corresponding primary key definitions between the tables orders and product. If such constraints exist, the corresponding condition will be generated and written into the editor. After executing JOIN completion, the SQL statement will look like this:

SELECT ord.amount, ord.order_date, prod.name
FROM orders ord
  JOIN product prod ON prod.id = ord.product_id

This feature requires the usage of the JOIN keyword. Joining tables in the WHERE clause is not supported.

By default SQL Workbench/J tries to create a join condition on the table from the "previous" JOIN condition (or the FROM) clause. If no foreign key constraint is found linking the "current" and the "previous" table, a popup window with all tables in the select statement that could be used for completion is displayed. This popup merely looks at the tables in the statement, no test for foreign key constraints is done when displaying this list.

You can configure this feature to generate a USING operator if the column names match. The case of the keywords in the generated condition is determined by the settings of the SQL Formatter.

SQL Workbench/J supports the selection of foreign key values (i.e. the primary key values of the referenced table) in two different situations: while editing a result set and while writing a DML statement.

15.3.1. Editing foreign key values

After starting to edit a cell, the context menu contains an item Select FK value. Once this item is selected SQL Workbench/J will detect the table that the current column references. If a foreign key is detected a dialog window will be shown containing the data from the referenced table. For performance reasons the check if the current column is referencing another table is only done after the menu item has been invoked. If no foreign key could be found, a message is displayed in the status bar.

	This is only supported for result sets that are based on a single table.

By default the dialog will not load more than 150 rows from that table. The number of retrieved rows can be configured through the "Max. Rows" input field.

There are two ways to find the desired target row which can be selected using the radio buttons above the input field.

• Applying a filter

  This mode is intended for small lookup tables. All rows are loaded into memory and the rows are filtered immediately when typing. The typed value is searched in all columns of the result set. Clicking on the reload button will always re-retrieve all rows.

• Retrieving data

  This mode is intended for large tables where not all rows can be loaded into memory. After entering a search term and hitting the ENTER key (or clicking on the reload button), a SQL statement to retrieve the rows containing the search statement will be executed. The returned rows are then displayed.

Once you have selected the desired row, clicking the OK will put the value(s) of the corresponding primary key column(s) into the currently edited row.

update film_category
    set category_id = |
  where film_id = 42;

To delete rows from the result set including all dependent rows, choose Data → Delete With Dependencies. In this case SQL Workbench/J will analyze all foreign keys referencing the update table, and will generate the necessary DELETE statements to delete the dependent rows, before sending the DELETE for the selected row(s).

Delete With Dependencies might take some time to detect all foreign key dependencies for the current update table. During this time a message will be displayed in the status bar. The selected row(s) will not be removed from the result set until the dependency check has finished.

	Note that the generated SQL statements to delete the dependent rows will only be shown if you have enabled the preview of generated DML statements in the options dialog

You can also generate a script to delete the selected and all depending rows through Data → Generate delete script. This will not remove any rows from the current result set, but instead create and display a script that you can run at a later time.

If you want to generate a SQL script to delete all dependent rows, you can also use the SQL Workbench/J command WbGenerateDelete.

You can define variables within SQL Workbench/J that can be referenced in your SQL statements. This is done through the internal command WbVarDef.

WbVarDef myvar=42 defines a variable with the name myvar and the value 42. If the variable does not exist, it will be created. If it exists its value will be overwriliteralen with the new value. To remove a variable simply set its value to nothing: WbVarDef myvar=. Alternatevily you can use the command WbVarDelete myvar to remove a variable definition.

Variable substitution is also done within Macros. If your macro definition contains a reference to a SQL Workbench/J variable, this will be treated the same way as in regular statements.

The definition of variables can also be read from a properties file. This can be done 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.

WbVarDef -file=/temp/myvars.def

This file has to be a standard Java "properties" file. Each variable is listed on a single line in the format variable=value. Lines starting with a # character are ignored (comments). Assuming the file myvars.def had the following content:

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

After executing WbVarDef -file=/temp/myvars.def there would be three variables available in the system: var_id, person_name, another_variable, that could be used e.g. in a SELECT query:

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

SQL Workbench/J would expand the variables and send the following statement to the server:

SELECT * FROM person where name='Dent' or id=42;

A variable can also be defined as the result of a SELECT statement. This indicated by using @ as the first character after the equal sign. The SELECT needs to be enclosed in double quotes, if you are using single quotes e.g. in the where clause:

WbVarDef myvar=@"SELECT id FROM person WHERE name='Dent'"

If the SELECT returns more than one column, multiple variables can be defined by specifying a comma separated list of variable names. The following statement will define the variables id and name based on the values returned from the SELECT statement:

WbVarDef id,name=@"SELECT id,firstname FROM person WHERE lastname='Dent'"

When executing the statement, SQL Workbench/J only retrieves the first row of the result set. Subsequent rows are ignored. If the select returns more columns than variable names, the additional values are ignored. If more variables are listed than columns are present in the result set, the additional variables will be undefined.

A variable can also be defined by reading the content of a file (this is different from reading the variable definition from a file).

WbVarDef -variable=somevar -contentFile=/temp/mydata.txt

When executing the statement, SQL Workbench/J will read the content of the file mydata.txt and use that as the value for the variable somevar.

If the file contents contains references to variables, these are replaced after the content as been loaded. To disable replacement, use the parameter -replaceVars=false.

Consider the following sequence of statements, where the file select.txt contains the statement SELECT * FROM person WHERE id = $[person_id]

WbVarDef person_id=42;
WbVarDef -variable=my_select -contentFile=select.txt;
$[my_select];

After running the above script, the variable my_select, will have the value of SELECT * FROM person WHERE id = 42. When "running" $[my_select], the row with id=42 will be retrieved.

To view a list of currently defined variables execute the command WbVarList. This will display a list of currently defined variables and their values. You can edit the resulting list similar to editing the result of a SELECT statement. You can add new variables by adding a row to the result, remove existing variables by deleting rows from the result, or edit the value of a variable. If you change the name of a variable, this is the same as removing the old, and creating a new one.

The defined variables can be used by enclosing them in special characters inside the SQL statement. The default is set to $[ and ], you can use a variable this way:

SELECT firstname, lastname FROM person WHERE id=$[id_variable];

If you have a variable with the name id_variable defined, the sequence $[id_variable] will be replaced with the current value of the variable.

Variables will be replaced after replacing macro parameters.

If the SQL statement requires quotes for the SQL literal, you can either put the quotes into the value of the variable (e.g. WbVarDef name="'Arthur'") or you put the quotes around the variable's placeholder, e.g.: WHERE name='$[name]';

	Variables will be replaced in string literals (e.g. '$[foo]') and comments (e.g. -- $[foo] or /* $[foo] */)

If you are using values in your regular statements that actually need the prefix ($[ or suffix ]) characters, please make sure that you have no variables defined. Otherwise you will unpredictable results. If you want to use variables but need to use the default prefix for marking variables in your statements, you can configure a different prefix and suffix for flagging variables. To change the the prefix e.g. to %# and the suffix (i.e end of the variable name) to #, add the following lines to your workbench.settings file:

workbench.sql.parameter.prefix=%#
workbench.sql.parameter.suffix=#

You may leave the suffix empty, but the prefix definition may not be empty.

You can also use variables in a way that SQL Workbench/J will prompt you during execution of a SQL statement that contains a variable.

If you want to be prompted for a value, simply reference the value with a quotation mark in front of its name:

SELECT id FROM person WHERE name like '$[?search_name]%'

If you execute this statement, SQL Workbench/J will prompt you for the value of the variable search_name. If the variable is already defined you will see the current value of the variable. If the variable is not yet defined it will be implicitly defined with an empty value.

If you use a variable more then once in your statement it is sufficient to define it once as a prompt variable. Prompting for a variable value is especially useful inside a macro definition.

You can also define a conditional prompt with using an ampersand instead of a quotation mark. In this case you will only be prompted if no value is assigned for the variable:

SELECT id FROM person WHERE name like '$[&search_name]%'

The first time you execute this statement (and no value has been assigned to search_name before using WBVARDEF or on the command line) you will be prompted for a value for search_name. Any subsequent execution of the statement (or any other statement referencing $[&search_name]) will re-use the value you entered.

When defining a variable, you can specify a list of values that should be entered in the dialog.

WbVardef -variable=status -values='active,pending,closed';

By default the variables shown in the prompt dialog are sorted alphabetically. This behavior can be changed by setting the configuration property workbench.sql.parameter.prompt.sort to true, e.g. using WbSetConfig

WbSetConfig workbench.sql.parameter.prompt.sort=false

If the property is set to false, the variables are shown in the order they were declared:

WbVarDef zzz='';
WbVarDef vvv='';
WbVarDef aaa='';

select *
from foobar
where col1 = $[?aaa]
  and col2 = $[?vvv]
  and col3 > $[?zzz]

The dialog to enter the variables will show them in the order zzz, vvv, aaa

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

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;'

After starting the console mode, SQL Workbench/J displays the prompt SQL> where you can enter SQL statements. The statement will not be sent to the database until it is either terminated with the standard semicolon, or with the alternate delimiter (that can be specified either in the used connection profile or on the commandline when starting the console mode).

As long as a statement is not complete, the prompt will change to ..>. Once a delimiter is identified the statement(s) are sent to the database.

SQL> SELECT * [enter]
..>FROM person;

A delimiter is only recognized at the end of the input line, thus you can enter more than one statement on a line (or multiple lines) if the intermediate delimiter is not at the end of one of the input lines:

SQL> DELETE FROM person; rollback;
DELETE executed successfully
4 row(s) affected.

ROLLBACK executed successfully
SQL>

To exit the application in console mode, enter exit when the default prompt is displayed. If the "continuation prompt" (..>) is displayed, this will not terminate the application. The keyword exit must not be terminated with a semicolon.

If you did not specify a connection on the command line when starting the console, you can set or change the current connection in console mode using the WbConnect command. Using WbConnect in console mode will automatically close the current connection, before establishing the new connection.

To disconnect the current connection in console mode, run the statement WbDisconnect. Note that this statement is only available in console mode.

If you are running SELECT statements in console mode, the result is displayed on the screen in "tabular" format. Note that SQL Workbench/J reads the whole result into memory in order to be to adjust the column widths to the displayed data.

You can disable the buffering of the results using the command line parameter bufferResults=false. In that case, the width of the displayed columns will not be adjusted properly. The column widths are taken from the information returned by the driver which typically results is a much larger display than needed.

The output in tabular format (if results are buffered) looks like this:

SQL> select id, firstname, lastname, comment from person;
id | firstname | lastname   | comment
---+-----------+------------+--------------------
1  | Arthur    | Dent       | this is a comment
2  | Zaphod    | Beeblebrox |
4  | Mary      | Moviestar  | comment
3  | Tricia    | McMillian  | test1

(4 Rows)
SQL>

If the size of the column values exceed the console's width the display will be wrapped, which makes it hard to read. In that case, you can switch the output so that each column is printed on a single line.

This is done by running the statement: WbDisplay record

SQL> WbDisplay record;
Display changed to single record format
Execution time: 0.0s
SQL> select id, firstname, lastname, comment from person;
---- [Row 1] -------------------------------
id        : 1
firstname : Arthur
lastname  : Dent
comment   : this is a very long comment that would not fit onto the screen when printed as the last column
---- [Row 2] -------------------------------
id        : 2
firstname : Zaphod
lastname  : Beeblebrox
comment   :
---- [Row 3] -------------------------------
id        : 4
firstname : Mary
lastname  : Moviestar
comment   :
---- [Row 4] -------------------------------
id        : 3
firstname : Tricia
lastname  : McMillian
comment   :

(4 Rows)
SQL> 

To switch back to the "tabular" display, use: WbDisplay tab.

Normally when executing a SQL script using WbInclude, the result of such a script (e.g. when it contains a SELECT statement) is not displayed on the console.

To run such a script, use the command WbRun instead of WbInclude. If you have the following SQL script (named select_person.sql):

SELECT *
FROM person;

and execute that using the WbInclude command:

SQL> WbInclude -file=select_person.sql;
SQL> Execution time: 0.063s

If you execute this script using WbRun the result of the script is displayed:

SQL> WbRun select_people.sql;
select *
from person;

id | firstname | lastname
---+-----------+------------
1  | Arthur    | Dent
4  | Mary      | Moviestar
2  | Zaphod    | Beeblebrox
3  | Tricia    | McMillian

(4 Rows)
Execution time: 0.078s
SQL>

In the SQL Workbench/J GUI window, you can limit the reusult of a query by entering a value in the "Max. Rows" field. If you want to limit the number of rows in console mode you can do this by running the statement

SQL> set maxrows 42;
MAXROWS set to 42
Execution time: 0.0s
SQL>

This will limit the number of rows retrieved to 42.

SET MAXROWS has no effect when run as a post-connect script.

To set the query timeout in console mode, you can run the following statement

SQL> set timeout 42;
TIMEOUT set to 42
Execution time: 0.0s
SQL>

This will set a query timeout of 42 seconds. Note that not all JDBC drivers support a query timout.

SET TIMEOUT has no effect when run as a post-connect script.

Connection profiles can be managed through several SQL Workbench/J specific commands. They are primarily intended to be used in console mode, but can also be used when running in GUI mode.

SQL> WbDeleteProfile {MyGroup}/SQL Server
Do you really want to delete the profile '{MyGroup}/SQL Server'? (Yes/No) yes
Profile '{MyGroup}/SQL Server' deleted
SQL>

SQL> WbStoreProfile {MyGroup}/PostgreSQL Production
Profile '{MyGroup}/PostgreSQL Production' added
SQL> 

SQL> WbStoreProfile -name="{MyGroup}/DevelopmentServer" -savePassword=true
Profile '{MyGroup}/DevelopmentServer' added
SQL> 

SQL> WbCreateProfile -name="Postgres" -profileGroup=DBA -savePassword=true -username=postgres -password=secret
..> -url=jdbc:postgresql://localhost/postgres
..> -driver=org.postgresql.Driver
..> -driverJar=c:/etc/libs/postgres/postgresql-9.4-1206-jdbc42.jar;
Profile '{DBA}/Postgres' added
SQL> 

Some of the SQL Workbench/J specific commands can be abbreviated using the command syntax from PostgreSQL's command line client psql. This is only implemented for very few commands and most of them don't work exactly the same way as the PostgreSQL command.

The following commands are available:

Even though those commands look like the psql commands, they don't work exactly like them. Most importantly they don't accept the parameters that psql supports. Parameters need to be passed as if the regular SQL Workbench/J command had been used.

WbExport is designed to directly write the rows that are retrieved from the database to the export file without buffering them in memory (except for the XLS and XLSX formats)

Some JDBC drivers (e.g. PostgreSQL, jTDS and the Microsoft driver) read the full result obtained from the database into memory. In that case, exporting large results might still require a lot of memory. Please refer to the chapter Common problems for details on how to configure the individual drivers if this happens to you.

If you need to export data for Microsoft Excel, additional libraries are required to write the native Excel formats (xls and the new xlsx introduced with Office 2007). Exporting the "SpreadsheetML" format introduced with Office 2003 does not require additional libraries.

SQL Workbench/J supports three different Excel file formats:

For a comparison of the different Microsoft Office XML formats please refer to: http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats

You can download all required POI libraries as a single archive from the SQL Workbench/J home page: http://www.sql-workbench.net/poi-add-on3.zip. After downloading the archive, unzip it into the directory where sqlworkbench.jar is located.

	To write the file formats XLS and XLSX the entire file needs to be built in memory. When exporting results with a large number of rows this will require a substantial amount of memory.

The WbExport command supports compressing of the generated output files. This includes the "main" export file as well as any associated LOB files.

When using WbImport you can import the data stored in the archives without unpacking them. Simply specify the archive name with the -file parameter. SQL Workbench/J will detect that the input file is an archive and will extract the information "on the fly". Assume the following export command:

WbExport -type=text -file=/home/data/person.txt -compress=true -sourceTable=person;

This command will create the file /home/data/person.zip that will contain the specified person.txt. To import this export into the table employee, you can use the following command:

WbImport -type=text -file=/home/data/person.zip -table=employee;

Assuming the PERSON table had a BLOB colum (e.g. a picture of the person), the WbExport command would have created an additional file called person_blobs.zip that would contain all BLOB data. The WbImport command will automatically read the BLOB data from that archive.

In order to import Microsoft Excel (XSL, XSLT) or OpenOffice Calc (ODS) files, additional libraries are needed. For Excel the same libraries are needed as for exporting those formats. For OpenOffice additional libraries are needed. All needed libraries are included in the download bundle named with-office-libs.zip If you did not download that bundle, you can download the libraries needed for OpenOffice from here: http://www.sql-workbench.net/odf-add-on3.zip.

You can tell if the needed libraries are installed if you invoke the auto-completion after typing the -type= parameter. If the types XLS or ODS are presented in the drop down, the libraries installed.

The Excel import supports XLS and XLSX, it does not support the "SpreadsheetML" format.

	To import XLS or XLSX files, the entire file needs to be read into memory. When importing large files this will require a substantial amount of memory.

The WbImport command has the following syntax

Parameter	Description
-type	Possible values: xml, text, ods, xls Defines the type of the input file. This is only needed if the input file has a non-standard file extensions. If this parameter is not specified, the import type is derived from the input file's extension.
-mode	Defines how the data should be sent to the database. Possible values are 'insert', 'update', 'insert,update' and 'update,insert' For details please refer to the update mode explanation. For some DBMS, the additional modes: 'upsert' and 'insertIgnore' are supported. For details please refer to the native upsert and native insertIgnore explanation.
-file	Defines the full name of the input file. Alternatively you can also specify a directory (using -sourcedir) from which all files are imported.
-table	Defines the table into which the data should be imported This parameter is ignored, if the files are imported using the -sourcedir parameter This parameter supports auto-completion.
-sourceDir	Defines a directory which contains import files. All files from that directory will be imported. If this switch is used with text files and no target table is specified, then it is assumed that each filename (without the extension) defines the target table. If a target table is specified using the -table parameter, then all files will be imported into the same table. The -deleteTarget will be ignored if multiple files are imported into a single table.
-extension	When using the -sourcedir switch, the extension for the files can be defined. All files ending with the supplied value will be processed. (e.g. -extension=csv). The extension given is case-sensitive (i.e. TXT is something different than txt
-ignoreOwner	If the file names imported with from the directory specified with -sourceDir contain the owner (schema) information, this owner (schema) information can be ignored using this parameter. Otherwise the files might be imported into a wrong schema, or the target tables will not be found.
-excludeFiles	Using -excludeFiles, files from the source directory (when using -sourceDir) can be excluded from the import. The value for this parameter is a comma separated list of partial names. Each file that contains at least one of the values supplied in this parameter is ignored. -excludeFiles=back,data will exclude any file that contains the value back or data in it, e.g.: backup, to_back, log_data_store etc.
-checkDependencies	When importing more than one file (using the -sourcedir switch), into tables with foreign key constraints, this switch can be used to import the files in the correct order (child tables first). When -checkDependencies=true is passed, SQL Workbench/J will check the foreign key dependencies for all tables. Note that this will not check dependencies in the data. This means that e.g. the data for a self-referencing table (parent/child) will not be order so that it can be imported. To import self-referencing tables, the foreign key constraint should be set to "initially deferred" in order to postpone evaluation of the constraint until commit time.
-commitEvery	If your DBMS neeeds frequent commits to improve performance and reduce locking on the import table you can control the number of rows after which a COMMIT is sent to the server. -commitEveryis numeric value that defines the number of rows after which a COMMIT is sent to the DBMS. If this parameter is not passed (or a value of zero or lower), then the import is run as a single transaction that is committed at the end. When using batch import and your DBMS requires frequent commits to improve import performance, the -commitBatch option should be used instead. You can turn off the use of a commit or rollback during import completely by using the option -transactionControl=false. Using -commitEvery means, that in case of an error the already imported rows cannot be rolled back, leaving the data in a potential invalid state.
-transactionControl	Possible values: true, false Controls if SQL Workbench/J handles the transaction for the import, or if the import must be committed (or rolled back) manually. If -transactionControl=false is specified, SQL Workbench/J will neither send a COMMIT nor a ROLLBACK at the end. This can be used when multiple files need to be imported in a single transaction. This can be combined with the cleanup and error scripts in batch mode.
-continueOnError	Possible values: true, false This parameter controls the behavior when errors occur during the import. The default is true, meaning that the import will continue even if an error occurs during file parsing or updating the database. Set this parameter to false if you want to stop the import as soon as an error occurs. The default value for this parameter can be controlled in the settings file and it will be displayed if you run WbImport without any parameters. With PostgreSQL continueOnError will only work, if the use of savepoints is enabled using -useSavepoint=true.
-emptyFile	Possible values: ignore, warning, fail This parameter controls the behavior when an empty file (i.e. with a length of zero bytes) is used for the input file. ignore means the file is ignored, no warning will be shown or written to the log file. warning means the file is ignored, but a warning will be shown and logged. With fail an empty file will be treated as an error unless -continueOnError=true is specified. The default value is fail
-useSavepoint	Possible values: true, false Controls if SQL Workbench/J guards every insert or update statement with a savepoint to recover from individual error during import, when continueOnError is set to true. Using a savepoint for each DML statement can drastically reduce the performance of the import.
-keyColumns	Defines the key columns for the target table. This parameter is only necessary if import is running in UPDATE mode. It is assumed that the values for the key columns will never be NULL. This parameter is ignored if files are imported using the -sourcedir parameter.
-ignoreIdentitiyColumns	Possible values: true, false Controls if identity or auto-increment columns will be included in the import. If this is used, the JDBC driver must correctly report the column to be excluded as an AUTOINCREMENT column. This can be verified in the table definition display of the DbExplorer. If the column is reported with YES for the AUTOINCREMENT property, then this column will be excluded during the import.
-schema	Defines the schema into which the data should be imported. This is necessary for DBMS that support schemas, and you want to import the data into a different schema, then the current one.
-encoding	Defines the encoding of the input file (and possible CLOB files) If auto-completion is invoked for this parameter, it will show a list of encodings defined through the configuration property workbench.export.defaultencodings This is a comma-separated list that can be changed using WbSetConfig
-deleteTarget	Possible values: true, false If this parameter is set to true, data from the target table will be deleted (using DELETE FROM ...) before the import is started. This parameter will only be used if -mode=insert is specified. This parameter is ignored for spreadsheet imports.
-truncateTable	Possible values: true, false This is essentially the same as -deleteTarget, but will use the command TRUNCATE to delete the contents of the table. For those DBMS that support this command, deleting rows is usually faster compared to the DELETE command, but it cannot be rolled back. This parameter will only be used if -mode=insert is specified.
-batchSize	A numeric value that defines the size of the batch queue. Any value greater than 1 will enable batch mode. If the JDBC driver supports this, the INSERT (or UPDATE) performance can be increased drastically. This parameter will be ignored if the driver does not support batch updates or if the mode is not UPDATE or INSERT (i.e. if -mode=update,insert or -mode=insert,update is used).
-commitBatch	Possible values: true, false If using batch execution (by specifying a batch size using the -batchSize parameter) each batch will be committed when this parameter is set to true. This is slightly different to using -commitEvery with the value of the -batchSize parameter. The latter one will add a COMMIT statement to the batch queue, rather than calling the JDBC commit() method. Some drivers do not allow to add different statements in a batch queue. So, if a frequent COMMIT is needed, this parameter should be used. When you specify -commitBatch the parameter -commitEvery will be ignored. If no batch size is given (using -batchSize, then -commitBatch will also be ignored.
-updateWhere	When using update mode an additional WHERE clause can be specified to limit the rows that are updated. The value of the -updatewhere parameter will be added to the generated UPDATE statement. If the value starts with the keyword AND or OR the value will be added without further changes, otherwise the value will be added as an AND clause enclosed in brackets. This parameter will be ignored if update mode is not active.
-startRow	A numeric value to define the first row to be imported. Any row before the specified row will be ignored. The header row is not counted to determine the row number. For a text file with a header row, the physical line 2 is row 1 (one) for this parameter. When importing text files, empty lines in the input file are silently ignored and do not add to the count of rows for this parameter. So if your input file has two lines to be ignored, then one empty line and then another line to be ignored, startRow must be set to 4.
-endRow	A numeric value to define the last row to be imported. The import will be stopped after this row has been imported. When you specify -startRow=10 and -endRow=20 11 rows will be imported (i.e. rows 10 to 20). If this is a text file import with a header row, this would correspond to the physical lines 11 to 21 in the input file as the header row is not counted.
-columnFilter	This defines a filter on column level that selects only certain rows from the input file to be sent to the database. The filter has to be defined as column1="regex",column2="regex". Only Rows matching all of the supplied regular expressions will be included by the import. This parameter is ignored when the -sourcedir parameter is used.
-badFile	Possible values: true, false If -continueOnError=true is used, you can specify a file to which rejected rows are written. If the provided filename denotes a directory a file with the name of the import table will be created in that directory. When doing multi-table inserts you have to specify a directory name. If a file with that name exists it will be deleted when the import for the table is started. The fill will not be created unless at least one record is rejected during the import. The file will be created with the same encoding as indicated for the input file(s).
-maxLength	With the parameter -maxLength you can truncate data for character columns (VARCHAR, CHAR) during import. This can be used to import data into columns that are not big enough (e.g. VARCHAR columns) to hold all values from the input file and to ensure the import can finish without errors. The parameter defines the maximum length for certain columns using the following format: -maxLength='firstname=30,lastname=20' Where firstname and lastname are columns from the target table. The above example will limit the values for the column firstname to 30 characters and the values for the column lastname to 20 characters. If a non-character column is specified this is ignored. Note that you have quote the parameter's value in order to be able to use the "embedded" equals sign.
-booleanToNumber	Possible values: true, false In case you are importing a boolean column (containing "true", "false") into a numeric column in the target DBMS, SQL Workbench/J will automatically convert the literal true to the numeric value 1 (one) and the literal false to the numeric value 0 (zero). If you do not want this automatic conversion, you have to specify -booleanToNumber=false for the import. The default values for the true/false literals can be overwritten with the -literalsFalse and -literalsTrue switches. To store different values than 0/1 in the target column, use the parameters -numericFalse and -numericTrue This parameter is ignored for spreadsheet imports
-numericFalse -numericTrue	These parameters control the conversion of boolean literals into numbers. If these parameters are used, any text input that is identified as a "false" literal, will be stored with the number specified with -numericFalse. Any text input that is identified as "true" will be stored as the number specified with -numericFalse. To use -1 for false and 1 for true, use the following parameters: -numericFalse='-1' -numericTrue='1'. Note that '-1' must be quoted due to the dash. If these parameters are used, -booleanToNumber will be assumed true implicitely. These parameters can be combined with -literalsFalse and -listeralsTrue. Please note: This conversion is only applied for "text" input values. Valid numbers in the input file will not be converted to the values specified with -numericFalse or -numericTrue. This means that you cannot change a 0 (zero) in the input file into a -1 in the target column. This parameter is ignored for spreadsheet imports
-literalsFalse -literalsTrue	These parameters control the conversion of boolean literals into boolean values. These two switches define the text values that represent the (boolean) values false and true in the input file. This conversion is applied when storing the data in a column that is of type boolean in the database. The value to these switches is a comma separated list of literals that should be treated as the specified value, e.g.: -literalsFalse='false,0' -literalsTrue='true,1' will define the most commonly used values for true/false. Please note: The definition of the literals is case sensitive! You always have to specify both switches, otherwise the definition will be ignored This parameter is ignored for spreadsheet imports
-constantValues	With this parameter you can supply constant values for one or more columns that will be used when inserting new rows into the database. The constant values will only be used when inserting rows (e.g. using -mode=insert) The format of the values is -constantValues="column1=value1,column2=value2". The parameter can be repeated multiple times, to make quoting easier: -constantValues="column1=value1" -constantValues="column2=value2" The values will be converted by the same rules as the input values from the input file. If the value for a character column is enclosed in single quotes, these will be removed from the value before sending it to the database. To include single quotes at the start or end of the input value you need to use two single quotes, e.g.-constantValues="name=''Quoted'',title='with space'" For the field name the value 'Quoted' will be sent to the database. for the field title the value with space will be sent to the database. To specify a function call to be executed, enclose the function call in ${...}, e.g. ${mysequence.nextval} or ${myfunc()}. The supplied function will be put into the VALUES part of the INSERT statement without further checking (after removing the ${ and } characters, of course). So make sure that the syntax is valid for your DBMS. If you do need to store a literal like ${some.value} into the database, you need to quote it: -constantValues="varname='${some.value}'". You can also specify a SELECT statement that retrieves information from the database based on values from the input file. This is useful when the input file contains e.g. values from a lookup table (but not the primary key from the lookup table). The syntax to specify a SELECT statement is similar to a function call: -constantValues="$@{SELECT type_id FROM type_definition WHERE type_name = $4" where $4 references the fourth column from the input file. The first column is $1 (not $0). The parameter for the SELECT statement do not need to be quoted as internally a prepared statement is used. However the values in the input file must be convertible by the JDBC driver. In addition to the function call or SELECT statements, WbImport provides three variables that can be used to access the name of the currently imported file. This can be used to store the source file of the data in the target table. The following three variables are supported _wb_import_file_path this contains the full path and file name of the current import file _wb_import_file_name this contains only the file name (without the path) _wb_import_file_dir this contains only the path of the file without the filename (and without the extension) Please refer to the examples for more details on the usage.
-insertSQL	Define the statement to be used for inserting rows. This can be used to use hints or customize the generated INSERT statement. The parameter may only contain the INSERT INTO part of the statement (i.e. INSERT INTO is the default if nothing is specified). This can be used to pass special hints to the database, e.g. to specify an append hint for Oracle: You have to quote the parameter value using single quotes, otherwise comments will be removed from the SQL statement! -insertSQL='INSERT /*+ append */ INTO'
-adjustSequences	Possible values: true, false For DBMS that support sequences which are associated with a column, this parameter can be used to adjust the next value for the sequence to the maximum value of the imported data. This can also be used to synchronize identity columns for DBMS that allow overriding the generated values. Currently this is implemented for PostgreSQL, DB2 (LUW), H2 Database and HyperSQL (aka HSQLDB).
-preTableStatement -postTableStatement	This parameter defines a SQL statement that should be executed before the import process starts inserting data into the target table. The name of the current table (when e.g. importing a whole directory) can be referenced using ${table.name}. To define a statement that should be executed after all rows have been inserted and have been committed, you can use the -postTableStatement parameter. These parameters can e.g. be used to enable identity insert for MS SQL Server: -preTableStatement="set identity_insert ${table.name} on" -postTableStatement="set identity_insert ${table.name} off" Errors resulting from executing these statements will be ignored. If you want to abort the import in that case you can specify -ignorePrePostErrors=false and -continueOnError=false. These statements are only used if more than one table is processed.
-runTableStatementOnError	Possible values: true, false Controls the execution of the post-table statement in case an error occurred while importing the data. By default the post-table statement is executed even if the import was not successful. If this is should not happen, use -runTableStatementOnError=false.
-ignorePrePostErrors	Possible values: true, false Controls handling of errors for the SQL statements defined through the -preTableStatement and -postTableStatement parameters. If this is set to true (the default), errors resulting from executing the supplied statements are ignored. If set to false then error handling depends on the parameter -continueOnError.
-showProgress	Valid values: true, false, <numeric value> Control the update frequence in the status bar (when running in GUI mode). The default is every 10th row is reported. To disable the display of the progress specify a value of 0 (zero) or the value false. true will set the progress interval to 1 (one).

The XML import only works with files generated by the WbExport command.

Both spreadsheet imports (Microsoft Excel, OpenOffice) support a subset of the parameters that are used for flat file imports.

These parameters are:

The spreadsheet import does not support specifying a date or timestamp format. It is expected that those columns are formatted in such a way that they can be identified as date or timestamps.

The spreadsheet import also does not support importing BLOB files that are referenced from within the spreadsheet. If you want to import this kind of data, you need to convert the spreadsheet into a text file.

The spreadsheet import supports one additional parameter that is not available for the text imports:

The -mode parameter controls the way the data is sent to the database. The default is INSERT. SQL Workbench/J will generate an INSERT statement for each record. If the INSERT fails no further processing takes place for that record.

If -mode is set to UPDATE, SQL Workbench/J will generate an UPDATE statement for each row. In order for this to work, the table needs to have a primary key defined, and all columns of the primary key need to be present in the import file. Otherwise the generated UPDATE statement will modify rows that should not be modified. This can be used to update existing data in the database based on the data from the export file.

To either update or insert data into the table, both keywords can be specified for the -mode parameter. The order in which they appear as the parameter value, defines the order in which the respective statements are sent to the database. If the first statement fails, the second will be executed. For -mode=insert,update to work properly a primary or unique key has to be defined on the table. SQL Workbench/J will catch any exception (=error) when inserting a record, then it will try updating the record, based on the specified key columns. The -mode=update,insert works the other way. First SQL Workbench/J will try to update the record based on the primary keys. If the DBMS signals that no rows have been updated, it is assumed that the row does not exist and the record will be inserted into the table. This mode is recommended when no primary or unique key is defined on the table, and an INSERT would always succeed.

The keycolumns defined with the -keycolumns parameter don't have to match the real primary key, but they should identify one row uniquely.

You cannot use the update mode, if the tables in question only consist of key columns (or if only key columns are specified). The values from the source are used to build up the WHERE clause for the UPDATE statement.