This document explains how to use SqlTool, the main purpose of which is to read your SQL text file or stdin, and execute the SQL commands therein against a JDBC database. There are also a great number of features to facilitate both interactive use (such as command-line editing and aliases) and automation (such as scripting variables and SQL transaction control and error handling).

Some of the examples below use quoting which works exactly as-is for any normal UNIX shell. I have not yet tested these commands on Windows, and I doubt whether the quoting will work just like this (though it is possible). SqlTool is still a very useful tool even if you have no quoting capability at all.

This document is now updated for version 1.37 of SqlTool and 1.86 of SqlFile (the latter is the class which does most of the work for SqlTool). The startup banner will report both versions when you run SqlTool interactively. I expect this version of this document to accurately describe SqlTool for some unknown number of versions into the future.

If you want to use SqlTool, then you either have an SQL text file, or you want to interactively type in SQL commands. If neither case applies to you, then you are looking at the wrong program.

Note that the --sql switch runs the given commands in addition to standard input and/or specified SQL files. If you want to run only SQL command(s) which you give on the command-line, then use the --sql switch and the --noinput switch.

If you put a file named auto.sql into your home directory, this file will be executed automatically every time that you run SqlTool interactively and without the --noAutoFile switch.

To use a JDBC Driver other than the HSQLDB driver, you can't use the -jar switch because you need to modify the classpath. You must add the hsqldb.jar file and your JDBC driver classes to your classpath, and you must tell SqlTool what the JDBC driver class name is. The latter can be accomplished by either using the "--driver" switch, or setting "driver" in your config file. The Authentication Setup section. explains the second method. Here's an example of the first method (after you have set the classpath appropriately).

java org.hsqldb.util.SqlTool --driver oracle.jdbc.OracleDriver urlid

If you want some sample database objects and data to play with, execute the sampledata.sql SQL file. sampledata.sql resides in the src/org/hsqldb/sample directory of your HSQLDB distribution. Run it like this from an SqlTool session

\i HSQLDB_HOME/src/org/hsqldb/sample/sampledata.sql

Authentication setup is accomplished by creating a text SqlTool configuration file. In this section, when I say configuration or config file, I mean an SqlTool configuration file (aka SqlTool RC file).

The following sample sqltool.rc file resides in the directory src/org/hsqldb/sample of your HSQLDB distribution.

# $Id: sqltool.rc,v 1.11 2004/07/18 21:34:35 unsaved Exp $

# This is a sample SqlTool configuration file, a.k.a. rc file.

# You can run SqlTool right now by copying this file to your home directory
# and running
#    java -jar /path/to/hsqldb.jar mem
# This will access the first urlid definition below in order to use a 
# personal Memory-Only database.

# If you have the least concerns about security, then secure access to
# your sqltool.rc file.
# See the documentation for SqlTool for various ways to use this file.

# A personal Memory-Only database.
urlid mem
url jdbc:hsqldb:mem:memdbid
username sa
password

# This is for a hsqldb Server running with default settings on your local
# computer (and for which you have not changed the password for "sa").
urlid localhost-sa
url jdbc:hsqldb:hsql://localhost
username sa
password


###########################################################################
# Template for a urlid for an Oracle database.
# You will need to put the oracle.jdbc.OracleDriver class into your 
# classpath.
# In the great majority of cases, you want to use the file classes12.zip
# (which you can get from the directory $ORACLE_HOME/jdbc/lib of any
# Oracle installation compatible with your server).
# Since you need to add to the classpath, you can't invoke SqlTool with
# the jar switch, like "java -jar .../hsqldb.jar..." or 
# "java -jar .../hsqlsqltool.jar...".
# Put both the HSQLDB jar and classes12.zip in your classpath (and export!)
# and run something like "java org.hsqldb.util.SqlTool...".

#urlid cardiff2
#url jdbc:oracle:thin:@aegir.admc.com:1522:TRAFFIC_SID
#username blaine
#password secretpassword
#driver oracle.jdbc.OracleDriver
###########################################################################


###########################################################################
# Template for a urlid for a Postgresql database.
# You will need to put the org.postgresql.Driver class into your 
# classpath.
# The postgresql jar will be named postgresql.jar (if you built Postgresql 
# from source), or something like pg73b1jdbc3.jar or jdbc7.2x-1.2.jar.
# You can obtain it from a client or server Postgresql installation, or 
# download it from http://jdbc.postgresql.org/download.html.
# Notice that the jar file names (other than "postgresql.jar") contain both
# the target Postgresql server version and the client-side JDBC level (which
# is determined by your client-side Java version, as explained at
# http://jdbc.postgresql.org/download.html).
# I recommend the latest production version for your JDBC version.  The 
# later JDBC drivers work better even with older Postgresql servers.
# (E.g. \dt won't list owners with an older driver).
# N.b.: Suse Linux 9.1 users should download a new driver from the PG site,
# since Suse distributes the 7.3 drivers with Postgresql 7.4 (why???).
# Since you need to add to the classpath, you can't invoke SqlTool with
# the jar switch, like "java -jar .../hsqldb.jar..." or 
# "java -jar .../hsqlsqltool.jar...".
# Put both the HSQLDB jar and the Postgresql jar in your classpath (and
# export!) and run something like "java org.hsqldb.util.SqlTool...".
# N.b.:  I notice that Postgresql is unusual in that it does not do an
# implicit commit before DDL commands.  If you get an error message 
# "... cannot run inside a transaction block", just run "commit;" and retry.

#urlid commerce
#url jdbc:postgresql://dbsvr2/commercedb
#username blaine
#password obscured
#driver org.postgresql.Driver
###########################################################################


###########################################################################
# Template for a TLS-encrypted HSQLDB Server.
# Remember that the hostname in hsqls (and https) JDBC URLs must match the
# CN of the server certificate (the port and instance alias that follows 
# are not part of the certificate at all).
# You only need to set "truststore" if the server cert is not approved by
# your system default truststore (which a commercial certificate probably
# would be).

#urlid tls
#url jdbc:hsqldb:hsqls://db.admc.com:9001/lm2
#username blaine
#password asecret
#truststore /home/blaine/ca/db/db-trust.store
###########################################################################

You can put this file anywhere you want to, and specify the location to SqlTool by using the "--rcfile" argument. If there is no reason to not use the default location (and there are situations where you would not want to), then use the default location and you won't have to give "--rcfile" arguments to SQLTool. The default location is sqltool.rc in your home directory. If you have any doubt about where that is, then just run SqlTool with a phony urlid and it will tell you where it expects the configuration file to be.

    java -jar $HSQLDB_HOME/lib/hsqldb.jar x

The config file consists of stanza(s) like this:

    urlid web
    url jdbc:hsqldb:hsql://localhost
    username web
    password webspassword

These four settings are required for every urlid. (There are optional settings also, which are described a couple paragraphs down). You can have as many blank lines and comments like

    # This comment

in the file as you like. The whole point is that the urlid that you give in your SqlTool command must match a urlid in your configuration file.

It should be readable, both locally and remotely, only to users who need to use the records in it to run SqlTool. On UNIX, this is easily accomplished by using chmod/chown commands and making sure that it is protected from anonymous remote access (like via NFS, FTP or Samba).

You can also put the following optional settings into a urlid stanza. The setting will, of course, only apply to that urlid.

Property and SqlTool command-line switches override settings made in the configuration file.

Do read the The Bare Minimum section before you read this section.

You run SqlTool interactively by specifying no SQL filepaths on the SqlTool command line. Like this.

    java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid

You exit your session by using the "\q" special command or ending input (like with Ctrl-D or Ctrl-Z).

When you are typing into SqlTool, you are always typing part of the current command. The buffer is the last SQL command. If you're typing an SQL command, then the previous SQL command will be in the buffer, not the one you are currently typing. The current command could be any type of command, but only SQL commands get moved to the buffer when they are completed. When you type command-editing commands, the current command is the editing command (like ":s/tbl/table/"), the result of which is to modify the SQL command in the buffer (which can thereafter be executed). The ":a" command (with no argument) is special in that it takes a copy of the SQL command in the buffer and makes that the current command, leaving you in a state where you are appending to that now current command. The buffer is the zeroeth item of the SQL command history.

This list here includes only the essential Special Commands, but n.b. that there are other useful Special Commands which you can list by running \?. (You can, for example, execute SQL from external SQL files, and save your interactive SQL commands to files). Some specifics of these other commands are specified immediately below, and the Generating Text or HTML Reports section explains how to use the "\o" and "\H" special commands to generate reports.

Be aware that the \! Special Command does not work for external programs that read from standard input. You can invoke non-interactive and graphical interactive programs, but not command-line interactive programs.

SqlTool executes \! programs directly, it does not run an operating system shell (this is to avoid OS-specific code in SqlTool). Because of this, you can give as many command-line arguments as you wish, but you can't use shell wildcards or redirection.

The \w command can be used to store any command in your SQL history to a file. Just restore the command to the buffer (which is the 0th element of the history) with a command like "\-4" before you give the \w command.

See the Procedural Language section below for information on using variables in other ways, and information on the other PL commands and features.

java -Dsqltool.historyLength=40 -jar $HSQLDB_HOME/lib/hsqldb.jar urlid

If for some reason you want SqlTool to process your commands as if it were reading an SQL file (i.e., no startup banner, no command prompts, no Buffer/history commands, aborting upon failure by default, allow blank lines with SQL statements), then specify a SQL filepath of "-", like

    java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid -

This is very useful in (at least) two situations. This is how you would pipe input into SqlTool, which is extremely useful in shell scripts where the SQL code is generated dynamically, like

    echo "Some SQL commands with '$VARIABLES';" |
    java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid -

This is also a good way to test what SqlTool will do when it encounters a specific command in a SQL file. You emulate SQL file execution while giving SqlTool commands interactively.

Read the Interactive section if you have not already, because much of what is in this section builds upon that. Even if your plans are to run SqlTool non-interactively, you should really learn to run it interactively because it's such a powerful debugging tool, and you can use it to prototype sql scripts.

As you'll see, SqlTool has many features that are very convenient for scripting. But what really makes it superior for automation tasks (as compared to SQL tools from other vendors) is the ability to reliably detect errors and to control JDBC transactions.

java -jar $HSQLDB_HOME/lib/hsqldb.jar --noinput --sql 'SQL statement;' urlid

java -jar $HSQLDB_HOME/lib/hsqldb.jar --noinput --sql 'SQL statement;' urlid '
    SQL statement number one;
    SQL statement
        number two;
    SQL statement three;
' urlid

    # A shell script
    USERCOUNT=`java -jar $HSQLDB_HOME/lib/hsqldb.jar --noinput --sql 'select count(*) from usertbl;' urlid` || {
        # Handle the SqlTool error
    }
    echo "There are $USERCOUNT users registered in the database."
    [ "$USECOUNT" -gt 3 ] && {   # If there are more than 3 users registered
        # Some conditional shell scripting

java -jar $HSQLDB_HOME/lib/hsqldb.jar sql... > /tmp/log.sql 2>&1

/*
    $Id: sample.sql,v 1.3 2004/07/12 16:51:01 unsaved Exp $
    Examplifies use of SqlTool.
    PCTASK Table creation
*/

/* Ignore error for the two drop statements
 * For HSQLDB databases, you can use "IF EXISTS" instead of ignoring errors:
 *    DROP TABLE x IF EXISTS;
 * "IF EXISTS" is non-portable, however.
 */
\* true
DROP TABLE pctasklist;
DROP TABLE pctask;
\* false

\p Creating table pctask
CREATE TABLE pctask (
    id integer identity,
    name varchar(40),
    description varchar,
    url varchar,
    UNIQUE (name)
);

\p Creating table pctasklist
CREATE TABLE pctasklist (
    id integer identity,
    host varchar(20) not null,
    tasksequence int not null,
    pctask integer,
    assigndate timestamp default current_timestamp,
    completedate timestamp,
    show bit default true,
    FOREIGN KEY (pctask) REFERENCES pctask,
    UNIQUE (host, tasksequence)
);

\p Granting privileges
GRANT select ON pctask TO public;
GRANT all ON pctask TO tomcat;
GRANT select ON pctasklist TO public;
GRANT all ON pctasklist TO tomcat;

\p Inserting test records
INSERT INTO pctask (name, description, url) VALUES (
    'task one', 'Description for task 1', 'http://cnn.com');
INSERT INTO pctasklist (host, tasksequence, pctask) VALUES (
    'admc-masq', 101, SELECT id FROM pctask WHERE name = 'task one');

commit;

    java -jar $HSQLDB_HOME/lib/hsqldb.jar  --sql "create user 'tomcat' password 'x';" mem path/to/hsqldb/src/org/hsqldb/sample/sample.sql

    SELECT count(*) FROM atable;
    /* Lots of
     comments interspersed among
     several lines */   SELECT count(*)
    FROM btable;

    SELECT count(*) FROM
    /* atable */
    btable;

java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid < filepath1.sql > /tmp/log.html 2>&1

cat filepath1.sql... |
java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid > /tmp/log.html 2>&1

java -Dsqltool.charset=UTF-8 -jar $HSQLDB_HOME/lib/hsqldb.jar urlid filepath1.sql...

This section is about making a file containing the output of database queries. You can generate reports by using operating system facilities such as redirection, tee, and cutting and pasting. But it is much easier to use the "\o" and "\H" special commands.

It is not just the output of "SELECT" statements that will make it into the report file, but

Most importantly, run SqlTool interactively and give the "* ?" command to see what PL commands are available to you.

PL variables will only be expanded after you run a PL command (or set variable(s) from the command-line). We only want to turn on variable expansion if the user wants variable expansion. People who don't use PL don't have to worry about strings getting accidentally expanded.

For all PL commands (other than plain "*") the leading "*" must be followed by whitespace. The only reason for this requirement is to provide for aliases, i.e., the ability to short-cut *VARNAME for *{VARNAME} at the beginning of a command. Therefore, "*X" is an alias whereas "* X" is a PL command.

The command "*" is an exception to this rule. it doesn't matter whether you put white space after it. All other PL commands imply the "*" command, so you only need to use the "*" statement if your script uses PL variables and it is possible that no variables may be set before-hand (and no PL commands have been run previously). In this case, without "*", your script would silently use a literal value like "*{x}" instead of trying to expand it. With a preceding "*" command, PL will notice that the variable x has not been set and will generate an error. (If x had been set here will be no issue because setting a variable automatically turns on PL variable expansion).

Aliasing may only be used for SQL statements. You can define variables for everything in a Special or PL Command, except for the very first character ("\" or "*"). Therefore, you can use variables but not aliasing in Special and PL Commands. Here is a hyperbolically impractical example to show the extent to which PL variables can be used in Special commands even though you can not use them as aliases.

        sql> * qq = p Hello Butch
        sql> \*{qq} done now
        Hello Butch done now

    /*
       grantwrite.sql

       Run SqlTool like this:
           java -jar path/to/hsqldb.jar -setvar USER=debbie grantwrite.sql
     */

    /* Explicitly turn on PL variable expansion, in case no variables have
       been set yet.  (Only the case if user did not set USER).
    */
    *

    GRANT all ON book TO *{USER};
    GRANT all ON category TO *{USER};

Below is an example SQL File that shows how to use most PL features. If you have a question about how to use a particular PL feature, check this example before asking for help. This file resides in the src/org/hsqldb/sample directory with the name pl.sql. Definitely give it a run, like

java -jar $HSQLDB_HOME/lib/hsqldb.jar mem $HSQLDB_HOME/src/org/hsqldb/sample/pl.jar

/*
    $Id: pl.sql,v 1.3 2004/06/10 01:44:52 unsaved Exp $
    SQL File to illustrate the use of SqlTool PL features.
    Invoke like
        java -jar .../hsqldb.jar .../pl.sql mem
                                                         -- blaine
*/

* if (! *MYTABLE)
    \p MYTABLE variable not set!
    /* You could use \q to Quit SqlTool, but it's often better to just
       break out of the current SQL file.
       If people invoke your script from SqlTool interactively (with
       \i yourscriptname.sql) any \q will kill their SqlTool session. */
    \p Use arguments "--setvar MYTABLE=mytablename" for SqlTool
    * break
* end if

/* Turning on Continue-upon-errors so that we can check for errors ourselves.*/
\* true

\p
\p Loading up a table named '*{MYTABLE}'...

/* This sets the PL variable 'retval' to the return status of the following
   SQL command */
* retval ~
CREATE TABLE *{MYTABLE} (
    i int,
    s varchar
);
\p CREATE status is *{retval}
\p

/* Validate our return status.  In logical expressions, unset variables like
   *unsetvar are equivalent to empty string, which is not equal to 0
   (though both do evaluate to false on their own, i.e. (*retval) is false
   and (0) is false */
* if (*retval != 0)
    \p Our CREATE TABLE command failed.
    * break
* end if

/* Default Continue-on-error behavior is what you usually want */
\* false
\p

/* Insert data with a foreach loop.
   These values could be from a read of another table or from variables
   set on the command line like
*/
\p Inserting some data int our new table (you should see 3 row update messages)
* foreach VALUE (12 22 24 15)
    * if (*VALUE > 23)
        \p Skipping *{VALUE} because it is greater than 23
        * continue
        \p YOU WILL NEVER SEE THIS LINE, because we just 'continued'.
    * end if
    INSERT INTO *{MYTABLE} VALUES (*{VALUE}, 'String of *{VALUE}');
* end foreach
\p

* themax ~
/* Can put Special Commands and comments between "* VARNAME ~" and the target 
   SQL statement. */
\p We're saving the max value for later.  You'll still see query output here:
SELECT MAX(i) FROM *{MYTABLE};

/* This is usually unnecessary because if the SELECT failed, retval would
   be undefined and the following print statement would make SqlTool exit with
   a failure status */
* if (! *themax)
    \p Failed to get the max value.
    /* It's possible that the query succeeded but themax is "0".
       You can check for that if you need to. */
    * break
    \p YOU WILL NEVER SEE THIS LINE, because we just 'broke'.
* end if

\p
\p ##############################################################
\p The results of our work:
SELECT * FROM *{MYTABLE};
\p MAX value is *{themax}

\p
\p Everything worked.

This section is only for those users who want to use SqlTool but without the overhead of hsqldb.jar.

If you do not need to directly use JDBC URLs like jdbc:hsqldb:mem: + something, jdbc:hsqldb:file: + something, or jdbc:hsqldb:res: + something, then you can use hsqlsqltool.jar in place of the much larger hsqldb.jar file. hsqlsqltool.jar will work for all JDBC databases other than HSQLDB Memory-only and In-process databases (the latter are fine if you access them via a HSQLB Server or WebServer). You will have to supply the JDBC driver for non-HSQLDB URLs.

The HSQLDB distribution doesn't "come with" a pre-built hsqlsqltool.jar file. I recommend that you either download the Java 1.4 jar at http://admc.com/dist/hsqlsqltool-1.7.2.jar (by right-clicking and downloading if you're reading this with a web browser), or build the jarsqltool target, as explained in the Building Hsqldb version 1.7.2 appendix.

If you are using the HSQLDB JDBC driver (i.e., you're connecting up to a URL like jdbc:hsqldb:hsql + something or jdbc:hsqldb:http + something), you run SqlTool exactly as with hsqldb.jar except you use the file path to your hsqlsqltool.jar file (for example, hsqlsqltool-1.7.2.jar) instead of the path to hsqldb.jar.

If you are using a non-HSQLDB JDBC driver, follow the instructions at the end of the The Bare Minimum section, but use your hsqlsqltool jar file in place of hsqldb.jar.

A unit testing framework is in place. This assures the robustness of SqlTool. See the file testrun/sqltool/readme.txt for instructions on running, modifying, or creating unit tests. To create a new unit test, you create a SQL file and embed metacommands in the SQL file inside of comments. The metacommands tell the test harness (org.hsqldb.test.SqlToolHarness) how to run SqlTool (like with what arguments) and what output to expect (i.e. the test criteria). You can run tests without JUnit, or you can make a JUnit wrapper in the normal fashion. Any SQL test file can be added to our JUnit SqlTool test suite by just adding the SQL file name and description to the testrun/sqltool/*.list file for the desired JUnit test method.

(The SqlTool unit tests require java 1.4).