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.