9. Using SQL Workbench/J

9.1. Displaying help
9.2. Resizing windows
9.3. Executing SQL statements
9.4. Displaying results
9.5. Dealing with BLOB and CLOB columns
9.6. Performance tuning when executing SQL
9.7. Using workspaces
9.8. Saving and loading SQL scripts
9.9. Displaying the structure of tables
9.10. Viewing server messages
9.11. Editing data
9.12. Deleting rows from the result
9.13. Sorting the result
9.14. Filtering the result
9.15. Running stored procedures
9.16. Export result data
9.17. Copy data to the clipboard
9.18. Import data into the result set

9.1. Displaying help

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 HelpContents 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 ToolsOption.

The PDF manual can be displayed by selecting HelpManual. In order to be able to display the PDF manual, you need to define the path to the executable for the PDF reader in the General options 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 HelpDBMS 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.

9.2. Resizing windows

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.

9.3. Executing SQL statements

9.3.1. Control the statement to be executed

There are three different ways to execute a SQL command

Execute the selected text

When you press Ctrl-E or select SQLExecute selected the currently selected text will be send to the DBMS for execution. If no text is selected the complete contents of the editor will be send to the database.

Execute current statement

When you press Ctrl-Enter or select SQLExecute current the current statement will be executed. The "current" statement will be the text between the next delimiter before the current cursor position and the delimiter after the cursor position.

Example (| indicating the cursor position)

SELECT firstname, lastname FROM person;

DELETE FROM person| WHERE lastname = 'Dent';
COMMIT;

When pressing Ctrl-Enter the DELETE statement will be exectuted

You can configure the editor to use the statement that is defined by the current line rather than the cursor location when using Execute current.

Consider the following editor contents:

SELECT firstname, lastname FROM person; |
DELETE FROM person WHERE lastname = 'Dent';
COMMIT;

If the option to use the current line is disabled and the cursor is located after the semicolon in the third line, Execute current will execute the SELECT statement because the cursor is logically located in the statement after the select.

If that option is enabled and the cursor is located after the semicolon in the third line, Execute current will execute the DELETE statement because the statement in the current line is the select statement. If there are multiple SQL statements in the current line, the first statement will be executed.

You can configure SQL Workbench/J to automatically jump to the next statement, after executing the current statement. Simply select SQLSettingsAuto advance to next The check mark next to the menu item indicates if this option is enabled. This option can also be changed through the Options dialog

Execute All

If you want to execute the complete text in the editor regardless of the current selection, use the Execute all command. Either by pressing Ctrl-Shift-E or selecting SQLExecute All

When executing all statements in the editor you have to delimit each statement, so that SQL Workbench/J can identify each statement. If your statements are not delimited using a semicolon, the whole editor text is sent as a single statement to the database. Some DBMS support this (e.g. Microsoft SQL Server), but most DBMS will throw an error in that case.

A script with two statements could look like this:

UPDATE person SET numheads = 2 WHERE name='Beeblebrox';
COMMIT;

or:

DELETE FROM person;
DELETE FROM address;
COMMIT;

INSERT INTO person
(id, firstname, lastname)
VALUES
(1, 'Arthur', 'Dent');

INSERT INTO person
(id, firstname, lastname)
VALUES
(4, 'Mary', 'Moviestar');

INSERT INTO person
(id, firstname, lastname)
VALUES
(2, 'Zaphod', 'Beeblebrox');

INSERT INTO person
(id, firstname, lastname)
VALUES
(3, 'Tricia', 'McMillian');

COMMIT;

You can specifiy an alternate delimiter that can be used instead of the semicolon. See the description of the alternate delimiter for details. This is also needed when running DDL scripts (e.g. for stored procedures) that contain semicolons that should not delimit the statements.

As long as at least one statement is running the title of the main window will be prefixed with the » sign. Even if the main window is minimized you can still see if a statement is running by looking at the window title.

You can use variables in your SQL statements that are replaced when the statement is executed. Details on how to use variables can be found in the chapter Variable substitution.

JDBC drivers do not support multi-threaded execution of statements on the same physical connection. If you want to run two statements at the same time, you will need to enable the Separate connection per tab option in your connection profile. In this case SQL Workbench/J will open a physical connection for each SQL tab, so that statements in the different tabs can run concurrently.

9.3.1.1. Statement history

When executing a statement the contents of the editor is put into an internal buffer together with the information about the text selection and the cursor position. Even when you select a part of the current text and execute that statement, the whole text is stored in the history buffer together with the selection information. When you select and execute different parts of the text and then move through the history you will see the selection change for each history entry.

The previous statement can be recalled by pressing Alt-Left or choosing SQLPrevious Statement statement from the menu. Once the previous statement(s) have been recalled the next statement can be shown using Alt-Right or choosing SQLNext Statement from the menu. This is similar to browsing through the history of a web browser.

You can clear the statement history for the current tab, but selecting SQLClear history

[Note]

When you clear the content of the editor (e.g. by selecting the whole text and then pressing the Del key) this will not clear the statement history. When you load the associated workspace the next time, the editor will automatically display the last statement from the history. You need to manually clear the statement history, if you want an empty editor the next time you load the workspace.

9.4. Displaying results

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 (labelled "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.

[Caution]

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 SQLAppend 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 DataClose result

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

9.4.1. Limiting result sizes

To prevent retrieving an large amount of rows (and possibly running out of memory), the maximum number of rows that are retrieved can be defined for each SQL panel in the "Max. Rows" input field of the status bar. This value will be stored in the workspace that is associated with the connection profile.

A default value that will be used for newly opened SQL tabs can be defined in the options dialog.

9.4.2. Displaying values with embedded newlines

Data from VARCHAR or CHAR columns is displayed as a single-line if the column's max. size is below 250 characters. If you have data in smaller columns that contains newlines (linebreaks) and you want to display directly in the result set, please adjust the limit to match your needs. The limit can be changed in the Data Display Options.

9.4.3. Naming result tabs

You can change the name of the result tab associated with a statement. To give a result set a name you have to provide a comment before the SQL statement that contains the keyword @WbResult followed by a whitespace and then the name that should appear as the result's name. The keywords must be specified in lowercase!

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;

As you can see, you can put the @wbresult keyword into a single-line or multi-line comment. The name that is used, will be everything after the 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.

You can automatically use the first table name from a SELECT statement to be used for the result name in the Data display options.

Re-using an existing named result tab

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.

[Note]
Re-using a result tab only works if SQLAppend new results is enabled.

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;

9.5. Dealing with BLOB and CLOB columns

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.

[Note]

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.

9.5.1. Updating BLOB data through SQL

To update a BLOB (or binary) column, use the placeholder {$blobfile=path_to_file} in the place where the actual value has to occur in the INSERT or UPDATE statement:

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

SQL Workbench/J will rewrite the UPDATE statement and send the contents of the file located in c:/data/image.bmp to the database. The syntax for inserting BLOB data is similar. Note that some DBMS might not allow you to supply a value for the blob column during an insert. In this case you need to first insert the row without the blob column, then use an UPDATE to send the blob data. You should make sure to update only one row by specifying an approriate WHERE clause.

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

This will create a new record with id=42 and the content of c:/data/image.bmp in the column blob_col

9.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).

[Note]

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.

9.5.3. Saving BLOB data to a file using SQL

To save the data stored in a BLOB column, the command WbSelectBlob can be used. The syntax of this command is similar to the regular SELECT command except that a target file has to be specified where the read data should be stored.

You can also use the WbExport command to export data. The contents of the BLOB columns will be saved into separate files. This works for both export formats (XML and Text).

9.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 dropdown. To open the BLOB content with one of the tools, select the tool from the dropdown list, then click on the button Open with next to the external tools dropdown. SQL Workbench/J will then retrieve the BLOB data from the server, store it in a temporary file on your harddisk, 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)

[Note]

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.

9.6. Performance tuning when executing SQL

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.

9.7. Using workspaces

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 WorkspaceSave Workspace as....

The default file extension for workspaces is wksp.

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

An existing workspace can be loaded with WorkspaceLoad 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.

9.8. Saving and loading SQL scripts

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

[Note]

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.

9.9. Displaying the structure of tables

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

9.10. Viewing server messages

9.10.1. PostgreSQL

PostgreSQL supports sending of messages to the client using the RAISE statement in PL/pgSQL. The following function will display a result set (with the number 42) and the message area will contain the message Thinking hard...

CREATE OR REPLACE FUNCTION the_answer()
  RETURNS integer
  LANGUAGE plpgsql
AS
$body$
BEGIN
  RAISE NOTICE 'Thinking hard...';
  RETURN 42;
END;
$body$
/

9.10.2. Oracle

For Oracle the DBMS_OUTPUT package is supported. Support for this package can be turned on with the ENABLEOUT command. If this support is not turned on, the messages will not be displayed. This is the same as using the SET SERVEROUTPUT ON command in SQL*Plus.

If you want to turn on support for DBMS_OUTPUT automatically when connecting to an Oracle database, you can put the ENABLEOUT command into the pre-connect script.

Any message "printed" with DBMS_OUTPUT.put_line() will be displayed in the message part after the SQL command has finished. Please refer to the Oracle documentation if you want to learn more about the DBMS_OUTPUT package.

dbms_output.put_line("The answer is 42");

Once the command has finished, the following will be displayed in the Messages tab.

The answer is 42

9.10.3. MS SQL Server

For MS SQL Server, any message written with the PRINT command will be displayed in the Messages tab after the SQL command has finished. The PRINT command is usually used in stored procedures for logging purposes, but it can also be used as a command on its own:

PRINT "Deleting records...";
DELETE from my_table WHERE value = 42;
PRINT "Done."

This will execute the DELETE. Once this script has finished, the Messages tab will contain the text:

Deleting records...
Done.

9.10.4. Other database systems

If your DBMS supports something similar, please let me know. I will try to implement it - provided I have free access to the DBMS. Please send your request to .

9.11. Editing data

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 columns are found, the JDBC driver is asked for a best row identifier. If that doesn't return any information, your defined PK Mapping will be queried. If still no PK columns can be found, you will be prompted to select the key columns based on the current result set.

[Important]

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

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

If your SELECT was based on more than one table, you will be prompted to specify which table should be updated. Only columns for the chosen table will be included in the UPDATE or INSERT statements. If no primary 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 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.

[Note]

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.

9.12. Deleting rows from the result

To delete a row from the result, select DataDelete 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.

9.13. Sorting the result

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.

9.14. Filtering the result

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.

[Note]
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.

9.14.1. Defining a filter manually

To define a filter, click on the Filter button () in the toolbar or select DataFilter 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 dropdown. If you select * for the column, the filter condition will be applied to all columns of the result set.

[Important]
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.

9.14.2. Defining a filter from the selection

You can also quickly filter the data based on the value(s) of the currenlty selected column(s). To apply the filter, select the column values by which you want to filter then click on the Quickfilter button () in the toolbar or select DataFilter by value from the menu bar.

Using the Alt key you can select individual columns of one or more rows. Together with the Ctrl key you can select e.g. the first, third and fourth column. You can also select the e.g. second column of the first, second and fifth row.

Whether the quick filter is available depends on the selected rows and columns. It will be enabled when:

  • You have selected one or more columns in a single row
  • You have selected one column in multiple rows

If only a single row is selected, the quick filter will use the values of the selected columns combined with AND to define the filter (e.g. username = 'Bob' AND job = 'Clerk'). Which columns are used depends on the way you select the row and columns. If the whole row in the result is selected, the quick filter will use the value of the focused column (the one with the yellow rectangle), otherwise the individually selected columns will be used.

If you select a single column in multiple rows, this will create a filter for that column, but with the values will be combined with OR (e.g. name = 'Dent' OR name = 'Prefect'). The quick filter will not be available if you select more than one column in multiple rows.

Once you have applied a quick filter, you can use the regular filter definition dialog to check the definition of the filter or to further modify it.

9.15. Running stored procedures

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.

9.16. Export result data

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

  • HTML
  • SQL statements (INSERT, UPDATE or DELETE & INSERT)
  • XML format
  • Tab separated text file. Columns are separated with a tab, rows are separated with a newline character
  • Spreadsheet Format (OpenDocument, Microsoft Excel)

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 DataSave 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 SQLExport query result to export the result of the currently selected SQL statement.

9.17. Copy data to the clipboard

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

  • Text (tab separated)

    This will use a tab as the column separator, and will not quote any values. The end-of-line sequence will be a newline (Unix style) and the column headers will be part of the copied data. Special characters (e.g. newlines) in the actual data will not be replaced (as it is possible with the WbExport command).

    When you hold down the Shift key when you select the menu item, the column headers will not be copied to the clipboard. When you hold down the Ctrl key when selecting the menu item, you can choose which columns should be copied to the clipboard. Pressing Shift and Ctrl together is also supported.

  • SQL (INSERT, UPDATE, or DELETE & INSERT)

    The end-of-line sequence will be a newline (Unix style). No cleanup of data will be done as it is possible with the WbExport command, apart from correctly quoting single quotes inside the values (which is required to generate valid SQL)

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 DataDefine key columns

[Note]

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.

9.18. Import data into the result set

9.18.1. Import a file into the current result set

SQL Workbench/J can import tab separated text files into the current result set. This means, that you need to issue the approriate SELECT statement first. The structure of the file has to match the structure of the result set, otherwise an error will occur. To initiate the import select DataImport file

When selecting the file, you can change some parameters for the import:

OptionDescription
Headerif this option this is checked, the first line of the import file will be ignored
Delimiterthe delimiter used to separate column values. Enter \t for the tab character
Date Format The format in which date fields are specified.
Decimal char The character that is used to indicate the decimals in numeric values (typically a dot or a comma)
Quote char The character used to quote values with special characters. Make sure that each opening quote is followed by a closing quote in your text file.

You can also import text and XML files using the WbImport command. Using the WbImport command is the recommended way to import data, as it is much more flexible, and - more important - it does not read the data into memory.

9.18.2. Import the clipboard into the current result

You can import the contents of the clipboard into the current result, if the format matches the result set. When you select DataImport from Clipboard SQL Workbench/J will check if the current clipboard contents can be imported into the current result. The data can automatically be imported if the first row of the data contains the column names. One of the following two conditions must be true in order for the import to succeed

  • The columns are delimited with a tab character and the first row contains column names. All matching columns will then be imported
  • If no column name matches (i.e. no header row is present) but the number of columns (identified by the number of tab characters in the first row) is identical to the number of columns in the current result.

If SQL Workbench/J cannot identify the format of the clipboard a dialog will be opened where you can specify the format of the clipboard contents. This is mainly necessary if the delimiter is not the tab character. You can manually open that dialog, by holding down the Ctrl key when clicking on the menu item.