DbVisualizer - Executing SQL statements in the SQL Commander

Executing SQL statements in the SQL Commander
DbVisualizer 4.0.3 (Free and Personal editions) January 2004	http://www.dbvis.com support@dbvis.com

[ Master documentation index ]

Introduction

The SQL Commander is used to execute SQL statements or SQL scripts (several SQL statements in one batch). The result is either displayed in grids or log entries depending on what result is returned from the execution. There is support for multiple editors, setting of font, key bindings, management of multiple result sets and a lot more. The SQL Commander tab is organized as follows:

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 editor in DbVisualizer is based on the NetBeans editor module and supports all standard editing features. The editor supports keyword coloring for the SQL syntax and the key bindings can be customized in Tool Properties.

The right click menu contains 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 and Catalog

The Database Connection and Catalog (aka Database) boxes 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: The Database Connection box

The Sticky box above the 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 Catalog box (more commonly known as Database) 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.

Figure: The Catalog box

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.

Editor shortcuts

The editor shortcuts or key bindings can be re-defined in the Tool Properties->Editor category. These settings are saved between invocations of DbVisualizer.

Figure: The Editor category in Tool Properties used to re-define shortcuts

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.

Comments in the SQL

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

Figure: The Comments category in Tool Properties

Multiple editors

Multiple SQL editors can be created with the File->Add 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->Remove 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 Editor, Editor 1 and Editor 2. 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.

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

Execution control

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:

Errors
Stop the execution if the SQL resulted in an error
Warnings
Stop the execution if the SQL executed successfully but no rows were affected

Note: The Stop Execution On controls are only effective when executing multiple SQL statements.

Selection executes

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.

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:

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

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;
/

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.

Control commands

The SQL Commander supports executing SQL script files without first needing to load the file into the SQL editor. This is accomplished by using the @ control character in the first position of a row. This is useful if you are using an external editor or IDE to edit the SQL and then use DbVisualizer to execute it. The following shows how to use the @ control character.

select * from MyTable;	-- Selects data from MyTable
@createDB.sql	-- Load and 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	-- Load and execute the content in the -- loadBackup.sql file. This file will now be -- loaded from the /home/mupp directory.

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.

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

variableName
Required. This is the name that will appear in the substitution dialog. If several variables have the same name then the substitution dialog will show only one and the entered value will be applied to all variables of that name.
defaultValue
The default value that will appear in the substitution dialog
type
The type of variable - String, Integer, BinaryData, etc. This is used to determine if the value will be enclosed by quotes or not. If no type is specified then it is treated as an Integer (no quotes).
options
The options part is used to express various things. Most interesting are the pk and where keywords.
(Note: There must be a whitespace character following a keyword).

pk
Defines whether an icon will appear before the variable name in the substitution dialog to indicate that it is a primary key field.
where
Defines that the variable is part of the where clause and so will appear last in the list of variables.

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.

Output View menu

The Output View menu in the lower right area contains the following choices:

Figure: The output view

Menu Choice	Description
Copy SQL into Editor	Copies the original SQL for the currently selected result set tab or window.
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.
Remove Pinned	Removes all pinned result sets directly.
Remove Unpinned	Removes all unpinned result sets directly.
Pin New Result Sets	Check this to make sure all new result sets are pinned by default.
Remove Empty Result Sets	Removes all empty result sets.
Clear Output View	Clears the current view. This operation is valid to use when the Log is being displayed and will clear all log entries.

Result set grids

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.

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 on the tab header:

Figure: The right click menu for tabs

Editing

A result set grid may be enabled for editing based on the following criterias:

The result really is a result set
The SQL is a SELECT command
Only one table is referenced in the FROM clause
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.

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.

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 just above the output view can be enabled to let the SQL Commander automatically clear the log between executions.