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

your database.

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

SQL*Plus).

\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

versions).

SQLRunner

56

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

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

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:

SQLRunner

57

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.