SQL Commander
DbVisualizer 6.5 October 2008	http://www.dbvis.com support@dbvis.com

• Introduction
  • Physical Database Connections and Transactions
• Editor
  • Database Connection, Catalog and Schema
  • Limiting Result Set size (Max Rows/Chars)
  • Load from and save to file
    • Load Recent
  • Editor Preferences
    • Multiple editors
  • Permissions
  • Charsets and Fonts
  • Key Bindings
  • Client side Comments
  • Auto Completion
  • SQL Formatter
  • SQL History
  • SQL Bookmarks
• Execution
  • SQL->Execute
  • SQL->Execute Current
  • SQL->Execute Buffer
  • SQL->Execute Explain Plan (Oracle, SQL Server and DB2)
  • Auto Commit, Commit and Rollback
  • SQL Scripts
  • Anonymous SQL blocks
  • Stored Procedures
  • Client Side Commands
    • @run - run SQL script from file
    • @cd <directory> - change directory
    • @<file> - run SQL script from file
    • @export - export result sets to file
      • Example 1: @export with minimum setup
      • Example 2: @export with automatic table name to file name mapping
      • Example 3: @export all result sets into a single file
      • Example 4: @export using predefined settings
    • @exit [nocheck] - Exit DbVisualizer
    • @echo - Echo text
    • @window iconify - Iconify the main window
    • @window restore - Raise the main window
    • @desc table - Describe the columns in table
    • @spool log - Save log to file
    • @stop on error - Stop execution if any error occurs
    • @stop on warning - Stop execution if any warning occurs
    • @set autocommit - Sets the auto commit state
    • @commit - Commits the current transaction
    • @rollback - Rollback the current transaction
    • @set serveroutput - Enable/disable the DBMS output management for Oracle
  • Variables
    • Variable Syntax
    • Pre-defined Variables
    • Variable Substitution in SQL statements
  • Parameter Markers
• Output View
  • Log
    • Log controls
    • Auto clear log
  • Result Set
    • Result set menu
    • Editing
    • Multiple result sets produced by a single SQL statement
    • Text
    • Chart
  • DBMS Output (Oracle)

Introduction

• Syntax coloring
• Auto completion
• Multiple SQL editors
• Multiple result sets
• SQL editors displayed as tabs or windows
• Result sets displayed as tabs or windows
• Support for stored procedures producing multiple result sets
• SQL formatter with extensive customization options
• Execution control (stop on error/warning)
• View result sets as grid, text or chart
• Editable result sets with the inline or form editors
• Support for BLOB, CLOB and binary data
• View BMP, TIFF, PNG, GIF and JPEG images
• View XML data in tree or text format
• Export result sets as CSV, HTML, Excel, XML, SQL or text
• Batch execution enabling export of unlimited sized result sets
• SQL history saved between sessions
• Bookmark management (save favorite SQLs)
• Sort, quick filter and basic calculations of result sets
• Parameterized queries
• Drop objects dragged from the Objects Tree
• Auto Commit on/off support with confirmation checks if uncommitted updates
• Full key binding support with predefined key maps for for Windows, Mac OS X, Linux-UNIX, SQL Query Analyzer and TOAD users

• Oracle, DB2 and SQL Server: Explain Plan queries presented either in tree or graph format
• Oracle: support for TIMESTAMPLTZ, TIMESTAMPTZ and XML data types
• Oracle: support for DBMS Output

Figure: SQL Commander overview

Physical Database Connections and Transactions

Editor

Figure: Editor tab menu

Figure: The SQL editor right click menu

Database Connection, Catalog and Schema

Figure: Database Connection, Database and Schema lists

Limiting Result Set size (Max Rows/Chars)

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

Load from and save to file

Figure: Loading a file into the SQL Commander

Load Recent

Figure: File Chooser

Editor Preferences

Figure: Editor preferences pane

Multiple editors

Permissions

Charsets and Fonts

Figure: SQL Editor with another font

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

Figure: The Key Bindings editor in Tool Properties

Client side Comments

Figure: The Comments category in Tool Properties

Auto Completion

Figure: Auto completion pop up showing table names

Figure: Auto completion pop up showing column names

• SELECT
• INSERT
• UPDATE
• DELETE

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.

Figure: Properties controlling auto completion qualifiers

select Name, Address from EMPLOYEE where Id > 240

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

Figure: Properties controlling delimited identifiers for auto completion

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

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

SQL Formatter

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

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

SQL Bookmarks

Execution

• 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
  

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

SQL->Execute

Figure: Selection execute

SQL->Execute Current

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

SQL->Execute Buffer

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

Figure: Explain Plan presented as a tree

Figure: Explain Plan presented as a graph

Figure: Explain Plan configuration

Auto Commit, Commit and Rollback

SQL Scripts

Figure: Statement Delimiters

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

Anonymous SQL blocks

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

Client Side Commands

@run - run SQL script from file

@cd <directory> - change directory

@<file> - run SQL script from file

• @run <file>
  Request to execute the file specified as parameter
• @cd <directory>
  Change the working directory for the following @run or @<file> commands
• @<file>
  Same as @run <file>
  

select * from MyTable; -- Selects data from MyTable
@run createDB.sql;     -- Execute the content in the
                       -- createDB.sql file without loading into the SQL editor.
                       -- 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

• @export on
  Defines that the SQL statements that follows will be exported rather then being presented in DbVisualizer
• @export set parm1="value1" parm2="value2"
  The set command is used to customize the export process. Check the table below for the complete set of parameters.
• @export off
  Defines that SQL statements that follows will be handled the normal way, i.e., the result sets are presented in the DbVisualizer tool

These parameters are supported:

Example 1: @export with minimum setup

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

Example 2: @export with automatic table name to file name mapping

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

• true
     The following result sets will all be exported to a single file
• false
     Turn off the append processing
• clear
     Same as the true value but this will in addition clear the file before the first result set is exported

@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

@export on;
@export set settings="c:\tmp\htmlsettings.xml" filename="c:\Backups\${table}";
select * from Orders;
select * from Products;
select * from Transactions;

@exit [nocheck] - Exit DbVisualizer

@echo - Echo text

@window iconify - Iconify the main window

@window restore - Raise the main window

@desc table - Describe the columns in table

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

@spool log - Save log to file

@spool log mylog.txt

@stop on error - Stop execution if any error occurs

@stop on warning - Stop execution if any warning occurs

@stop on error;
@stop on warning;

@continue on error;
@continue on warning;

@set autocommit - Sets the auto commit state

@commit - Commits the current transaction

@rollback - Rollback the current transaction

@set serveroutput - Enable/disable the DBMS output management for Oracle

Variables

Variable Syntax

${FullName||Andersson||String||where pk }$

• name
  Required. This is the name that appear in the substitution dialog. If multiple variables have the same name, the substitution dialog shows only one and the entered value will be applied to all variables of that name.

• default
  The default value that appears in the substitution dialog

• type
  The type of variable - String, Integer, Float, etc. This is used to determine if the value should be enclosed by quotes or not. If no type is specified, it is treated as an Integer (no quotes).

• options
  The options part is used to express certain conditions:
  
• pk
  Indicates that the variable is part of the primary key in the final SQL. Represented with a key icon
• where
  Defines that the variable is part of the WHERE clause. The green star icon further illustrate this condition
• noshow
  This option define that the variable should not appear in the substitution dialog. A proper default value must be set if using noshow

There must be a whitespace character following a keyword in the options section).

Pre-defined Variables

Note that none of the above variables will appear in the Variable Substitution window explained belo

Variable Substitution in SQL statements

For variable processing to work in the SQL Commander, make sure the SQL->Process Variables in SQL is enabled.

INSERT
INTO
   "SCOTT"."EMP"
VALUES
(
   ${EMPNO}$,
   '${ENAME}$',
   '${JOB}$',
   ${MGR}$,
   '${HIREDATE}$',
   ${SAL}$,
   ${COMM}$,
   ${DEPTNO}$
)

Figure: The substitute variables window

• Set Default Values
  This will set the value to the default value for the variable. If a default value was not specified in the variable, (null) will appear
  
• Set Previously Used Values
  Set the value for each variable to the values used in the previous run (if there is no matching set of variables then this button is disabled).

• The variable set is the same as in a previous execution (by name). Note that multiple variable sets are supported and not only the last used.
  
• If there are no default values set in the variable definition(s)
  

update SCOTT.EMP set 
  EMPNO = ${EMPNO||7698||BigDecimal||pk ds=22 dt=NUMERIC }$, 
  ENAME = ${ENAME||BLAKE||String||nullable ds=10 dt=VARCHAR }$, 
  JOB = ${JOB||MANAGER||String||nullable ds=9 dt=VARCHAR }$, 
  MGR = ${MGR||7839||BigDecimal||nullable ds=22 dt=NUMERIC }$, 
  HIREDATE = ${HIREDATE||1981-05-01 00:00:00.0||Timestamp||nullable ds=7 dt=TIMESTAMP }$, 
  SAL = ${SAL||2850||BigDecimal||nullable ds=22 dt=NUMERIC }$, 
  COMM = ${COMM||(null)||BigDecimal||nullable ds=22 dt=NUMERIC }$, 
  DEPTNO = ${DEPTNO||30||BigDecimal||nullable ds=22 dt=NUMERIC }$ 
where EMPNO = ${EMPNO (where)||7698||BigDecimal||where pk ds=22 dt=NUMERIC }$

Figure: The substitute variables window

Parameter Markers

select * from EMP where ENAME = ? or ENAME = ?

Parameter marker processing is managed by the JDBC driver and not all drivers supports it. One example is the Oracle JDBC driver which lacks support completely.

Figure: The parameter marker substitution window

Output View

Figure: The output view

Log

Figure: The Log destination controls

Figure: The Log with one failed statement

Log controls

Auto clear log

Result Set

• Grid
  The result is presented in a grid.
  
• Text
  The result is presented in a tabular format.
  
• Chart
  Read more in Monitor and Charts.

Figure: The windows output view

Figure: The right click menu for tabs

Result set menu

Editing

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
   

Multiple result sets produced by a single SQL statement

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

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

Text

The column widths may vary between executions of the SQL.

Figure: The Text result set format

Chart

DBMS Output (Oracle)

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