Chapter 1. SqlTool

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 2.2.5 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 4720 and 4863 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 2.2.5. [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 2.0.0 HyperSQL release.

To reduce duplication, new features are listed in the Recent Functional Changes section are not repeated here, so check that list too.

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 variable, you would invoke SqlTool like this.

    java org.hsqldb.cmdline.SqlTool ...

If you are using the -cp switch instead of a CLASSPATH variable, stick it after java. After "SqlTool", give any SqlTool parameters exactly as you would put after java -jar .../sqltool.jar if you didn't need to customize the CLASSPATH. You can specify a JDBC driver class to use either with the --driver switch to SqlTool, or in your RC file stanza (the last method is usually more convenient).

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 minimize 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. (Actually DosKey does work from vanilla Windows MSDOS console windows. Be aware that it suffers from the same 20-year old quirks as DOS command-line editing. Very often the command line history will get shifted and you won't be able to find the command you want to recall. Usually you can work around this by typing a comment... "::" to DOS or "--" to SqlTool then re-trying on the next command line).

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

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.

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;
        * NEWVAR = something
        \p A SqlTool Special command which echoes NEWVAR: *{NEWVAR}
            number two;
        SQL statement three;
    ' urlid

Notice that the SQL string is not strictly SQL, but SqlTool input, so it may contain Special or PL commands. The variable is set this way only for educational purposes. The same thing could be accomplished more elegantly by using the -p switch.

	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 contains error handling statements, they will take precedence.

You can also use \i in SQL files to pull in (nest) additional SQL files. This is a powerful way to hierarchically maintain and configuration manage a set of scripts for a product, project, or database. For encapsulation, tracking, and collaboration purposes, it's usually best to keep each SQL script focused on one task or goal, for example: creating a table, it's trigger, and loading initial data from a DSV file. Usually a set of such scripts will have to be executed in a precise order so that referenced tables are created before tables with foreign keys to them, etc. I make a super-script for every database project that I manage. Besides this strategy proving and configuration managing an installation procedure known to work, I can recreate large and complex, custom product databases for deployments or tests in seconds. With the addition of some very simple PL coding, I can re-create all database structures in a parallel schema by just specifying a schema name when I invoke the super-script.

Only for interactive SqlTool invocations, the file auto.sql in your home directory will be executed before your typed (or piped) input. It is processed in the same way as a script file specified on the command-line except that since you are running SqlTool interactively, the interactive SqlTool rules will apply. If your auto.sql does setup that you need done for non-interactive SQL files, then add a \i ${user.home}/auto.sql to the top of the script, understanding that if you execute that script interactively you will cause auto.sql to be executed a second time, but see the following tip about preventing that.

Preventing redundant execution of utility or shared scripts.

There's a common idiom used in UNIX login and shell initialization scripts that prevents redundant executions, and it works great for SqlTool too. Think up a unique PL variable name which will keep track of whether the script has been sourced in the current SqlTool session, and the script will have no effect if called a 2nd, 3rd, etc. time. This example applies the idiom to an auto.sql file, but it can be used in any script that you want to prevent superfluous executions in a developer-friendly way. * if (*AUTO_EXECUTED) * break * end if * AUTO_EXECUTED = true It would be slightly more efficient, but less reliable, to put the test on the caller's side instead of the callee side, like this: * if (! *AUTO_EXECUTED) * \i ${user.home}/auto.sql * end if

As you would probably guess, all of SQL's file commands (for example loading or saving SQL scripts, binary data, DSV data) take either relative or absolute file paths. However, when you nest scripts, you will usually want to begin your paths with the @/. The initial @ character in file paths means the directory containing the current script. This is important because of Java's frustrating inability to switch the current directory. By using @, you can cross-reference between SQL scripts in one or several co-located directories, and everything will just work, regardless of your current directory when you invoke script(s).

	Make use of @/ construct for CMD scripts with nesting
	If you use a managed set of nested scripts, you are advised to prefix all relative file paths inside of SqlTool scripts with @/. Without this, relative file paths would be resolved relative to the invocation directory-- making your scripts require a specific invocation directory. The at character will resolve to the directory containing the current script.

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

This shows how you can mix SQL on the command line, and SQL inside an SQL file.

	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...;" argument in the example creates an account which the following 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 immediately.

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 -

    java -jar $HSQLDB_HOME/lib/sqltool.jar urlid - < myFile.sql

For a shell not as graceful as the Bourne-compatible shells, you would need to type this all on the same line (or use a line-continuation trick).

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 and -p switches are great facilities to use with shell scripts.

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.

Tip

Since SqlTool religiously returns meaningful exit status, you can use the following idiom to send alerts about failed batch jobs (for example, jobs started by cron, at, AutoSys, Quartz, Hudson). Example 1.4. Error-handling Idiom java -jar $HSQLDB_HOME/lib/sqltool.jar urlid $HOME/app/myFile.sql >> $HOME/log/app.log 2>&1 || echo "See log file y:$HOME/log/app.log" | mailx -s "App aborted on host y" recip1@z.com recip2@z.com

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

Generally, 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. However, the important scripting commands * VARNAME :, /: VARNAME, \x :, and \xq :, as well as Raw Mode do use the edit buffer.

You can, of course, use any SqlTool command at all interactively. The goal here is just 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 successful exit, whereas \q Message means to abort immediately with error status. Both commands will exit gracefully.
\p [text to print]	Print the given string to stdout. Just give "\p" alone to print a blank line.
\l SEVERITY_LEVEL text to log	The logging subsystem will display and/or log and/or email or whatever, depending on how you have it configured. To see where messages go by default, just play with it interactively. Run "\l?" to list the available severity levels.
\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 ROLLBACK; 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.

Some script developers may run into cases where they want to run with sql files but they also 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>&1

or

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

For a shell not as graceful as the Bourne-compatible shells, you would need to type this all on the same line (or use a line-continuation trick).

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.

I am raising this PL variable topic out of order here because it is important to understand, and I'd like you to have the concept firmly in mind before digging into other details about variables. In this sub-section I am only talking about PL variable values (not system properties or SQL engine variables, etc.).

Very similarly to Java system properties, if the value of a PL variable is null, then the variable is unset; and (if you set the sqltool.REMOVE_EMPTY_VARS mode as suggested) there is no way to directly assign a variable to null. If, for example, you ran

              * MYVAR = null

that would assign the string value of null to your variable, not the real null value. If a variable is assigned null indirectly, say by fetching a null cell value into a variable, or when variable ? is assigned null due to a SQL failure, this action is entirely equivalent to unsetting the variable.

	Recap
	If a variable is unset, or has never yet been set, then that variable's value is null. If a variable's value is null, then the variable is unset.

You can assign a PL variable the empty string value by using a command switch -P or --setVar. For example

java -jar .../sqltool.jar -Pvarname= urlid script.sql

You can see it's an empty string by echoing the value:

\p One*{varname}Two
OneTwo

With the next minor release of SqlTool, or now if you set Java system property sqltool.REMOVE_EMPTY_VARS to false, that you can use the vanilla PL assignment command to assign empty values like this:

* MYVAR =

    * list MYVAR

    java -jar .../sqltool.jar -pEMPTYSTRING=
    ....
    * if (*MYVAR == **NULL)
        \p MYVAR really is null
    * end if
    * if (*MYVAR == EMPTYSTING)
        \p MYVAR is now an empty string
    * end if

Following subsections explain important things about specific variable types. Here we just list the variable types and give a few points about variable usage generally.

System PL Variables

	Important
	Definitely run command * ? to view in-program help about referencing PL variables, and run * ? assign if you will be assigning variables.

SqlTool automatically assign values to a few special system variables. As I write this, the special variables are only *START_TIME, *REVISION, *TIMESTAMP. *START_TIME is a date and time string formatted for the user's locale. *REVISION is SqlTool's version string (i.e., it is not a valid real or integer number). *TIMESTAMP is a user-configurable date or time string configurable with another system variable *TIMESTAMP_FORMAT.

SqlTool System PL variables are the mechanism used to configure SqlTool behavior. You can list all set PL variables by running the SqlTool command * listvalues. If a SqlTool System variable is not shown, then it is unset (which is equivalent to non-null). But if a system variable is not set, that doesn't mean that the setting behavior will be unset, but rather that the default behavior will apply. For example, if you * listvalues and the variable *DSV_COL_DELIM is not listed, that doesn't mean that there will be no DSV column delimiter, but that the default DSV column delimiter will be used. The in-program help can be used to determine what the default behavior is. (In the case of *DSV_COL_DELIM, you can see the default behavior by running \x?.

See the list of system variables in the SqlTool System PL Variables appendix.

    * if (*? == *NULL)

sql> \p At startup ? is equal to empty string.  See between A and B:  A*{?}B
At startup ? is equal to empty string.  See between A and B:  AB
sql> * if (A*{?}B == AB) \p ? is the empty string
? is the empty string
sql> 
sql> CREATE TABLE t(i INTEGER, vc VARCHAR(20));
sql> INSERT INTO t VALUES(1, 'one');
1 row updated.
sql> INSERT INTO t VALUES(2, 'two');
1 row updated.
sql> * res ~
sql> SELECT * FROM t;
I  VC
-  ---
1  one
2  two

Fetched 2 rows.
sql> \p *{?}
two
sql> \p *{res}
1
sql> * listvalues ? res
Listing all 'set' variables (any var not seen is unset and equal to null).
The outermost parentheses are not part of the values.
    ?: (two)
    res: (1)
sql> 
sql> INSERT INTO t VALUES (3, null);
1 row updated.
sql> *res ~
sql> SELECT vc FROM t WHERE i = 3;
[null]
sql> \p *{?}
[null]
sql> * if (*res == **NULL) \p res really is null
res really is null
sql> * listvalues ? res
Listing all 'set' variables (any var not seen is unset and equal to null).
The outermost parentheses are not part of the values.
    ?: ([null])
sql> 
sql> -- This will prevent SqlTool from aborting when we run a bad SQL statement:
sql> \c true
Continue-on-error is set to true.
sql> *res ~
sql> SELECT hocus FROM pocus;
SEVERE  SQL Error at '<stdin>' line 23:
"SELECT hocus FROM pocus"
user lacks privilege or object not found: POCUS
sql> * if (*? == **NULL) \p ? really is null
? really is null
SEVERE  Did not finish setting variable 'res' before a code block exited.
SEVERE  Rolling back SQL transaction.
sql> * if (*res == **NULL) \p res really is null
res really is null
sql> * listvalues ? res
Listing all 'set' variables (any var not seen is unset and equal to null).
The outermost parentheses are not part of the values.
sql>

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

, you could narrow the query variously during different macro invocations, like

    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  \p Work completed by
    sql> /notate Blaine;

If you don't type the ;, you will just recall the command to the buffer (from which you can execute or edit it, if you wish to).

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

SqlTool functions are macros which take positional parameters. They are functions in the shell-programming sense. They do not return values in the sense of functions as distinguished from procedures or methods. As the /? in-program help shows, they can be defined by literal assignment or by buffer contents, and optional appendages work as one would want-- just like regular macros. They are intuitive to define and use, so one example should be all the instruction needed.

\.
INSERT INTO t(i, vc) VALUES(*{1}, '*{2}');
SELECT * FROM t
  WHERE i = *{1}
.
/: writeread()  AND audited is null

This is a non-trivial example where we insert into a table with some automatically generated columns, and we want to see the entire created record before deciding whether to commit the new record. Since what we want to do will take multiple lines of SQL, and indeed 2 SQL statements, we use raw mode to write the multi-line SQL statement to the edit buffer, then use the /: MACRONAME [appendage] construct to define a macro with body of the previous edit buffer contents. As described elsewhere, if you want to do this in a SQL file (as opposed to interactively), you have to use raw mode as we have done here. Just by assigning a name ending with () we have made a function instead of a regula macro. Notice how we used positional parameters references *{1} and *{2} in the macro body. We wanted to add a little to what was in the edit buffer, so we added an appendage to the /: command. Note the extra space after () or we would have ended up with resulting body of "... i = *{1}AND audited...".

/writeread(10, ten);

Not much to explain. Though the second character is for a string value to insert into the a varchar column, we wrote the function so that the function body supplies the single-quotes instead of having to type them in ever time we use the function. Leading and trailing white space is trimmed from each parameter. So if you want your value to have leading or trialing space, you will have to type in the quotes at invocation time. Another limitation caused by this convenient parsing is that functions just won't work when your invocation parameters need to contain commas. Just like for regular macros, the terminating ; causes the expanded macro to execute.

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 -pUSER=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 notification of 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).

Spaces are not allowed in elements of logical expressions. These are examples of illegal logical expressions: * while (two words), * if (*x == two words). You can certainly do what you want to do, however, by using variables to hold multi-word strings. You can achieve the goals for the two previous attempts with

    *tmpVar = two words
    * while (*tmpVar)
    ...
    * if (*x == *tmpVar)

It is critically important here to use *tmpvar instead of *{tmpvar} in this situation, because *{...} would not delay expansion and would therefore be equivalent to entering the multiple words.

SqlTool's logical expressions are purposefully minimalistic. We do not support nested operations or mixing with assignment commands. Notice that there are no ||, &&, AND, or OR operations in the table below. You can not assign the value of a boolean expression directly. You can achieve that goal with an * while and mathematical assignments.

As stated earlier, inside of logical expressions you should normally reference PL variables without curly braces. This syntatic simplification is allowed because multi-word tokens are not allowed in logical expressions (therefore {...} is not needed to group words). For example, "word", ">", and "*VARNAME" are all separate atoms.

You can indeed use the curly format like "*{THIS}" inside of logical expressions, but the casual user should stick to "*THIS". There is a 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). Another difference between *{VARNAME} and *VARNAME is that the latter resolves to unset (this is very different from the empty string that *{:VARNAME} would resolve to).

If you do use the braces, make sure that the expansion value doesn't contain quotes or whitespace. (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. Hence-- casual users should not use braces inside of logical expressions.

Though tokens inside logical expressions are atomic, you definitely can and should do tests on strings that contain spaces. You just have to use a variable for each such string value. For example, if I want to see if the special variable ? is equal to one two three, then you must do it like this:

    * cfString = one  two three
    * if (*cfString == ?)

As noted elsewhere in this guide, internal spaces are preserved as given. For assignments, trailing spaces are generally preserved. Leading spaces are preserved only for the : assignment commands.

*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 should 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.

Only integer math is supported, and only in mathematical assignment commands. Math assignment commands are of the format

<ASSIGNEE> <ASSIGNMENT_OP> <INTEGER_EXPRESSION>

For example,

SQUARE_FOOTAGE += (FOYER_FEET + 20) * 3 + 300 * BATHS

This works very close to Bash and Korn shell ((...)) integer math. The primary difference from those shells is that we prohibit useless non-assignment commands. Therefore, our math assignment commands always begin with the assignee variable name and an assignment operator.

Those users unfamiliar with programs that do strictly integer math should play around with it before using it for anything important. It may surprise you that real numbers like 2.9 are not automatically converted to an integer, but are simply prohibited; and that results of expressions are truncated to an integer, not rounded.

The list below is available from the program by running * ? (they are listed after the words "Assignment OPs:"). Note that though we support assignment operator ++, we do not support -- because that conflicts with our single-line comment delimiter --. The work-around is to use -=1 instead.

To the right of the assignment operator is the integer math expression consisting of raw variable names, integers, and mathematical operators. The variables referenced, if any, must all contain integer values. In the expression only user PL variables may be used. Not Java system properties nor SqlTool system PL variables.

The list below is available from the program by running * ? (they are listed after the words "Internal ops:").

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

	Important
	Definitely run command * ? control to view a list of the available flow control statements, and details about how to use them.

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

Definitely read the section Logical Expressions.

The values of control variables for * foreach and * forrows 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).

There is now also an inline * if command that is very handy and concise. Try these samples on.

    * if (*x == *NULL) \q Aborting program
    ....
    * while...
        * if (*exitCondition) * break
    ...
    * if (*notableEvent) \l SEVERE Something bad happened

Below is the example SQL file sample/pl.sql, which shows how to use most of the basic PL features ^[1]. If you have a question about how to use a particular PL feature, check this file in your distribution before asking for help... and definitely read the in-program help for * ? carefully! Give it a run, like

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

It will suggest that you re-run it with another parameter. Insert the new parameter before "mem".

/*
    $Id: pl.sql 4564 2011-10-19 04:27:37Z unsaved $
    SQL File to illustrate the use of some basic SqlTool PL features.
    Invoke like
        java -jar .../sqltool.jar mem .../pl.sql
                                                         -- 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 argument "-pMYTABLE=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}'...

CREATE TABLE *{MYTABLE} (
    i int,
    s varchar(20)
);
-- PL variable ? is always set to status or fetched value of last SQL
-- statement.  It will be null/unset if the last SQL statement failed.
\p CREATE status is *{?}
\p

/* Validate our return status.
   In case of success of a CREATE TABLE, *? will be 0, and therefore a
   '* if (*?)' would be false.
   So we follow the general practice of testing *? for the error indicator
   value of null, using the reserved SqlTool system variable *NULL.
 */
* if (*? == *NULL)
    \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 into our new table
* 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

/* This time instead of using the ? variable, we're assigning the SELECT value
   to a User variable, 'themax'. */
* 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};

/* No need to test for failure status (either ? or themax being unset/null),
   because we are in \c mode and would have aborted if the SELECT failed. */
* if (0 == *themax)
    \p Got 0 as the max value.
    * 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 Counting down to exit
* ((i = 3))
* while (*i > 0)
    \p *{i}...
    * ((i -= 1))  -- i++ is supported but i-- is not, because -- marks comments
* end while

\p
\p Everything worked.  Signing off.

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

We could also have run \x icf.projects (which would have created a file named icf.projects.dsv) instead of changing the session schema. In this example we have chosen to make the export file name independent of the schema to facilitate importing it into a different schema.

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 characteristics 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

If the DSV file was named with the target schema, you would have skipped the SET SCHEMA command, like \m newschema.projects.dsv. In order to allow for more flexibility, the default input input delimiters are not exactly the same as the output delimiters. The input delimiters are regular expressions. The input column delimiter happens to be the regular expression corresponding exactly to "|"; but the input record delimiter matches UNIX, Windows, Mac, and HTTP line breaks.

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

(assuming that you are using the supplied sqltool.rc file or have have urlid mem set up).

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. Regular expressions have their own mechanism for including special characters. *DSV_NULL_REP effects normal displaying of VARCHAR output to screen or stdout, not just importing and exporting-- so you should reset the value if you want to revert to normal display behavior. When specifying output delimiters, you can use the escape sequences \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.

*DSV...DELIM vs *DSV...SPLITTER settings

Both the ...DELIM and the ...SPLITTER settings are for delimiting cells of data, but whereas our DELIM values are literal things that SqlTool will write right into a DSV file, SPLITTER values are patterns for detecting the literal delimiters in existing DSV files. The settings named like *DSV...SPLITTER are input delimiters specified as regular expressions following the rules in the API spec for java.util.regex.Pattern. The settings named like *DSV...SPLITTER are output delimiters specified as constant strings which can contain escape sequences to represent special characters (as documented in this section).

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

Note that I specified the column label alias "Double aval" so that the label for that column in the DSV file header will not be blank. You can type a query line as long long as you want to, but if you want to use a specified query that spans multiple lines, then you must use the command variant \x : to use the query in the previous edit buffer. (To populate the edit buffer with your multi-line SQL query, you must execute the command before... usually undesirable, or end the SQL with a blank line instead of a ;... only works interactively, or use Raw Mode).

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.

Reject reports use the same templating system as SqlTool HTML reports. Therefore you can set SqlTool system PL variables *TOP_HTMLFRAG_FILE or *BOTTOM_HTMLFRAG_FILE to use your own opening and closing HTML and to completely replace the styling. If you use the default templates you can set user PL variable REPORT_TITLE for the obvious reason, and you can place a file named overrides.css into the same directory as your generated report, for the obvious purpose. You can use PL variable references in your own fragment files (remember to use the ${:VARNAME} construct to prevent errors for variables that are not set). You can also use automatically set variables like *TIMESTAMP and *REVISION

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 would require quoting in interactive SQL statements. 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, but this can be disabled (by setting DSV_SKIP_PREFIX to the empty string) or you can change the delimiter character from # to whatever you want (by setting DSV_SKIP_PREFIX to that value).

	Use In-Program Help for Importing and Exporting
	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. The in-program help is the definitive reference for available options, not this manual.

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 multiple 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
    }

I'll just note that the prefixes for the header rows must be of format target-table-name + :. You can use * for target-table-name here, for the obvious purpose.

    targettable=t

This last example is from the SqlTool unit test file dsv-trimming.dsv. These special commands must be at the top of the file (before any normal data or header lines).

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.