7. Editing SQL Statements

7.1. Editing files
7.2. Working with bookmarks
7.3. Code completion
7.4. Show hints for INSERT statements
7.5. Customizing keyword highlighting
7.6. Reformat SQL
7.7. Create SQL value lists
7.8. Programming related editor functions

7.1. Editing files

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

[Note]

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.

7.2. Working with bookmarks

7.2.1. Defining bookmarks

A bookmark inside the editor is defined by adding the keyword @WbTag followed by the name of the bookmark into a SQL comment:

-- @WbTag delete everything
  truncate table orders,order_line,customers;
  commit;
      

The keyword is not case sensitive, @wbtag will work just as wel as @WBTAG, or @WbTag. A multiline comment can be used as well as a single line comment.

The annotations for naming a result can additionally be included in the bookmark list. This is enabled in the options panel for the editor.

7.2.2. Jumping to a bookmark

To jump to a bookmark select ToolsBookmarks. A dialog box with all defined bookmarks will be displayed. You can filter the list of displayed bookmarks by entering a value in the input field. Depending on the option Filter on name only the value will either be compared only against the bookmark name. If that option is disabled then the bookmark name and the name of the SQL tab will be checked for the entered value.

[Note]

The selection in the bookmark list can be moved with the UP/DOWN keys even when the cursor is located in the filter input field.

If the option Only for current tab is enabled, then the dialog will open showing only bookmarks for the current tab.

7.2.3. Configuring the display of the bookmark list

There are two options to influence how the list of bookmarks is displayed. Both options are available when displaying the context menu for the list header (usually through a click with the right mouse button):

  • Remember column widths - if this is enabled, the columns are not automatically adjusted to the width of the content. Instead the list remembers whatever width was adjusted.
  • Remember sort order - by default the list is sorted by the name of the bookmark. When this option is selected, whatever sort order is selected (by clicking on the column headers) will be saved and restored the next time the dialog is opened.

7.3. Code completion

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

7.4. Show hints for INSERT statements

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

7.5. Customizing keyword highlighting

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

7.6. Reformat SQL

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.

7.7. Create SQL value lists

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 SQLCode toolsCreate 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 SQLCode toolsCreate 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.

7.8. Programming related editor functions

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

7.8.1. Copy Code Snippet

Suppose you have created the SQL statement that you wish to use inside your application to access your DBMS. The menu item SQLCode toolsCopy Code Snippet 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 in you workbench.settings file. 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.

7.8.2. Clean Java code

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 SQLCode toolsClean Java Code 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;

7.8.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 SQLSettingsDetect 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 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 SELECT, INSERT, UPDATE and DELETE statements.

[Note]

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:

Drivers known to not support this feature:

  • Oracle 11g driver (ojdbc6.jar, ojdbc7.jar)
  • Microsoft SQL Server 2000/2005 driver (sqljdbc4.jar)