~ubuntu-branches/ubuntu/maverick/hsqldb/maverick

      Command</a></span></dt><dt><span class="section"><a href="#N10ECE">Manual Changes to the .script File</a></span></dt></dl></dd><dt><span class="section"><a href="#N10F02">Backing Up Databases</a></span></dt></dl></dd><dt><span class="chapter"><a href="#texttables-chapter">6. Text Tables</a></span></dt><dd><dl><dt><span class="section"><a href="#N10F46">The Implementation</a></span></dt><dd><dl><dt><span class="section"><a href="#N10F49">Definition of Tables</a></span></dt><dt><span class="section"><a href="#N10F58">Scope and Reassignment</a></span></dt><dt><span class="section"><a href="#N10F73">Null Values in Columns of Text Tables</a></span></dt><dt><span class="section"><a href="#N10F7F">Configuration</a></span></dt></dl></dd><dt><span class="section"><a href="#N11030">Text File Issues</a></span></dt><dt><span class="section"><a href="#N11055">Text File Global Properties</a></span></dt><dt><span class="section"><a href="#N11093">Importing from a Text Table file</a></span></dt></dl></dd><dt><span class="chapter"><a href="#tls-chapter">7. TLS</a></span></dt><dd><dl><dt><span class="section"><a href="#N110D1">Requirements</a></span></dt><dt><span class="section"><a href="#N110EE">Encrypting your JDBC connection</a></span></dt><dd><dl><dt><span class="section"><a href="#N110F3">Client-Side</a></span></dt><dt><span class="section"><a href="#N1115E">Server-Side</a></span></dt></dl></dd><dt><span class="section"><a href="#jsse-section">JSSE</a></span></dt><dt><span class="section"><a href="#privatekey-section">Making a Private-key Keystore</a></span></dt><dd><dl><dt><span class="section"><a href="#N1119A">CA-Signed Cert</a></span></dt><dt><span class="section"><a href="#N111C3">Non-CA-Signed Cert</a></span></dt></dl></dd><dt><span class="section"><a href="#N111D0">Automatic Server or WebServer startup on UNIX</a></span></dt></dl></dd><dt><span class="chapter"><a href="#sqltool-chapter">8. SqlTool</a></span></dt><dd><dl><dt><span class="section"><a href="#N1120E">Purpose</a></span></dt><dd><dl><dt><span class="section"><a href="#N11217">Recent changes</a></span></dt></dl></dd><dt><span class="section"><a href="#baremin-section">The Bare Minimum</a></span></dt><dd><dl><dt><span class="section"><a href="#N112EC">Non-displayable Types</a></span></dt><dt><span class="section"><a href="#N11311">Desktop shortcuts</a></span></dt><dt><span class="section"><a href="#N1138F">Loading sample data</a></span></dt></dl></dd><dt><span class="section"><a href="#auth-section">RC File Authentication Setup</a></span></dt><dt><span class="section"><a href="#N11428">

        </a></span></dt><dt><span class="section"><a href="#int-section">Interactive</a></span></dt><dd><dl><dt><span class="section"><a href="#N114AE">Command Types</a></span></dt><dt><span class="section"><a href="#N11502">Special Commands</a></span></dt><dt><span class="section"><a href="#N115B6">Buffer Commands</a></span></dt><dt><span class="section"><a href="#interactive_pl_commands-section">PL Commands</a></span></dt><dt><span class="section"><a href="#binary_files-section">

                Storing and retrieving binary files</a></span></dt><dt><span class="section"><a href="#N116DA">SQL History</a></span></dt><dt><span class="section"><a href="#N116E9">Shell scripting and command-line piping</a></span></dt><dt><span class="section"><a href="#N116F2">Emulating Non-Interactive mode</a></span></dt></dl></dd><dt><span class="section"><a href="#nonint-section">Non-Interactive</a></span></dt><dd><dl><dt><span class="section"><a href="#sqlswitch-section">Giving SQL on the Command Line</a></span></dt><dt><span class="section"><a href="#N11755">SQL Files</a></span></dt><dt><span class="section"><a href="#scripting-section">Piping and shell scripting</a></span></dt><dt><span class="section"><a href="#N117BD">Optimally Compatible SQL Files</a></span></dt><dt><span class="section"><a href="#N117D0">Comments</a></span></dt><dt><span class="section"><a href="#N117F3">Special Commands and Buffer Commands in SQL Files</a></span></dt><dt><span class="section"><a href="#N1185E">Automation</a></span></dt><dt><span class="section"><a href="#N11869">Getting Interactive Functionality with SQL Files</a></span></dt><dt><span class="section"><a href="#charencoding-section">

                Character Encoding</a></span></dt></dl></dd><dt><span class="section"><a href="#report-section">Generating Text or HTML Reports</a></span></dt><dt><span class="section"><a href="#pl-section">SqlTool Procedural Language</a></span></dt><dd><dl><dt><span class="section"><a href="#N118F8">Variables</a></span></dt><dt><span class="section"><a href="#pl_alias-section">PL Aliases</a></span></dt><dt><span class="section"><a href="#N11976">Logical Expressions</a></span></dt><dt><span class="section"><a href="#N119EF">Flow Control</a></span></dt></dl></dd><dt><span class="section"><a href="#chunk-section">Chunking</a></span></dt><dd><dl><dt><span class="section"><a href="#N11A4D">Why?</a></span></dt><dt><span class="section"><a href="#N11A54">How?</a></span></dt></dl></dd><dt><span class="section"><a href="#raw-section">Raw Mode</a></span></dt><dt><span class="section"><a href="#N11A81">PL/SQL</a></span></dt><dt><span class="section"><a href="#N11AA2">Using hsqltool.jar and hsqldbutil.jar</a></span></dt><dt><span class="section"><a href="#N11AFB">Unit Testing SqlTool</a></span></dt></dl></dd><dt><span class="chapter"><a href="#sqlsyntax-chapter">9. SQL Syntax</a></span></dt><dd><dl><dt><span class="section"><a href="#N11B4F">Notational Conventions Used in this Chapter</a></span></dt><dt><span class="section"><a href="#N11B6C">SQL Commands</a></span></dt><dd><dl><dt><span class="section"><a href="#alter_index-section">ALTER INDEX</a></span></dt><dt><span class="section"><a href="#alter_sequence-section">ALTER SEQUENCE</a></span></dt><dt><span class="section"><a href="#N11B8B">ALTER SCHEMA</a></span></dt><dt><span class="section"><a href="#alter_table-section">ALTER TABLE</a></span></dt><dt><span class="section"><a href="#alter_user-section">ALTER USER</a></span></dt><dt><span class="section"><a href="#call-section">CALL</a></span></dt><dt><span class="section"><a href="#checkpoint-section">CHECKPOINT</a></span></dt><dt><span class="section"><a href="#commit-section">COMMIT</a></span></dt><dt><span class="section"><a href="#connect-section">CONNECT</a></span></dt><dt><span class="section"><a href="#create_alias-section">CREATE ALIAS</a></span></dt><dt><span class="section"><a href="#create_index-section">CREATE INDEX</a></span></dt><dt><span class="section"><a href="#create_role-section">CREATE ROLE</a></span></dt><dt><span class="section"><a href="#N11CA5">CREATE SCHEMA</a></span></dt><dt><span class="section"><a href="#create_sequence-section">CREATE SEQUENCE</a></span></dt><dt><span class="section"><a href="#create_table-section">CREATE TABLE</a></span></dt><dt><span class="section"><a href="#create_trigger-section">CREATE TRIGGER</a></span></dt><dt><span class="section"><a href="#create_user-section">CREATE USER</a></span></dt><dt><span class="section"><a href="#create_view-section">CREATE VIEW</a></span></dt><dt><span class="section"><a href="#delete-section">DELETE</a></span></dt><dt><span class="section"><a href="#disconnect-section">DISCONNECT</a></span></dt><dt><span class="section"><a href="#drop_index-section">DROP INDEX</a></span></dt><dt><span class="section"><a href="#N11E14">DROP ROLE</a></span></dt><dt><span class="section"><a href="#drop_sequence-section">DROP SEQUENCE</a></span></dt><dt><span class="section"><a href="#N11E2E">DROP SCHEMA</a></span></dt><dt><span class="section"><a href="#drop_table-section">DROP TABLE</a></span></dt><dt><span class="section"><a href="#drop_trigger-section">DROP TRIGGER</a></span></dt><dt><span class="section"><a href="#drop_user-section">DROP USER</a></span></dt><dt><span class="section"><a href="#drop_view-section">DROP VIEW</a></span></dt><dt><span class="section"><a href="#explain-section">EXPLAIN PLAN</a></span></dt><dt><span class="section"><a href="#grant-section">GRANT</a></span></dt><dt><span class="section"><a href="#insert-section">INSERT</a></span></dt><dt><span class="section"><a href="#revoke-section">REVOKE</a></span></dt><dt><span class="section"><a href="#rollback-section">ROLLBACK</a></span></dt><dt><span class="section"><a href="#savepoint-section">SAVEPOINT</a></span></dt><dt><span class="section"><a href="#script-section">SCRIPT</a></span></dt><dt><span class="section"><a href="#select-section">SELECT</a></span></dt><dt><span class="section"><a href="#set_autocommit-section">SET AUTOCOMMIT</a></span></dt><dt><span class="section"><a href="#collation-section">SET DATABASE COLLATION</a></span></dt><dt><span class="section"><a href="#N11FBD">SET CHECKPOINT DEFRAG</a></span></dt><dt><span class="section"><a href="#set_ignorecase-section">SET IGNORECASE</a></span></dt><dt><span class="section"><a href="#set_logsize-section">SET LOGSIZE</a></span></dt><dt><span class="section"><a href="#set_password-section">SET PASSWORD</a></span></dt><dt><span class="section"><a href="#set_property-section">SET PROPERTY</a></span></dt><dt><span class="section"><a href="#set_refint-section">SET REFERENTIAL INTEGRITY</a></span></dt><dt><span class="section"><a href="#N1201E">SET SCHEMA</a></span></dt><dt><span class="section"><a href="#set_scriptformat-section">SET SCRIPTFORMAT</a></span></dt><dt><span class="section"><a href="#set_table_index-section">SET TABLE INDEX</a></span></dt><dt><span class="section"><a href="#set_table_readonly-section">SET TABLE READONLY</a></span></dt><dt><span class="section"><a href="#set_table_source-section">SET TABLE SOURCE</a></span></dt><dt><span class="section"><a href="#set_write_delay-section">SET WRITE DELAY</a></span></dt><dt><span class="section"><a href="#shutdown-section">SHUTDOWN</a></span></dt><dt><span class="section"><a href="#update-section">UPDATE</a></span></dt></dl></dd><dt><span class="section"><a href="#datatypes-section">Data Types</a></span></dt><dt><span class="section"><a href="#N12234">SQL Comments</a></span></dt><dt><span class="section"><a href="#stored-section">Stored Procedures / Functions</a></span></dt><dt><span class="section"><a href="#N12265">Built-in Functions and Stored Procedures</a></span></dt><dt><span class="section"><a href="#expression-section">SQL Expression</a></span></dt></dl></dd><dt><span class="appendix"><a href="#building-appendix">A. Building HSQLDB</a></span></dt><dd><dl><dt><span class="section"><a href="#N1256A">Purpose</a></span></dt><dt><span class="section"><a href="#N12573">Building with Ant</a></span></dt><dd><dl><dt><span class="section"><a href="#N1257A">Obtaining Ant</a></span></dt><dt><span class="section"><a href="#N12592">Building Hsqldb with Ant</a></span></dt></dl></dd><dt><span class="section"><a href="#N12641">Building with DOS Batch Files</a></span></dt><dt><span class="section"><a href="#N1265A">Hsqldb CodeSwitcher</a></span></dt><dt><span class="section"><a href="#N12686">Building documentation</a></span></dt></dl></dd><dt><span class="appendix"><a href="#firstclient-appendix">B. First JDBC Client Example</a></span></dt><dt><span class="appendix"><a href="#N126E1">C. Hsqldb Database Files and Recovery</a></span></dt><dd><dl><dt><span class="section"><a href="#N126F8"></a></span></dt><dt><span class="section"><a href="#N12728">States</a></span></dt><dd><dl><dt><span class="section"><a href="#N1272B"></a></span></dt><dt><span class="section"><a href="#N1275A"></a></span></dt><dt><span class="section"><a href="#N12789"></a></span></dt></dl></dd><dt><span class="section"><a href="#N127BB">Procedures</a></span></dt><dd><dl><dt><span class="section"><a href="#N127C0">Clean Shutdown</a></span></dt><dt><span class="section"><a href="#N1281A">Startup</a></span></dt><dt><span class="section"><a href="#N1286C">Repair</a></span></dt></dl></dd></dl></dd><dt><span class="appendix"><a href="#N128B2">D. Running Hsqldb with OpenOffice.org 1.1.x</a></span></dt><dd><dl><dt><span class="section"><a href="#N128D2">Introduction</a></span></dt><dt><span class="section"><a href="#N128DB">Installing</a></span></dt><dt><span class="section"><a href="#N128E0">Setting up OpenOffice.org</a></span></dt><dd><dl><dt><span class="section"><a href="#N128F2">On Windows</a></span></dt><dt><span class="section"><a href="#N12905">On Linux</a></span></dt></dl></dd></dl></dd><dt><span class="appendix"><a href="#N12925">E. Hsqldb Test Utility</a></span></dt><dt><span class="appendix"><a href="#N1299B">F. Database Manager</a></span></dt><dd><dl><dt><span class="section"><a href="#N129C4">Brief Introduction</a></span></dt><dt><span class="section"><a href="#N129EE">Auto tree-update</a></span></dt><dt><span class="section"><a href="#N12A05">Automatic Connection</a></span></dt><dt><span class="section"><a href="#N12A13">RC File</a></span></dt><dt><span class="section"><a href="#N12A38">Using the current DatabaseManagers with an older HSQLDB

    distribution.</a></span></dt><dt><span class="section"><a href="#N12A6A">DatabaseManagerSwing as an Applet</a></span></dt></dl></dd><dt><span class="appendix"><a href="#N12AC8">G. Transfer Tool</a></span></dt><dd><dl><dt><span class="section"><a href="#N12AE5">Brief Introduction</a></span></dt></dl></dd></dl></div><div class="list-of-tables"><p><b>List of Tables</b></p><dl><dt>1. <a href="#N1004D">Alternate formats of this document</a></dt><dt>4.1. <a href="#N108EC">Hsqldb URL Components</a></dt><dt>4.2. <a href="#N109FA">Connection Properties</a></dt><dt>4.3. <a href="#N10A81">Hsqldb Server Properties Files</a></dt><dt>4.4. <a href="#N10ACE">Property File Properties</a></dt><dt>4.5. <a href="#N10B3C">Server Property File Properties</a></dt><dt>4.6. <a href="#N10B6F">WebServer Property File Properties</a></dt><dt>4.7. <a href="#N10BEF">Database-specific Property File Properties</a></dt><dt>4.8. <a href="#N10D6C">SQL command properties</a></dt><dt>9.1. <a href="#N12142">Data Types</a></dt></dl></div><div class="list-of-examples"><p><b>List of Examples</b></p><dl><dt>1.1. <a href="#N1018D">Java code to connect to the local Server above</a></dt><dt>2.1. <a href="#N102E6">Column values which satisfy a 2-column UNIQUE

        constraint</a></dt><dt>2.2. <a href="#N103AB">Query comparison</a></dt><dt>2.3. <a href="#N104F2">Numbering returned rows of a SELECT in sequential order</a></dt><dt>3.1. <a href="#N107DB">server.properties fragment</a></dt><dt>3.2. <a href="#N107EE">example sqltool.rc stanza</a></dt><dt>7.1. <a href="#N11110">Exporting certificate from the server's keystore</a></dt><dt>7.2. <a href="#N11125">Adding a certificate to the client keystore</a></dt><dt>7.3. <a href="#N11134">Specifying your own trust store to a JDBC client</a></dt><dt>7.4. <a href="#N1116A">Running an Hsqldb server with TLS encryption</a></dt><dt>7.5. <a href="#N111AA">Getting a pem-style private key into a JKS keystore</a></dt><dt>8.1. <a href="#N113BD">Sample RC File</a></dt><dt>8.2. <a href="#alias-example">Defining and using a PL alias (PL variable)</a></dt><dt>8.3. <a href="#N116C8">Inserting binary data into database from a file</a></dt><dt>8.4. <a href="#N116CE">Downloading binary data from database to a file</a></dt><dt>8.5. <a href="#N117AD">Piping input into SqlTool</a></dt><dt>8.6. <a href="#N117D9">Valid comment example</a></dt><dt>8.7. <a href="#N117E1">Invalid comment example</a></dt><dt>8.8. <a href="#N11968">Simple SQL file using PL</a></dt><dt>8.9. <a href="#N11A31">SQL File showing use of most PL features</a></dt><dt>8.10. <a href="#N11A46">Single-line chunking example</a></dt><dt>8.11. <a href="#N11A59">Multi-line chunking example</a></dt><dt>8.12. <a href="#N11A78">Raw Mode example</a></dt><dt>8.13. <a href="#N11A9B">PL/SQL Example</a></dt><dt>A.1. <a href="#N12633">Buiding the standard Hsqldb jar file with Ant</a></dt><dt>A.2. <a href="#N12663">Example source code before CodeSwitcher is run</a></dt><dt>A.3. <a href="#N1266B">CodeSwitcher command line invocation</a></dt><dt>A.4. <a href="#N12677">Source code after CodeSwitcher processing</a></dt><dt>A.5. <a href="#N12698">Building HTML User Guides</a></dt><dt>A.6. <a href="#N126A9">Building User Guides in all formats</a></dt><dt>B.1. <a href="#N126DA">JDBC Client source code example</a></dt></dl></div><div class="preface" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="N10030"></a>Introduction</h2></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="#N1003A">Available formats for this document</a></span></dt></dl></div><p>

      alterations under the terms of the HSQLDB license.</p></div></div><div><p class="pubdate">$Date: 2005/07/02 09:11:39 $</p></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="#N1009E">Introduction</a></span></dt><dt><span class="section"><a href="#N100B7">Running Tools</a></span></dt><dt><span class="section"><a href="#N100F3">Running Hsqldb</a></span></dt><dt><span class="section"><a href="#N1013D">Server Modes</a></span></dt><dd><dl><dt><span class="section"><a href="#N10148">Hsqldb Server</a></span></dt><dt><span class="section"><a href="#N10157">Hsqldb Web Server</a></span></dt><dt><span class="section"><a href="#N10168">Hsqldb Servlet</a></span></dt><dt><span class="section"><a href="#N101A8">In-Process (Standalone) Mode</a></span></dt><dt><span class="section"><a href="#N101CA">Memory-Only Databases</a></span></dt></dl></dd><dt><span class="section"><a href="#N101D8">General</a></span></dt><dd><dl><dt><span class="section"><a href="#N101DB">Closing the Database</a></span></dt><dt><span class="section"><a href="#N101E6">Using Multiple Databases in One JVM</a></span></dt><dt><span class="section"><a href="#N101EF">Creating a New Database</a></span></dt></dl></dd><dt><span class="section"><a href="#N10202">Using the Database Engine</a></span></dt><dd><dl><dt><span class="section"><a href="#N1023C">Different Types of Tables</a></span></dt><dt><span class="section"><a href="#N10255">Constraints and Indexes</a></span></dt><dt><span class="section"><a href="#N10268">SQL Support</a></span></dt><dt><span class="section"><a href="#N10281">JDBC Support</a></span></dt></dl></dd></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N1009E"></a>Introduction</h2></div></div><div></div></div><p>The HSQLDB jar package is located in the /lib directory and contains

    java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 mydb -dbname.0 xdb</pre></div><p>The command line argument <tt class="literal">-?</tt> can be used to get

        </p><pre class="programlisting"># $Id: sqltool.rc,v 1.14 2005/05/22 04:46:16 unsaved Exp $

# Template for a MySQL database

#url jdbc:mysql:///test

    use the SQL therein as a model (using any SQL client at all).</p></div></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="tls-chapter"></a>Chapter&nbsp;7.&nbsp;TLS</h2></div><div><h3 class="subtitle"><i>TLS Support (a.k.a. SSL)</i></h3></div><div><div class="authorgroup"><div class="author"><h3 class="author"><span class="firstname">Blaine</span> <span class="surname">Simpson</span></h3><div class="affiliation"><span class="orgname">HSQLDB Development Group<br></span></div><tt class="email">&lt;<a href="mailto:blaine.simpson@admc.com">blaine.simpson@admc.com</a>&gt;</tt></div></div></div><div><p class="pubdate">$Date: 2005/05/31 20:59:13 $</p></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="#N110D1">Requirements</a></span></dt><dt><span class="section"><a href="#N110EE">Encrypting your JDBC connection</a></span></dt><dd><dl><dt><span class="section"><a href="#N110F3">Client-Side</a></span></dt><dt><span class="section"><a href="#N1115E">Server-Side</a></span></dt></dl></dd><dt><span class="section"><a href="#jsse-section">JSSE</a></span></dt><dt><span class="section"><a href="#privatekey-section">Making a Private-key Keystore</a></span></dt><dd><dl><dt><span class="section"><a href="#N1119A">CA-Signed Cert</a></span></dt><dt><span class="section"><a href="#N111C3">Non-CA-Signed Cert</a></span></dt></dl></dd><dt><span class="section"><a href="#N111D0">Automatic Server or WebServer startup on UNIX</a></span></dt></dl></div><p>

                    X590 cert like this.

    keytool -import -trustcacerts -keystore trust.store -alias new_alias -file server.cer</pre></div><p>

    java -Djavax.net.ssl.keyStorePassword=secret -Djavax.net.ssl.keyStore=/usr/hsqldb/db/db3/server.store  \

    openssl pkcs8 -topk8 -outform DER -in Xpvk.pem -inform PEM -out Xpvk.pk8 -nocrypt

 

    openssl x509 -in Xcert.pem -out Xcert.der -outform DER

 

    java DERImport new.keystore NEWALIAS Xpvk.pk8 Xcert.der</pre></div><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>

        </p></div></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="sqltool-chapter"></a>Chapter&nbsp;8.&nbsp;SqlTool</h2></div><div><h3 class="subtitle"><i>SqlTool Manual</i></h3></div><div><div class="author"><h3 class="author"><span class="firstname">Blaine</span> <span class="surname">Simpson</span></h3><div class="affiliation"><span class="orgname">HSQLDB Development Group<br></span></div><tt class="email">&lt;<a href="mailto:blaine.simpson@admc.com">blaine.simpson@admc.com</a>&gt;</tt></div></div><div><p class="pubdate">$Date: 2005/06/04 17:43:30 $</p></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="#N1120E">Purpose</a></span></dt><dd><dl><dt><span class="section"><a href="#N11217">Recent changes</a></span></dt></dl></dd><dt><span class="section"><a href="#baremin-section">The Bare Minimum</a></span></dt><dd><dl><dt><span class="section"><a href="#N112EC">Non-displayable Types</a></span></dt><dt><span class="section"><a href="#N11311">Desktop shortcuts</a></span></dt><dt><span class="section"><a href="#N1138F">Loading sample data</a></span></dt></dl></dd><dt><span class="section"><a href="#auth-section">RC File Authentication Setup</a></span></dt><dt><span class="section"><a href="#N11428">

        </a></span></dt><dt><span class="section"><a href="#int-section">Interactive</a></span></dt><dd><dl><dt><span class="section"><a href="#N114AE">Command Types</a></span></dt><dt><span class="section"><a href="#N11502">Special Commands</a></span></dt><dt><span class="section"><a href="#N115B6">Buffer Commands</a></span></dt><dt><span class="section"><a href="#interactive_pl_commands-section">PL Commands</a></span></dt><dt><span class="section"><a href="#binary_files-section">

                Storing and retrieving binary files</a></span></dt><dt><span class="section"><a href="#N116DA">SQL History</a></span></dt><dt><span class="section"><a href="#N116E9">Shell scripting and command-line piping</a></span></dt><dt><span class="section"><a href="#N116F2">Emulating Non-Interactive mode</a></span></dt></dl></dd><dt><span class="section"><a href="#nonint-section">Non-Interactive</a></span></dt><dd><dl><dt><span class="section"><a href="#sqlswitch-section">Giving SQL on the Command Line</a></span></dt><dt><span class="section"><a href="#N11755">SQL Files</a></span></dt><dt><span class="section"><a href="#scripting-section">Piping and shell scripting</a></span></dt><dt><span class="section"><a href="#N117BD">Optimally Compatible SQL Files</a></span></dt><dt><span class="section"><a href="#N117D0">Comments</a></span></dt><dt><span class="section"><a href="#N117F3">Special Commands and Buffer Commands in SQL Files</a></span></dt><dt><span class="section"><a href="#N1185E">Automation</a></span></dt><dt><span class="section"><a href="#N11869">Getting Interactive Functionality with SQL Files</a></span></dt><dt><span class="section"><a href="#charencoding-section">

                Character Encoding</a></span></dt></dl></dd><dt><span class="section"><a href="#report-section">Generating Text or HTML Reports</a></span></dt><dt><span class="section"><a href="#pl-section">SqlTool Procedural Language</a></span></dt><dd><dl><dt><span class="section"><a href="#N118F8">Variables</a></span></dt><dt><span class="section"><a href="#pl_alias-section">PL Aliases</a></span></dt><dt><span class="section"><a href="#N11976">Logical Expressions</a></span></dt><dt><span class="section"><a href="#N119EF">Flow Control</a></span></dt></dl></dd><dt><span class="section"><a href="#chunk-section">Chunking</a></span></dt><dd><dl><dt><span class="section"><a href="#N11A4D">Why?</a></span></dt><dt><span class="section"><a href="#N11A54">How?</a></span></dt></dl></dd><dt><span class="section"><a href="#raw-section">Raw Mode</a></span></dt><dt><span class="section"><a href="#N11A81">PL/SQL</a></span></dt><dt><span class="section"><a href="#N11AA2">Using hsqltool.jar and hsqldbutil.jar</a></span></dt><dt><span class="section"><a href="#N11AFB">Unit Testing SqlTool</a></span></dt></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N1120E"></a>Purpose</h2></div></div><div></div></div><p>

            as-is for any normal UNIX shell.

            This document is now updated for version 1.46 of SqlTool and

            1.114 of SqlFile (the latter is the class which does most of the

            I expect this version of this document to accurately describe 

            SqlTool for some unknown number of versions into the future.

        </p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11217"></a>Recent changes</h3></div></div><div></div></div><p>This section lists changes to SqlTool since the last

                For this version of this document, that means, changes since

                HSQLDB versions 1.7.x.

                    Slight adjustments to command-line switch behavior to make

                    them more convenient for the most common forms of usage.

                </li><li>

                    Changed PL alias prefix from * to / (in order to allow for

                    the following improvement).

                </li><li>

                    Removed the funny whitespace requirements for PL commands.

                </li><li>

                    Uppercase/lowercase requirements for special commands are

                    relegated to the database.

                    Therefore, in special commands only, you are safest to key 

                    in object names in the case exactly as the real object name

                    in your database.

                    This is the only way for me to remove ambiguity from 

                    case-specific object names without having to code different

                    rules for every database.

                    (Because, for example, Postgresql defaults object names to

                    lowercase and Oracle defaults them to uppercase).

                </li><li>

                    Added special command \dn.

                </li><li>

                    Added special command \ds (old \ds command is now \dS).

                </li><li>

                    Added special command \di.

                </li><li>

                    Renamed special command \* to \c.  (\* still supported

                    for backward compatibility).

                </li><li>

                    Not so stingy about saving commands in history.

                </li><li>

                    Allow optional filter substring for many more \d commands,

                    including for "\d TABLENAME".

                </li><li>

                    Implemented special use of substrings ending with dot.

                    These do not just filter on the string, but mean to

                    filter to only objects with this exact schema.

                </li><li>

                    \da and \ds commands now work for HSQLDB servers, even 

                    though HSQLDB Aliases and Sequences are not listed in the

                    JDBC Metadata.

                </li><li>

                    Improved exception handling.

                </li><li>

                    Implemented Raw SQL mode, and ability for users to enter

                    PL/SQL.

                </li><li>

                    Fixed open Statement bug (HSQLDB bug #1191524).

                </li><li>

                    Improved Oracle idiosyncracy work-arounds.

                </li><li>

                    Added support for all non-text type fields, including BLOBs.

                </li><li>

                    Can download and upload both ASCII and binary column

                    values to/from local files.

                </li><li>

                    Better display of null values occurring in result sets.

                </li><li>

                    RC file functionality isolated into separate class, RCData.

                    Other programs can now use RC files by using this class.

                </li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="baremin-section"></a>The Bare Minimum You Need to Know to Run SqlTool</h2></div></div><div></div></div><div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Warning</h3><p>

                    <a href="#auth-section" title="RC File Authentication Setup">RC File Authentication Setup</a> section.

        </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N112EC"></a>Non-displayable Types</h3></div></div><div></div></div><p>

            </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11311"></a>Desktop shortcuts</h3></div></div><div></div></div><p>

                </p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N1138F"></a>Loading sample data</h3></div></div><div></div></div><p>

            Authentication setup is accomplished by creating a text RC

            configuration file.

        </p><div class="example"><a name="N113BD"></a><p class="title"><b>Example&nbsp;8.1.&nbsp;Sample RC File</b></p><pre class="programlisting"># $Id: sqltool.rc,v 1.14 2005/05/22 04:46:16 unsaved Exp $

# Template for a MySQL database

#url jdbc:mysql:///test

        </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N11428"></a>

CLASSPATH=/path/to/hsqldbutil.jar:/home/bob/myapp/classes:/usr/local/lib/pg.jdbc3.jar

        </p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N114AE"></a>Command Types</h3></div></div><div></div></div><div class="variablelist"><p class="title"><b>Command types</b></p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>

                <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3>

                    The requirement for a space after the asterisk has been

                    removed with this version of SqlFile.

                    It is up to you whether to put a space after the *.

                </div>

            </dd></dl></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11502"></a>Special Commands</h3></div></div><div></div></div><div class="variablelist"><p class="title"><b>Essential Special Commands</b></p><dl><dt><span class="term">\?</span></dt><dd>

                </p>

                    Note that this is a change in behavior from SqlFile

                    before about version 1.100, where substring tests

                    were case-insensitive.

                <p>

                    Beginning with the current version of SqlFile, filter

                    substrings ending with "." are special.

                </p></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>

                Several new \dX commands have been added with the current

                of SqlFile.

                Be aware that the meaning of the \s command has changed!

                Indexes may not be searched for by 

                <span class="emphasis"><em>substring</em></span>, only by

                exact target table name.

                So if <tt class="literal">I1</tt> is an index on table

                <tt class="literal">T1</tt>, then you list this index by running

                "\di T1".

                In addition, many database vendors will report on indexes 

                only if a target table is identified.

                Therefore, "\di" with no argument will fail if your database

                vendor does not support it.

            </p></div></dd><dt><span class="term">\d objectname [filter]</span></dt><dd><p>

        </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N115B6"></a>Buffer Commands</h3></div></div><div></div></div><div class="variablelist"><p class="title"><b>Buffer Commands</b></p><dl><dt><span class="term">:?</span></dt><dd>

        </p><div class="example"><a name="N116C8"></a><p class="title"><b>Example&nbsp;8.3.&nbsp;Inserting binary data into database from a file</b></p><pre class="screen">

    INSERT INTO musictbl (id, stream) VALUES(3112, ?);</pre></div><div class="example"><a name="N116CE"></a><p class="title"><b>Example&nbsp;8.4.&nbsp;Downloading binary data from database to a file</b></p><pre class="screen">

        </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N116DA"></a>SQL History</h3></div></div><div></div></div><p>

        </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N116E9"></a>Shell scripting and command-line piping</h3></div></div><div></div></div><p>

            </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N116F2"></a>Emulating Non-Interactive mode</h3></div></div><div></div></div><p>

                With the current version of SqlTool, we have changed the default

                behavior of the <tt class="literal">--sql</tt> switch.

                the (new) <span class="emphasis"><em>--stdinput</em></span> switch.

                This was changed because it turns out that this is the desired

                behavior well over 90% of the time that you would want to use

                the <tt class="literal">--sql</tt> switch.

                Beginning with the current version of SqlTool, SqlTool will

                automatically add a trailing semicolon to your

                This was changed because in the very few sitations where you

                do not want to execute your SQL by a terminating semicolon, you

                would be better off using an SQL file.

    USERCOUNT=`java -jar $HSQLDB_HOME/lib/hsqldb.jar --sql 'select count(*) from usertbl' urlid` || {

            </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11755"></a>SQL Files</h3></div></div><div></div></div><p>

    java -jar $HSQLDB_HOME/lib/hsqldb.jar  --sql "create user 'tomcat' password 'x'" mem path/to/hsqldb/src/org/hsqldb/sample/sample.sql</pre></div>

            <div class="example"><a name="N117AD"></a><p class="title"><b>Example&nbsp;8.5.&nbsp;Piping input into SqlTool</b></p><pre class="screen">

            </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N117BD"></a>Optimally Compatible SQL Files</h3></div></div><div></div></div><p>

            </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N117D0"></a>Comments</h3></div></div><div></div></div><p>

            </p><div class="example"><a name="N117D9"></a><p class="title"><b>Example&nbsp;8.6.&nbsp;Valid comment example</b></p><pre class="programlisting">

            </p><div class="example"><a name="N117E1"></a><p class="title"><b>Example&nbsp;8.7.&nbsp;Invalid comment example</b></p><pre class="programlisting">

            </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N117F3"></a>Special Commands and Buffer Commands in SQL Files</h3></div></div><div></div></div><p>

                    </p></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>

                        In previous versions of SqlTool, this special command

                        was "\*".  This usage is still supported, but is

                        deprecated.  It was changed because "\*" is a very

                        poor mnemonic.

                        Even the author of the program had to constantly 

                        look up whether "\* true" meant to Continue on

                        error or to Abort upon error.

                        Now, the "c" signifies <span class="emphasis"><em>Continue</em></span>.

                    </p></div></dd></dl></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N1185E"></a>Automation</h3></div></div><div></div></div><p>

            </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11869"></a>Getting Interactive Functionality with SQL Files</h3></div></div><div></div></div><p>

java -Dsqlfile.charset=UTF-8 -jar $HSQLDB_HOME/lib/hsqldb.jar urlid filepath1.sql...</pre></div></p><p>

        </p><p> <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3>

                Users of previous versions of SqlFile should notice that

                whitespace is no longer required after the *

                (though you may use whitespace there if you wish).

        </div> </p><p>

        </p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N118F8"></a>Variables</h3></div></div><div></div></div><div class="itemizedlist"><ul type="disc"><li>

            </p><div class="example"><a name="N11968"></a><p class="title"><b>Example&nbsp;8.8.&nbsp;Simple SQL file using PL</b></p><pre class="programlisting">

            </p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11976"></a>Logical Expressions</h3></div></div><div></div></div><p>

            </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N119EF"></a>Flow Control</h3></div></div><div></div></div><p>

        </p><div class="example"><a name="N11A31"></a><p class="title"><b>Example&nbsp;8.9.&nbsp;SQL File showing use of most PL features</b></p><pre class="programlisting">/*

        <div class="example"><a name="N11A46"></a><p class="title"><b>Example&nbsp;8.10.&nbsp;Single-line chunking example</b></p><pre class="screen">

        </p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11A4D"></a>Why?</h3></div></div><div></div></div><p>

            </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11A54"></a>How?</h3></div></div><div></div></div><p>

                <div class="example"><a name="N11A59"></a><p class="title"><b>Example&nbsp;8.11.&nbsp;Multi-line chunking example</b></p><pre class="screen">

            <div class="example"><a name="N11A78"></a><p class="title"><b>Example&nbsp;8.12.&nbsp;Raw Mode example</b></p><pre class="screen">

        </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N11A81"></a>PL/SQL</h2></div></div><div></div></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>

            <div class="example"><a name="N11A9B"></a><p class="title"><b>Example&nbsp;8.13.&nbsp;PL/SQL Example</b></p><pre class="programlisting">/*

        </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N11AA2"></a>Using hsqltool.jar and hsqldbutil.jar</h2></div></div><div></div></div><p>

        </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N11AFB"></a>Unit Testing SqlTool</h2></div></div><div></div></div><p>

            A unit testing framework is in place.

            This assures the robustness of SqlTool.

            See the file <tt class="filename">testrun/sqltool/readme.txt</tt>

            for instructions on running, modifying, or creating unit

            tests.

            To create a new unit test, you create a SQL file and embed

            metacommands in the SQL file inside of comments.

            The metacommands tell the test harness

            (<tt class="classname">org.hsqldb.test.SqlToolHarness</tt>)

            how to run SqlTool (like with what arguments) and what

            output to expect (i.e. the test criteria).

            You can run tests without JUnit, or you can make a JUnit

            wrapper in the normal fashion.  Any SQL test file can

            be added to our JUnit SqlTool test suite by just adding

            the SQL file name and description to the 

            <tt class="filename">testrun/sqltool/*.list</tt> file for the 

            desired JUnit test method.

        </p><p>

            (The SqlTool unit tests require java 1.4).

        </p></div></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="sqlsyntax-chapter"></a>Chapter&nbsp;9.&nbsp;SQL Syntax</h2></div><div><div class="authorgroup"><h3 class="corpauthor">The Hypersonic SQL Group</h3><div class="author"><h3 class="author"><span class="firstname">Fred</span> <span class="surname">Toussi</span></h3><div class="affiliation"><span class="orgname">HSQLDB Development Group<br></span></div><tt class="email">&lt;<a href="mailto:ft@cluedup.com">ft@cluedup.com</a>&gt;</tt></div><div class="author"><h3 class="author"><span class="firstname">Peter</span> <span class="surname">Hudson</span></h3><div class="affiliation"><span class="orgname">HSQLDB Development Group<br></span></div></div><div class="author"><h3 class="author"><span class="firstname">Joe</span> <span class="surname">Maher</span></h3><div class="affiliation"><span class="orgname">HSQLDB Development Group<br></span></div><tt class="email">&lt;<a href="mailto:jrmaher@ameritech.net">jrmaher@ameritech.net</a>&gt;</tt></div><h4 class="editedby">Edited by</h4><h3 class="editor"><span class="firstname">Blaine</span> <span class="surname">Simpson</span></h3></div></div><div><p class="pubdate">$Date: 2005/06/30 22:39:11 $</p></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="#N11B4F">Notational Conventions Used in this Chapter</a></span></dt><dt><span class="section"><a href="#N11B6C">SQL Commands</a></span></dt><dd><dl><dt><span class="section"><a href="#alter_index-section">ALTER INDEX</a></span></dt><dt><span class="section"><a href="#alter_sequence-section">ALTER SEQUENCE</a></span></dt><dt><span class="section"><a href="#N11B8B">ALTER SCHEMA</a></span></dt><dt><span class="section"><a href="#alter_table-section">ALTER TABLE</a></span></dt><dt><span class="section"><a href="#alter_user-section">ALTER USER</a></span></dt><dt><span class="section"><a href="#call-section">CALL</a></span></dt><dt><span class="section"><a href="#checkpoint-section">CHECKPOINT</a></span></dt><dt><span class="section"><a href="#commit-section">COMMIT</a></span></dt><dt><span class="section"><a href="#connect-section">CONNECT</a></span></dt><dt><span class="section"><a href="#create_alias-section">CREATE ALIAS</a></span></dt><dt><span class="section"><a href="#create_index-section">CREATE INDEX</a></span></dt><dt><span class="section"><a href="#create_role-section">CREATE ROLE</a></span></dt><dt><span class="section"><a href="#N11CA5">CREATE SCHEMA</a></span></dt><dt><span class="section"><a href="#create_sequence-section">CREATE SEQUENCE</a></span></dt><dt><span class="section"><a href="#create_table-section">CREATE TABLE</a></span></dt><dt><span class="section"><a href="#create_trigger-section">CREATE TRIGGER</a></span></dt><dt><span class="section"><a href="#create_user-section">CREATE USER</a></span></dt><dt><span class="section"><a href="#create_view-section">CREATE VIEW</a></span></dt><dt><span class="section"><a href="#delete-section">DELETE</a></span></dt><dt><span class="section"><a href="#disconnect-section">DISCONNECT</a></span></dt><dt><span class="section"><a href="#drop_index-section">DROP INDEX</a></span></dt><dt><span class="section"><a href="#N11E14">DROP ROLE</a></span></dt><dt><span class="section"><a href="#drop_sequence-section">DROP SEQUENCE</a></span></dt><dt><span class="section"><a href="#N11E2E">DROP SCHEMA</a></span></dt><dt><span class="section"><a href="#drop_table-section">DROP TABLE</a></span></dt><dt><span class="section"><a href="#drop_trigger-section">DROP TRIGGER</a></span></dt><dt><span class="section"><a href="#drop_user-section">DROP USER</a></span></dt><dt><span class="section"><a href="#drop_view-section">DROP VIEW</a></span></dt><dt><span class="section"><a href="#explain-section">EXPLAIN PLAN</a></span></dt><dt><span class="section"><a href="#grant-section">GRANT</a></span></dt><dt><span class="section"><a href="#insert-section">INSERT</a></span></dt><dt><span class="section"><a href="#revoke-section">REVOKE</a></span></dt><dt><span class="section"><a href="#rollback-section">ROLLBACK</a></span></dt><dt><span class="section"><a href="#savepoint-section">SAVEPOINT</a></span></dt><dt><span class="section"><a href="#script-section">SCRIPT</a></span></dt><dt><span class="section"><a href="#select-section">SELECT</a></span></dt><dt><span class="section"><a href="#set_autocommit-section">SET AUTOCOMMIT</a></span></dt><dt><span class="section"><a href="#collation-section">SET DATABASE COLLATION</a></span></dt><dt><span class="section"><a href="#N11FBD">SET CHECKPOINT DEFRAG</a></span></dt><dt><span class="section"><a href="#set_ignorecase-section">SET IGNORECASE</a></span></dt><dt><span class="section"><a href="#set_logsize-section">SET LOGSIZE</a></span></dt><dt><span class="section"><a href="#set_password-section">SET PASSWORD</a></span></dt><dt><span class="section"><a href="#set_property-section">SET PROPERTY</a></span></dt><dt><span class="section"><a href="#set_refint-section">SET REFERENTIAL INTEGRITY</a></span></dt><dt><span class="section"><a href="#N1201E">SET SCHEMA</a></span></dt><dt><span class="section"><a href="#set_scriptformat-section">SET SCRIPTFORMAT</a></span></dt><dt><span class="section"><a href="#set_table_index-section">SET TABLE INDEX</a></span></dt><dt><span class="section"><a href="#set_table_readonly-section">SET TABLE READONLY</a></span></dt><dt><span class="section"><a href="#set_table_source-section">SET TABLE SOURCE</a></span></dt><dt><span class="section"><a href="#set_write_delay-section">SET WRITE DELAY</a></span></dt><dt><span class="section"><a href="#shutdown-section">SHUTDOWN</a></span></dt><dt><span class="section"><a href="#update-section">UPDATE</a></span></dt></dl></dd><dt><span class="section"><a href="#datatypes-section">Data Types</a></span></dt><dt><span class="section"><a href="#N12234">SQL Comments</a></span></dt><dt><span class="section"><a href="#stored-section">Stored Procedures / Functions</a></span></dt><dt><span class="section"><a href="#N12265">Built-in Functions and Stored Procedures</a></span></dt><dt><span class="section"><a href="#expression-section">SQL Expression</a></span></dt></dl></div><p>HSQLDB version 1.8.0 supports the SQL statements and syntax described

  in this chapter.</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N11B4F"></a>Notational Conventions Used in this Chapter</h2></div></div><div></div></div><p><tt class="literal">[A]</tt> means A is optional.</p><p><tt class="literal">{ B | C }</tt> means either B or C must be

    characters '(' and ')' used in statements.</p><p>UPPERCASE words are keywords</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N11B6C"></a>SQL Commands</h2></div></div><div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="alter_index-section"></a>ALTER INDEX^{[<a href="#ftn.posthyper" name="posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">ALTER INDEX &lt;indexname&gt; RENAME TO &lt;newname&gt;;</pre><p>Index names can be changed so long as they do not conflict with

      other user-defined or sytem-defined names.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="alter_sequence-section"></a>ALTER SEQUENCE^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">ALTER SEQUENCE &lt;sequencename&gt; RESTART WITH &lt;value&gt;;</pre><p>Resets the next value to be returned from the sequence.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11B8B"></a>ALTER SCHEMA^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">ALTER SCHEMA &lt;schemaname&gt; RENAME TO &lt;newname&gt;;</pre><p>Renames schema as specified. All objects of the schema will

      hereafter be accessible only with the new schema name.</p><p>Requires Administrative privileges.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="alter_table-section"></a>ALTER TABLE^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ADD [COLUMN] &lt;columnname&gt; <a href="#datatypes-section" title="Data Types">Datatype</a> [(columnSize[,precision])]

    [{DEFAULT &lt;defaultValue&gt; |

        converted.</li></ul></div><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ALTER COLUMN &lt;columnname&gt; RESTART WITH &lt;new sequence vale&gt;</pre><p>This form is used exclusively for IDENTITY columns and changes the

      next automatic value for the identity sequence.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ADD [CONSTRAINT &lt;constraintname&gt;] CHECK (&lt;search condition&gt;);</pre><p>Adds a check constraint to the table. In the current version, a

      existing rows are unique or include a null value.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ADD [CONSTRAINT &lt;constraintname&gt;] PRIMARY KEY (&lt;column list&gt;);</pre><p>Adds a primary key constraint to the table, using the same

      definition.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ADD [CONSTRAINT &lt;constraintname&gt;] FOREIGN KEY (&lt;column list&gt;) REFERENCES &lt;exptablename&gt; (&lt;column list&gt;)

      quoted. Use "" for an empty password.</p><p>Only an administrator can do this.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="call-section"></a>CALL</h3></div></div><div></div></div><pre class="programlisting">CALL <a href="#expression-section" title="SQL Expression">Expression</a>;</pre><p>Any expression can be called like a stored procedure, including,

      <a href="#revoke-section" title="REVOKE">REVOKE</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create_alias-section"></a>CREATE ALIAS</h3></div></div><div></div></div><pre class="programlisting">CREATE ALIAS &lt;function&gt; FOR &lt;javaFunction&gt;;</pre><p>Creates an alias for a Java function. The function must be

      accessible from the JVM in which the database runs. Example:</p><div class="informalexample"><pre class="programlisting">    CREATE ALIAS ABS FOR "java.lang.Math.abs";</pre></div><p>See also: <a href="#call-section" title="CALL">CALL</a>,

      privileges.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11CA5"></a>CREATE SCHEMA^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">CREATE SCHEMA &lt;schemaname&gt; AUTHORIZATION DBA [&lt;createStatement&gt; [&lt;grantStatement&lt;] [...];</pre><p>Creates the named schema.</p><p>Optional (nested) CREATE and GRANT statements can be given only

    CREATE TABLE AB(A INTEGER, ...)

    CREATE TABLE CD(C CHAHR, ...)

    CREATE VIEW VI AS SELECT ...

    GRANT SELECT TO PUBLIC ON AB

    GRANT SELECT TO JOE ON CD;

</pre></div></p><p>Requires Administrative privileges.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create_sequence-section"></a>CREATE SEQUENCE^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">CREATE SEQUENCE &lt;sequencename&gt; [AS {INTEGER | BIGINT}] [START WITH &lt;startvalue&gt;] [INCREMENT BY &lt;incrementvalue&gt;];</pre><p>Creates a sequence. The default type is INTEGER. The default start

        <div class="informalexample"><pre class="programlisting">SELECT [...,] NEXT VALUE FOR &lt;sequencename&gt; [, ...] FROM &lt;tablename&gt;</pre></div>

( &lt;columnDefinition&gt; [, ...] [, &lt;constraintDefinition&gt;...] ) [ON COMMIT {DELETE | PRESERVE} ROWS];</pre><p>Creates a tables in memory (default) or on disk and only cached in

      allowed.</p><div class="variablelist"><p class="title"><b>Components of a CREATE TABLE command</b></p><dl><dt><a name="columnDef-entry"></a><span class="term"><a name="columnDef-title"></a>columnDefinition</span></dt><dd><pre class="programlisting">    columnname <a href="#datatypes-section" title="Data Types">Datatype</a> [(columnSize[,precision])] [{DEFAULT &lt;defaultValue&gt; |

    GENERATED BY DEFAULT AS IDENTITY (START WITH &lt;n&gt;[, INCREMENT BY &lt;m&gt;])}] |

            (where Id is the identity column):</p><div class="informalexample"><pre class="programlisting">    INSERT INTO Test (Id, Name) VALUES (NULL,'Test');

    CALL IDENTITY();</pre></div></dd><dt><span class="term">constraintDefinition</span></dt><dd><pre class="programlisting">    [CONSTRAINT &lt;name&gt;]

    FOREIGN KEY ( &lt;column&gt; [,&lt;column&gt;...] ) REFERENCES &lt;refTable&gt; ( &lt;column&gt; [,&lt;column&gt;...]) 

    [ON {DELETE | UPDATE} {CASCADE | SET DEFAULT | SET NULL}]^{[<a href="#ftn.posthyper">1</a>]} |

            ROWS.</p></dd></dl></div><p>See also: <a href="#drop_table-section" title="DROP TABLE">DROP TABLE</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create_trigger-section"></a>CREATE TRIGGER^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">CREATE TRIGGER &lt;name&gt; {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON &lt;table&gt; [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL &lt;TriggerClass&gt;;</pre><p>TriggerClass is an application-supplied class that implements the

      Object</tt>. Note that the number of elements in the row arrays is

      larger than the number of columns by one or two elements. Never modify

      the last elements of the array, which are not part of the actual

      change a password afterwards using a <a href="#alter_user-section" title="ALTER USER">ALTER USER^{[<a href="#ftn.posthyper">1</a>]}</a> command.</p><p>Only an administrator can do this.</p><p>See also: <a href="#connect-section" title="CONNECT">CONNECT</a>, <a href="#grant-section" title="GRANT">GRANT</a>, <a href="#revoke-section" title="REVOKE">REVOKE</a>. <a href="#alter_user-section" title="ALTER USER">ALTER USER^{[<a href="#ftn.posthyper">1</a>]}</a>,</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create_view-section"></a>CREATE VIEW^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">CREATE VIEW &lt;viewname&gt;[(&lt;viewcolumn&gt;,..) AS SELECT ... FROM ... [WHERE Expression];

      the index backs a UNIQUE of FOREIGN KEY constraint.</p><p>See also: <a href="#create_index-section" title="CREATE INDEX">CREATE INDEX</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11E14"></a>DROP ROLE^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">DROP ROLE &lt;rolename&gt;;</pre><p>Removes all members from specified role, then removes the role

      database objects.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11E2E"></a>DROP SCHEMA^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">DROP SCHEMA &lt;schemaname&gt; [RESTRICT | CASCADE];</pre><p>Removes the specified schema from the database. The RESTRICT

      grantee for a table or for a class. To allow a user to call a function

      from a class, the right ALL must be used. Examples: <div class="informalexample"><pre class="programlisting">    GRANT SELECT ON Test TO GUEST;

    GRANT ALL ON CLASS "java.lang.String" TO PUBLIC;</pre></div></p><p>The second form of the GRANT command gives the specified

      &lt;grantee&gt; membership in the specified role.</p><p>Require Administrative privileges.</p><p>See also: <a href="#revoke-section" title="REVOKE">REVOKE</a>, <a href="#create_user-section" title="CREATE USER">CREATE USER</a>, <a href="#create_role-section" title="CREATE ROLE">CREATE ROLE^{[<a href="#ftn.posthyper">1</a>]}</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="insert-section"></a>INSERT</h3></div></div><div></div></div><pre class="programlisting">INSERT INTO table [( column [,...] )]

[{ UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT] } | INTERSECT [DISTINCT] } selectStatement]

      database.</p><div class="variablelist"><p class="title"><b>Components of a SELECT command</b></p><dl><dt><span class="term">tableList</span></dt><dd><pre class="programlisting">    table [{CROSS | INNER | LEFT OUTER | RIGHT OUTER} JOIN table ON Expression] [, ...]</pre></dd><dt><span class="term">table</span></dt><dd><pre class="programlisting">    { (selectStatement) [AS] label | tableName}</pre></dd><dt><span class="term">selectExpression</span></dt><dd><pre class="programlisting">    { Expression | COUNT(*) | {COUNT | MIN | MAX | SUM | AVG | SOME | EVERY | VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP} ([ALL | DISTINCT]^{[<a href="#ftn.posthyper">1</a>]}] Expression) } [[AS] label]</pre><p>If DISTINCT is specified, only one instance of several

            the join.</p></dd><dt><span class="term">orderExpression</span></dt><dd><pre class="programlisting">    { columnNr | columnAlias | selectExpression } [ASC | DESC]</pre></dd><dt><span class="term">LIMIT n m</span></dt><dd><p>Creates the result set for the SELECT statement first and

      the set of collations in the source for org.hsqldb.Collation. Once this

      command has been issued, the database can be opened in any JVM and will

      retain its collation.</p><p>Once this command has been issued, the database can be opened in

      any JVM and will retain its collation.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11FBD"></a>SET CHECKPOINT DEFRAG^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">SET CHECKPOINT DEFRAG &lt;size&gt;;</pre><p>The parameter <tt class="literal">size</tt> is the megabytes of

      some columns case sensitive and some not, even in the same table.</p><p>Only an administrator may do this.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_logsize-section"></a>SET LOGSIZE</h3></div></div><div></div></div><pre class="programlisting">SET LOGSIZE &lt;size&gt;;</pre><p>Sets the maximum size in MB of the .log file. Default is 200 MB.

        using SQL queries and take appropriate actions.</p></div><p>Only an administrator may do this.</p><p>See also: <a href="#create_table-section" title="CREATE TABLE">CREATE TABLE</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N1201E"></a>SET SCHEMA^{[<a href="#ftn.posthyper">1</a>]}</h3></div></div><div></div></div><pre class="programlisting">SET SCHEMA &lt;schemaname&gt;;</pre><p>Sets the current JDBC session's schema. The sole purpose for the

      consists of:</p><pre class="programlisting">    &lt;file and options&gt;::= &lt;doublequote&gt; &lt;filepath&gt; [&lt;semicolon&gt; &lt;option&gt;...] &lt;doublequote&gt;</pre><p>Example:</p><div class="informalexample"><pre class="programlisting">    SET TABLE mytable SOURCE "myfile;fs=|;vs=.;lvs=~"</pre></div><div class="variablelist"><p class="title"><b>Supported Properties</b></p><dl><dt><span class="term">quoted = { true | false }</span></dt><dd><p>default is true. If false, treats double quotes as normal

            startup.</p></dd></dl></div><p>Only an administrator may use the SHUTDOWN command.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="update-section"></a>UPDATE</h3></div></div><div></div></div><pre class="programlisting">UPDATE table SET column = Expression [, ...] [WHERE Expression];</pre><p>Modifies data of a table in the database.</p><p>See also: <a href="#select-section" title="SELECT">SELECT^{[<a href="#ftn.posthyper">1</a>]}</a>, <a href="#insert-section" title="INSERT">INSERT</a>, <a href="#delete-section" title="DELETE">DELETE</a>.</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="datatypes-section"></a>Data Types</h2></div></div><div></div></div><div class="table"><a name="N12142"></a><p class="title"><b>Table&nbsp;9.1.&nbsp;Data Types. The types on the same line are equivalent.</b></p><table summary="Data Types. The types on the same line are equivalent." width="100%" border="1"><colgroup><col><col><col></colgroup><thead><tr><th align="left">Name</th><th align="left">Range</th><th align="left">Java Type</th></tr></thead><tbody><tr><td align="left">INTEGER | INT</td><td align="left">as Java type</td><td align="left"><tt class="literal">int</tt> |