Executing SQL statements in the SQL Commander

 DbVisualizer Logo
DbVisualizer 4.3
January 2006 		http://www.dbvis.com
support@dbvis.com

[ Master documentation index ]

Introduction

The SQL Commander is used to edit and 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, auto completion, 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, auto completion 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, Catalog and Schema

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

The Schema box 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.

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

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. You can close the pop up either by selecting an entry in the list or simply by pressing the ESC button.

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

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 indepentent 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 how auto completion should work.

Figure: Properties controlling auto completion

Prepend Column Name with Table Name specifies if completed column names should be prefixed with the actual table name i.e TABLE.COLUMN. This setting will only have effect if NOT using table name aliases.

The Prepend Table Name with Schema/Database Name setting is by its name quite self explanatory...

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:
Note: The Stop Execution On controls are only effective when executing multiple SQL statements.

Execute statement at cursor position

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.

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:

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;

/

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 - running SQL scripts 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.

@set serveroutput - enabling Oracle DBMS_OUTPUT

Stored procedures, functions and package bodies in Oracle supports the DBMS_OUTPUT PL/SQL command. It is used to output information to the end user. In order to grab these outputs you need to enable DbVisualizer to catch and display them. Use the following commands to either enable or disable capturing DBMS_OUTPUT.
When enabled will the output appear in the SQL Commander log.

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.

Output View menu

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


Figure: The output view

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

Editing

A result set grid may be enabled for editing based on the following criterias:
  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 tabullar style. The column widhts 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.

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.

Copyright © 2006 Onseven Software AB. All rights reserved.