SqlTool runs on any Java 1.5 or later platform. I know that SqlTool works well with Sun and OpenJDK JVMs. I haven't run other vendors' JVMs in years (IBM, JRockit, etc.). As my use with OpenJDK proves that I don't depend on Sun-specific classes, I expect it to work well with other (1.5-compatible) Java implementations.

SqlTool no longer writes any files without being explicitly instructed to. Therefore, it should work fine on read-only systems, and you'll never have orphaned temp files left around.

The command-line examples in this chapter work as given on all platforms (if you substitute in a normalized path in place of $HSQLDB_HOME), except where noted otherwise. When doing any significant command-line work on Windows (especially shell scripting), you're better off to completely avoid paths with spaces or funny characters. If you can't avoid it, use double-quotes and expect problems. As with any Java program, file or directory paths on the command line after "java" can use forward slashes instead of back slashes (this goes for System properties and the CLASSPATH variable too). I use forward slashes because they can be used consistently, and I don't have to contort my fingers to type them :).

If you are using SqlTool from a HyperSQL distribution of version 1.8.x or earlier, you should use the documentation with that distribution, because this manual documents many new features, several significant changes to interactive-only commands, and a few changes effecting backwards-compatibility (see next section about that). This document is now updated for the current versions of SqlTool and SqlFile at the time I am writing this (versions 3406 and 3604 correspondingly-- SqlFile is the class which actually processes the SQL content for SqlTool). Therefore, if you are using a version of SqlTool or SqlFile that is more than a couple revisions greater, you should find a newer version of this document. (The imprecision is due to content-independent revision increments at build time, and the likelihood of one or two behavior-independent bug fixes after public releases). The startup banner will report both versions when you run SqlTool interactively. (Dotted version numbers of SqlTool and SqlFile definitely indicate ancient versions).

This guide covers SqlTool as bundled with HSQLDB after 1.8.x. ^[1]

This section lists changes to SqlTool since the last major release of HSQLDB which may effect the portability of SQL scripts. For this revision of this document, this list consists of script-impacting changes made to SqlTool after the final 1.8.0.0 HSQLDB release. I'm specifically not listing changes to interactive(:)-only commands ( with one legacy exception which is listed below),i since these commands can't be used in SQL scripts; and I'm specifically not listing backwards-compatible feature additions and enhancements. The reason for limiting the change list to only portability- impacting changes is that a list of all enhancements since just 1.8.1.1 would be pages long.

Although it doesn't effect scripts, I will mention a significant recent change to interactive commands which could confuse existing power users. Special and PL commands are now stored to the edit buffer and to command history, so they can be recalled and edited just like SQL commands. Now, all commands other than edit/history : commands are stored to the buffer and history.

To repeat what is stated in the JavaDoc for the SqlTool class itself: Programmatic users will usually want to use the objectMain(String[]) method if they want arguments and behavior exactly like command-line SqlTool. If you don't need invocation parameter parsing, auto.sql exection, etc., you will have more control and efficiency by using the SqlFile class directly. The file src/org/hsqldb/sample/SqlFileEmbedder.java in the HyperSQL distribution provides an example for this latter strategy.

There are some SQL types which SqlTool (being a text-based program) can't display properly. This includes the SQL types BLOB, JAVA_OBJECT, STRUCT, and OTHER. When you run a query that returns any of these, SqlTool will save the very first such value obtained to the binary buffer and will not display any output from this query. You can then save the binary value to a file, as explained in the Storing and retrieving binary files section.

There are other types, such as BINARY, which JDBC can make displayable (by using ResultSet.getString()), but which you may very well want to retrieve in raw binary format. You can use the \b command to retrieve any-column-type-at-all in raw binary format (so you can later store the value to a binary file).

Another restriction which all text-based database clients have is the practical inability for the user to type in binary data such as photos, audio streams, and serialized Java objects. You can use SqlTool to load any binary object into a database by telling SqlTool to get the insert/update datum from a file. This is also explained in the Storing and retrieving binary files section.

Desktop shortcuts and quick launch icons are useful, especially if you often run SqlTool with the same set of arguments. It's really easy to set up several of them-- one for each way that you invoke SqlTool (i.e., each one would start SqlTool with all the arguments for one of your typical startup needs). One typical setup is to have one shortcut for each database account which you normally use (use a different urlid argument in each shortcut's Target specification.

Desktop icon setup varies depending on your Desktop manager, of course. I'll explain how to set up a SqlTool startup icon in Windows XP. Linux and Mac users should be able to take it from there, since it's easier with the common Linux and Mac desktops.

If you want some sample database objects and data to play with, execute the sample/sampledata.sql SQL file ^[1]. To separate the sample data from your regular data, you can put it into its own schema by running this before you import:

    CREATE SCHEMA sampledata AUTHORIZATION dba;
    SET SCHEMA sampledata;

    \i HSQLDB_HOME/sample/sampledata.sql

For memory-only databases, you'll need to run this every time that you run SqlTool. For other (persistent) databases, the data will reside in your database until you drop the tables.

If you are using SqlTool to access non-HSQLDB database(s), then you should use the latest and greatest-- just grab the newest public release of SqlTool (like from the latest public HyperSQL release) and skip this subsection.

You are strongly encouraged to use the latest SqlTool release to access older HSQLDB databases, to enjoy greatly improved SqlTool robustness and features. It is very easy to do this.

For these situations, you need to add your custom, third-party, or SQL driver classes to your Java CLASSPATH. Java doesn't support adding arbitrary elements to the classpath when you use the -jar, so you must set a classpath containing sqltool.jar plus whatever else you need, then invoke SqlTool without the -jar switch, as briefly described at the end of the The Bare Minimum section. For embedded apps, invoke your own main class instead of SqlTool, and you can invoke SqlTool or SqlFile from your code base.

To customize the classpath, you need to set up your classpath by using your operating system or shell variable CLASSPATH or by using the java switch -cp (or the equivalent -classpath). I'm not going to take up space here to explain how to set up a Java CLASSPATH. That is a platform-dependent task that is documented well in tons of Java introductions and tutorials. What I'm responsible for telling you is what you need to add to your classpath. For the non-embedded case where you have set up your CLASSPATH environmental varialbe, you would invoke SqlTool like this.

    java org.hsqldb.cmdline.SqlTool ...

Note that without the -jar switch, SqlTool will still automatically pull in HyperSQL JDBC driver or engine classes from HyperSQL jar files in the same directory. It's often a good practice to minimize your runtime classpath. To prevent the possibility of pulling in classes from other HyperSQL jar files, just copy sqltool.jar to some other directory (which does not contain other HyperSQL jar files) and put the path to that one in your classpath.

You can distribute SqlTool along with your application, for standalone or embedded invocation. For embedded use, you will need to customize the classpath as discussed in the previous item. Either way, you should minimize your application footprint by distributing only those HyperSQL jar files needed by your app. You will obviously need sqltool.jar if you will use the SqlTool or SqlFile class in any way. If your app will only connect to external HyperSQL listeners, then build and include hsqljdbc.jar. If your app will also run a HyperSQL Listener, you'll need to include hsqldb.jar. If your app will connect directly to a in-process catalog, then include hsqldbmain.jar. Note that you never need to include more than one of hsqldb.jar, hsqldbmain.jar, hsqljdbc.jar, since the former jars include everything in the following jars.

If you just want to be able to run SqlTool (interactively or non-interactively) on a PC, and have no need for documentation, then it's usually easiest to just copy sqltool.jar and hsqldb.jar to the PCs (plus JDBC driver jars for any other target databases). If you want to minize what you distribute, then build and distribute hsqljdbc.jar or hsqldbmain.jar instead of hsqldb.jar, according to the criteria listed in the previous sub-section.

If you are really comfortable with grep, perl, or vim, you will instantly be an expert with SqlTool command-line editing. Due to limitations of Java I/O, we can't use up-arrow recall, which many people are used to from DosKey and Bash shell. If you don't know how to use regular expressions, and don't want to learn how to use them, then just forget command-recall.

If you use regular expressions to search through your command history, or to modify commands, be aware that the command type of commands in history are fixed. You can search and modify the text after a \ or * prefix (if any), but you can't search on or change a prefix (or add or remove one).

When you are typing into SqlTool, you are always typing part of the immediate command. If the immediate command is an SQL statement, it is executed as soon as SqlTool reads in the trailing (unquoted) semi-colon. Commands of the other command types are executed as soon as you hit ENTER. The interactive : commands can perform actions with or on the edit buffer. The edit buffer usually contains a copy of the last command executed, and you can always view it with the :b command. If you never use any : commands, you can entirely ignore the edit buffer. If you want to repeat commands or edit previous commands, you will need to work with the edit buffer. The immediate command contains whatever (and exactly what) you type. The command history and edit buffer may contain any type of command other than comments and : commands (i.e., : commands and comments are just not copied to the history or to the edit buffer).

Hopefully an example will clarify the difference between the immediate command and the edit buffer. If you type in the edit buffer Substitution command ":s/tbl/table/", the :s command that you typed is the immediate command (and it will never be stored to the edit buffer or history, since it is a : command), but the purpose of the substitution command is to modify the contents of the edit buffer (perform a substitution on it)-- the goal being that after your substitutions you would execute the buffer with the ":;" command. The ":a" command is special in that when you hit ENTER to execute it, it copies the contents of the edit buffer to a new immediate command and leaves you in a state where you are appending to that immediate command (nearly) exactly as if you had just typed it in.

You don't set the ? variable. It is just like the Bourne shell variable $? in that it is always automatically set to the first value of a result set (or the return value of other SQL commands). It works just like the * VARNAME ~ command described above, but it all happens automatically. You can, of course, dereference ? like any PL variable, but it does not list with the list and listvalues commands. You can see the value whenever you want by running

    \p *{?}

Note that PL commands are used to upload and download column values to/from local ASCII files, but the corresponding actions for binary files use the special \b commands. This is because PL variables are used for ASCII values and you can store any number of column values in PL variables. This is not true for binary column values. The \b commands work with a single binary byte buffer.

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

You can upload binary files such as photographs, audio files, or serialized Java objects into database columns. SqlTool keeps one binary buffer which you can load from files with the \bl command, or from a database query by doing a one-row query for any non-displayable type (including BLOB, OBJECT, and OTHER). In the latter case, the data returned for the first non-displayable column of the first result row will be stored into the binary buffer.

Once you have data in the binary buffer, you can upload it to a database column (including BLOB, OBJECT, and OTHER type columns), or save it to a file. The former is accomplished by the special command \bp followed by a prepared SQL query containing one question mark place-holder to indicate where the data gets inserted. The latter is accomplished with the \bd command.

You can also store the output from normal, displayable column into the binary buffer by using the special command \b. The very first column value from the first result row of the next SQL command will be stored to the binary byte buffer.

    \bl /tmp/favoritesong.mp3
    \bp
    INSERT INTO musictbl (id, stream) VALUES(3112, ?);

    SELECT stream FROM musictbl WHERE id = 3112;
    \bd /tmp/favoritesong.mp3

You can also store and retrieve text column values to/from ASCII files, as documented in the Essential PL Command section.

The SQL history shown by the \h command, and used by other commands, is truncated to 100 entries, since its utility comes from being able to quickly view the history list. You can change the history length by setting the system property sqltool.historyLength to the desire integer value (using any of the System Property mechanisms provided by Java). If there is any demand, I'll make the setting of this value more convenient.

The SQL history list contains all executed commands other than Edit Buffer commands and comments, even if the command has a syntax error or fails upon execution. The reason for including bad commands is so that you can recall and fix them if you wish to. The same applies to the edit buffer. If you copy a command to the edit buffer by entering blank line, or if you edit the edit buffer, that edit buffer value will never make it into the command history until and if you execute it.

You normally use non-interactive mode for input piping. You specify "-" as the SQL file name. See the Piping and shell scripting subsection of the Non-Interactive chapter.

You can run SqlTool interactively, but have SqlTool behave exactly as if it were processing an SQL file (i.e., no command-line prompts, error-handling that defaults to fail-upon-error, etc.). Just specify "-" as the SQL file name in the command line. This is a good way to test what SqlTool will do when it encounters any specific command in an SQL file. See the Piping and shell scripting subsection of the Non-Interactive chapter for an example.

If you just have a couple Commands to run, you can run them directly from the comand-line or from a shell script without an SQL file, like this.

java -jar $HSQLDB_HOME/lib/sqltool.jar --sql="SQL statement;" urlid

	Note
	The --sql switch automatically implies --noinput, so if you want to execute the specified SQL before and in addition to an interactive session (or stdin piping), then you must also give the --stdinput switch.

Since SqlTool transmits SQL statements to the database engine only when a line is terminated with ";", if you want feedback from multiple SQL statements in an --sql expression, you will need to use functionality of your OS shell to include linebreaks after the semicolons in the expression. With any Bourne-compatible shell, you can include linebreaks in the SQL statements like this.

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

	Note
	The multi-line examples in this section will only work as-is with a Bourne-compatible shell. With some ugliness they can be converted to C shell. For Windows, you are better off to stick with SQL files for multi-line input.

If you don't need feedback, just separate the SQL commands with semicolons and the entire expression will be chunked.

The --sql switch is very useful for setting shell variables to the output of SQL Statements, like this.

    # A shell script
    USERCOUNT=`java -jar $HSQLDB_HOME/lib/sqltool.jar --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

Just give paths to sql text file(s) on the command line after the urlid.

Often, you will want to redirect output to a file, like

 java -jar $HSQLDB_HOME/lib/sqltool.jar urlid file.sql... > /tmp/file.log 2>&1

You can also execute SQL files from an interactive session with the "\i"' Special Command, but be aware that the default behavior in an interactive session is to continue upon errors. If the SQL file was written without any concern for error handling, then the file will continue to execute after errors occur. You could run \c false before \i filename, but then your SqlTool session will exit if an error is encountered in the SQL file. If you have an SQL file without error handling, and you want to abort that file when an error occurs, but not exit SqlTool, the easiest way to accomplish this is usually to add \c false to the top of the script.

If you specify multiple SQL files on the command-line, the default behavior is to exit SqlTool immediately if any of the SQL files encounters an error.

SQL files themselves have ultimate control over error handling. Regardless of what command-line options are set, or what commands you give interactively, if a SQL file gives error handling statements, they will take precedence.

You can also use \i in SQL files. This results in nested SQL files.

You can use the following SQL file, sample/sample.sql, from your HyperSQL distribution ^[1]. It contains SQL as well as Special Commands making good use of most of the Special Commands documented below.

/*
    $Id: sample.sql 3637 2010-06-07 00:59:13Z unsaved $
    Exemplifies use of SqlTool.
    PCTASK Table creation
*/

/* Ignore error for these two statements */
\c true
DROP TABLE pctasklist;
DROP TABLE pctask;
\c false

\p Creating table pctask
CREATE TABLE pctask (
    id integer identity,
    name varchar(40),
    description varchar(256),
    url varchar(80),
    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 boolean 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;

You can execute this SQL file with a Memory Only database with a command like

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

	Note
	The example above uses Bourne shell syntax. C shell syntax would be similar. You would need to use an SQL file to accomplish this on Windows.

(The --sql="create...;" arguments create an account which the script uses). You should see error messages between the Continue-on-error...true and Continue-on-error...false. The script purposefully runs commands that might fail there. The reason the script does this is to perform database-independent conditional table removals. (The SQL clause IF EXISTS is more graceful and succinct, so you may want to use that if you don't need to support databases which don't support IF EXISTS). If an error occurs when continue-on-error is false, the script would abort immedately.

You can of course, redirect output from SqlTool to a file or another program.

java -jar $HSQLDB_HOME/lib/sqltool.jar urlid file.sql > file.txt 2>&1

java -jar $HSQLDB_HOME/lib/sqltool.jar urlid file.sql 2>&1 | someprogram...

You can type commands in to SqlTool while being in non-interactive mode by supplying "-" as the file name. This is a good way to test how SqlTool will behave when processing your SQL files.

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

This is how you have SqlTool read its input from another program:

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

Warning

Beware of null stdin to SqlTool (or SqlFile). At least with Java 6 on UNIX, System.in returns megabytes of garbage for reads if stdin is closed. I consider this an obvious bug. Therefore, unlike any other program you would invoke from scripts, check stdin before running any Java program that will read from it. I consider this a big ugly bug in Java. This is not just theoretical, because many remote execution environments will have stdin closed off.

Make sure that you also read the Giving SQL on the Command Line section. The --sql switch is a great facility to use with shell scripts.

If you want your SQL scripts optimally compatible among other SQL tools, then don't use any Special or PL Commands. SqlTool has default behavior which I think is far superior to the other SQL tools, but you will have to disable these defaults in order to have optimally compatible behavior.

These switches provide compatibilty at the cost of poor control and error detection.

You don't have to worry about accidental expansion of PL variables, since SqlTool will never expand PL variables if you don't set any variables on the command line, or give any "* " PL commands. (And you could not have "* " commands in a compatible SQL file).

Comments of the form /*...*/ or -- behave as a SQL programmer would expect, in all contexts other than in interactive edit/history commands.

If a comment occurs outside of an SQL statement, SqlTool will not send the comment to the database (to improve performance). Raw mode can be used to send just comments to the database. In order to proactively catch accidents, SqlTool will complain if you attempt to send an empty SQL statement (i.e., just whitespace) to the database, even in raw mode.

Don't use Edit Buffer / History Commands in your sql files, because they won't work. Edit Buffer / History Commands are for interactive use only. (But, see the Raw Mode section for an exception). You can, of course, use any SqlTool command at all interactively. I just wanted to group together the commands most useful to script-writers.

\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 .../sqltool.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. As a result, \q means to make an immediate but graceful exit, whereas \q Message means to abort immediately.
\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.
\o [file/path.txt]	Tee output to the specified file (or stop doing so). See the Generating Text or HTML Reports section.
\=	A database-independent way to commit your SQL session. Useful for database which have no COMMIT SQL statement.
\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 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.

SqlTool is ideal for mission-critical automation because, unlike other SQL tools, SqlTool returns a dependable exit status and gives you control over error handling and SQL transactions. Autocommit is off by default, so you can build a completely dependable solution by intelligently using \c commands (Continue upon Errors) and commit statements, and by verifying exit statuses.

Using the SqlTool Procedural Language, you have ultimate control over program flow, and you can use variables for database input and output as well as for many other purposes. See the SqlTool Procedural Language section.

Some script developers may run into cases where they want to run with sql files but they alwo want SqlTool's interactive behavior. For example, they may want to do command recall in the sql file, or they may want to log SqlTool's command-line prompts (which are not printed in non-interactive mode). In this case, do not give the sql file(s) as an argument to SqlTool, but pipe them in instead, like

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

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

There are several levels of encoding settings. First there are your platform defaults. These can be changed, temporarily or permanently, with system settings or environmental variables. Java system properties may be used to change the encodings for the JVM run. Finally, can specify a different encoding in your RC file, as documented in the RC File Authentication Setup section, though these will not effect stdin or stdout (as explained there). Programmatic users of SqlFile have complete control over encoding by setting up Readers and PrintWriters, or by using constructors with an encoding parameter. Developers should understand that where a SqlFile constructor takes a Reader or a PrintWriter parameter, we will not apply encoding settings to them, leaving that up to you.

Macros are just shortcut commands that you can run in place of the full commands which they stand for. Macros stand for SQL, Special or PL commands, whereas PL variables can only be used for elements within a command. It is very easy to define, list, and use macros. Run the command "/?" to see how. If you often run a particular query, then for the effort of about 5 extra keystrokes, you can define a macro for it so that you can enter just "/q;" to run it, whether the original query is 1 line or 40 lines. (You can use any name in place of "q", and the target command can be any kind of SQL, special, or PL command).

When you run/use a macro, you can append to the macro value. appendage in the "/?" listing shows where you can append additional text to the original command. So, if you define

    sql> /= myworkers  SELECT name FROM employees

    sql> /myworkers WHERE dept = 20;
    sql> /myworkers WHERE name like 'Karen%';

Just like when recalling a command from history, you use ";" to execute even Special and PL macro commands.

    sql> /= notate  Work completed by
    sql> /notate Blaine;

To make a macro for a mult-line SQL statement, you use the "/= name :" construct. First, get the target command into the command buffer. If you have already run the command, then run ":h" to see the command number and load it to the buffer like ":13". If you haven't run the command yet, then just enter the command, but end it with a blank line (and no semi-colon). You can check the buffer with ":b" to make sure it is what you want. Then just run "/= name :" to define a macro with name "name".

Here is a short SQL file that gives the specified user write permissions on some application tables.

    /*
       grantwrite.sql

       Run SqlTool like this:
           java -jar path/to/sqltool.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};

Note that this script will work for any (existing) user just by supplying a different user name on the command-line. I.e., no need to modify the tested and proven script. There is no need for a commit statement in this SQL file since no DML is done. If the script is accidentally run without setting the USER variable, SqlTool will give a very clear notificaton of that.

The purpose of the plain "*" command is just so that the *{USER} variables will be expanded. (This would not be necessary if the USER variable, or any other variable, were set, but we don't want to depend upon that).

Logical expressions occur only inside of logical expression parentheses in PL statements. For example, if (*var1 > astring) and while (*checkvar). (The parentheses after "foreach" do not enclose a logical expression, they just enclose a list).

There is a critical difference between *{VARNAME} and *VARNAME inside logical expressions. *{VARNAME} is expanded one time when the parser first encounters the logical expression. *VARNAME is re-expanded every time that the expression is evaluated. So, you would never want to code * while (*{X} < 5) because the statement will always be true or always be false. (I.e. the following block will loop infinitely or will never run).

Don't use quotes or whitespace of any kind in *{VARNAME} variables in expressions. (They would expand and then the expression would most likely no longer be a valid expression as listed in the table below). Quotes and whitespace are fine in *VARNAME variables, but it is the entire value that will be used in evaluations, regardless of whether quotes match up, etc. I.e. quotes and whitespace are not special to the token evaluator.

*VARNAMEs in logical expressions, where the VARNAME variable is not set, evaluate to an empty string. Therefore (*UNSETVAR = 0) would be false, even though (*UNSETVAR) by itself is false and (0) by itself is false. Another way of saying this is that *VARNAME in a logical expression is equivalent to *{:VARNAME} out of a logical expression.

When developing scripts, you definitely use SqlTool interactively to verify that SqlTool evaluates logical expressions as you expect. Just run * if commands that print something (i.e. \p) if the test expression is true.

Flow control works by conditionally executing blocks of Commands according to conditions specified by logical expressions.

The conditionally executed blocks are called PL Blocks. These PL Blocks always occur between a PL flow control statement (like * foreach, *while, * if) and a corresponding * end PL Command (like * end foreach).

The values of control variables for foreach and while PL blocks will change as expected.

There are * break and * continue, which work as any shell scripter would expect them to. The * break command can also be used to quit the current SQL file without triggering any error processing. (I.e. processing will continue with the next line in the including SQL file or interactive session, or with the next SQL file if you supplied multiple on the command-line).

Below is the example SQL file sample/pl.sql, which shows how to use most PL features ^[1]. If you have a question about how to use a particular PL feature, check this file in your distrubition before asking for help. Definitely give it a run, like

java -jar $HSQLDB_HOME/lib/sqltool.jar mem $HSQLDB_HOME/pl.jar

/*
    $Id: pl.sql 3353 2009-12-15 19:52:13Z unsaved $
    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.*/
\c 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(20);
);
\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 */
\c 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.

The first general reason to chunk SQL commands is performance. For standalone databases, the most common performance bottleneck is network latency. Chunking SQL commands can dramatically reduce network traffic.

The second reason is that there are a couple SQL commands which require the terminating ";" to be sent to the database engine. For simplicity and efficiency, it's usually better for general JDBC clients like SqlTool to strip off the final delimiter. Raw commands retains everything that the user types.

The third general reason to chunk SQL commands is if your database requires you to send multiple commands in one transmission. This is usually the case with the following types of commands:

Use raw mode. Go to the Raw Mode section to see how. You can enter any text at all, exactly how you want it to be sent to the database engine. Therefore, in addition to chunking SQL commands, you can give commands for non-SQL extensions to the database. For example, you could enter JavaScript code to be used in a stored procedure.

Even if you need to change delimiters, table names, or file names from the defaults, I suggest that you run one export and import with default settings as a practice run. A memory-only HyperSQL instance is ideal for test runs like this.

This command exports the table icf.projects to the file projects.dsv in the current directory (where you invoked SqlTool from). By default, the output file name will be the specified source table name plus the extension .dsv.

    SET SCHEMA icf;
    \x projects

Take a look at the output file. Notice that the first line consists of column names, not data. This line is present because it will be needed if the file is to used for a DSV import. Notice the following characterstics about the export data. The column delimiter is the pipe character "|". The record delimiter is the default line delimiter character(s) for your operating system. The string used to represent database NULLs is [null]. See the next section for how to change these from their default values.

	Warning
	You can not DSV import Array values where any Array elements contain commas, for example an Array of VARCHARs which contain one or more commas. There is no such limitation on DSV exports, which you can use for purposes other than SqlTool importing, or you could use a script to change the commas to some other character.

This command imports the data from the file projects.dsv in the current directory (where you invoked SqlTool from) into the table newschema.projects. By default, the output table name will be the input filename after removing optional leading directory and trailing final extension.

    SET SCHEMA newschema;
    \m projects.dsv

For a hands on example of a DSM import which generates an import report and uses some other options, change to directory HSQLDB/sample and play with the working script dsv-sample.sql ^[1]. You can execute it like

    java -jar ../lib/sqltool.jar mem dsv-sample.sql

The header line in the DSV file is required at this time. (If there is user demand, it can be made optional for exporting, but it will remain required for importing).

Your export will fail if the output column or record delimiter, or the null representation value occurs in the data being exported. You change these values by setting the PL variables *DSV_COL_DELIM, *DSV_ROW_DELIM, *DSV_NULL_REP. Notice that the asterisk is part of the variable names, to indicate that these variables are used by SqlTool internally. When specifying delimiters, you can use the escape seqpences \n, \r, \f, \t, \\, and decimal, octal or hex specifications like \20, \020, \0x20. For example, to change the column delimiter to the tab character, you would give the command

    * *DSV_COL_DELIM = \t

The input (\m) delimiter values, *DSV_COL_SPLITTER and *DSV_ROW_SPLITTER, are set using normal Perl/Java regexp syntax. There are escapes for specifying special characters, and anything else you would need. Input vs. output row and column delimiters are easily distinguished by containing "SPLITTER" for splitting input (\m) files; or "DELIM" for the delimiters that we will write (\x) among the data.

For imports, you must always specify the source DSV file path. If you want to export to a different file than one in the current directory named according to the source table, set the PL variable *DSV_TARGET_FILE, like

    * *DSV_TARGET_FILE = /tmp/dtbl.dsv

For exports, you must always specify the source table name or query. If you want to import to a table other than that derived from the input DSV file name, set the PL variable *DSV_TARGET_TABLE. The table name may contain a schema name prefix.

You don't need to import all of the columns in a data file. To designate the fields to be skipped, iether set the PL PL variable *DSV_SKIP_COLUMNS, or replace the column names in the header line to "-" (hyphen). The value of *DSV_SKIP_COLUMNS is case-insensitive, and multiple column names are separated with white space and/or commas.

You can specify a query instead of a tablename with the \x command in order to filter or transform data from a table or view, or to export the output of a join, etc. You must set the PL variable *DSV_TARGET_FILE, as explained above (since there is no table name from which to automatically map a file name).

    * *DSV_TARGET_FILE = outfile.txt
    \x SELECT entrydate, 2 * aval "Double aval", modtime FROM bs.dtbl

By default, imports will abort as soon as a error is encountered during parsing the file or inserting data. If you invoke SqlTool with a SQL script on the command line, the failure will cause SqlTool to roll back and exit. If run interactively, you can decide whether to commit or roll back the rows that inserted before the failure. You can modify this behavior with the \a and \c settings.

If you set either a reject dsv file or a reject report file, then failures during imports will be reported but will not cause the import to abort. When run in this way, SqlTool will give you a report at the end about how many records were skipped, rejected, and successfully inserted. The reject dsv file is just a dsv file with exact copies of the dsv records that failed to insert. The reject report file is a HTML report which lists, for every rejected record, why that record was rejected. \m? will show you that the required PL variables for this functionality are *DSV_REJECT_FILE and *DSV_REJECT_REPORT. In both cases, you set the variable value to the path of the file which SqlTool will create.

To allow for user-friendly entry of headers, we require that tables for DSV import/exports use standard column names. I.e., no column names that require quoting. The DSV import and export parsers are very smart and user-friendly. The data types of columns are checked so that the parser can make safe assumptions about white space and blank entries in the data. If a column is a JDBC Boolean type, for example, then we know that a field value of " True " obviously means "True", and that a field value of "" obviously means null. Since we require vanilla style column names, we allow white space anywhere in the header column. We allow blank lines anywhere (where "lines" are delimited by *DSV_ROW_DELIM). By default, commented lines are ignored, and the comment character can be changed from its default value.

Run the command "\x?" or "\m?" to see the several system PL variables which you can set to adjust reject file behavior, commenting behavior, and other DSV features.

You can also define some settings right in the DSV file, and you can even specify multiple header lines in a single DSV file. I use this last feature to import data from one data set into multple tables that are joined. Since I don't have any more time to dedicate to explaining all of these features, I'll give you some examples from working DSV files and let you take it from there.

    # RCS keyword was here.

    headerswitch{
    itemdef:name|-|-|hardness|breakdc|-
    simpleitemdef:itemdef_name|maxvalue|weight|-|-|maxhp
    }

    targettable=t

There is also the *DSV_CONST_COLS setting, which you can use to automatically write static, constant values to the specified columns of all inserted rows.