## org.hsqldb.cmdline
Class SqlFile

    java.lang.Object
      org.hsqldb.cmdline.SqlFile

    public class SqlFile

extends Object

Encapsulation of SQL text and the environment under which it will executed with a JDBC Connection. 'SqlInputStream' would be a more precise name, but the content we are talking about here is what is colloqially known as the contents of "SQL file"s.

The file `src/org/hsqldb/sample/SqlFileEmbedder.java` in the HSQLDB distribution provides an example for using SqlFile to execute SQL files directly from your own Java classes.

The complexities of passing userVars and macros maps are to facilitate strong scoping (among blocks and nested scripts).

Some implementation comments and variable names use keywords based on the following definitions.

- 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 very differently than Postgresql does, in part 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 dictionary, one must change the database instance (to do that would require work to restore the original state and could have disastrous effects upon transactions).

The process*() methods, other than processBuffHist() ALWAYS execute on "buffer", and expect it to contain the method specific prefix (if any).

The input/output Reader/Stream are generally managed by the caller. An exception is that the input reader may be closed automatically or on demand by the user, since in some cases this class builds the Reader. There is no corresponding functionality for output since the user always has control over that object (which may be null or System.out).

Author:
  Blaine Simpson (blaine dot simpson at admc dot com)
See Also:
  The SqlTool chapter of the HyperSQL Utilities Guide, `SqlFileEmbedder`

Field Summary
`static String` `LS` Platform-specific line separator

Constructor Summary
`SqlFile(File inputFile)` Convenience wrapper for the SqlFile(File, String) constructor
`SqlFile(File inputFile, String encoding)` Convenience wrapper for the SqlFile(File, String, boolean) constructor
`SqlFile(File inputFile, String encoding, boolean interactive)` Constructor for non-interactive usage with a SQL file, using the specified encoding and sending normal output to stdout.
`SqlFile(Reader reader, String inputStreamLabel, PrintStream psStd, String encoding, boolean interactive, File baseDir)` Instantiate a SqlFile instance for SQL input from 'reader'.
`SqlFile(String encoding, boolean interactive)` Constructor for interactive usage with stdin/stdout

Method Summary
`void` `addMacros(Map<String,org.hsqldb.cmdline.sqltool.Token> newMacros)`
`void` `addUserVars(Map<String,String> newUserVars)`
`static byte[]` `bitCharsToBytes(String hexChars)` Just a stub for now.
`static boolean` `canDisplayType(int i)` This method is used to tell SqlFile whether this Sql Type must ALWAYS be loaded to the binary buffer without displaying.
`void` `closeReader()` Close the reader.
`static String` `convertEscapes(String inString)` Translates user-supplied escapes into the traditionaly corresponding corresponding binary characters.
`void` `dsvSafe(String s)` Validate that String is safe to write TO DSV file.
`static String` `escapeHtml(String s)` Escaping rules taken from 'Reserved Characters in HTML table at http://www.w3schools.com/tags/ref_entities.asp
`void` `execute()` Process all the commands from the file or Reader associated with "this" object.
`static String` `getBanner(Connection c)` Returns a String report for the specified JDBC Connection.
`Connection` `getConnection()`
`String` `getCurrentSchema()`
`Map<String,org.hsqldb.cmdline.sqltool.Token>` `getMacros()`
`Map<String,String>` `getUserVars()` Get a reference to the user variable map.
`static byte[]` `hexCharOctetsToBytes(String hexChars)` Convert a String to a byte array by interpreting every 2 characters as an octal byte value.
`void` `importDsv(String filePath, String skipPrefix)` Name is self-explanatory.
`static byte[]` `loadBinary(File binFile)` Binary file load
`void` `setAutoClose(boolean autoClose)` Specify whether the supplied or generated input Reader should automatically be closed by the execute() method.
`void` `setConnection(Connection jdbcConn)`
`void` `setContinueOnError(boolean continueOnError)`
`void` `setMaxHistoryLength(int maxHistoryLength)`
`static String` `sqlTypeToString(int i)` Return a String representation of the specified java.sql.Types type.
`static byte[]` `streamToBytes(InputStream is)` As the name says...
`String` `streamToString(InputStream isIn, String cs)` As the name says...

Methods inherited from class java.lang.Object
`equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

Field Detail

### LS

    public static String LS

  Platform-specific line separator

Constructor Detail

### SqlFile

    public SqlFile(File inputFile)
            throws IOException

  Convenience wrapper for the SqlFile(File, String) constructor

  Throws:
    `IOException`
  See Also:
    `SqlFile(File, String)`

### SqlFile

    public SqlFile(File inputFile,
                   String encoding)
            throws IOException

  Convenience wrapper for the SqlFile(File, String, boolean) constructor

  Parameters:
    `encoding` - is applied to both the given File and other files read in or written out. Null will use your env+JVM settings.
  Throws:
    `IOException`
  See Also:
    `SqlFile(File, String, boolean)`

### SqlFile

    public SqlFile(File inputFile,
                   String encoding,
                   boolean interactive)
            throws IOException

  Constructor for non-interactive usage with a SQL file, using the specified encoding and sending normal output to stdout.

  Parameters:
    `encoding` - is applied to the given File and other files read in or written out. Null will use your env+JVM settings.
    `interactive` - If true, prompts are printed, the interactive Special commands are enabled, and continueOnError defaults to true.
  Throws:
    `IOException`
  See Also:
    `SqlFile(Reader, String, PrintStream, String, boolean, File)`

### SqlFile

    public SqlFile(String encoding,
                   boolean interactive)
            throws IOException

  Constructor for interactive usage with stdin/stdout

  Parameters:
    `encoding` - is applied to other files read in or written out (but not to stdin or stdout). Null will use your env+JVM settings.
    `interactive` - If true, prompts are printed, the interactive Special commands are enabled, and continueOnError defaults to true.
  Throws:
    `IOException`
  See Also:
    `SqlFile(Reader, String, PrintStream, String, boolean, File)`

### SqlFile

    public SqlFile(Reader reader,
                   String inputStreamLabel,
                   PrintStream psStd,
                   String encoding,
                   boolean interactive,
                   File baseDir)
            throws IOException

  Instantiate a SqlFile instance for SQL input from 'reader'. After any needed customization, the SQL can be executed by the execute method.

  Most Special Commands and many Buffer commands are only for interactive use.

  This program never writes to an error stream (stderr or alternative). All meta messages and error messages are written using the logging facility.

  Parameters:
    `reader` - Source for the SQL to be executed. Caller is responsible for setting up encoding. (the 'encoding' parameter will NOT be applied to this reader).
    `psStd` - PrintStream for normal output. If null, normal output will be discarded. Caller is responsible for settingup encoding (the 'encoding' parameter will NOT be applied to this stream).
    `interactive` - If true, prompts are printed, the interactive Special commands are enabled, and continueOnError defaults to true.
  Throws:
    `IOException`
  See Also:
    `execute()`

Method Detail

### setConnection

    public void setConnection(Connection jdbcConn)

### getConnection

    public Connection getConnection()

### setContinueOnError

    public void setContinueOnError(boolean continueOnError)

### setMaxHistoryLength

    public void setMaxHistoryLength(int maxHistoryLength)

### addMacros

    public void addMacros(Map<String,org.hsqldb.cmdline.sqltool.Token> newMacros)

### addUserVars

    public void addUserVars(Map<String,String> newUserVars)

### getUserVars

    public Map<String,String> getUserVars()

  Get a reference to the user variable map. Since you are getting a reference to the private map used inside this class, update this map with great caution and attention to lifecycle handling of the variable map.

### getMacros

    public Map<String,org.hsqldb.cmdline.sqltool.Token> getMacros()

### setAutoClose

    public void setAutoClose(boolean autoClose)

  Specify whether the supplied or generated input Reader should automatically be closed by the execute() method.

  execute() will close the Reader by default (i.e. 'autoClose' defaults to true). You may want to set this to false if you want to stop execution with \q or similar, then continue using the Reader or underlying Stream.

  The caller is always responsible for closing the output object (if any) used by SqlFile.

### execute

    public void execute()
                 throws org.hsqldb.cmdline.SqlToolError,
                        SQLException

  Process all the commands from the file or Reader associated with "this" object. SQL commands in the content get executed against the current JDBC data source connection.

  Throws:
    `SQLExceptions` - thrown by JDBC driver. Only possible if in "\c false" mode.
    `org.hsqldb.cmdline.SqlToolError` - all other errors. This includes including QuitNow, BreakException, ContinueException for recursive calls only.
    `SQLException`

### closeReader

    public void closeReader()

  Close the reader. The execute method will run this automatically, by default.

### getCurrentSchema

    public String getCurrentSchema()
                            throws org.hsqldb.cmdline.SqlFile.BadSpecial,
                                   org.hsqldb.cmdline.SqlToolError

  Throws:
    `org.hsqldb.cmdline.SqlFile.BadSpecial`
    `org.hsqldb.cmdline.SqlToolError`

### streamToString

    public String streamToString(InputStream isIn,
                                 String cs)
                          throws IOException

  As the name says... This method always closes the input stream.

  Throws:
    `IOException`

### streamToBytes

    public static byte[] streamToBytes(InputStream is)
                                throws IOException

  As the name says...

  Throws:
    `IOException`

### loadBinary

    public static byte[] loadBinary(File binFile)
                             throws IOException

  Binary file load

  Returns:
    The bytes which are the content of the fil
  Throws:
    `IOException`

### canDisplayType

    public static boolean canDisplayType(int i)

  This method is used to tell SqlFile whether this Sql Type must ALWAYS be loaded to the binary buffer without displaying.

  N.b.: If this returns "true" for a type, then the user can never "see" values for these columns. Therefore, if a type may-or-may-not-be displayable, better to return false here and let the user choose. In general, if there is a toString() operator for this Sql Type then return false, since the JDBC driver should know how to make the value displayable.

  See Also:
    http://java.sun.com/docs/books/tutorial/jdbc/basics/retrieving.html The table on this page lists the most common SqlTypes, all of which must implement toString(), `Types`

### sqlTypeToString

    public static String sqlTypeToString(int i)

  Return a String representation of the specified java.sql.Types type.

### dsvSafe

    public void dsvSafe(String s)
                 throws org.hsqldb.cmdline.SqlToolError

  Validate that String is safe to write TO DSV file.

  Throws:
    `org.hsqldb.cmdline.SqlToolError` - if validation fails.

### convertEscapes

    public static String convertEscapes(String inString)

  Translates user-supplied escapes into the traditionaly corresponding corresponding binary characters. Allowed sequences:
  - \0\d+ (an octal digit)
  - \[0-9]\d* (a decimal digit)
  - \[Xx][0-9]{2} (a hex digit)
  - \n Newline (Ctrl-J)
  - \r Carriage return (Ctrl-M)
  - \t Horizontal tab (Ctrl-I)
  - \f Form feed (Ctrl-L)
  Java 1.4 String methods will make this into a 1 or 2 line task.

### importDsv

    public void importDsv(String filePath,
                          String skipPrefix)
                   throws org.hsqldb.cmdline.SqlToolError

  Name is self-explanatory.

  Throws:
    `org.hsqldb.cmdline.SqlToolError` - Would prefer to throw an internal exception, but we want this method to have external visibility.

### hexCharOctetsToBytes

    public static byte[] hexCharOctetsToBytes(String hexChars)

  Convert a String to a byte array by interpreting every 2 characters as an octal byte value.

### bitCharsToBytes

    public static byte[] bitCharsToBytes(String hexChars)

  Just a stub for now.

### getBanner

    public static String getBanner(Connection c)

  Returns a String report for the specified JDBC Connection. For databases with poor JDBC support, you won't get much detail.

### escapeHtml

    public static String escapeHtml(String s)

  Escaping rules taken from 'Reserved Characters in HTML table at http://www.w3schools.com/tags/ref_entities.asp

Copyright © 2001 - 2010 HSQL Development Group.