Command types

    INSERT INTO t1 VALUES(0); SELECT * FROM t1;

SQL Statement

Any command that you enter which does not begin with "\", ":", or "* " is an SQL Statement. The command is not terminated when you hit ENTER, like most OS shells. You terminate SQL Statements with either ";" at the end of a line, or with a blank line. In the former case, the SQL Statement will be executed against the SQL database and the command will go into the command buffer and SQL command history for editing or viewing later on. In the former case, execute against the SQL database means to transmit the SQL text to the database engine for execution. In the latter case (you end an SQL Statement with a blank line), the command will go to the buffer and SQL history, but will not be executed (but you can execute it later from the buffer). (See the note immediately above about multiple SQL statements in one SqlTool command).

(Blank lines are only interpreted this way when SqlTool is run interactively. In SQL files, blank lines inside of SQL statements remain part of the SQL statement).

As a result of these termination rules, whenever you are entering text that is not a Special Command, Buffer Command, or PL Command, you are always appending lines to an SQL Statement. (In the case of the first line, you will be appending to an empty SQL statement. I.e. you will be starting a new SQL Statement).

Special Command

Run the command "\?" to list the Special Commands. All of the Special Commands begin with "\". I'll describe some of the most useful Special Commands below.

Buffer Command

Run the command ":?" to list the Buffer Commands. All of the Buffer Commands begin with ":". Buffer commands operate upon the command "buffer", so that you can edit and/or (re-)execute previously entered commands.

PL Command

Procedural Langage commands. Run the command "*?" to list the PL Commands. All of the PL Commands begin with "*". PL commands are for setting and using scripting variables and conditional and flow control statements like * if and * while. A few PL features (such as PL aliases and updating and selecing data directly from/to files) can be a real convenience for nearly all users, so these features will be discussed briefly in this section. More detailed explanation of PL variables and the other PL features, with examples, are covered in the SqlTool Procedural Language section.

Note

The requirement for a space after the asterisk has been removed with this version of SqlFile. It is up to you whether to put a space after the *.

Raw Mode

The descriptions of command-types above do not apply to Raw Mode. In raw mode, SqlTool doesn't interpret what you type at all. It all just goes into a buffer which you can send to the database engine. Beginners can safely ignore raw mode. You will never encounter it unless you run the "\." special command, or enter a PL/SQL command. See the Raw Mode section for the details.

Essential Special Commands

\?

help

\q

quit

\dt [filter_substring]

\dv [filter_substring]

\ds [filter_substring]

\di [table_name]

\dS [filter_substring]

\da [filter_substring]

\dn [filter_substring]

\du [filter_substring]

\d* [filter_substring]

Lists available objects of the given type.

• t: non-system Tableѕ
• v: Views
• s: Synonyms
• i: Indexes
• S: System tableѕ
• a: Aliases
• n: schema Names
• u: database Users
• *: all table-like objects

If your database supports schemas, then the schema name will also be listed.

If you supply an optional filter substring, then only items which contain the given substring (in the object name or schema name) will be listed.

Important

The substring test is case-sensitive! Even though in SQL queries and for the "\d objectname" command object names are usually case-insensitive, for the \dX commands, you must capitalize the filter substring exactly as it will appear in the special command output. This is an inconvenience, since the database engine will change names in SQL to default case unless you double-quote the name, but that is server-side functionality which cannot (portably) be reproduced by SqlTool. You can use spaces and other special characters in the string.

Note that this is a change in behavior from SqlFile before about version 1.100, where substring tests were case-insensitive.

Tip

Beginning with the current version of SqlFile, filter substrings ending with "." are special. If a substring ends with ".", then this means to narrow the search by the exact, case-sensitive schema name given. For example, if I run "\d* BLAINE.", this will list all table-like database objects in the "BLAINE" schema. The capitalization of the schema must be exactly the same as how the schema name is listed by the "\dn" command. You can use spaces and other special characters in the string. (I.e., enter the name exactly how you would enter it inside of double-quotes in an SQL command). This is an inconvenience, since the database engine will change names in SQL to default case unless you double-quote the name, but that is server-side functionality which cannot (portably) be reproduced by SqlTool.

Note

Several new \dX commands have been added with the current of SqlFile. Be aware that the meaning of the \s command has changed!

Important

Indexes may not be searched for by substring, only by exact target table name. So if I1 is an index on table T1, then you list this index by running "\di T1". In addition, many database vendors will report on indexes only if a target table is identified. Therefore, "\di" with no argument will fail if your database vendor does not support it.

\d objectname [filter]

Lists names of columns in the specified table or view. objectname may be a base table name or a schema.object name.

If you supply a filter string, then only columns with a name containing the given filter will be listed. The objectname is nearly always case-insensitive (depends on your database), but the filter is always case-sensitive. You'll find this filter is a great convenience compared to other database utilities, where you have to list all columns of large tables when you are only interested in one of them.

Tip

When working with real data (as opposed to learning or playing), I often find it useful to run two SqlTool sessions in two side-by-side terminal emulator windows. I do all of my real work in one window, and use the other mostly for \d commands. This way I can refer to the data dictionary while writing SQL commands, without having to scroll.

\s

Shows the SQL command history. The SQL command history will show a number (a negative number) for each SQL Statement that has made it into the buffer so fare (by either executing or entering a blank line). You can then use the "\-" command (which is described next) to retrieve commands from the SQL history to work with. To list just the very last command, you would use the ":l" buffer command to list the buffer contents, instead of this command.

\-[3]

Enter "\" followed by the command number from SQL history, like "\-3". That command will be written to the buffer so that you can execute it or edit it using buffer commands.

(You can append a semicolon to a recall command in order to execute the recalled buffer immediately, like "\-3;". This is actually just a shortcut for running the Special Command "\-3" and the Buffer Command ":;".)

Buffer Commands

:?

help

:;

Executes the SQL statement in the current buffer against the database. This is an extremely useful command. It's easy to remember because it consists of ":", meaning Buffer Command; plus a line-terminating ";", which sends the preceding SQL to the database engine for execution.

:l

(This is a lower case L). List the current contents of the buffer.

:a

Enter append mode with the contents of the buffer as the current SQL Statement. Things will be exactly as if you physically re-typed the command that is in the buffer. Whatever line you type next will be appended to the SQL Statement. You can execute the command by terminating a line with ";", or send it back to the buffer by entering a blank line.

You can, optionally, put a string after the :a, in which case this text will be appended and you will remain in append mode. (Unless the text ends with ';', in which case the resultant statement will be executed immediately). Note that if you do put text after the "a", exactly what you type immediately after "a" will be appended. If your buffer contains SELECT x FROM mytab and you run a:le, the resultant command will be SELECT x FROM mytable. If your buffer contains SELECT x FROM mytab and you run a: ORDER BY y, the resultant command will be SELECT x FROM mytab ORDER BY y. Notice that in the latter case the append text begins with a space character.

:s/from string/to string/switches

This is the primary command for SqlTool command editing-- it operates upon the current buffer. The "to string" and the "switches" are both optional. To start with, I'll discuss the use and behavior if you don't supply any substitution mode switches.

Don't use "/" if it occurs in either "from string" or "to string". You can use any character that you want in place of "/", but it must not occur in the from or to strings. Example

    :s@from string@to string@

The to string is substituted for the first occurrence of the (case-specific)from string. The replacement will consider the entire SQL statement, even if it is a multi-line statement.

All occurrences of "$" in the from string and the to string are treated as line breaks. For example, from string of "*$FROM mytable" would actually look for occurrences of

     *
     FROM mytable

Here is a another meaningful example using $.

    :s/e)$/e) WHERE col1 is not null$/

This command appends "WHERE col1 is not null" to the line(s) which end with "e)".

The to string may be empty, in which case, occurrences of the from string are just deleted. For example

    :s/this//

would remove the first occurrence of "this". (With the "g" substitution mode switch, as explained below, it would remove all occurrences of "this").

Don't end a to string with ";" in attempt to make a SQL statement execute. There is a substitution mode switch to use for that purpose.

You can use any combination of the substitution mode switches.

• Use "i" to make the searches for from string case insensitive.

• Use "g" to substitute globally, i.e., for all occurrences of from string which are found in the text under consideration.

• Use ";" to execute the command immediately after the substitution is performed.

• Use an integer (from 1 to 9) to narrow the text under consideration to a specific line of a multi-line buffer.

The substitution facility doesn't support any regular expressions at all. When we stop supporting Java versions older than 1.4, I'll start supporting regular expressions and other advanced string manipulation functions.

Essential PL Command

* VARNAME = value

Set the value of a variable. If the variable doesn't exist yet, it will be created. The most common use for this is so that you can later use it in SQL statements, print statements, and PL conditionals, by using the *{VARNAME} construct.

If you set a variable to an SQL statement (without the terminating ";") you can then use it as a PL alias like *VARNAME, as shown in this example.

Example 8.2. Defining and using a PL alias (PL variable)

    * q = SELECT COUNT(*) FROM mytable
    \p The stored query is '*{q}'
    /q;
    /q WHERE mass > 200;

If you put variable definitions into the SQL file auto.sql in your home directory, those aliases/variables will always be available for interactive use.

* load VARNAME /file/path.txt

Sets VARNAME to the content of the specified ASCII file.

* prepare VARNAME

Indicate that next command should be a SQL INSERT or UPDATE command containing one question mark. The value of VARNAME will be substuted for the ? variable. This does work for CLOB columns.

* VARNAME _

When next SQL command is run, instead of displaying the rows, just store the very first column value to variable VARNAME. This works for CLOB columns. It also works with Oracle XML type columns if you use column labels and the getclobval function.

* dump VARNAME /file/path.txt

Store the value of VARNAME to the specified ASCII file.

\q [abort message]

Be aware that the \q command will cause SqlTool to completely exit. If a script x.sql has a \q command in it, then it doesn't matter if the script is executed like

    java -jar .../hsqldb.jar urlid a.sql x.sql z.sql

or if you use \i to read it in interactively, or if another SQL file uses \i to nest it. If \q is encountered, SqlTool will quit. See the SqlTool Procedural Language section for commands to abort an SQL file (or even parts of an SQL file) without causing SqlTool to exit.

\q takes an optional argument, which is an abort message. If you give an abort message, the message is displayed to the user and SqlTool will exit with a failure status. If you give no abort message, then SqlTool will exit quietly with successful status.

\p [text to print]

Print the given string to stdout. Just give "\p" alone to print a blank line.

\i /path/to/file.sql

Include another SQL file at this location. You can use this to nest SQL files. For database installation scripts I often have a master SQL file which includes all of the other SQL files in the correct sequence. Be aware that the current continue-upon-error behavior will apply to included files until such point as the SQL file runs its own error handling commands.

\H

Toggle HTML output mode. If you redirect output to a file, this can make a long session log much easier to view. This will HTML-ify the entire session. For example,

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

(See the Generating Text or HTML Reports section about how to easily store just the query output to file.)

\a [true|false]

This turns on and off SQL transaction autocommits. Auto-commit defaults to false, but you can change that behavior by using the --autoCommit command-line switch.

\c [true|false]

A "true" setting tells SqlTool to Continue when errors are encountered. The current transaction will not be rolled back upon SQL errors, so if \c is true, then run the ROLLCACK; command yourself if that's what you want to happen. The default for interactive use is to continue upon error, but the default for non-interactive use is to abort upon error. You can override this behavior by using the --continueOnErr or the --abortOnErr command-line switch.

With database setup scripts, I usually find it convenient to set "true" before dropping tables (so that things will continue if the tables aren't there), then set it back to false so that real errors are caught. DROP TABLE tablename IF EXISTS; is a more elegant, but less portable, way to accomplish the same thing.

Tip

It depends on what you want your SQL files to do, of course, but I usually want my SQL files to abort when an error is encountered, without necessarily killing the SqlTool session. If this is the behavior that you want, then put an explicit \c false at the top of your SQL file and turn on continue-upon-error only for sections where you really want to permit errors, or where you are using PL commands to handle errors manually. This will give the desired behavior whether your script is called by somebody interactively, from the SqlTool command-line, or included in another SQL file (i.e. nested).

Important

The default settings are usually best for people who don't want to put in any explicit \c or error handling code at all. If you run SQL files from the SqlTool command line, then any errors will cause SqlTool to roll back and abort immediately. If you run SqlTool interactively and invoke SQL files with \i commands, the scripts will continue to run upon errors (and will not roll back). This behavior was chosen because there are lots of SQL files out there that produce errors which can be ignored; but we don't want to ignore errors that a user won't see. I reiterate that any and all of this behavior can (and often should) be changed by Special Commands run in your interactive shell or in the SQL files. Only you know whether errors in your SQL files can safely be ignored.

Note

In previous versions of SqlTool, this special command was "\*". This usage is still supported, but is deprecated. It was changed because "\*" is a very poor mnemonic. Even the author of the program had to constantly look up whether "\* true" meant to Continue on error or to Abort upon error. Now, the "c" signifies Continue.

Logical Operators

TOKEN

The token may be a literal, a *{VARNAME} which is expanded early, or a *VARNAME which is expanded late. (You usually do not want to use *{VARNAME} in logical expressions). False if the token is not set, empty, or "0". True otherwise.

TOKEN1 == TOKEN2

True if the two tokens are equivalent "strings".

TOKEN1 <> TOKEN2

Ditto.

TOKEN1 >< TOKEN2

Ditto.

TOKEN1 > TOKEN2

True if the TOKEN1 string is longer than TOKEN2 or is the same length but is greater according to a string sort.

TOKEN1 < TOKEN2

Similarly to TOKEN1 > TOKEN2.

! LOGICAL_EXPRESSION

Logical negation of any of the expressions listed above.