Capítulo 10. SQLRunner
SQLRunner is a "fall-back" tool that lets you work at the raw SQL command level. This is an advanced
topic and should only be used by those familiar with the intricacies of SQL commands and the details of
SQLRunner was written by Ian Darwin, and is distributed under a liberal free-software, open-source license
which permits its inclusion in programs such as Power*Architect.
SQLRunner is started from the menu entry under the Tools menu, and begins with the GUI window shown
below. The first thing you should do is select which database connection you wish to use. The list of
Connections is the same as the main program uses, as set up in the JDBC Connections window.
The basic steps to using SQLRunner are to type a command in the top (SQL Command) window and
click the Run button; the results are displayed in the bottom (SQL Results) window. To save you some
typing, there is a "Statement Template" mechanism that will insert a template for SELECT, INSERT or
UPDATE SQL statements (just select the template you want and click "Apply Template" and the template
will replace the current Input Statement).
The command can actually be one of two kinds: either one of a half-dozen escape commands listed below,
or, anything that is valid input to your database's command interface (e.g., programs such as psql or Oracle
Escape Sequence Action
\dt Describe list of all tables
\dtT Describe column names of table named T
\dmX Set the mode, where X is the first letter of the mode
(t for text, s for SQL, h for HTML or x for XML; not
needed in the embedded version because the GUI
has a control for this)
\oF Send output to the given file instead of the screen
(though you can usually just view the output and
copy-and-paste to save parts of it into a file; does not
work in GUI versions).
\q Exit the program (not supported in embedded
SQL Statements are entered one at a time, can be more than one line long, and need not end with a
semicolon. These statements are not interpreted by SQLRunner itself, so anything that the given database
and driver accepts can be used. For example, with Oracle, you can use PL*SQL statements. With most
drivers you should be able to use stored procedures. Each SQL statement is executed in its own transaction
context, that is, changes are committed immediately (so be careful!).
Output (Results) Window
Command Output in the chosen format (see below) appears in the SQL Output window. A scrollbar will
appear if the information cannot all be seen at once.
A visual indication of the success or failure of the command is displayed below the output: green for
success, red for failure. As well, failures will be accompanied by a pop-up window containing details on
The Clear Output button clears the contents of the output window.
There are several output modes for the display of SQL "select" results: text, SQL, HTML, XML, and
Table. Output from the escape commands are always displayed as plain text. Text mode is the default, and
is primarily a raw display format. SQL output is most useful with the output of a SELECT statement; it
will generate SQL that will attempt to re-create the data in another database. HTML mode generates an
HTML table to display the results of a Select. XML format is similar but may be used for exporting data
into other applications. Finally, table mode provides a friendlier interface which ensures all of the columns
are lined up properly. In this mode, it is even possible to rearrange the columns by dragging them.
For example, with SQL mode selected, a "select * from anecdotes" (a table in a sample bookstore web
site's database, used to display a casual quotation about books) looked like this:
This could, as you can see, be used to create a SQL script to re-create the contents of the database. In fact,
some developers use SQLRunner primarily for this purpose: to create stable test databases from "live"
data that was created by their application.
You can view this same data in HTML just by changing the Format selection to HTML and clicking the
Run button again:
When copied and pasted into an HTML file and viewed in a browser, the output looked like this:
With a bit of formatting, or even a CSS style sheet, this HTML page could be made quite usable.
SQLRunner is not perfect, but it is adequate for many purposes involving direct use of SQL.