SQL Commander

 DbVisualizer Logo
DbVisualizer 5.1
October 2006 		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 and controls above and the output view in the lower part of the screen. The following sections give a detailed explanation of all features and controls in the SQL Commander.

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 CLOB's in the form editor.

Database Connection, Catalog and Schema

The Database Connection and Database (or Catalog) lists above the editor specify which connection and database the SQL in the editor will be executed by. The list of connections shows all connections as they are ordered in the Database Objects tree with the exception that all currently active connections are listed first.


Figure: Database Connection, Database and Schema lists

The Sticky box above the Database Connection list specifies when enabled that the current connection selection will not change automatically when passing SQL statements from other parts of DbVisualizer. One example is passing an SQL bookmark from the Bookmark Editor. Consider an SQL bookmark defined for database connection "ProdDB".  If the Sticky setting is disabled the database connection will automatically be changed to ProdDB. If however the Sticky setting is enabled then the current setting of database connection will be unchanged. The Sticky setting is per SQL editor instance.

The Database list (or Catalog) is used to set what catalog in the connection will be the target for the execution. In the event of catalogs not being supported by the database connection the header will indicate this with No Catalogs for the Database Connection.

The Schema list is used only to help the auto completion feature to limit what tables to show in the completion pop up. It does not define that the actual SQL should be executed in the selected schema.

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 then 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. Columns that contain more characters then the specified Max Chars shows with red background color to highlight that it is truncated.


Figure: Max Chars exceeded warning

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

Load from and save to file

The SQL editor supports loading from file and saving to file. Use the standard file operations, Load, Save and Save As in the File main menu to accomplish this. Loading a file always loads 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.

DbVisualizer will ask at exit if there are any pending edits that need to be saved.

Load Recent

The File->Load Recent sub menu lists the recently loaded files. You may choose an entry and that file will be current in the file chooser. The file chooser allows setting what target SQL editor the file should be loaded in:

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

Multiple SQL editors can be created with the File->Create SQL Editor main menu operation. Editors can be organized as tabs or internal windows using the View buttons. 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. To remove all but the Main Editor select the File->Close all SQL Editors menu operation.

The following figures show 3 editors organized in the tabs style and the windows style
Tabs style
The SQL editors in the figure below show the Main: test.sql, 1: informix.sql and 2: Untitled. A file has been loaded into Editor 1 and the label shows the file name and indicates with an asterisk if the content in the editor has been modified. Remove an editor by choosing the Close operation in the right click menu while over the tab header.


Figure: Multiple SQL editors in the Tabs view

Windows style
The following figure shows the same editors but in the Windows view.


Figure: Multiple SQL editors in the Windows view

Remove an SQL editor window by selecting the close (red cross) button in the window header. Windows can be automatically organized using the Tile and Cascade operations in the Window main menu.

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. The default behavior is that all SQL's are allowed withouyt delete operations must be confirmed while insert and update need no confirmation.

Charsets and Fonts

The SQL editor supports changing 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 in order to set the font for the SQL Editor. (It is advisable 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. The reason is that the character encoding on the client side (in which DbVisualizer runs) and 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 re-defined 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. 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 simply change the delimiters for the block comment to something that doesn't interfere with the /*+ ... */ notation that Oracle use.


Figure: The Comments category in Tool Properties

Auto Completion

Auto completion is a convenient feature used to assist in the editing of SQL statements. The auto completion support in DbVisualizer currently supports completing table and columns names for the following DML commands:
To display the completion pop up then use the key binding Ctrl-SPACE. An entry is selected in the pop up via one of mouse double click, ENTER key or 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 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 is managed in Tool Properties.

The following 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

Here follows a couple of examples. 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 will display 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 will result in that all columns will be added. Each table is 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

Qualify disabled (for both table names and columns):
 
select Name, Address from EMPLOYEE where Id > 240

Qualify enabled:

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

(The setting of Qualify Columns is ignored if having table name aliases in the SQL).

These settings defines whether delimited identifiers should be part of the completed SQL.


Figure: Properties controlling delimited identifiers for auto completion

Delimited Identifiers disabled:

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

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 Tool Properties->SQL Editor->SQL Formatting. If the default settings for the SQL formatter is not sufficient then edit the settings in Tool Properties, press Apply and format again to see the result. The formatter source SQL may be enclosed by quotes (as copied from a program or similar), it supports formatting the final SQL in a number of language formats such as 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 History operations available in the View main menu are used to walk forward and backward through the history of executed SQL statements. These operations are performed in the currently selected editor and simply insert the next or previously executed SQL with accompanying settings for Database Connection and Catalog (if Sticky is disabled).

The history entries are in fact SQL Bookmarks and managed by 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

Execute Selected Block

Selection Executes is useful when a batch of SQL statements are in the SQL editor and you just want to execute one or a few of the statement(s).


Figure: Selection execute

The above figure will result in only the highlighted statement being executed.

SQL->Execute

The SQL->Execute main menu operation is used to execute the SQL in the current (selected) SQL editor. The SQL Commander does this by analyzing the content in the editor to determine the SQL statements. It will then execute the statement(s) and indicate the progress. All statements in one editor are executed by the Database Connection that has been selected. The SQL Commander does not support executing SQL's 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 will automatically be displayed to indicate the error.

SQL->Execute Current

The Execute Current operation is useful when having a script of several SQL statements. Use it to execute the statement at the cursor position without first needing 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 procedure, 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 tool tip description what that column represents. The following screen 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 in 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 goto Connection Properties->[database]->Explain Plan.


Figure: Explain Plan configuration

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

Commit and Rollback

The commit and rollback SQL commands and the accompanying operations in the Database main menu are enabled only if the setting of Auto Commit is off for the database connection. 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 then it is very important to manually issue the commit or rollback operations when appropriate.

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 go word 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 to let 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 then use the SQLl->Execute Buffer operation. Doing this requires no begin or 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 ok 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 what commands are available.

@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 file without first loading the script into the SQL editor. This is useful if you are using an external editor or a development environment to edit the SQL and then 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 in 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 loaded from the /home/mupp directory.

@export - export result sets to file

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

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
SettingsFile    
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 using default settings to the C:\Backups\Orders.csv file.

@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 that the file name will be 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 file name 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 then 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 pre-defined settings
The export grid wizard supports saving export settings to a file for later use in the export wizard. A export settings file can in addition be referenced in the @export set command.

@export on;
@export set settingsfile="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 is useful if starting DbVisualizer using the -invisible, -sql and -execute program arguments. Having @exit last in the loaded SQL file will force DbVisualizer to exit once the script has been executed. The nocheck argument defines that no confirmation dialogs should be displayed during exit.

@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 prefix the table name accordingly.

@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 occur

@stop on warning - Stop execution if any warning occur

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

@stop on error;
@stop on warning;

@continue on error;
@continue on warning;

@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

Parameterized queries (variables)

Variables can be used to build parameterized SQL statements. The SQL Commander will at execution check for variables and prompt for replacement values of the variables. 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 with these is that DbVisualizer automatically substitutes the pre-defined variable names with correct values once the templates are used instead of prompting for values as the SQL Commander does.

A variable has the following format in its simplest use:

$$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 will search for variables and display a window with the name of each variable and an input (value) field. Enter the value for each variable and then press Execute. This will replace the original variable with the value and finally let 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 LastName = 'Svensson' where Id = 100; Variable Syntax

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

$$FullName||Swansons||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 SQL's 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 will automatically switch 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 keeps 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 then 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 Show controls below the log are used to define what information will appear in the log. The Filter controls are used to specify what entries will be displayed.

Auto clear log

The Auto Clear Log control can be enabled to let the SQL Commander automatically clear 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

This figure shows the Windows output view with three result set grids. The Max Rows and Max Chars fields at the bottom of the figure are used to set the maximum number of rows and columns (for text data) that will be fetched and presented in new result set grids. The labels for the number of rows and columns in the grid will be displayed in red if either of these exceed their respective maximum settings. A result set grid can be closed using the red cross in the window frame header.

If the output view is Tabs then use the Close right click menu choice when the mouse pointer is in the tab header:

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 Close 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 Pin the current result set (prevent it from being removed at next execution).
Unpin Current Unpin the current result set
Pin All
 		Pins all result sets. Pinning a result set will prevent 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 matching names
If all the above is true then the standard editing tool bar will appear just above the grid. Read more about editing in the Edit Table Data document.

If any of the above fail to comply will the editing tool bar not appear.

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 will simply check 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 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 to identify in the log which entry matches what result set grid. 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 columns 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 © 2006 Onseven Software AB. All rights reserved.