Working with SQL and retrieved data

SQLRunner provides a couple of features to work with data.
* Run single statement as well as scripts and loading scripts with start or @ command (like Oracle SQLPlus)
* Edit cells comfurtable
* Create new datasets
* Delete one or mulitple datasets at once
* Sum and count content of all or selected rows in a column
* Copy&Paste selected rows and columns into the clipboard as tab delimited text
* Navigating over imported and exported keys to referenced and referencing datasets

Create statements with multiple help sources

There are 4 possibilities to avoid typing identifiers:
1. Use the syntax completion by hit CTRL+Space -> choose the one and hit enter
2. Double click on items in the tree for tables, views, procedures, constraints, indexes
(There are a lot of context menu entries to use statement templates for these elements)
3. Double click on items in the table for columns (of the current selected table in data model)
4. Double click on a cell of the result table header

Highlighting current identifier helps examine huge statements

This is a real live example of a query collecting data in a very complex way.
There is nothing to do, if the cursor hits a identifier (at the start, at the end or within) this word will be highlighted in the whole editor window.
In a next version, you can decide hightlighting this in all other windows as well.

Run statements or scripts

All statements are separated by semikolon. If there are embedded procedure code starting like "create or replace procedure..." then separating by semikolon is suspended from start of the procedure code and waits until a slash appears mark the end of procedure code to start again with separating next statements.
1. Example of loading scripts. These scripts can use relative pathes to the current script. If there is no current script, you have to use absolute pathes.
2. If you want run selected statements (also as script) the select the text and click on Run.
You can run stored procedures and functions exactly in the way you need them embedded in you Java code.
3. If there are parameteres ( or binding variables) an modal dialog will collect all values for parameters. You can define a parameter as output. After running this statement, the result table will show the output parameter values.

Create and show the explain plan for a statement

The button "Explain" as well as the menu entry Database->Explain is enabled if the database provides this feature and there is an mplementation in SQLRunner.
A click on the button of menu "Explain" creates and shows the explain plan in the result table area. This works as well for selected text.
This feature is currently implemented for Oracle and PostgreSQL. DB2 is work in progress.

Edit cell values

Double click or using context menu (Open value editor) brings up this dialog in order to edit the cell value. It is a editor in which also very large values can be edited (think about XML content in CLOB columns). This editor has search and replace capabilities and can trim value before update.
You can open multiple cell editors simultaneously. SQLRunner take care, that only one editor at once bound to the same cell.

Working with more than one column and row

By selecting more than one row
... you can count and sum the selected cell values (sum only for numic cell content - redardless of cell data type)
... or you can delete the selected rows
... or you can copy the selected cells content into the clipboard

Navigating over imported or exported keys

This context menu opens a new window (behaviour can changed in the preferences) with all dataset referencing to the selected cell.
It is also possible to get a referenced dataset.
In the tables header a greater-than shows that this column will be referenced and a greater-than after the column name shows you this cell reference an other dataset.

Searching in result table without new select request

You can search content in you newly retrieved datasets without asking the database. Sometimes it is a havy operation to get the data and you want to find something within....

Vertical view

Sometimes it is useful to have a larger view to the cell contents. In this view all rows and all columns all are rows. You can edit in this view as well!

SQL statement history

You can open the history be clicking in the toolbar or by using menu Database->History...
The last 1000 statements will be saved in the history per default.
For special purposes you can freez this list (e.g. for mass execution of statements to prevent overrun and performance leaks)
If a list contains important information and they need to saved as proof -> the history can be saved as SQL script with runtime informations as comment.
With the button "Accept" or simple hit enter you copy the current selected statement in the editor to further execution.

Green colored list entries: successful executed statements
Yellow colored list entries: currently waiting or running statements
Red colored list entries: Failed statements (of course with the error message)

All statements contains following informations:
* Index within a script
* success state
* start time
* duration of execution in the database (e.g. building a cursor)
* duration of fetching datasets
* count result datasets and result state of fetching data
* if applicable: used parameters and there values (e.g. for prepared statements)

Previous Page: Common usage Table of Contents Next Page: Compare schemas