SQL Commander

 DbVis Software
DbVisualizer 7.0 http://www.dbvis.com
support@dbvis.com

Master documentation index

Introduction

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

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

Figure: SQL Commander overview

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

Physical Database Connections and Transactions

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

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

Editor

The SQL Commander always has 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 when editing CLOBs in the form editor.

Database Connection, Catalog and Schema

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


Figure: Database Connection, Database and Schema lists

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

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

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

Limiting Result Set size (Max Rows/Chars)

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


Figure: Max Rows exceeded warning

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

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

Load from and save to file

The SQL editor supports loading statements from a file and saving the content of the editor to a file. Use the standard file operations, Open, Save and Save As in the File main menu or the toolbar to accomplish this. Loading a file always loads it into the currently selected editor.


Figure: Loading a file into the SQL Commander

The name of the loaded file is listed in the status bar of the editor, with the full file path shown in the window title. The editor tracks any modifications and indicates changes with an asterisk (*) after the filename.

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

Load Recent

The File->Load Recent sub menu lists the recently loaded files. When you choose an entry, the file is opened in the current SQL editor.

Figure: Open Recent Files menu

Quick File Open

You can also use the Quick File Open feature to open recent files as well as Bookmarks and History entries. By default, it is bound to the Ctrl+Alt+O key combination, and is also available via a toolbar button in the SQL Editor as well as in the main File->Quick File Open menu.


Figure: Quick File Open dialog

Editor Preferences

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


Figure: Editor preferences pane

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

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

Multiple editors

There is always one default editor named Main Editor. You can open additional SQL editors with the File->Create SQL Editor main menu operation. Editors can be organized as tabs or internal windows using the View buttons. To remove all but the Main Editor select the File->Close all SQL Editors menu operation.

Permissions

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

Charsets and Fonts

You can change the SQL editor font, which is useful and necessary in order to display characters for languages like Chinese, Japanese, etc.


Figure: SQL Editor with another font

Open Tool Properties and select the Font category to set the font for the SQL Editor. (It is a good idea to set the same font for both the SQL editor and the grid components).

Displaying data correctly is not just a matter of setting the font, because the character encoding on the client side (in which DbVisualizer runs) and in the database server may not be compatible. There is experimental support to set encodings to accomplish proper conversation between different encodings. Please see the Getting Started and General Overview document for more information.

Key Bindings

The editor shortcuts, or key bindings, can be redefined in the Tool Properties Key Bindings category. Select the Editor Commands folder to browse all editor actions.


Figure: The Key Bindings editor in Tool Properties

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

Client-Side Comments

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

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


Figure: The SQL Editor -> Comments category in Tool Properties

Auto Completion

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

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


Figure: Auto completion pop up showing table names

Here is another completion pop-up showing column names.


Figure: Auto completion pop up showing column names

DbVisualizer currently provides auto completion for table and columns names for the following DML commands:
To display the completion pop-up, use the key binding Ctrl-SPACE. You select an entry in the pop-up menu with a mouse double-click, the ENTER key, or the TAB key. To cancel the pop-up, press the ESC key.

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

Note 1: If there are several SQL statements in the editor then make sure to separate them using the statement delimiter character (default to ";").

Note 2: In order for the column name completion pop-up to appear, 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 to assist the auto completion feature to limit which tables to list in the pop-up.

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

Here are some examples of how the auto completion works depending on when it is activated. The <AC> symbol indicates the position where the auto completion pop-up is requested. The currently selected catalog is empty and the selected schema is HR. (These examples are when accessing an Oracle database).

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

It is possible to fine-tune how auto completion shall work in the connection properties. The following settings can be used to adjust whether table and column names should be qualified.

Figure: Properties controlling auto completion qualifiers

With Qualify disabled (for both table names and columns):

select Name, Address from EMPLOYEE where Id > 240

With Qualify enabled:

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


(The setting of Qualify Columns is ignored when an alias is used for a table name in the SQL).

The property settings in the figure below define whether delimited identifiers should be part of the completed SQL.


Figure: Properties controlling delimited identifiers for auto completion

With Delimited Identifiers disabled:

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

With Delimited Identifiers enabled:

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

SQL Formatter

The SQL->Format SQL feature is used to format the complete editor buffer or current SQL (at cursor position) according to the settings defined in the Tool Properties SQL Editor->SQL Formatting category. There are many things you can configure. After making some changes, press Apply and format again to see the result. The formatter can work with the source SQL enclosed in quotes (e.g., when copied from a program), and it can format the final SQL for inclusion in a program written in languages like Java, C#, PHP, VB, etc.

Example of the SQL before formatting:
select
CompanyName, ContactName, Address,
City, Country, PostalCode from
Northwind.dbo.Customers OuterC
where CustomerID in (select top 2 InnerC.CustomerId
from Northwind.dbo.[Order Details] OD
join Northwind.dbo.Orders O on OD.OrderId = O.OrderID
join Northwind.dbo.Customers InnerC
on O.CustomerID = InnerC.CustomerId
Where Region = OuterC.Region
group by Region, InnerC.CustomerId
order by sum(UnitPrice * Quantity * (1-Discount)) desc)
order by Region
And after formatting has been applied: 
SELECT
    CompanyName,
    ContactName,
    Address,
    City,
    Country,
    PostalCode
FROM
    Northwind.dbo.Customers OuterC
WHERE 
    CustomerID in 
    (
    SELECT 
        top 2 InnerC.CustomerId 
    FROM 
        Northwind.dbo.[
    ORDER 
        Details] OD 
    JOIN 
        Northwind.dbo.Orders O 
        ON 
        OD.OrderId = O.OrderID 
    JOIN 
        Northwind.dbo.Customers
InnerC 
        ON 
        O.CustomerID =
InnerC.CustomerId 
    WHERE 
        Region = OuterC.Region 
    GROUP BY 
        Region, 
        InnerC.CustomerId 
    ORDER BY 
        sum(UnitPrice * Quantity *
(1-Discount)) desc
    ) 
ORDER BY 
    Region

History

The SQL Editor keeps track of all executed SQL statements. You can use the Previous and Next buttons in the editor toolbar to walk forward and backward through the statements. They insert the previously executed SQL, with accompanying settings for Database Connection, Catalog and Schema (if Sticky is disabled). Please see Bookmarks and History for more details.

Bookmarks

Bookmarks are used to save frequently used SQL statements between invocations of DbVisualizer. They are managed primarily in the Scripts tab but edited and executed in the SQL Commander. Please see Bookmarks and History for more details.

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:

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

SQL->Execute

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

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

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


Figure: Selection execute

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

Comments in the SQL editor are not sent to the database when you use SQL->Execute. If you want comments to be preserved when creating or changing a stored procedure or function, please use the Create Procedure dialog and Procedure Editor instead of the SQL Commander.

SQL->Execute Current

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

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 and no parsing of individual statements based on any delimiters is made. This operation is useful when executing anonymous SQL blocks or SQLs used to create procedures, functions, etc.

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

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

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


Figure: Explain Plan presented as a tree

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


Figure: Explain Plan presented as a graph

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


Figure: Explain Plan configuration

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

Auto Commit, Commit and Rollback

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

The following commands can be executed in the SQL Commander for database independent commit and rollback:

@commit
@rollback

The Auto Commit setting is enabled by default and can be adjusted in the Connection Properties. You may also adjust the  auto commit state for the SQL editor you are using in the SQL Commander with the following command:

@set autocommit true/false

SQL Scripts

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


Figure: Statement Delimiters

The following SQL script illustrates some uses of the SQL statement delimiters based on the settings in the previous figure: 
select * from MyTable;                                       /* Stmt 1 */
insert into table MyTable                                    /* 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
You can also use the @delimiter client side command to temporarily change the delimiter in a script.

Execute Large SQL Scripts

If you have a large script (tens of MB), loading it into the SQL Commander and generating log entries in the GUI for all statements require a lot of memory.

For a script that is large but still small enough to load into the SQL Commander, you can save memory (and therefore run it faster and more efficiently) by selecting to log to a file instead of the GUI:



To save even more memory, you can use the @run client side command to run the script without loading it into the SQL Commander:
@run my_huge_script.sql;
The @run command reads one statement at a time from the file. There are, however, still a few things that require the whole file to be read before the statements can be executed: parsing the script for variables, parameter markers, and restricted commands, as well as counting all statements in order to provide progress information. When you run a script that is large enough (more than 10 MB) for these things to potentially cause memory problems and slow down the processing, DbVisualizer gives you a chance to turn off this preprocessing and progress reporting so that the statements instead can be executed directly as the are read from the file, one at a time.



Figure: Disable preprocessing dialog

To ensure that you don't have any problems running scripts this large, you must specify a file for logging. We also strongly recommend that you click Continue w/o Preprocessing, thereby disabling all variable, parameter and restricted commands processing. Only click Continue Normally if you know for sure that you have enough memory available and have adjusted your installation so that DbVisualizer can use it. With the preprocessing disabled and all logging going to a file instead of the GUI, you should be able to execute scripts of any size (we have tested with scripts as large as 4 GB).

Another alternative for execution of large scripts is to use the DbVisualizer command line interface instead of the GUI application. This option is the absolute most efficient and fastest.

Anonymous SQL Blocks

An anonymous SQL block is a block of code which contains not only standard SQL but also proprietary code for a specific database. The anonymous SQL block support in the SQL Commander uses another technique in the JDBC driver to execute these blocks. The way you tell the SQL Commander 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 for the Begin Identifier it is --/ and for the End Identifier it is /.

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

--/ script to disable foreign keys
      
declare cursor tabs is select table_name, constraint_name
  from user_constraints where constraint_type = 'R' and owner = user;
      
begin
  for j in tabs loop
    execute immediate ('alter table '||j.table_name||' disable constraint'||j.constraint_name);
end loop;
end;
/
If you want to execute the complete editor buffer as an anonymous SQL block, use the SQL->Execute Buffer operation. In this case, you do not need the begin and end identifiers.

Stored Procedures

With DbVisualizer Personal, you can execute stored procedures and functions for databases with extended support using the @call client side command described below and in the Procedure Editor chapter.

For other databases it is not officially supported by DbVisualizer even though the native commands (e.g. CALL or EXEC) work for some databases. The best way to figure it out is to try. Our internal tests show that the Sybase ASE and SQL Server procedure calls work okay with literal IN parameters 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 commands that you can use in the SQL Editor. A command begins with the at sign, "@". The following sections describe the available commands.

@run - run SQL script from file

@cd <directory> - change directory

Use the following commands to locate and execute SQL scripts directly from a file without first loading the file into the SQL editor. This is useful if you are using an external editor or a development environment to edit the SQL but use DbVisualizer to execute it.
Example of a script utilizing the file referencing commands: 
select * from MyTable; -- Selects data from MyTable
@run createDB.sql;     -- Execute the content 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
@run loadBackup.sql;   -- Execute the content in the loadBackup.sql
                       -- file. This file will now be read from the
                       -- /home/mupp directory.

@export - export result sets to file

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

These parameters are supported:

Parameter Default Valid Values
AppendFile false true, false, clear
BinaryFileDir
Directory path for data files when BinaryFormat is set to File
BinaryFormat Don't Export Don't Export, Size, Value, Hex, Base64, File
BooleanFalseFormat
 false
 false, no, 0, off
BooleanTrueFormat
 true
 true, yes, 1, on
CLOBFileDir
Directory path for data files when CLOBFormat is set to File
CLOBFormat
 Value
 Don't Export, Size, Value, File
CsvColumnDelimiter \t (TAB)
  
CsvIncludeColumnHeader
 true true, false
CsvIncludeSQLCommand false true, false
CvsRemoveNewlines
 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
  
ExcelIncludeColumnHeader
 true
 true, false
ExcelIncludeSQLCommand
 false
ExcelIntroText
Any description
ExcelTextOnly
 false
 true, false
ExcelTitle
 DbVisualizer export output
 Any title
Filename REQUIRED  
Format CSV CSV, HTML, XML, SQL, XLS
HtmlIncludeSQLCommand false true, false
HtmlIntroText    Any description
HtmlTitle DbVisualizer export output  Any title
NumberFormat Unformatted See valid formats in Tool Properties document
QuoteDuplicateEmbedded
 true
 true, false (quote char is the same as QuoteTextData)
QuoteTextData None
 None, Single, Double
Settings    
ShowNullAs (null)  
SqlIncludeCreateDDL
 false
 true, false
SqlIncludeSQLCommand false true, false
SqlRowCommentIdentifier --  
SqlSeparator ;  
TableName
Can be set if DbVisualizer cannot determine the value for the ${dbvis-object} variable
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    
XmlStyle
 DbVisualizer
 DbVisualizer, XmlDataSet, FlatXmlDataSet
Example 1: @export with minimum setup
The following example shows the minimum commands to export a result set.
The result set produced by the select * from Orders will be exported to the C:\Backups\Orders.csv file, using the default settings.
@export on;
@export set filename="c:\Backups\Orders.csv";
select * from Orders;
Example 2: @export with automatic table name to file name mapping
This example shows how to make the filename the same as the table name in the select statement. The example also shows several select statements. Each will be exported in the SQL format. Since the filename is defined to be automatically set, this means that there will be one file per result set and each file is named by the name of its table.

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\${dbvis-object}$" format="sql";
select * from Orders;
select * from Products;
select * from Transactions;
Example 3: @export all result sets into a single file
This example shows how all result sets can be exported to a single file. The AppendFile parameter supports the following values.
@export on;
@export set filename="c:\Backups\alltables.sql" appendfile="clear" format="sql";
select * from Orders;
select * from Products;
select * from Transactions;
Example 4: @export using predefined settings
The Export dialogs let you save export settings to a file for later use. Such an export settings file can be referenced in the @export set command.
@export on;
@export set settings="c:\tmp\htmlsettings.xml" filename="c:\Backups\${dbvis-object}$";
select * from Orders;
select * from Products;
select * from Transactions;

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

@delimiter - Temporarily change the statement delimiter

When you use SQL statements to create functions and stored procedures in a script that also contains other SQL statements, the statement delimiters for statements within the code body of the CREATE statement often clash with the delimiters for the other statements. One way to handle this is with Anonymous SQL Blocks, but it may be more convenient to temporarily change the statement delimiter. That is what the @delimiter command is for:
@delimiter ++;
CREATE OR REPLACE FUNCTION HELLO (p1 IN VARCHAR2) RETURN VARCHAR2
AS
BEGIN
 RETURN 'Hello ' || p1;
END;
++
@delimiter ;++
@call ${returnValue||(null)||String||noshow dir=out}$ = HELLO('World');
@echo returnValue = ${returnValue}$;
The first @delimiter command sets the delimiter to "++" so that the default ";" delimiter can be used within the function body in the CREATE statement. The "++" delimiter is then used to end the CREATE statement, and another @delimiter command sets the delimiter back to ";" for the remaining commands in the script.

Note that current delimiter must be used to delimit the @delimiter command itself from the other statements: the first @delimiter command uses ";" and the second uses "++".

@call - Execute a function or stored procedure

You can use the @call command to execute a function or a stored procedure.

For a function, returning a value, use this syntax:
@call <OutVariable> = <FunctionName>(<ParamList>);
where the <FunctionName> may need to be fully qualified with a schema (and/or catalog/database) and the <ParamList> is a comma separated list of literal values or variables. Here's an example:
@call ${return_value||(null)||String||dir=out noshow}$ = get_some_value();
For a procedure, use this syntax:
@call <ProcedureName>(<ParamList>);
where the <ProcedureName> may need to be fully qualified with a schema (and/or catalog/database) and the <ParamList> is a comma separated list of literal values or variables. Here's an example:
@call my_process('literal input', 
                 ${var_in||(null)||String||dir=in}$, 
                 ${var_out||(null)||String||dir=out noshow}$,
                 ${var_inout||'in_value'||String||dir=inout}$);
As shown in these examples, you must use the dir option to specify how the variable is to be used (in, out or inout) and you may use the noshow option to prevent being prompted for a value for an output variable .

You can use the @echo command described earlier to write the value assigned to an output variable to the log.

The Procedure Editor chapter shows a few more examples, and how you can generate a script for calling a procedure or function. The Variables section below describes the variable syntax in more detail.

@echo - Echo text

The @echo command simply echos the supplied text or the value of a variable in the output.

@window iconify - Iconify the main window

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

@window restore - Raise the main window

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

@desc table - Describe the columns in table

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

@ddl - Generate DDL command

The @ddl command is used to generate a DDL command (CREATE statement) for a number of different database object types. The command supports this general syntax:

@ddl <objType>="<objId>" [ drop="true | false" ] [ constrCtrl="<constrCtrl>" ]
<objType> is one of table, indexesfortable, view, procedure, function, package (Oracle only), packagebody (Oracle only), module (Mimer only) or trigger, and <objId> is the qualified identifier for the object (case sensitive).

If drop is set to true, a DROP statement is included before the CREATE statement.

The constrCtrl parameter only applies to tables. It accepts two values: noconstr means that no constraints should be included in the statement that can potentially cause creating the table or inserting data into it to fail (FK and CHECK constraints), while onlyconstr means that an ALTER statement adding the remaining constraints should be generated instead of a CREATE statement.

@spool log - Save log to file

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

@stop on error - Stop execution if any error occurs

@stop on warning - Stop execution if any warning occurs

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

@continue on error;
@continue on warning;

@set autocommit - Set the auto commit state

Pass either true or false as a parameter and it will set the auto commit state accordingly.

@commit - Commit the current transaction

Commit the current transaction via this database independent command.

@rollback - Rollback the current transaction

Rollback the current transaction via this database independent command.

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

Pass either true or false as a parameter to start or stop the DBMS output management for Oracle.

@set maxrows <number> - Temporarily set the row limit for the script

Limit the number of rows retrieved for a result set to the specified number. The limit applies to statements following this command in the script. The limit for the SQL editor is not changed.

@set maxchars <number> - Temporarily set the text field width limit for the script

Limit the number of characters that are presented for text fields in a result set to the specified number. The limit applies to statements following this command in the script. The limit for the SQL editor is not changed.

Variables

Variables are used to build parameterized SQL statements and let DbVisualizer prompt you for the values when the SQL is executed. This is handy if you are executing the same SQL repetitively, just wanting to pass new data in the same SQL statement.

Variable Syntax

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

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

Here is the complete variable syntax:

${name || value || type || options}$

Pre-defined Variables

A few pre-defined DbVisualizer variables can be used anywhere in the SQL. These are replaced with actual values just before  the SQL is sent to the DB server. The final value for these variables are self explanatory.

${dbvis-date}$
${dbvis-time}$
${dbvis-timestamp}$

By default, the values are formatted as defined in Tool Properties->Data Formats, but you can also specify a custom format for a single use of the variable, e.g.

${dbvis-date||||||format=[yyyyMMdd]}$

The following variables can be used only when monitoring a SQL statement that produce a result set and the Allowed Row Count for the monitor is > 0. The output format is seconds and milliseconds. Ex: 2.018

${dbvis-exec-time}$
${dbvis-fetch-time}$

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

Variable Substitution in SQL statements

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

A simple variable may look like this:

${FullName}$

A variable is identified by the start and end sequences, ${ ... }$. (These can be re-defined in Tool Properties). During execution, the SQL Commander searches for variables and displays a window with the name of each variable and an input (value) field. Enter the value for each variable and then press Execute. This will then replace the variable with the value and finally let the database execute the statement.

Consider the following SQL statement with variables. It is the simplest use of variables as it only contains the variable names. In this case it is also necessary to enclose text values within quotes since the substitution window cannot determine the actual data type from these variable expressions.
INSERT
INTO
   "SCOTT"."EMP"
VALUES
(
   ${EMPNO}$,
   '${ENAME}$',
   '${JOB}$',
   ${MGR}$,
   '${HIREDATE}$',
   ${SAL}$,
   ${COMM}$,
   ${DEPTNO}$
)
Executing the above SQL will result in the following window being displayed:

Figure: The substitute variables window

The substitution window have the same look and functionality as the Form Data Editor i.e. you can sort, filter, insert pre-defined data, copy, paste and edit cells in the multi line editor, plus a lot of other things. In addition the substitution window adds two new commands (leftmost in the toolbar and in the form right-click menu) specifically for the substitution window:
The SQL Preview area shows the statement with all variables substituted with the values.

Here is an example of a more complex use of variables.
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 }$
This example use the full capabilities of variables. It is in fact generated by the Script to SQL Editor->INSERT COPY INTO TABLE right click menu choice in the Data tab grid.


Figure: The substitute variables window

To highlight that a variable is part of the WHERE clause in the final SQL it is represented with a green icon in front of the name.

When executing a statement that consist of variables, DbVisualizer replaces each variable with either the value as inline text or as a parameter marker. Using parameter markers to pass data with a statement is more reliable and safe than inline values. It is also the recommended technique to set values as the database engine may then pre-compile these statements properly. DbVisualizer will automatically generate a parameter marker if the variable have the type section set and if there is no nobind option specified.

The following will be replaced with a parameter marker:

${Name||rolle||String}$

These will be replaced with the variable value:

${Name||rolle}$
${Name||rolle||String||nobind}$

Variables in DbVisualizer may be used anywhere in a statement. However, there may be problems once the final statement is passed to the database for execution and it contain parameter markers in non supported places. A simple example is Oracle that don't accept parameter markers for a table name. To solve this problem either clear the type part of the variable expression or add the option nobind (see above).

Parameter Markers

Parameter markers are are usually represented in a SQL statement with a question mark, ? or a string prefixed with a colon, :somename. Example:
select * from EMP where ENAME = ? or ENAME = ?
Parameter markers are primarily used in prepared SQL statements that will be cached by the database server. The purpose with cached statements is that the database server will analyze the execution plan once when the SQL is first executed. Subsequent invocations of the same SQL will then only replace the parameter markers with appropriate values, which results in much better response than executing SQLs with dynamic values directly in the SQL.

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

With a JDBC driver that does support parameter marker processing, the following window appears when executing the previous SQL statement.


Figure: The parameter marker substitution window

(For parameter marker processing to work in the SQL Commander, make sure the SQL->Process Parameter Markers in SQL main menu option is enabled).

Output View

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


Figure: The output view

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

Log

At the top of the Log tab, you can choose to log information about the execution of your SQL statements to the GUI or to a file.


Figure: The Log destination controls

If you choose to log to file, you can enter the file path in the text field or click the button to the right of the field to launch a file browser. By default, the log information is written to the GUI, below the log destination controls.

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


Figure: The Log with one failed statement

The detail level in an error message depends on the driver and database that is being used. Some databases are very good at telling what went wrong and why, while others provide less detail.

Clicking the icon to the left of each log entry selects the corresponding SQL statement in the SQL editor. The icon also has a right-click menu with two choices: Load SQL into Editor and Insert SQL into Editor. The first choice replaces the current content of the SQL Editor with the SQL statement corresponding to the log entry, while the second inserts it at the caret position in the SQL Editor.

Log controls

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

Auto clear log

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

Result Set

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

Figure: The windows output view

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

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

Figure: The right click menu for tabs

Result set menu

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

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

Editing

A result set grid may be enabled for editing based on the following criteria:
  1. The result really is a result set
  2. The SQL is a SELECT command
  3. Only one table is referenced in the FROM clause
  4. All columns in the result set exist in the table with exactly the same names
For some databases, you must also either qualify the table name with the schema name, or make sure that the table belongs to the schema selected in the Schema list for the SQL Editor. If all of the above is true, the standard editing tool bar appears just above the grid. Read more about editing in the Edit Table Data chapter.

Multiple result sets produced by a single SQL statement

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


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

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


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

All entries with the log message "Result set fetched" are represented by a grid 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.

The column widths may vary between executions of the SQL.


Figure: The Text result set format

Chart

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

DBMS Output (Oracle)

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

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


Figure: DBMS Output tab


Copyright © 2010 DbVis Software AB. All rights reserved.