SQL Commander

 DbVisualizer Logo
DbVisualizer 6.0
July 2007 		http://www.dbvis.com
support@dbvis.com

[ Master documentation index ]

Introduction

The SQL Commander is used to edit, format and execute SQL statements or SQL scripts. Multiple editors may be open at the same time, each controlling its own SQL log and result sets. Result sets can be displayed in grid, text or chart formats.

The SQL Commander supports the following features:
Database specific support:

Figure: SQL Commander overview

The figure shows the editing area with its controls above and the output view in the lower part of the screen. The following sections describe all features and controls in the SQL Commander in detail.

Physical Database Connections and Transactions

The SQL Commander supports database transaction control via Auto Commit or manually using commit or rollback. The Use Single Physical Database Connection setting in connection properties specifies whether DbVisualizer will use one or multiple physical database connection. This setting is disabled by default and DbVisualizer will then always use at least one physical connection and one for every SQL editor that is created. Running a statement or sequence of statements in one SQL editor will not lock the rest of the user interface while it is executing. If however using a single physical database connection all of the UI is locked until the execution in the SQL Editor has completed. The reason for this behavior is that otherwise it could lead to data corruption if using the same physical database connection.

Another important feature is that the editor status bar show the number of uncommitted requests if auto commit is off. Pay extra attention to this as it indicates that you should complete the current transaction with either commit or rollback.

Editor

The SQL Commander always have at least one editor. It is called the primary editor and cannot be removed. To create additional editors use the File->Create SQL Editor menu choice or the appropriate key binding. To close an editor use the right-click menu on the editor tab or the close operations in the File menu.


Figure: Editor tab menu

The SQL editor in DbVisualizer is based on the NetBeans editor module and supports all standard editing features. The right-click menu have the following operations:


Figure: The SQL editor right click menu

The SQL editor is also used in the Bookmark Editor and when editing CLOBs in the form editor.

Database Connection, Catalog and Schema

You use the Database Connection and Database (or Catalog) lists above the editor to specify which connection and database to use when executing the SQL in the editor. The list of connections shows all connections as they are ordered in the Database Objects tree,  except that all currently active connections are listed first.


Figure: Database Connection, Database and Schema lists

If you check the Sticky box above the Database Connection, the current connection selection will not change automatically when passing SQL statements from other parts of DbVisualizer, for instance, when passing an SQL bookmark from the Bookmark Editor. Consider an SQL bookmark defined for database connection "ProdDB". If the Sticky checkbox is not checked (i.e., disabled), the database connection is automatically changed to ProdDB when you pass the SQL from the Bookmark Editor. However, if the Sticky checkbox is checked (i.e., enabled),  the current database connection setting is unchanged. The Sticky setting is per SQL editor instance.

The Database list (or Catalog) defines which catalog in the connection is the target for the execution. Since not all databases use catalogs, this list may be disabled.

For most databases, the schema selected in the Schema list is used only to limit the tables the auto completion feature shows in the completion pop-up; it does not define a default schema for tables referenced in the SQL, because most databases do not allow the default schema to be changed during a session. For the databases that do allow the default schema to be changed, however, the selected schema is also used as the default schema, i.e., the schema used for unqualified table names in the SQL. Currently, the databases that support setting a default schema are DB2, JavaDB/Derby and Oracle. If you don't want the selected schema to be used as the default schema for these database, you can disable this behavior in the Tool Properties, under the database node's SQL Editor settings.

Limiting Result Set size (Max Rows/Chars)

The Max Rows field is used to control how many rows that DbVisualizer will fetch for each result set. If there are more rows available than presented in the result set, you will see a warning indicator in the grid status bar.

Note: Setting Max Rows technically means that it is the JDBC driver limiting the rows. This may for some databases also affect non result set operations such as DELETE. MS SQL Server is one example.


Figure: Max Rows exceeded warning

Click on the icon below the grid shows more information about the warning.

Setting Max Chars limits the number of characters that are presented for text data. A column that contains values with more characters than the specified Max Chars is shown with a different background color to highlight that it is truncated.

Load from and save to file

The SQL editor supports loading statements from a file and saving the content of the editor to a file. Use the standard file operations, Load, Save and Save As in the File main menu 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. The editor tracks any modifications and indicates changes with an asterisk (*) after the filename.

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

Load Recent

The File->Load Recent sub menu lists the recently loaded files. When you choose an entry, a file chooser dialog is displayed with that file selected. The file chooser lets you to select the target SQL editor for the file:

Figure: File Chooser

Editor Preferences

The Editor preferences pane is activated via the SQL->Show/Hide Editor Controls menu option. It keeps settings that control the appearance of the SQL editor, result sets and the log.


Figure: Editor preferences pane

All settings made in the editor preferences pane are saved between invocations.

Tip: The Result Set Naming Scheme may include HTML code, typically used to change the style of the elements.
Example: <html>${index}: ${sql} <b>(${rows})</b></html>

Multiple editors

There is always one default editor named Main Editor. This editor is used when passing SQL bookmarks from the Bookmarks Editor or when issuing requests from other parts of DbVisualizer that activate the SQL Commander. You can open additional SQL editors with the File->Create SQL Editor main menu operation. Editors can be organized as tabs or internal windows using the View buttons. To remove all but the Main Editor select the File->Close all SQL Editors menu operation.

Permissions

All SQL commands executed in the SQL Commanded are checked with the DbVisualizer Permission verifier before being executed by the database server. The permission verifier use various rules to determine if a specific SQL is allowed, denied or need confirmation before being executed. Specify in Tool Properties->Permissions the rules for the verifier.

Charsets and Fonts

You can change the SQL editor font, 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 Font category to set the font for the SQL Editor. (It is a good idea to set the same font for both the SQL editor and the grid components).

Note: 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. There is experimental support to set encodings to accomplish proper conversation between different encodings. Please see the Getting Started and General Overview document for more information.

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.


Figure: The Key Bindings editor in Tool Properties

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

Client side Comments

Comments in the SQL editor are identified by the comment identifiers in Tool Properties. These are client side comments and are removed by DbVisualizer before execution.

Sometimes the comments need to be passed to the database. Oracle, for example, uses the block comment identifier to express "hints" for the database. These must be passed to the database for processing. To enable this, just change the delimiters for the block comment to something that doesn't interfere with the /*+ ... */ notation that Oracle uses.


Figure: The Comments category in Tool Properties

Auto Completion

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

The following figure shows the completion pop up with table names.


Figure: Auto completion pop up showing table names

Here is another completion pop-up showing column names.


Figure: Auto completion pop up showing column names

DbVisualizer currently provides auto completion for table and columns names for the following DML commands:
To display the completion pop-up, use the key binding Ctrl-SPACE. 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.

Tip: The SPACE key can be configured to select entries in the pop up. Do this in the Tool Properties General->Key Bindings category. Select the Editor Commands key bindings and add the SPACE key for the Insert Newline editor action.

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 then 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 Schema list above the editor is used only to assist the auto completion feature to limit what tables to list in the pop up.

General display settings for the auto completion feature are managed in Tool Properties.

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

select * from <AC> Shows all tables in the HR schema (since HR is the selected schema)
select * from SYS.<AC> The pop up displays all tables in the SYS schema independent of the schema list selection
select * from SYS.a<AC> Lists all tables in the SYS schema beginning with the A character
select <AC> from SYS.all_objects Lists all column in the SYS.all_objects table
select <AC> from SYS.all_objects all, EMPLOYEES Lists all columns in the SYS.all_objects and EMPLOYEES table (in the HR schema)
select emp.<AC> from EMPLOYEES emp
 Lists all columns in the EMPLOYEES table, here identified by the alias emp
select emp.N<AC> from EMPLOYEES emp
 Lists all columns in the EMPLOYEES table identified by alias emp starting with the N character
insert into EMPLOYEES (<AC>
 Lists all columns in the EMPLOYEES table. Selecting the -All Columns- in the pop-up results in all columns being added, comma separated.

It is possible to fine tune how auto completion shall work in the connection properties. The following settings can be used to adjust if 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

SQL Formatter

The SQL->Format SQL feature is used to format the editor buffer 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. The formatter can work with the source SQL enclosed in quotes (e.g., when copied from a program), and it can format the final SQL for inclusion in a program written in languages like Java, C#, PHP, VB, etc.

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

SQL History

The SQL Editor keeps track of all executed SQL statements. You can use the Previous and Next buttons in the editor toolbar to walk forward and backward through the statements. They insert the previously executed SQL, with accompanying settings for Database Connection, Catalog and Schema (if Sticky is disabled). The SQL main menu also contains operations for walking through the statement history.

The history entries are also managed as SQL Bookmarks, collected in the History root folder in the Bookmark Editor.

SQL Bookmarks

SQL Bookmarks are used to manage favorite SQL statements between invocations of DbVisualizer. These are handled by the Bookmark Editor but the execution is performed in the SQL Commander. Please refer to the SQL Bookmarks document for how to use the Bookmarks main menu operations in the SQL Commander.

Execution

The execution of multiple SQL statements can be controlled using the Stop Execution On controls. These define whether the execution of the following SQL statements will be stopped based on two states:
Note: The Stop Execution On controls are only effective when executing multiple SQL statements

SQL->Execute

Use the SQL->Executemain menu operation to execute the SQL in the current (selected) SQL editor. The SQL Commander executes the statements one by one and indicates the progress in the log area. The currently selected Database Connection is used for all statements. The SQL Commander does not support executing SQLs for multiple database connections in one batch.

The result of the execution is displayed in the output view based on what result(s) are returned. If there are several results and an error occurred in one of them, the Log view is automatically displayed to indicate the error.

If you select a statement in the SQL editor and choose SQL->Execute, only the selected statement is executed. This is a useful feature when you have several SQL statements are in the SQL editor and you just want to execute one or a few of the statements.


Figure: Selection execute

In the above figure, only the highlighted statement is being executed.

SQL->Execute Current

The Execute Current operation is useful when you have a script with several SQL statements. It lets you execute the statement at the cursor position without first having to select the SQL statement. The default key binding for execute current is Ctrl-PERIOD (Ctrl-.).

Note: Execute Current determines the actual statement by parsing the editor buffer using the standard statement delimiters.

Tip: If you are unsure what the boundaries are for the current statement then use Edit->Select Current Statement. This will highlight the current statement without executing it.

SQL->Execute Buffer

Execute Buffer sends the complete editor buffer for execution as one statement. No comments are removed or parsing of individual statements based on any delimiters is made. This operation is useful when executing anonymous SQL blocks or SQLs used to create procedures, functions, etc.

SQL->Execute Explain Plan (Oracle, SQL Server and DB2)

Explain Plan is supported for Oracle, DB2 and SQL Server. Explain Plan executes your query and records the plan that the database devises to execute it. By examining this plan, you can find out if the database is picking the right indexes and joining your tables in the most efficient manner. The explain plan feature works much the same as executing SQLs to present result sets; you may highlight statements, run a script or load from file. The explain plan results can easily be compared by using the pin feature in combination with window style presentation.

DbVisualizer presents the plan either in a tree style format or in a graph. What information is shown depends on what database it is. In the tree view put the mouse pointer on the column header for a tooltip description what that column represents. The following screenshot shows the SQL in the editor at top and the resulting explain plan as result.


Figure: Explain Plan presented as a tree

The Graph View shows the plan as a graph. The graph can be exported to an image file or printed. Use the File menu choices to export and print.


Figure: Explain Plan presented as a graph

Each of the supported databases use different techniques to manage their explain plan support. To control this, either click the Preferences toolbar button or go to Connection Properties->[database]->Explain Plan.


Figure: Explain Plan configuration

The configuration options are different for each of the supported databases.

Auto Commit, Commit and Rollback

The commit and rollback SQL commands and the accompanying operations in the Database main menu are enabled if the Auto Commit setting is off for the actual SQL editor. The default setting for Auto Commit is on, which means that the driver/database automatically commits each SQL that is executed. If Auto Commit is disabled, it is very important to manually issue the commit or rollback operations when appropriate.

The Auto Commit setting is enabled by default and can be adjusted in the Connection Properties.

SQL Scripts

An SQL script is composed of several SQL statements and can be executed in a batch. Each SQL statement is separated by a single character, a sequence of characters, or the word "go" on a single line. The default settings for the separator characters are defined in Tool Properties and can be modified to match your needs.


Figure: Statement Delimiters

The following SQL script illustrates some uses of the SQL statement delimiters based on the settings in the previous figure:

select * from MyTable; /* Stmt 1 */
insert into table MyTable
(Id, Name) /* This is a comment */ values (1, 'Arnold')
go 		/* Stmt 2 */
update MyTable set Name = 'George' where Id = 1; /* Stmt 3 */
select * from
MyTable; // This is a comment 		/* Stmt 4 */

Anonymous SQL blocks

An anonymous SQL block is a block of code which contains not only standard SQL but also proprietary code for a specific database. The anonymous SQL block support in the SQL Commander uses another technique in the JDBC driver to execute these blocks. The way you tell the SQL Commander know that a SQL block is to be executed is to insert a begin identifier just before the block and an end identifier after the block. The figure in the previous section shows these settings and the default values:

Begin Identifier:
 --/
End Identifier:
 /

Here follows an example of an anonymous SQL block for Oracle:

--/ script to disable foreign keys

declare cursor tabs is select table_name, constraint_name
from user_constraints where constraint_type = 'R' and owner = user;

begin
for j in tabs loop
execute immediate ('alter table '||j.table_name||' disable constraint '||j.constraint_name);
end loop;
end;
/

If you want to execute the complete editor buffer as an anonymous SQL block, use the SQL->Execute Buffer operation. In this case, you do not need the begin and end identifiers.

Stored Procedures

Executing stored procedures is not officially supported by DbVisualizer even though it works for some databases. The best way to figure it out is to test.

Our internal tests show that the Sybase ASE and SQL Server procedure calls work okay in the SQL Commander. DbVisualizer also presents multiple result sets from a single procedure call as of version 4.0 for these databases.

Client Side Commands

The SQL Commander supports a number of DbVisualizer specific editor commands. An editor command begins with the at sign, "@". The following sections describe the available commands.

@run - run SQL script from file

@cd <directory> - change directory

@<file> - run SQL script from file

Use the following commands to locate and execute SQL scripts directly from a file without first loading the file into the SQL editor. This is useful if you are using an external editor or a development environment to edit the SQL but use DbVisualizer to execute it.
Example of a script utilizing the file referencing commands:

select * from MyTable; -- Selects data from MyTable
@run createDB.sql; -- Execute the content of the
-- createDB.sql file. The location
-- of this file is the same as the working
-- directory for DbVisualizer.
@cd /home/mupp; -- Request to change directory to /home/mupp
@loadBackup.sql; -- Execute the content in the
-- loadBackup.sql file. This file will now 
-- be read from the /home/mupp directory.

@export - export result sets to file

The @export commands are used to declare that any result sets from the SQL statements that follows should be written to a file instead of being presented in the DbVisualizer tool. This is really useful, since it enables dumping very large tables to a file for later processing or, for example, to perform backups. The following commands are used to control the export:
These parameters are supported:

Parameter Default Value Valid Values
AppendFile false true, false, clear
BinaryFormat Don't Export Don't Export, Value, Hex, Base64
CsvColumnDelimiter \t (TAB)
  
CsvIncludeColumnHeader
 true true, false
CsvIncludeSQLCommand false true, false
CsvRowCommentIdentifier    
CsvRowDelimiter \n \n (UNIX/Linux/Mac OS X), \r\n (Windows)
DateFormat yyyy-MM-dd See valid formats in Tool Properties document
DecimalNumberFormat Unformatted See valid formats in Tool Properties document
Destination File File
Encoding UTF-8
  
Filename REQUIRED  
Format CSV CSV, HTML, XML, SQL
HtmlIncludeSQLCommand false true, false
HtmlIntroText    
HtmlTitle DbVisualizer export output  
NumberFormat Unformatted See valid formats in Tool Properties document
QuoteTextData None (ANSI if Format="SQL")
 None, Single, Double, ANSI
Settings    
ShowNullAs (null)  
SqlIncludeSQLCommand false true, false
SqlRowCommentIdentifier --  
SqlSeparator ;  
TimeFormat HH:mm:ss See valid formats in Tool Properties document
TimeStampFormat yyyy-MM-dd HH:mm:ss.SSSSSS See valid formats in Tool Properties document
XmlIncludeSQLCommand false true, false
XmlIntroText    
Example 1: @export with minimum setup
The following example shows the minimum commands to export a result set.
The result set produced by the select * from Orders will be exported to the C:\Backups\Orders.csv file, using the default settings.

@export on;
@export set filename="c:\Backups\Orders.csv";

select * from Orders;
Example 2: @export with automatic table name to file name mapping
This example shows how to make the filename the same as the table name in the select statement. The example also shows several select statements. Each will be exported in the SQL format. Since the filename is defined to be automatically set, this means that there will be one file per result set and each file is named by the name of its table.

Note: There must be only one table name in a select statement in order to automatically set the filename, i.e if the select joins from several tables or pseudo tables are used, you must explicitly name the file.

@export on;
@export set filename="c:\Backups\${table}" format="sql";

select * from Orders;
select * from Products;
select * from Transactions;
Example 3: @export all result sets into a single file
This example shows how all result sets can be exported to a single file. The AppendFile parameter supports the following values.
@export on;
@export set filename="c:\Backups\alltables.sql" appendfile="clear" format="sql";

select * from Orders;
select * from Products;
select * from Transactions;
Example 4: @export using predefined settings
The Export Grid Wizard lets you save export settings to a file for later use. Such an export settings file can be referenced in the @export set command.

@export on;
@export set settings="c:\exportsettings\htmlsettings.xml" filename="c:\Backups\${table}";

select * from Orders;
select * from Products;
select * from Transactions;

The example shows that all settings will be read from the c:\exportsettings\html.xml file.

@exit [nocheck] - Exit DbVisualizer

The @exit command is the same as selecting the File->Exit operation. This command can be useful if you start DbVisualizer using the -invisible, -sql and -execute program arguments. Having @exit last in the loaded SQL file forces DbVisualizer to exit once the script has been executed. The nocheck argument defines that no confirmation dialogs should be displayed during exit.

@echo - Echo text

The @echo command simply echos the supplied text in the output.

@window iconify - Iconify the main window

This command results in the main window being lowered (iconified).

@window restore - Raise the main window

This command results in the main window being raised (if iconified).

@desc table - Describe the columns in table

Use the @desc command to show column information for a table. For tables that are not in the current database or schema, you need to qualify the table name properly.

@desc table;
@desc database.table;
@desc schema.table;

@spool log - Save log to file

The @spool log command is used to save the log to file. (The log is not cleared after being saved).

@spool log mylog.txt

@stop on error - Stop execution if any error occurs

@stop on warning - Stop execution if any warning occurs

The @stop on error and warning can be used to control that the script processing should stop if any error or warning occurs. The corresponding @continue on xxx command is used to ignore any error or warning conditions.

@stop on error;
@stop on warning;

@continue on error;
@continue on warning;

Parameterized queries (variables)

Variables can be used to build parameterized SQL statements. When you execute a statement, the SQL Commander looks for variables and prompt you to enter or replace the variable values. Variables are also used internally in DbVisualizer. The SQL templates that are listed in the Tool Properties SQL->SQL Statements category are used inside DbVisualizer in various situations. The difference compared to variables you enter in the SQL editor is that DbVisualizer automatically substitutes the predefined variable names with the correct values when the templates are used instead of prompting you for values.

A variable has the following format in its simplest form:

$$FullName$$

A variable must begin and end with the character(s) identified by the Variable Identifier property in the Tool Properties SQL category (default is $$, as in the example above). During execution, the SQL Commander searches for variables and displays a window with the name of each variable and an input (value) field. Enter the value for each variable and then press Execute. This replaces the variable with the value and finally lets the database execute the statement.

Tip: Use the Ctrl->Enter key binding as a shortcut for Execute.


Figure: The substitute variables window

The above example is the simplest case, as it only contains the variable name. In this case it is also necessary to place the text value within quotes since the substitution window cannot determine from the variable itself if it is a number or text variable.

The final substituted SQL statement that results from the initial SQL and variable value is:

update Friends set FirstName = 'Benny', LastName = 'Andersson' where Id = 9121;

Variable Syntax

The variable format supports setting a default value, data type and a few options as in the following example:

$$FullName||Andersson||String||where pk $$

The full format of the variable syntax is:

$$variableName [|| defaultValue [|| type [|| options]]] 

Output View

The Output View in the lower area of the SQL Commander is used to display the result of the SQLs being executed. How the results are presented is based on what type of result it is. A log entry is always produced in the Log view for each SQL statement that is executed. This entry shows at a minimum the execution time and how many rows were affected by the SQL. There may also be a result set if the SQL returned one. These result sets are presented either as tabs or windows based on your choice.


Figure: The output view

If an error occurs during execution, the SQL Commander automatically switches to the Log view so that you can further analyze the problem.

Log

The log keeps an entry for each SQL statement that has been executed. It provides generic information, such as how many rows were affected and the execution time. The important piece of information is the execution message which shows how the execution of that specific statement ended. If an error occurred, the complete log entry will be in red, indicating that something went wrong.


Figure: The Log with one failed statement

The detail level in an error message is dependent on the driver and database that is being used. Some databases are very good at telling what went wrong and why, while others are very quiet. The icon to the left of each log entry is used to pass the  SQL for the entry into the current SQL editor when clicked.

Log controls

The Editor Control area contains a Log tab where you can control the log content. Use the Show controls to define which information you want to appear in the log. The Filter controls are used to specify which entries should be displayed.

Auto clear log

If you enable the Auto Clear Log control, the SQL Commander automatically clears the log between executions.

Result Set

A result set grid is created for every SQL that returns one or more result sets. These grids can be displayed in a tab or window style view, similar to how the SQL editors are displayed. Each grid shares the common layout and features as described in the Getting Started and General Overview document. The format of the result can be one:

Figure: The windows output view

The figure above shows the windows output style with three result set grids. A result set grid can be closed using the red cross in the window frame header.

With the tabs style, you use the Close right click menu choice when the mouse pointer is in the tab header to close a result set:

Figure: The right click menu for tabs

Result set menu

The result set menu is available by right-clicking on a tab or on the result set desktop (window style). It contains options to control the current result set and all result sets. The following actions are available:

Menu Choice
 Description
Load SQL into Editor
 		Loads the SQL for the selected result set tab or window into the current editor.
Insert SQL into Editor
 		Inserts the SQL for the selected result set tab or window into the current editor at the cursor position.
Close Current Closes the current result set
Close All Closes all result sets
Close All But Current Closes all but the current result set
Close All Empty Closes all result sets that are empty (no data)
Pin Current Pins the current result set (preventing it from being removed at the next execution).
Unpin Current Unpins the current result set
Pin All
 		Pins all result sets. Pinning a result set prevents it from being removed at the next execution.
Unpin All
 		Unpins any pinned result sets, making them candidates for removal during the next execution.
Close All Pinned
 		Removes all pinned result sets directly.
Close All Unpinned
 		Removes all unpinned result sets directly.
Show Grids Changes the display mode to show the grid tab for all result sets
Show Texts Changes the display mode to show the text tab for all result sets
Show Charts Changes the display mode to show the chart tab for all result sets

Editing

A result set grid may be enabled for editing based on the following criteria:
  1. The result really is a result set
  2. The SQL is a SELECT command
  3. Only one table is referenced in the FROM clause
  4. All columns in the result set exist in the table with exactly the same names
If all of the above is true, the standard editing tool bar appears just above the grid. Read more about editing in the Edit Table Data chapter.

Multiple result sets produced by a single SQL statement

Some SQL statements may produce multiple result sets. Examples of this are stored procedures in Sybase ASE and SQL Server. The SQL Commander checks the results as returned by the JDBC driver and add grids to the output view accordingly. The following shows the sp_help Emps command which returns several result sets with various information about the Emps table.


Figure: Multiple result set grids produced by a single SQL statement

The result set grids in the figure above all share the same label, sp_help Emps. The number after the label represents the order number for the actual result. A stored procedure can return different results, not all being result sets. The number helps you identify which entry matches which result set grid in the log. Here is the Log output view for the previous example.


Figure: The Log after executing an SQL statement that returns multiple results

All entries with the log message "Result set fetched" are represented in the previous figure.

Text

The Text format for a result set presents the data in a tabular style. The column widths are calculated based on the length of each value and the length of the column label.

Note: The column widths may vary between executions of the SQL.

Figure: The Text result set format

Chart

A result set can be charted using the Chart view in a grid. Please read more about it in the Monitor and Charts document.

DBMS Output (Oracle)

The DBMS Output tab for Oracle is used to enable and disable capturing of messages produced by stored procedures, packages, and triggers. These messages are typically inserted in the code for debugging purposes. For SQL*Plus users, the corresponding feature is enabled via the set serveroutput on command. To enable display of DBMS messages in DbVisualizer, select the DBMS Output tab and press the Enable button.

Once DBMS output is enabled, the icon in the tab header is changed. Invoking a stored procedure in the SQL editor will result in the following being displayed in the output tab. (Each block of output is separated with a timestamp).



Figure: DBMS Output tab


Copyright © 2007 Onseven Software AB. All rights reserved.