SQL Editor

• Introduction
• Editor Area
• Editor Features
  • Syntax Color Coding
  • Charsets and Fonts
  • Loading and Saving Scripts
    • Load Recent
    • Quick File Open
  • Drag and Drop a File
  • Drag and Drop Database Objects
  • Loading and Saving Bookmarks and Monitors
  • Navigating Between History Entries
  • Menu Operations
  • Key Bindings
  • SQL Formatting
  • Auto Completion
  • Recording and Playing Edit Macros
  • Folding Selected Text
  • Selecting a Rectangular Area

Introduction

The SQL Commander contains the SQL Editor, used to edit SQL scripts. In this section, you can read about the editor features, while the SQL Commander section describes how to work with multiple editors open at the same time, execute scripts and process the results.

Editor Area

The editor area looks like this:

Figure: SQL Editor Area

Above the editor is a toolbar with buttons related both to execution of scripts and to editing. The editing related buttons are covered below.

The left margin shows the line numbers.

Below the editor, you see a Status Bar. The first field shows the current caret position in the format:

<line>:<column> [<position from top>] 

The last figure, within square brackets, is the caret position from the top. This can be useful when you get an error message executing a script that contains this information rather than a line/column location.

The next field in the Status Bar shows INS if characters you type will be inserted at the caret position or OVR if they will overwrite the current text at the caret position. You can toggle this mode using the Toggle Typing Mode key binding, described in the Key Bindings section.

The next field shown in the screenshot is only visible when working with macros, described in the Recording and Playing Edit Macros
section.

Next comes the Auto Commit Status field, showing whether Auto Commit is enabled. You can read more about that in the the SQL Commander section.

The last two fields show information about the file loaded in the editor, if any. First the character encoding and then the filename. If you just type into the editor without loading a file, the filename "Untitled" is shown instead. An asterisk after the filename indicates that there are unsaved edits.

Editor Features

The SQL Editor is like any editor you're used to when it comes to typing, scrolling etc. But it also offers additional features to help you specifically with editing SQL scripts. These are described in the following sections.

Syntax Color Coding

An SQL script consists of keywords, operators, object identifiers, quoted text, etc. It may also contain comments. To make it easier to see at a glance what is what, the SQL Editor displays words using different font styles depending on their classification. For instance, keywords are displayed with a bold blue font, while quoted text is displayed with a regular type red font. You can change how to display the different kinds of words, as well as the editor background color, in the Tool Properties dialog, in the Appearance->Fonts category.

Figure: Font and SQL Editor Styles settings in Tool Properties

Charsets and Fonts

You can also change the SQL Editor font family, which is useful and necessary in order to display characters for languages like Chinese, Japanese, etc.

Figure: SQL Editor with another font

Open Tool Properties and select the Appearance->Fonts category to set the font for the SQL Editor.

Displaying data correctly is not just a matter of setting the font, because the character encoding on the client side (in which DbVisualizer runs) and in the database server may not be compatible.  Most JDBC drivers provide properties for mapping between the encodings used by the client and the server, see the documentation for your JDBC driver for details.

Loading and Saving Scripts

The SQL editor supports loading statements from a file and saving the content of the editor to a file. Use the standard file operations, Open, Save and Save As in the File main menu or the toolbar to accomplish this. Loading a file always loads it into the currently selected editor.

Figure: Loading a file into the SQL Commander

The name of the loaded file is listed in the status bar of the editor, with the full file path shown in the window title. The editor tracks any modifications and indicates changes with an asterisk (*) after the filename.

When you exit DbVisualizer, you are asked what to do if there are any pending edits that need to be saved.

Load Recent

The File->Open Recent sub menu lists the recently loaded files. When you choose an entry, the file is opened in the current SQL editor.

Figure: Open Recent Files menu

Quick File Open

You can also use the Quick File Open feature to open recent files as well as Bookmarks and History entries. By default, it is bound to the Ctrl+Alt+O key combination, and is also available via a toolbar button in the SQL Editor as well as in the main File->Quick File Open menu.

Figure: Quick File Open dialog

Drag and Drop a File

Another way to load a file is to select it in the platform's file browser and drop it into the editor. The file content replaces the current content of the editor.

Drag and Drop Database Objects

If you want to include the name of an object shown in the database objects tree, you can select the node and drop it in the text where you want the name inserted.

Loading and Saving Bookmarks and Monitors

Bookmarks and Monitors are also files, but with special meaning. See the Bookmarks and History and Monitors and Charts sections for how to create and edit them in the SQL Editor.

Navigating Between History Entries

When you execute a script, DbVisualizer saves it as a history entry, see the Bookmarks and History section for details. You can use the Previous and Next buttons in the editor toolbar to navigate between (load) these entries.

Figure: Next and Previous buttons

Menu Operations

In the editor's right-click menu, you find a number of editing related operations:


Some of the same operations are also available via the toolbar and the Edit and SQL main window menus.

Key Bindings

The editor shortcuts, or key bindings, can be redefined in the Tool Properties Key Bindings category. Select the Editor Commands folder to browse all editor actions and the Main Menu->Edit folder to see the key bindings for the edit operations in the right-click editor menu and the main window Edit menu.

Figure: The Key Bindings editor in Tool Properties

Read more about configuring key bindings in the Tool Properties document.

SQL Formatting

The SQL->Format SQL menu contains four operations for formatting SQL statements.

Format Buffer is used to format the complete editor content and Format Current formats the current SQL (at cursor position). The formatting is done according to the settings defined in the Tool Properties SQL Editor->SQL Formatting category. There are many things you can configure. After making some changes, press Apply and format again to see the result.

Copy Formatted and Paste Formatted are powerful tools for copying SQL statements between programs written in languages like Java, C#, PHP, VB, etc. and the SQL Editor. Both operations display a dialog where you can adjust some of the formatting options, most importantly the Target SQL option and the SQL is Between option. Target SQL can be set to a number of common programming language formats. If you set it to e.g. Java StringBuffer, Copy Formatted formats a copy of the editor content, wraps it in Java statements for adding it to a Java StringBuffer and places the result on the system clipboard. You can then paste it into another tool, such as a Java IDE, in a format suitable for a Java program. Paste Formatted combined with SQL is Between does the reverse.  It extracts all the text on the system clipboard found between the specified delimiter characters (such as double-quotes), formats it and pastes it into the SQL Editor at the caret position. This allows you to copy a SQL statement wrapped in programming language code and paste a clean, formatted version into the SQL Editor.

Example of the SQL before formatting:

select
CompanyName, ContactName, Address,
City, Country, PostalCode from
Northwind.dbo.Customers OuterC
where CustomerID in (select top 2 InnerC.CustomerId
from Northwind.dbo.[Order Details] OD
join Northwind.dbo.Orders O on OD.OrderId = O.OrderID
join Northwind.dbo.Customers InnerC
on O.CustomerID = InnerC.CustomerId
Where Region = OuterC.Region
group by Region, InnerC.CustomerId
order by sum(UnitPrice * Quantity * (1-Discount)) desc)
order by Region

And after formatting has been applied:

SELECT
    CompanyName,
    ContactName,
    Address,
    City,
    Country,
    PostalCode
FROM
    Northwind.dbo.Customers OuterC
WHERE 
    CustomerID in 
    (
    SELECT 
        top 2 InnerC.CustomerId 
    FROM 
        Northwind.dbo.[
    ORDER 
        Details] OD 
    JOIN 
        Northwind.dbo.Orders O 
        ON 
        OD.OrderId = O.OrderID 
    JOIN 
        Northwind.dbo.Customers
InnerC 
        ON 
        O.CustomerID =
InnerC.CustomerId 
    WHERE 
        Region = OuterC.Region 
    GROUP BY 
        Region, 
        InnerC.CustomerId 
    ORDER BY 
        sum(UnitPrice * Quantity *
(1-Discount)) desc
    ) 
ORDER BY 
    Region

Auto Completion

Auto completion is a convenient feature used to assist you when editing SQL statements.

With the caret in any place in a statement where you can type something other than a table name or a column name, and at least one character just before the caret, activating auto completion displays a list of keywords that starts with the letters you have typed so far. As you continue to type, the list narrows.

Figure: Auto completion pop up showing keywords

The list of keywords is database specific, selected based on the database type for the connection currently selected in the Database Connection list above the editor.

With the caret placed where a table or view name may be typed in a supported SQL statement type, the auto completion list shows a list of tables and views from the currently selected database connection, assuming you are actually connected to the database. The following figure shows the completion pop up with table names.

Figure: Auto completion pop up showing table names

A completion pop-up showing column names is shown when the caret is placed where a column name may be typed.

Figure: Auto completion pop up showing column names

DbVisualizer currently provides auto completion for table and columns names for the following DML commands:

• SELECT
• INSERT
• UPDATE
• DELETE

To display the completion pop-up, use the key binding Ctrl-SPACE (by default). You select an entry in the pop-up menu with a mouse double-click, the ENTER key, or the TAB key. To cancel the pop-up, press the ESC key.

Note 1: If there are several SQL statements in the editor then make sure to separate them using the statement delimiter character (default to ";").

Note 2: In order for the column name completion pop-up to appear, you must first make sure there are table names in the statement.

Note 3: All table names that has been listed in the completion pop-up are cached by DbVisualizer to make sure subsequent displays of the pop-up is performed quickly without asking the database. The cache is cleared only when doing a Refresh in the database objects tree or reconnecting the database connection.

Note 4: The Database and Schema lists above the editor is used to assist the auto completion feature to limit which tables to list in the pop-up.

Here are some examples of how the auto completion works depending on when it is activated. The <AC> symbol indicates the position where the auto completion pop-up is requested. The currently selected catalog is empty and the selected schema is HR. (These examples are when accessing an Oracle database).


It is possible to fine-tune how auto completion shall work in the connection properties. The following settings can be used to adjust whether table and column names should be qualified.

Figure: Properties controlling auto completion qualifiers

With Qualify disabled (for both table names and columns):

select Name, Address from EMPLOYEE where Id > 240

With Qualify enabled:

select EMPLOYEE.Name, EMPLOYEE.Address from HR.EMPLOYEE where EMPLOYEE.Id > 240

(The setting of Qualify Columns is ignored when an alias is used for a table name in the SQL).

The property settings in the figure below define whether delimited identifiers should be part of the completed SQL.

Figure: Properties controlling delimited identifiers for auto completion

With Delimited Identifiers disabled:

select Name, Address from HR.EMPLOYEE where Id > 240

With Delimited Identifiers enabled:

select "Name", "Address" from HR."EMPLOYEE" where "Id" > 240

You can also adjust other settings for how auto completion should behave in the SQL Editor->Auto Completion category.

Figure: Auto Completion properties

Recording and Playing Edit Macros

If you repeatedly need to run a sequence of edit operation, you can record them as a macro and play it as many times as needed during an editing session. The editor status bar indicates when a recording is in progress an then that a macro is available to play.

As an example, suppose you have some plain text that you need to convert into INSERT statements:

12345 123456
89012 890123
45678 456789

Place the caret at the beginning of the first line and start the macro recording, using the right-click menu or the corresponding key binding, and then type text and use key bindings (or menu items) to perform the following operations:

1. Type insert into mytable values('
2. Insertion Point to End of Word
3. Type ',
   
4. Insertion Point to Next Word
5. Type '
6. Insertion Point to End of Word
7. Type ');
8. Insertion Point Down
9. Insertion Point to Beginning of Line

Then stop the recording. You now have a macro for converting a single line to an INSERT  statement. To convert the remaining lines, just use Play Macro for each line. The result will look like this:

insert into mytable values('12345', '123456');
insert into mytable values('89012', '890123');
insert into mytable values('45678', '456789');

Note: The Find operation, by default mapped to the Find key and Ctrl-F key stroke, can not be recorded. You must instead use Find Selection, Find with Dialog, Find Next and Find Previous. Mouse gestures are also not recorded, only key strokes and menu selections.

Folding Selected Text

If you work with a large script, it can sometimes be helpful to hide parts of it. You can do so using the Code Folding feature.

Select the text you want to hide and then choose Toggle Fold Selection in the right-click menu. The selected text is then replaced (visually only) with a folding marker.

Figure: Script before folding

Figure: Script after folding

You can fold more than one part of a script using the same procedure.

To unfold just one part, select the folding marker (be careful to select all of it) and then choose Toggle Fold Selection from the menu again. To unfold all folded parts, use Expand All Foldings.

Selecting a Rectangular Area

In some cases, it is handy to be able to select a rectangular area in the middle of a script. Say, for instance, that you need to copy just the first part of a few lines and paste it at the beginning of some other lines.

To do this in the SQL Editor, click the mouse where you want to start the selection and then press the Ctrl key while you extend the selection by dragging the mouse.

Figure: A rectangular selection

---