alvinalexander.com | career | drupal | java | mac | mysql | perl | scala | uml | unix  

HSQLDB example source code file (SqlFile.java)

This example HSQLDB source code file (SqlFile.java) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.

Java - HSQLDB tags/keywords

badspecial, badspecial, file, fileoutputstream, io, ioexception, ioexception, jdbc, ls, outputstreamwriter, regex, runtimeexception, sql, sqlexception, sqltoolerror, sqltoolerror, string, string, util

The HSQLDB SqlFile.java source code

/* Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


package org.hsqldb.util;

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.HashSet;
import java.util.Map;
import java.util.SortedMap;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;

/* $Id: SqlFile.java,v 1.169 2007/08/02 18:15:32 unsaved Exp $ */

/**
 * Encapsulation of a sql text file like 'myscript.sql'.
 * The ultimate goal is to run the execute() method to feed the SQL
 * commands within the file to a jdbc connection.
 *
 * Some implementation comments and variable names use keywords based
 * on the following definitions.  <UL>
 * <LI> COMMAND = Statement || SpecialCommand || BufferCommand
 * Statement = SQL statement like "SQL Statement;"
 * SpecialCommand =  Special Command like "\x arg..."
 * BufferCommand =  Editing/buffer command like ":s/this/that/"
 *
 * When entering SQL statements, you are always "appending" to the
 * "immediate" command (not the "buffer", which is a different thing).
 * All you can do to the immediate command is append new lines to it,
 * execute it, or save it to buffer.
 * When you are entering a buffer edit command like ":s/this/that/",
 * your immediate command is the buffer-edit-command.  The buffer
 * is the command string that you are editing.
 * The buffer usually contains either an exact copy of the last command
 * executed or sent to buffer by entering a blank line,
 * but BUFFER commands can change the contents of the buffer.
 *
 * In general, the special commands mirror those of Postgresql's psql,
 * but SqlFile handles command editing much different from Postgresql
 * because of Java's lack of support for raw tty I/O.
 * The \p special command, in particular, is very different from psql's.
 *
 * Buffer commands are unique to SQLFile.  The ":" commands allow
 * you to edit the buffer and to execute the buffer.
 *
 * \d commands are very poorly supported for Mysql because
 * (a) Mysql lacks most of the most basic JDBC support elements, and
 * the most basic role and schema features, and
 * (b) to access the Mysql data dictionay, one must change the database
 * instance (to do that would require work to restore the original state
 * and could have disastrous effects upon transactions).
 *
 * To make changes to this class less destructive to external callers,
 * the input parameters should be moved to setters (probably JavaBean
 * setters would be best) instead of constructor args and System
 * Properties.
 *
 * The process*() methods, other than processBuffHist() ALWAYS execute
 * on "buffer", and expect it to contain the method specific prefix
 * (if any).
 *
 * @version $Revision: 1.169 $
 * @author Blaine Simpson unsaved@users
 */

public class SqlFile {
    private static final int DEFAULT_HISTORY_SIZE = 40;
    private File             file;
    private boolean          interactive;
    private String           primaryPrompt    = "sql> ";
    private String           rawPrompt      = null;
    private String           contPrompt       = "  +> ";
    private Connection       curConn          = null;
    private boolean          htmlMode         = false;
    private Map              userVars; // Always a non-null map set in cons.
    private List             history          = null;
    private int              rawMode          = RAW_FALSE;
    private String           nullRepToken     = null;
    private String           dsvTargetFile    = null;
    private String           dsvTargetTable   = null;
    private String           dsvConstCols     = null;
    private String           dsvRejectFile    = null;
    private String           dsvRejectReport  = null;
    public static String     LS = System.getProperty("line.separator");
    private int              maxHistoryLength = 1;
    private SqltoolRB        rb               = null;
    private String           magicPrefix      = null;
    // For append editing, this is automatically prefixed to what the
    // user enters.

    private static final int RAW_FALSE = 0; // Raw mode off
    private static final int RAW_EMPTY = 1; // Raw mode on, but no raw input yet
    private static final int RAW_DATA  = 2; // Raw mode on and we have input

    /**
     * N.b. javax.util.regex Optional capture groups (...)? are completely
     * unpredictable wrt whether you get a null capture group vs. no capture.
     * Must always check count!
     */
    private static Pattern   specialPattern =
            Pattern.compile("\\s*\\\\(\\S+)(?:\\s+(.*\\S))?\\s*");
    private static Pattern   plPattern  =
            Pattern.compile("\\s*\\*\\s*(.*\\S)?\\s*");
    private static Pattern   foreachPattern =
            Pattern.compile("\\s*\\*\\s*foreach\\s+(\\S+)\\s*\\(([^)]*)\\)\\s*");
    private static Pattern   ifwhilePattern =
            Pattern.compile("\\s*\\*\\s*\\S+\\s*\\(([^)]*)\\)\\s*");
    private static Pattern   varsetPattern =
            Pattern.compile("\\s*\\*\\s*(\\S+)\\s*([=_~])\\s*(?:(.*\\S)\\s*)?");
    private static Pattern   substitutionPattern =
            Pattern.compile("(\\S)(.+?)\\1(.*?)\\1(.+)?\\s*");
            // Note that this pattern does not include the leading ":s".
    private static Pattern   slashHistoryPattern =
            Pattern.compile("\\s*/([^/]+)/\\s*(\\S.*)?");
    private static Pattern   historyPattern =
            Pattern.compile("\\s*(-?\\d+)?\\s*(\\S.*)?");
            // Note that this pattern does not include the leading ":".
    private static Pattern wincmdPattern = null;

    static {
        if (System.getProperty("os.name").startsWith("Windows")) {
            wincmdPattern = Pattern.compile("([^\"]+)?(\"[^\"]*\")?");
        }
    }
    // This can throw a runtime exception, but since the pattern
    // Strings are constant, one test run of the program will tell
    // if the patterns are good.

    /**
     * Encapsulate updating local variables which depend upon PL variables.
     *
     * Right now this is called whenever the user variable map is changed.
     * It would be more efficient to do it JIT by keeping track of when
     * the vars may be "dirty" by a variable map change, and having all
     * methods that use the settings call a conditional updater, but that
     * is less reliable since there is no way to guarantee that the vars
     * are not used without checking.
     */
    private void updateUserSettings() {
        dsvSkipPrefix = SqlFile.convertEscapes(
                (String) userVars.get("*DSV_SKIP_PREFIX"));
        if (dsvSkipPrefix == null) {
            dsvSkipPrefix = DEFAULT_SKIP_PREFIX;
        }
        dsvSkipCols = (String) userVars.get("*DSV_SKIP_COLS");
        dsvColDelim =
            SqlFile.convertEscapes((String) userVars.get("*DSV_COL_DELIM"));
        if (dsvColDelim == null) {
            dsvColDelim =
                SqlFile.convertEscapes((String) userVars.get("*CSV_COL_DELIM"));
        }
        if (dsvColDelim == null) {
            dsvColDelim = DEFAULT_COL_DELIM;
        }

        dsvRowDelim =
            SqlFile.convertEscapes((String) userVars.get("*DSV_ROW_DELIM"));
        if (dsvRowDelim == null) {
            dsvRowDelim =
                SqlFile.convertEscapes((String) userVars.get("*CSV_ROW_DELIM"));
        }
        if (dsvRowDelim == null) {
            dsvRowDelim = DEFAULT_ROW_DELIM;
        }

        dsvTargetFile = (String) userVars.get("*DSV_TARGET_FILE");
        if (dsvTargetFile == null) {
            dsvTargetFile = (String) userVars.get("*CSV_FILEPATH");
        }
        dsvTargetTable = (String) userVars.get("*DSV_TARGET_TABLE");
        if (dsvTargetTable == null) {
            dsvTargetTable = (String) userVars.get("*CSV_TABLENAME");
            // This just for legacy variable name.
        }

        dsvConstCols = (String) userVars.get("*DSV_CONST_COLS");
        dsvRejectFile = (String) userVars.get("*DSV_REJECT_FILE");
        dsvRejectReport = (String) userVars.get("*DSV_REJECT_REPORT");

        nullRepToken = (String) userVars.get("*NULL_REP_TOKEN");
        if (nullRepToken == null) {
            nullRepToken = (String) userVars.get("*CSV_NULL_REP");
        }
        if (nullRepToken == null) {
            nullRepToken = DEFAULT_NULL_REP;
        }
    }

    /**
     * Private class to "share" a variable among a family of SqlFile
     * instances.
     */
    private static class BooleanBucket {
        BooleanBucket() {}
        private boolean bPriv = false;

        public void set(boolean bIn) {
            bPriv = bIn;
        }

        public boolean get() {
            return bPriv;
        }
    }

    BooleanBucket possiblyUncommitteds = new BooleanBucket();
    // This is an imperfect solution since when user runs SQL they could
    // be running DDL or a commit or rollback statement.  All we know is,
    // they MAY run some DML that needs to be committed.

    private static final String DIVIDER =
        "-----------------------------------------------------------------"
        + "-----------------------------------------------------------------";
    // Needs to be at least as wide as the widest field or header displayed.
    private static final String SPACES =
        "                                                                 "
        + "                                                                 ";
    // Needs to be at least as wide as the widest field or header displayed.
    private static String revnum = null;

    static {
        revnum = "354";
    }

    private String DSV_OPTIONS_TEXT = null;
    private String D_OPTIONS_TEXT = null;
    private String RAW_LEADIN_MSG = null;

    /**
     * Interpret lines of input file as SQL Statements, Comments,
     * Special Commands, and Buffer Commands.
     * Most Special Commands and many Buffer commands are only for
     * interactive use.
     *
     * @param inFile  inFile of null means to read stdin.
     * @param inInteractive  If true, prompts are printed, the interactive
     *                       Special commands are enabled, and
     *                       continueOnError defaults to true.
     * @throws IOException  If can't open specified SQL file.
     */
    public SqlFile(File inFile, boolean inInteractive, Map inVars)
            throws IOException {
        // Set up ResourceBundle first, so that any other errors may be
        // reported with localized messages.
        try {
            rb = new SqltoolRB();
            rb.validate();
            rb.setMissingPosValueBehavior(
                    ValidatingResourceBundle.NOOP_BEHAVIOR);
            rb.setMissingPropertyBehavior(
                    ValidatingResourceBundle.NOOP_BEHAVIOR);
        } catch (RuntimeException re) {
            System.err.println("Failed to initialize resource bundle");
            throw re;
        }
        rawPrompt = rb.getString(SqltoolRB.RAWMODE_PROMPT) + "> ";
        DSV_OPTIONS_TEXT = rb.getString(SqltoolRB.DSV_OPTIONS);
        D_OPTIONS_TEXT = rb.getString(SqltoolRB.D_OPTIONS);
        RAW_LEADIN_MSG = rb.getString(SqltoolRB.RAW_LEADIN);
        DSV_X_SYNTAX_MSG = rb.getString(SqltoolRB.DSV_X_SYNTAX);
        DSV_M_SYNTAX_MSG = rb.getString(SqltoolRB.DSV_M_SYNTAX);
        nobufferYetString = rb.getString(SqltoolRB.NOBUFFER_YET);

        file        = inFile;
        interactive = inInteractive;
        userVars    = inVars;
        if (userVars == null) {
            userVars = new HashMap();
        }
        updateUserSettings();

        if (file != null &&!file.canRead()) {
            throw new IOException(rb.getString(SqltoolRB.SQLFILE_READFAIL,
                    file.toString()));
        }
        if (interactive) {
            history = new ArrayList();
            String histLenString = System.getProperty("sqltool.historyLength");
            if (histLenString != null) try {
                maxHistoryLength = Integer.parseInt(histLenString);
            } catch (Exception e) {
            } else {
                maxHistoryLength = DEFAULT_HISTORY_SIZE;
            }
        }
    }

    /**
     * Constructor for reading stdin instead of a file for commands.
     *
     * @see #SqlFile(File,boolean)
     */
    public SqlFile(boolean inInteractive, Map inVars) throws IOException {
        this(null, inInteractive, inVars);
    }

    /**
     * Process all the commands on stdin.
     *
     * @param conn The JDBC connection to use for SQL Commands.
     * @see #execute(Connection,PrintStream,PrintStream,boolean)
     */
    public void execute(Connection conn,
                        Boolean coeOverride)
                        throws SqlToolError, SQLException {
        execute(conn, System.out, System.err, coeOverride);
    }

    /**
     * Process all the commands on stdin.
     *
     * @param conn The JDBC connection to use for SQL Commands.
     * @see #execute(Connection,PrintStream,PrintStream,boolean)
     */
    public void execute(Connection conn,
                        boolean coeOverride)
                        throws SqlToolError, SQLException {
        execute(conn, System.out, System.err, new Boolean(coeOverride));
    }

    // So we can tell how to handle quit and break commands.
    public boolean      recursed     = false;
    private String      lastSqlStatement   = null;
    private int         curLinenum   = -1;
    private PrintStream psStd        = null;
    private PrintStream psErr        = null;
    private PrintWriter pwQuery      = null;
    private PrintWriter pwDsv        = null;
    StringBuffer        immCmdSB     = new StringBuffer();
    private boolean             continueOnError = false;
    /*
     * This is reset upon each execute() invocation (to true if interactive,
     * false otherwise).
     */
    private static final String DEFAULT_CHARSET = null;
    // Change to Charset.defaultCharset().name(); once we can use Java 1.5!
    private BufferedReader      br              = null;
    private String              charset         = null;
    private String              buffer          = null;

    /**
     * Process all the commands in the file (or stdin) associated with
     * "this" object.
     * Run SQL in the file through the given database connection.
     *
     * This is synchronized so that I can use object variables to keep
     * track of current line number, command, connection, i/o streams, etc.
     *
     * Sets encoding character set to that specified with System Property
     * 'sqlfile.charset'.  Defaults to "US-ASCII".
     *
     * @param conn The JDBC connection to use for SQL Commands.
     * @throws SQLExceptions thrown by JDBC driver.
     *                       Only possible if in "\c false" mode.
     * @throws SqlToolError  all other errors.
     *               This includes including QuitNow, BreakException,
     *               ContinueException for recursive calls only.
     */
    public synchronized void execute(Connection conn, PrintStream stdIn,
                                     PrintStream errIn,
                                     Boolean coeOverride)
                                     throws SqlToolError,
                                         SQLException {
        psStd      = stdIn;
        psErr      = errIn;
        curConn    = conn;
        curLinenum = -1;

        String  inputLine;
        String  trimmedInput;
        String  deTerminated;
        boolean inComment = false;    // Gobbling up a comment
        int     postCommentIndex;
        boolean rollbackUncoms = true;

        continueOnError = (coeOverride == null) ? interactive
                                                : coeOverride.booleanValue();

        if (userVars.size() > 0) {
            plMode = true;
        }

        String specifiedCharSet = System.getProperty("sqlfile.charset");

        charset = ((specifiedCharSet == null) ? DEFAULT_CHARSET
                                              : specifiedCharSet);

        try {
            br = new BufferedReader((charset == null)
                    ?  (new InputStreamReader((file == null)
                            ? System.in : (new FileInputStream(file))))
                    :  (new InputStreamReader(((file == null)
                            ? System.in : (new FileInputStream(file))),
                                    charset)));
            // Replace with just "(new FileInputStream(file), charset)"
            // once use defaultCharset from Java 1.5 in charset init. above.
            curLinenum = 0;

            if (interactive) {
                stdprintln(rb.getString(SqltoolRB.SQLFILE_BANNER, revnum));
            }

            while (true) {
                if (interactive && magicPrefix == null) {
                    psStd.print((immCmdSB.length() > 0 || rawMode == RAW_DATA)
                            ? contPrompt : ((rawMode == RAW_FALSE)
                                    ? primaryPrompt : rawPrompt));
                }

                inputLine = br.readLine();
                if (magicPrefix != null) {
                    inputLine = magicPrefix + inputLine;
                    magicPrefix = null;
                }

                if (inputLine == null) {
                    /*
                     * This is because interactive EOD on some OSes doesn't
                     * send a line-break, resulting in no linebreak at all
                     * after the SqlFile prompt or whatever happens to be
                     * on their screen.
                     */
                    if (interactive) {
                        psStd.println();
                    }

                    break;
                }

                curLinenum++;

                if (inComment) {
                    postCommentIndex = inputLine.indexOf("*/") + 2;

                    if (postCommentIndex > 1) {
                        // I see no reason to leave comments in history.
                        inputLine = inputLine.substring(postCommentIndex);

                        // Empty the buffer.  The non-comment remainder of
                        // this line is either the beginning of a new SQL
                        // or Special command, or an empty line.
                        immCmdSB.setLength(0);

                        inComment = false;
                    } else {
                        // Just completely ignore the input line.
                        continue;
                    }
                }

                trimmedInput = inputLine.trim();

                try {
                    if (rawMode != RAW_FALSE) {
                        boolean rawExecute = inputLine.equals(".;");
                        if (rawExecute || inputLine.equals(":.")) {
                            if (rawMode == RAW_EMPTY) {
                                rawMode = RAW_FALSE;
                                throw new SqlToolError(
                                        rb.getString(SqltoolRB.RAW_EMPTY));
                            }
                            rawMode = RAW_FALSE;

                            setBuf(immCmdSB.toString());
                            immCmdSB.setLength(0);

                            if (rawExecute) {
                                historize();
                                processSQL();
                            } else if (interactive) {
                                stdprintln(rb.getString(
                                            SqltoolRB.RAW_MOVEDTOBUFFER));
                            }
                        } else {
                            if (rawMode == RAW_DATA) {
                                immCmdSB.append('\n');
                            }
                            rawMode = RAW_DATA;

                            if (inputLine.length() > 0) {
                                immCmdSB.append(inputLine);
                            }
                        }

                        continue;
                    }

                    if (immCmdSB.length() == 0) {
                    // NEW Immediate Command (i.e., not appending).
                        if (trimmedInput.startsWith("/*")) {
                            postCommentIndex = trimmedInput.indexOf("*/", 2)
                                               + 2;

                            if (postCommentIndex > 1) {
                                // I see no reason to leave comments in
                                // history.
                                inputLine = inputLine.substring(
                                    postCommentIndex + inputLine.length()
                                    - trimmedInput.length());
                                trimmedInput = inputLine.trim();
                            } else {
                                // Just so we get continuation lines:
                                immCmdSB.append("COMMENT");

                                inComment = true;

                                continue;
                            }
                        }

                        if (trimmedInput.length() == 0) {
                            // This is just to filter out useless newlines at
                            // beginning of commands.
                            continue;
                        }

                        if ((trimmedInput.charAt(0) == '*'
                                && (trimmedInput.length() < 2
                                    || trimmedInput.charAt(1) != '{'))
                                || trimmedInput.charAt(0) == '\\') {
                            setBuf(trimmedInput);
                            processFromBuffer();
                            continue;
                        }

                        if (trimmedInput.charAt(0) == ':' && interactive) {
                            processBuffHist(trimmedInput.substring(1));
                            continue;
                        }

                        String ucased = trimmedInput.toUpperCase();

                        if (ucased.startsWith("DECLARE")
                                || ucased.startsWith("BEGIN")) {
                            rawMode = RAW_EMPTY;

                            immCmdSB.append(inputLine);

                            if (interactive) {
                                stdprintln(RAW_LEADIN_MSG);
                            }

                            continue;
                        }
                    }

                    if (trimmedInput.length() == 0 && interactive &&!inComment) {
                        // Blank lines delimit commands ONLY IN INTERACTIVE
                        // MODE!
                        setBuf(immCmdSB.toString());
                        immCmdSB.setLength(0);
                        stdprintln(rb.getString(SqltoolRB.INPUT_MOVEDTOBUFFER));
                        continue;
                    }

                    deTerminated = SqlFile.deTerminated(inputLine);

                    // A null terminal line (i.e., /\s*;\s*$/) is never useful.
                    if (!trimmedInput.equals(";")) {
                        if (immCmdSB.length() > 0) {
                            immCmdSB.append('\n');
                        }

                        immCmdSB.append((deTerminated == null) ? inputLine
                                                                   : deTerminated);
                    }

                    if (deTerminated == null) {
                        continue;
                    }

                    // If we reach here, then immCmdSB contains a complete
                    // SQL command.

                    if (immCmdSB.toString().trim().length() == 0) {
                        immCmdSB.setLength(0);
                        throw new SqlToolError(rb.getString(
                                    SqltoolRB.SQLSTATEMENT_EMPTY));
                        // There is nothing inherently wrong with issuing
                        // an empty command, like to test DB server health.
                        // But, this check effectively catches many syntax
                        // errors early, and the DB check can be done by
                        // sending a comment like "// comment".
                    }

                    setBuf(immCmdSB.toString());
                    immCmdSB.setLength(0);
                    historize();
                    processSQL();
                } catch (BadSpecial bs) {
                    // BadSpecials ALWAYS have non-null getMessage().
                    errprintln(rb.getString(SqltoolRB.ERRORAT,
                            new String[] {
                                ((file == null) ? "stdin" : file.toString()),
                                Integer.toString(curLinenum),
                                inputLine,
                                bs.getMessage(),
                            }
                    ));
                    Throwable cause = bs.getCause();
                    if (cause != null) {
                        errprintln(rb.getString(SqltoolRB.CAUSEREPORT,
                                cause.toString()));

                    }

                    if (!continueOnError) {
                        throw new SqlToolError(bs);
                    }
                } catch (SQLException se) {
                    errprintln("SQL " + rb.getString(SqltoolRB.ERRORAT,
                            new String[] {
                                ((file == null) ? "stdin" : file.toString()),
                                Integer.toString(curLinenum),
                                lastSqlStatement,
                                se.getMessage(),
                            }));
                    // It's possible that we could have
                    // SQLException.getMessage() == null, but if so, I think
                    // it reasonsable to show "null".  That's a DB inadequacy.

                    if (!continueOnError) {
                        throw se;
                    }
                } catch (BreakException be) {
                    String msg = be.getMessage();

                    if (recursed) {
                        rollbackUncoms = false;
                        // Recursion level will exit by rethrowing the BE.
                        // We set rollbackUncoms to false because only the
                        // top level should detect break errors and
                        // possibly roll back.
                    } else if (msg == null || msg.equals("file")) {
                        break;
                    } else {
                        errprintln(rb.getString(SqltoolRB.BREAK_UNSATISFIED,
                                msg));
                    }

                    if (recursed ||!continueOnError) {
                        throw be;
                    }
                } catch (ContinueException ce) {
                    String msg = ce.getMessage();

                    if (recursed) {
                        rollbackUncoms = false;
                    } else {
                        errprintln(rb.getString(SqltoolRB.CONTINUE_UNSATISFIED,
                                msg));
                    }

                    if (recursed ||!continueOnError) {
                        throw ce;
                    }
                } catch (QuitNow qn) {
                    throw qn;
                } catch (SqlToolError ste) {
                    errprint(rb.getString(SqltoolRB.ERRORAT,
                            new String[] {
                                ((file == null) ? "stdin" : file.toString()),
                                Integer.toString(curLinenum),
                                inputLine,
                                ((ste.getMessage() == null)
                                        ? "" : ste.getMessage())
                            }
                    ));
                    if (ste.getMessage() != null) errprintln("");
                    Throwable cause = ste.getCause();
                    if (cause != null) {
                        errprintln(rb.getString(SqltoolRB.CAUSEREPORT,
                                cause.toString()));
                    }
                    if (!continueOnError) {
                        throw ste;
                    }
                }

                immCmdSB.setLength(0);
            }

            if (inComment || immCmdSB.length() != 0) {
                errprintln(rb.getString(SqltoolRB.INPUT_UNTERMINATED,
                        immCmdSB.toString()));
                throw new SqlToolError(rb.getString(
                        SqltoolRB.INPUT_UNTERMINATED, immCmdSB.toString()));
            }

            rollbackUncoms = false;
            // Exiting gracefully, so don't roll back.
        } catch (IOException ioe) {
            throw new SqlToolError(rb.getString(
                    SqltoolRB.PRIMARYINPUT_ACCESSFAIL), ioe);
        } catch (QuitNow qn) {
            if (recursed) {
                throw qn;
                // Will rollback if conditions otherwise require.
                // Otherwise top level will decide based upon qn.getMessage().
            }
            rollbackUncoms = (qn.getMessage() != null);

            if (rollbackUncoms) {
                errprintln(rb.getString(SqltoolRB.ABORTING, qn.getMessage()));
                throw new SqlToolError(qn.getMessage());
            }

            return;
        } finally {
            closeQueryOutputStream();

            if (fetchingVar != null) {
                errprintln(rb.getString(SqltoolRB.PLVAR_SET_INCOMPLETE,
                        fetchingVar));
                rollbackUncoms = true;
            }

            if (br != null) try {
                br.close();
            } catch (IOException ioe) {
                throw new SqlToolError(rb.getString(
                        SqltoolRB.INPUTREADER_CLOSEFAIL), ioe);
            }

            if (rollbackUncoms && possiblyUncommitteds.get()) {
                errprintln(rb.getString(SqltoolRB.ROLLINGBACK));
                curConn.rollback();
                possiblyUncommitteds.set(false);
            }
        }
    }

    /**
     * Returns a copy of given string without a terminating semicolon.
     * If there is no terminating semicolon, null is returned.
     *
     * @param inString Base String, which will not be modified (because
     *                 a "copy" will be returned).
     * @returns Null if inString contains no terminating semi-colon.
     */
    private static String deTerminated(String inString) {
        int index = inString.lastIndexOf(';');

        if (index < 0) {
            return null;
        }

        for (int i = index + 1; i < inString.length(); i++) {
            if (!Character.isWhitespace(inString.charAt(i))) {
                return null;
            }
        }

        return inString.substring(0, index);
    }

    /**
     * Utility nested Exception class for internal use only.
     *
     * Do not instantiate with null message.
     */
    static private class BadSpecial extends AppendableException {
        static final long serialVersionUID = 7162440064026570590L;

        BadSpecial(String s) {
            super(s);
            if (s == null)
                throw new RuntimeException(
                        "Must construct BadSpecials with non-null message");
        }
        BadSpecial(String s, Throwable t) {
            super(s, t);
            if (s == null)
                throw new RuntimeException(
                        "Must construct BadSpecials with non-null message");
        }
    }

    /**
     * Utility nested Exception class for internal use.
     * This must extend SqlToolError because it has to percolate up from
     * recursions of SqlTool.execute(), yet SqlTool.execute() is public.
     * Therefore, external users have no reason to specifically handle
     * QuitNow.
     */
    private class QuitNow extends SqlToolError {
        static final long serialVersionUID = 1811094258670900488L;

        public QuitNow(String s) {
            super(s);
        }

        public QuitNow() {
            super();
        }
    }

    /**
     * Utility nested Exception class for internal use.
     * This must extend SqlToolError because it has to percolate up from
     * recursions of SqlTool.execute(), yet SqlTool.execute() is public.
     * Therefore, external users have no reason to specifically handle
     * BreakException.
     */
    private class BreakException extends SqlToolError {
        static final long serialVersionUID = 351150072817675994L;

        public BreakException() {
            super();
        }

        public BreakException(String s) {
            super(s);
        }
    }

    /**
     * Utility nested Exception class for internal use.
     * This must extend SqlToolError because it has to percolate up from
     * recursions of SqlTool.execute(), yet SqlTool.execute() is public.
     * Therefore, external users have no reason to specifically handle
     * ContinueException.
     */
    private class ContinueException extends SqlToolError {
        static final long serialVersionUID = 5064604160827106014L;

        public ContinueException() {
            super();
        }

        public ContinueException(String s) {
            super(s);
        }
    }

    /**
     * Utility nested Exception class for internal use only.
     */
    private class BadSubst extends Exception {
        static final long serialVersionUID = 7325933736897253269L;

        BadSubst(String s) {
            super(s);
        }
    }

    /**
     * Utility nested Exception class for internal use only.
     */
    private class RowError extends AppendableException {
        static final long serialVersionUID = 754346434606022750L;

        RowError(String s) {
            super(s);
        }

        RowError(Throwable t) {
            this(null, t);
        }

        RowError(String s, Throwable t) {
            super(s, t);
        }
    }

    /**
     * Execute processSql/processPL/processSpecial from buffer.
     */
    public void processFromBuffer()
            throws BadSpecial, SQLException, SqlToolError {
        historize();
        if (buffer.charAt(0) == '*' && (buffer.length() < 2
                || buffer.charAt(1) != '{')) {
            // Test above just means commands starting with *, EXCEPT
            // for commands beginning with *{.
            processPL(buffer);
            return;
        }

        if (buffer.charAt(0) == '\\') {
            processSpecial(buffer);
            return;
        }
        processSQL();
    }

    /**
     * Process a Buffer/History Command.
     *
     * Due to the nature of the goal here, we don't trim() "other" like
     * we do for other kinds of commands.
     *
     * @param inString Complete command, less the leading ':' character.
     * @throws SQLException  thrown by JDBC driver.
     * @throws BadSpecial    special-command-specific errors.
     * @throws SqlToolError  all other errors.
     */
    private void processBuffHist(String inString)
    throws BadSpecial, SQLException, SqlToolError {
        if (inString.length() < 1) {
            throw new BadSpecial(rb.getString(SqltoolRB.BUFHIST_UNSPECIFIED));
        }

        // First handle the simple cases where user may not specify a
        // command number.
        char commandChar = inString.charAt(0);
        String other       = inString.substring(1);
        if (other.trim().length() == 0) {
            other = null;
        }
        switch (commandChar) {
            case 'l' :
            case 'b' :
                enforce1charBH(other, 'l');
                if (buffer == null) {
                    stdprintln(nobufferYetString);
                } else {
                    stdprintln(rb.getString(SqltoolRB.EDITBUFFER_CONTENTS,
                            buffer));
                }

                return;

            case 'h' :
                enforce1charBH(other, 'h');
                showHistory();

                return;

            case '?' :
                stdprintln(rb.getString(SqltoolRB.BUFFER_HELP));

                return;
        }

        Integer histNum = null;
        Matcher hm = slashHistoryPattern.matcher(inString);
        if (hm.matches()) {
            histNum = historySearch(hm.group(1));
            if (histNum == null) {
                stdprintln(rb.getString(SqltoolRB.SUBSTITUTION_NOMATCH));
                return;
            }
        } else {
            hm = historyPattern.matcher(inString);
            if (!hm.matches()) {
                throw new BadSpecial(rb.getString(SqltoolRB.EDIT_MALFORMAT));
                // Empirically, I find that his pattern always captures two
                // groups.  Unfortunately, there's no way to guarantee that :( .
            }
            histNum = ((hm.group(1) == null || hm.group(1).length() < 1)
                    ? null : new Integer(hm.group(1)));
        }
        if (hm.groupCount() != 2) {
            throw new BadSpecial(rb.getString(SqltoolRB.EDIT_MALFORMAT));
            // Empirically, I find that his pattern always captures two
            // groups.  Unfortunately, there's no way to guarantee that :( .
        }
        commandChar = ((hm.group(2) == null || hm.group(2).length() < 1)
                ? '\0' : hm.group(2).charAt(0));
        other = ((commandChar == '\0') ? null : hm.group(2).substring(1));
        if (other != null && other.length() < 1) other = null;
        String targetCommand = ((histNum == null)
                ? null : commandFromHistory(histNum.intValue()));
        // Every command below depends upon buffer content.

        switch (commandChar) {
            case '\0' :  // Special token set above.  Just history recall.
                setBuf(targetCommand);
                stdprintln(rb.getString(SqltoolRB.BUFFER_RESTORED, buffer));
                return;

            case ';' :
                enforce1charBH(other, ';');

                if (targetCommand != null) setBuf(targetCommand);
                if (buffer == null) throw new BadSpecial(
                        rb.getString(SqltoolRB.NOBUFFER_YET));
                stdprintln(rb.getString(SqltoolRB.BUFFER_EXECUTING, buffer));
                processFromBuffer();

                return;

            case 'a' :
                if (targetCommand == null) targetCommand = buffer;
                if (targetCommand == null) throw new BadSpecial(
                        rb.getString(SqltoolRB.NOBUFFER_YET));
                immCmdSB.append(targetCommand);

                if (other != null) {
                    String deTerminated = SqlFile.deTerminated(other);

                    if (!other.equals(";")) {
                        immCmdSB.append(((deTerminated == null)
                                ? other : deTerminated));
                    }

                    if (deTerminated != null) {
                        // If we reach here, then immCmdSB contains a
                        // complete command.

                        setBuf(immCmdSB.toString());
                        immCmdSB.setLength(0);
                        stdprintln(rb.getString(SqltoolRB.BUFFER_EXECUTING,
                                buffer));
                        processFromBuffer();

                        return;
                    }
                }

                magicPrefix = immCmdSB.toString();
                immCmdSB.setLength(0);
                if (interactive) stdprint(magicPrefix);

                return;

            case 'w' :
                if (targetCommand == null) targetCommand = buffer;
                if (targetCommand == null) throw new BadSpecial(
                        rb.getString(SqltoolRB.NOBUFFER_YET));
                if (other == null) {
                    throw new BadSpecial(rb.getString(
                            SqltoolRB.DESTFILE_DEMAND));
                }
                String targetFile = dereference(other.trim(), false);
                // Dereference and trim the target file name
                // This is the only case where we dereference a : command.

                PrintWriter pw = null;
                try {
                    pw = new PrintWriter((charset == null)
                            ?  (new OutputStreamWriter(
                                    new FileOutputStream(targetFile, true)))
                            :  (new OutputStreamWriter(
                                    new FileOutputStream(targetFile, true),
                                            charset))
                            // Appendmode so can append to an SQL script.
                    );
                    // Replace with just "(new FileOutputStream(file), charset)"
                    // once use defaultCharset from Java 1.5 in charset init.
                    // above.

                    pw.print(targetCommand);
                    if (!targetCommand.matches("\\s*[*:\\\\].*")) pw.print(';');
                    pw.println();
                    pw.flush();
                } catch (Exception e) {
                    throw new BadSpecial(rb.getString(SqltoolRB.FILE_APPENDFAIL,
                            targetFile), e);
                } finally {
                    if (pw != null) pw.close();
                }

                return;

            case 's' :
                boolean modeExecute = false;
                boolean modeGlobal = false;
                if (targetCommand == null) targetCommand = buffer;
                if (targetCommand == null) throw new BadSpecial(
                        rb.getString(SqltoolRB.NOBUFFER_YET));

                try {
                    if (other == null || other.length() < 3) {
                        throw new BadSubst(rb.getString(
                                SqltoolRB.SUBSTITUTION_MALFORMAT));
                    }
                    Matcher m = substitutionPattern.matcher(other);
                    if (!m.matches()) {
                        throw new BadSubst(rb.getString(
                                SqltoolRB.SUBSTITUTION_MALFORMAT));
                    }

                    // Note that this pattern does not include the leading :.
                    if (m.groupCount() < 3 || m.groupCount() > 4) {
                        // Assertion failed
                        throw new RuntimeException(
                                "Matched substitution pattern, but "
                                + "captured " + m.groupCount() + " groups");
                    }
                    String optionGroup = (
                            (m.groupCount() > 3 && m.group(4) != null)
                            ? (new String(m.group(4))) : null);

                    if (optionGroup != null) {
                        if (optionGroup.indexOf(';') > -1) {
                            modeExecute = true;
                            optionGroup = optionGroup.replaceFirst(";", "");
                        }
                        if (optionGroup.indexOf('g') > -1) {
                            modeGlobal = true;
                            optionGroup = optionGroup.replaceFirst("g", "");
                        }
                    }

                    Matcher bufferMatcher = Pattern.compile("(?s"
                            + ((optionGroup == null) ? "" : optionGroup)
                            + ')' + m.group(2)).matcher(targetCommand);
                    String newBuffer = (modeGlobal
                            ? bufferMatcher.replaceAll(m.group(3))
                            : bufferMatcher.replaceFirst(m.group(3)));
                    if (newBuffer.equals(targetCommand)) {
                        stdprintln(rb.getString(
                                SqltoolRB.SUBSTITUTION_NOMATCH));
                        return;
                    }

                    setBuf(newBuffer);
                    stdprintln(rb.getString((modeExecute
                            ? SqltoolRB.BUFFER_EXECUTING
                            : SqltoolRB.EDITBUFFER_CONTENTS), buffer));
                } catch (PatternSyntaxException pse) {
                    throw new BadSpecial(
                            rb.getString(SqltoolRB.SUBSTITUTION_SYNTAX), pse);
                } catch (BadSubst badswitch) {
                    throw new BadSpecial(
                            rb.getString(SqltoolRB.SUBSTITUTION_SYNTAX));
                }

                if (modeExecute) {
                    immCmdSB.setLength(0);
                    processFromBuffer();
                }

                return;
        }

        throw new BadSpecial(rb.getString(SqltoolRB.BUFFER_UNKNOWN,
                Character.toString(commandChar)));
    }

    private boolean doPrepare   = false;
    private String  prepareVar  = null;
    private String  dsvColDelim = null;
    private String  dsvSkipPrefix = null;
    private String  dsvRowDelim = null;
    private String  dsvSkipCols = null;
    private String  DSV_X_SYNTAX_MSG = null;
    private String  DSV_M_SYNTAX_MSG = null;
    private String  nobufferYetString = null;

    private void enforce1charSpecial(String token, char command)
            throws BadSpecial {
        if (token.length() != 1) {
            throw new BadSpecial(rb.getString(SqltoolRB.SPECIAL_EXTRACHARS,
                     Character.toString(command), token.substring(1)));
        }
    }
    private void enforce1charBH(String token, char command)
            throws BadSpecial {
        if (token != null) {
            throw new BadSpecial(rb.getString(SqltoolRB.BUFFER_EXTRACHARS,
                    Character.toString(command), token));
        }
    }

    /**
     * Process a Special Command.
     *
     * @param inString TRIMMED complete command, including the leading
     *                 '\' character.
     * @throws SQLException thrown by JDBC driver.
     * @throws BadSpecial special-command-specific errors.
     * @throws SqlToolError all other errors, plus QuitNow,
     *                      BreakException, ContinueException.
     */
    private void processSpecial(String inString)
    throws BadSpecial, QuitNow, SQLException, SqlToolError {
        if (inString.equals("\\")) {
            throw new BadSpecial(rb.getString(SqltoolRB.SPECIAL_UNSPECIFIED));
        }
        Matcher m = specialPattern.matcher(
                plMode ? dereference(inString, false) : inString);
        if (!m.matches()) {
            throw new BadSpecial(rb.getString(SqltoolRB.SPECIAL_MALFORMAT));
            // I think it's impossible to get here, since the pattern is
            // so liberal.
        }
        if (m.groupCount() < 1 || m.groupCount() > 2) {
            // Failed assertion
            throw new RuntimeException(
                    "Pattern matched, yet captured " + m.groupCount()
                    + " groups");
        }

        String arg1 = m.group(1);
        String other = ((m.groupCount() > 1) ? m.group(2) : null);

        switch (arg1.charAt(0)) {
            case 'q' :
                enforce1charSpecial(arg1, 'q');
                if (other != null) {
                    throw new QuitNow(other);
                }

                throw new QuitNow();
            case 'H' :
                enforce1charSpecial(arg1, 'H');
                htmlMode = !htmlMode;

                stdprintln(rb.getString(SqltoolRB.HTML_MODE,
                        Boolean.toString(htmlMode)));

                return;

            case 'm' :
                if (arg1.equals("m?") ||
                        (arg1.equals("m") && other != null
                                 && other.equals("?"))) {
                    stdprintln(DSV_OPTIONS_TEXT + LS + DSV_M_SYNTAX_MSG);
                    return;
                }
                if (arg1.length() != 1 || other == null) {
                    throw new BadSpecial(DSV_M_SYNTAX_MSG);
                }
                boolean noComments = other.charAt(other.length() - 1) == '*';
                String skipPrefix = null;

                if (noComments) {
                    other = other.substring(0, other.length()-1).trim();
                    if (other.length() < 1) {
                        throw new BadSpecial(DSV_M_SYNTAX_MSG);
                    }
                } else {
                    skipPrefix = dsvSkipPrefix;
                }
                int colonIndex = other.indexOf(" :");
                if (colonIndex > -1 && colonIndex < other.length() - 2) {
                    skipPrefix = other.substring(colonIndex + 2);
                    other = other.substring(0, colonIndex).trim();
                }

                importDsv(other, skipPrefix);

                return;

            case 'x' :
                if (arg1.equals("x?") ||
                        (arg1.equals("x") && other != null
                                 && other.equals("?"))) {
                    stdprintln(DSV_OPTIONS_TEXT + LS + DSV_X_SYNTAX_MSG);
                    return;
                }
                try {
                    if (arg1.length() != 1 || other == null) {
                        throw new BadSpecial(DSV_X_SYNTAX_MSG);
                    }

                    String tableName = ((other.indexOf(' ') > 0) ? null
                                                                 : other);

                    if (dsvTargetFile == null && tableName == null) {
                        throw new BadSpecial(rb.getString(
                                    SqltoolRB.DSV_TARGETFILE_DEMAND));
                    }
                    File dsvFile = new File((dsvTargetFile == null)
                                            ? (tableName + ".dsv")
                                            : dsvTargetFile);

                    pwDsv = new PrintWriter((charset == null)
                       ? (new OutputStreamWriter(new FileOutputStream(dsvFile)))
                       : (new OutputStreamWriter(new FileOutputStream(dsvFile),
                               charset)));
                    // Replace with just "(new FileOutputStream(file), charset)"
                    // once use defaultCharset from Java 1.5 in charset init.
                    // above.

                    ResultSet rs = curConn.createStatement().executeQuery(
                            (tableName == null) ? other
                                                : ("SELECT * FROM "
                                                   + tableName));
                    List colList = new ArrayList();
                    int[] incCols = null;
                    if (dsvSkipCols != null) {
                        Set skipCols = new HashSet();
                        String[] skipColsArray = dsvSkipCols.split("\\s*\\Q"
                                + dsvColDelim + "\\E\\s*");
                        for (int i = 0; i < skipColsArray.length; i++) {
                            skipCols.add(skipColsArray[i].toLowerCase());
                        }
                        ResultSetMetaData rsmd = rs.getMetaData();
                        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                            if (!skipCols.remove(rsmd.getColumnName(i)
                                    .toLowerCase())) {
                                colList.add(new Integer(i));
                            }
                        }
                        if (colList.size() < 1) {
                            throw new BadSpecial(rb.getString(
                                    SqltoolRB.DSV_NOCOLSLEFT, dsvSkipCols));
                        }
                        if (skipCols.size() > 0) {
                            throw new BadSpecial(rb.getString(
                                    SqltoolRB.DSV_SKIPCOLS_MISSING,
                                        skipCols.toString()));
                        }
                        incCols = new int[colList.size()];
                        for (int i = 0; i < incCols.length; i++) {
                            incCols[i] = ((Integer) colList.get(i)).intValue();
                        }
                    }
                    displayResultSet(null, rs, incCols, null);
                    pwDsv.flush();
                    stdprintln(rb.getString(SqltoolRB.FILE_WROTECHARS,
                            Long.toString(dsvFile.length()),
                            dsvFile.toString()));
                } catch (FileNotFoundException e) {
                    throw new BadSpecial(rb.getString(SqltoolRB.FILE_WRITEFAIL,
                            other), e);
                } catch (UnsupportedEncodingException e) {
                    throw new BadSpecial(rb.getString(SqltoolRB.FILE_WRITEFAIL,
                            other), e);
                } finally {
                    // Reset all state changes
                    if (pwDsv != null) {
                        pwDsv.close();
                    }

                    pwDsv       = null;
                }

                return;

            case 'd' :
                if (arg1.equals("d?") ||
                        (arg1.equals("d") && other != null
                                 && other.equals("?"))) {
                    stdprintln(D_OPTIONS_TEXT);
                    return;
                }
                if (arg1.length() == 2) {
                    listTables(arg1.charAt(1), other);

                    return;
                }

                if (arg1.length() == 1 && other != null) try {
                    int space = other.indexOf(' ');

                    if (space < 0) {
                        describe(other, null);
                    } else {
                        describe(other.substring(0, space),
                                 other.substring(space + 1).trim());
                    }

                    return;
                } catch (SQLException se) {
                    throw new BadSpecial(rb.getString(
                            SqltoolRB.METADATA_FETCH_FAIL), se);
                }

                throw new BadSpecial(rb.getString(SqltoolRB.SPECIAL_D_LIKE));
            case 'o' :
                enforce1charSpecial(arg1, 'o');
                if (other == null) {
                    if (pwQuery == null) {
                        throw new BadSpecial(rb.getString(
                                SqltoolRB.OUTPUTFILE_NONETOCLOSE));
                    }

                    closeQueryOutputStream();

                    return;
                }

                if (pwQuery != null) {
                    stdprintln(rb.getString(SqltoolRB.OUTPUTFILE_REOPENING));
                    closeQueryOutputStream();
                }

                try {
                    pwQuery = new PrintWriter((charset == null)
                            ? (new OutputStreamWriter(
                                    new FileOutputStream(other, true)))
                            : (new OutputStreamWriter(
                                    new FileOutputStream(other, true), charset))
                    );
                    // Replace with just "(new FileOutputStream(file), charset)"
                    // once use defaultCharset from Java 1.5 in charset init.
                    // above.

                    /* Opening in append mode, so it's possible that we will
                     * be adding superfluous <HTML> and  tags.
                     * I think that browsers can handle that */
                    pwQuery.println((htmlMode
                            ? ("<HTML>" + LS + "