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→ or right click on the tab's label and choose 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.
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
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
PERSON table (because the cursor is located after the
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
FROM part of the statement. The behaviour 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
(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.
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 tooltip 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 tooltip containing the text
'hello' as that is the value that corresponds to column1. When the cursor is located at the number
pressing Ctrl-# will show the text
column3 in the tooltip.
When no matching column or value can be found, the tooltip will contain a hint that the "other" element is missing.
If the values inserted are the result of a
SELECT statement, the tooltip 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),
datatypes (e.g. VARCHAR), functions (e.g. upper()) and operators (e.g. JOIN). Each keyword type
is read from a separate file:
The files contain one keyword per line. Case does not matter (
select are treated identically).
If you want to add a specific word to the list of global keywords, simply create a plain
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.
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
geometryThe 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-
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 →
the selected text will be converted into a format that can be used as an expression for an
Dent Beeblebrox Prefect Trillian Marvin
will be converted to:
('Dent', 'Beeblebrox', 'Trillian', 'Prefect', 'Marvin')
The function→ 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).
Suppose you have created the SQL statement that you wish to use inside your application to access your DBMS. The menu item→ will create a piece of code that defines a String variable which contains the current SQL statement (or the currently selected statement if any text is selected).
If you have the following SQL statement in your editor:
SELECT p.name, p.firstname, a.street, a.zipcode, a.phone FROM person p, address a WHERE p.person_id = a.person_id;
When copying the code snippet, the following text will be placed into the clipboard
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";
You can now paste this code into your application.
If you don't like the
\n character in
your code, you can disable the generation of the newline characters
See Manual settings
for details. You can also customize the prefix (
String sql =) and
the concatenation character that is used,
in order to support the programming language that you use.
When using the Copy Code Snippet feature during development, the SQL statement usually needs refinement after testing the Java class. You can Copy & Paste the generated Java code into SQL Workbench/J, then when you select the pasted text, and call → the selected text will be "cleaned" from the Java stuff around it. The algorithm behind that is as follows: remove everything up to the first " at the beginning of the line. Delete everything up to the first " searching backwards from the end of the line. Any trailing white-space including escaped characters such as \n will be removed as well. Lines starting with // will be converted to SQL single line comments starting with -- (keeping existing quotes!). The following code:
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"
will be converted to:
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;
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 →
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 presevered, 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
This feature requires that the getParameterCount() and
methods of the
The following drivers have been found to support (at least partially) this feature:
Drivers known to not support this feature: