There are three different ways to execute a SQL command

Execute the selected text

When you press Ctrl-E or select SQL → Execute 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 SQL → Execute 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 SQL → Settings → Auto 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 SQL → Execute 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.

12.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 SQL → Editor history back statement from the menu. Once the previous statement(s) have been recalled the next statement can be shown using Alt-Right or choosing SQL → Editor history next 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 SQL → Clear history

	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.

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.

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 (line breaks) 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.

There are two ways to assign a name to the result tab of a query:

By default results are displayed in a scrollable table. You can enable the display of results as text. In that case the results will be displayed in the messages area, formatted in the same way as the console mode. This display can be enabled through SQL → Settings → Show results as text or through the annotation @WbResultAsText.

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 appropriate 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

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.

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

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.

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$

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 set serveroutput on 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

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.

Due to the way the JDBC API works, the messages are only show after the statement has finished (this is different to e.g. SQL Server Management Studio where the messages are displayed as soon as PRINT is called, even when the overall script or procedure is still running.

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 <support@sql-workbench.net>.

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.

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 Data → Filter 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:

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.

SQL Workbench/J can import tab separated text files into the current result set. This means, that you need to issue the appropriate 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 Data → Import file

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

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.

You can import the contents of the clipboard into the current result, if the format matches the result set. When you select Data → Import 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

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.