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)