SqlTool HOWTO

SqlTool HOWTO

Author: Blaine Simpson. blaine.simpson@admc.com
$Revision: 1.11 $

Note that these links are actually to a copy of the doc at the same location where you are viewing this from. If the link doesn't work, then that document doesn't exist there. In that case, cut and paste the URL shown into your browser window (or wget or whatever).

Split-up HTML

http://admc.com/blaine/howtos/sqltool/index.html

PDF

http://admc.com/blaine/howtos/sqltool/sqltool.pdf

Info

http://admc.com/blaine/howtos/sqltool/sqltool.info (useful if you are on UNIX and want to view this in a terminal window instead of with a web browser, and without losing the linking features).

• Intro: Introduction
• BareBones: The Bare Minimum You Need to Know to Run SqlTool
• Auth: Authentication
• Interactive: Interactive
• NonInteractive: Non-Interactive
• Webapp: Web Application

Introduction

This document explains how to use SqlTool, a program which reads your SQL text file or stdin, and executes the SQL commands therein against a JDBC database.

If this document comes from a hsqldb distribution, then it covers the version of SqlTool bundled in this distribution. Otherwise, this document applies to the version of SqlTool which ships with hsqldb version 1.7.2.

The Bare Minimum You Need to Know to Run SqlTool

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

To run SQLTool...

1. Set up an SqlTool configuration file, as documented in the Auth section of this document.
   

2. Set up your CLASSPATH to include the classes org.hsqldb.util.SqlTool, org.hsqldb.util.SqlFile, and the JDBC driver that you want to use. If you are using a hsqldb database, this is most easily accomplished by setting (and exporting) CLASSPATH to the path to hsqldb.jar (which lives in the lib subdirectory of the hsqldb distribution). (You can, of course, use a classpath argument to java instead of setting your CLASSPATH variable).
   

3. Run

                 java org.hsqldb.util.SqlTool --help
             

   to see what command-line arguments are available. Assuming that you set up your SqlTool configuration file at the default location and you want to use the hsqldb JDBC driver, you will want to run either

                 java org.hsqldb.util.SqlTool urlid
             

   for interactive use, or

                 java org.hsqldb.util.SqlTool --noinput --sql 'SQL statement;' urlid
             

   or

                 java org.hsqldb.util.SqlTool urlid filepath1.sql...
             

   where the arguments following the urlid are paths to text SQL files. For the filepaths, you can use whatever wildcards your operating system shell supports.

   The urlid in these commands is a key into your SqlTol configuration file, as explained in the Auth section of this document.

To use a JDBC Driver other than the hsqldb driver, you must put the jar/zip file in the classpath (as explained in number 2 above), and you must tell SqlTool what the JDBC driver class name is. The latter can be accomplished by either using the "--driver" switch, or setting "driver" in your config file. The Authentication section of this document explains the second method. Here's an example of the first method.

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

Authentication

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

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

         java org.hsqldb.util.SqlTool x
     

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

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

         # This comment
     

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

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

charset

Sets encoding character set for input. See Character Encoding in the Non-Interactive section of this document. You can, alternatively, set this for one SqlTool invocation by setting the system property sqltool.charset. Defaults to US-ASCII.

driver

Sets the JDBC driver class name. You can, alternatively, set this for one SqlTool invocation by using the SqlTool switch --driver. Defaults to org.hsqldb.jdbcDriver.

Interactive

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

         java org.hsqldb.util.SqlTool urlid
     

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

Command types.

SQL Statement

Any command that you enter that does not begin with "\" or ":" is a SQL Statement. The command is not terminated when you hit enter, like most OS shells. You terminate SQL Statements with either ";" at the end of a line, or with a blank line. In the former case, the SQL Statement will be executed against the SQL database and the command will go into the command buffer and command history for editing or viewing later on. In the latter case (you end an SQL Statement with a blank line), the command will go to the buffer and history, but will not be executed (unless you later execute it from the buffer).

As a result of these termination rules, whenever you are entering text that is not a Special Command or Buffer Command, you are always appending lines to an SQL Statement. (If it's the first line, then you will be starting an SQL Statement, or appending to an empty SQL Statement).

Special Command

Run the command "\?" to list the Special Commands. All of the Special Commands begin with "\". You can't enter a Special Command if you have already typed some line(s) of a SQL Statement, because the new line containing the Special Command would just be interpreted as another line in the SQL Statement. In this case, you just hit enter to clear the current SQL Statement and send it off to the buffer (where you can ignore it forever if you wish to). I'll describe some of the most useful Special Commands below.

Buffer Command

Run the command ":?" to list the Special Commands. All of the Special Commands begin with ":". The text above about entering Special Commands if you have already typed some line(s) of a SQL Statement applies equally to Buffer Commands. Buffer commands operate upon the command "buffer", so that you can edit and/or (re-)execute previously entered commands.

Essential Special Commands

\?

help

\dt

Lists available database tables. I'm still working on how to properly filter out system tables from the listing. For the moment, it works great for Hsqldb, and is better than nothing for other database.

\s

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

\-[3]

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

Buffer Commands

:?

help

:a

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

:l

List the current contents of the buffer.

:;

Executes the current buffer against the database.

:s/from string/to string/

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

              :s@from string@to string@
          

All occurrences of the "from string" are substituted with "to string". If the SQL Statement is multi-line, the substitution will happen to all lines. (This easily be changed to accommondate editing only specific lines of the buffer-- let me know if that would be useful to you).

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

              "*
              FROM mytable"
          

This is very useful, as it permits you to append to or prefix to specific lines within a multi-line SQL Statement, like

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

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

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

              :s/this//
          

would remove all occurrences of "this".

Raw mode

If for some reason you want SqlTool to process your commands as if it were reading an SQL file, i.e., without giving a login banner or command prompts, and aborting upon failure by default, then specify a SQL filepath of "-", like

         java org.hsqldb.util.SqlTool urlid -
     

Non-Interactive

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

Giving SQL on the Command Line

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

         java org.hsqldb.util.SqlTool --noinput --sql 'SQL statement;' urlid
     

         java org.hsqldb.util.SqlTool --noinput --sql 'SQL statement;' urlid
             SQL statement number one;
             SQL statement
                 number two;
             SQL statement three;
         ' urlid
     

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

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

SQL Files

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 org.hsqldb.util.SqlTool urlid filepath1.sql... > /tmp/log.sql 2>&1
     

If you are viewing this document as an HTML document, then you can right click on this link to download a sample sql file. The sample contains SQL as well as Special Commands making good use of the Special Commands documented below.

If you want your SQL scripts optimally compatible among multiple databases, then don't use any Special Commands. (If you do this, you will probably want a command-line option to turn on continue-upon-error behavior. As I say below, email me if you need this).

Comments

You can use comments of the form /* An sql comment */ inside or outside of SQL Statements. You can also use -- style SQL comments. /*...*/ comments that are not intertwined with non-comment SQL, and -- always, are sent to the database. Stand-alone /*...*/ comments are just dropped, to avoid unnecessary network traffic and cluttering of command history.

Special Commands and Buffer Commands in SQL Files

Don't use Buffer Commands in your sql files, because they won't work. Buffer Commands are for interactive use only.

Most Special Commands won't work in sql files, but the following not only work, but were designed for use in sql files.

\p [line to print]

Print the given string to stdout

\H

Toggle HTML output mode. If you redirect output to a file, this can make query output much easier to view. For example,

              java org.hsqldb.util.SqlTool urlid filepath1.sql... > /tmp/log.html 2>&1
          

\* [true|false]

A "true" setting tells SqlTool to continue when errors are encountered. The default for interactive use is to continue upon error, but the default for non-interactive use is to abort upon error. With database setup scripts, I usually find it convenient to set "true" before deleting tables (so that things will continue if the tables aren't there), then set it back to false so that real errors are caught.

(I'm considering adding another command-line option to allow setting the initial continue-on-error behavior that way, because sql scripts written for other databases usually assume continue-on-error since most databases don't give you any ability to abort upon error. Let me know if you need this ability.)

Getting Interactive Functionality with SQL Files

Some script developers may run into cases where they want to run with sql files but they 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 org.hsqldb.util.SqlTool urlid < filepath1.sql > /tmp/log.html 2>&1
     

         cat filepath1.sql... |
         java org.hsqldb.util.SqlTool urlid > /tmp/log.html 2>&1
     

Character Encoding

SqlTool defaults to the US-ASCII character set (for reading). You can use another character set by setting the system property sqltool.charset, like

         java -Dsqltool.charset=UTF=8 org.hsqldb.util.SqlTool urlid filepath1.sql...
     

Web Application

It wouldn't bee much trouble for me to make a web application that uses SqlTool so that users can use a HTML text area field to execute SQL Statements. I already have a war that does a similar thing with a different technology. So, email me at blaine.simpson@admc.com if you would like me to assemble a war like this.