File: ch09.html

package info (click to toggle)
hsqldb 1.8.0.7-1etch1
links: PTS
area: main
in suites: etch
size: 11,824 kB
ctags: 11,226
sloc: java: 71,953; xml: 10,832; sql: 1,038; sh: 875; makefile: 58
file content (685 lines) | stat: -rw-r--r-- 106,774 bytes
parent folder | download | duplicates (2)
<html><head><META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>Chapter&nbsp;9.&nbsp;SQL Syntax</title><link href="guide.css" rel="stylesheet" type="text/css"><meta content="DocBook XSL Stylesheets V1.65.1" name="generator"><meta name="keywords" content="Hsqldb, Syntax, SQL"><meta name="keywords" content="Hsqldb, Hypersonic, Database, JDBC, Java"><link rel="home" href="index.html" title="Hsqldb User Guide"><link rel="up" href="index.html" title="Hsqldb User Guide"><link rel="previous" href="ch08.html" title="Chapter&nbsp;8.&nbsp;SqlTool"><link rel="next" href="apa.html" title="Appendix&nbsp;A.&nbsp;Building HSQLDB"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table summary="Navigation header" width="100%"><tr><th align="center" colspan="3">Chapter&nbsp;9.&nbsp;SQL Syntax</th></tr><tr><td align="left" width="20%"><a accesskey="p" href="ch08.html"><img src="navicons/prev.gif" alt="Prev"></a>&nbsp;</td><th align="center" width="60%">&nbsp;</th><td align="right" width="20%">&nbsp;<a accesskey="n" href="apa.html"><img src="navicons/next.gif" alt="Next"></a></td></tr></table><hr></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/11/06 22:13:26 $</p></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="ch09.html#N11BFC">Notational Conventions Used in this Chapter</a></span></dt><dt><span class="section"><a href="ch09.html#N11C19">SQL Commands</a></span></dt><dd><dl><dt><span class="section"><a href="ch09.html#alter_index-section">ALTER INDEX</a></span></dt><dt><span class="section"><a href="ch09.html#alter_sequence-section">ALTER SEQUENCE</a></span></dt><dt><span class="section"><a href="ch09.html#N11C38">ALTER SCHEMA</a></span></dt><dt><span class="section"><a href="ch09.html#alter_table-section">ALTER TABLE</a></span></dt><dt><span class="section"><a href="ch09.html#alter_user-section">ALTER USER</a></span></dt><dt><span class="section"><a href="ch09.html#call-section">CALL</a></span></dt><dt><span class="section"><a href="ch09.html#checkpoint-section">CHECKPOINT</a></span></dt><dt><span class="section"><a href="ch09.html#commit-section">COMMIT</a></span></dt><dt><span class="section"><a href="ch09.html#connect-section">CONNECT</a></span></dt><dt><span class="section"><a href="ch09.html#create_alias-section">CREATE ALIAS</a></span></dt><dt><span class="section"><a href="ch09.html#create_index-section">CREATE INDEX</a></span></dt><dt><span class="section"><a href="ch09.html#create_role-section">CREATE ROLE</a></span></dt><dt><span class="section"><a href="ch09.html#N11D63">CREATE SCHEMA</a></span></dt><dt><span class="section"><a href="ch09.html#create_sequence-section">CREATE SEQUENCE</a></span></dt><dt><span class="section"><a href="ch09.html#create_table-section">CREATE TABLE</a></span></dt><dt><span class="section"><a href="ch09.html#create_trigger-section">CREATE TRIGGER</a></span></dt><dt><span class="section"><a href="ch09.html#create_user-section">CREATE USER</a></span></dt><dt><span class="section"><a href="ch09.html#create_view-section">CREATE VIEW</a></span></dt><dt><span class="section"><a href="ch09.html#delete-section">DELETE</a></span></dt><dt><span class="section"><a href="ch09.html#disconnect-section">DISCONNECT</a></span></dt><dt><span class="section"><a href="ch09.html#drop_index-section">DROP INDEX</a></span></dt><dt><span class="section"><a href="ch09.html#N11ED7">DROP ROLE</a></span></dt><dt><span class="section"><a href="ch09.html#drop_sequence-section">DROP SEQUENCE</a></span></dt><dt><span class="section"><a href="ch09.html#N11EF1">DROP SCHEMA</a></span></dt><dt><span class="section"><a href="ch09.html#drop_table-section">DROP TABLE</a></span></dt><dt><span class="section"><a href="ch09.html#drop_trigger-section">DROP TRIGGER</a></span></dt><dt><span class="section"><a href="ch09.html#drop_user-section">DROP USER</a></span></dt><dt><span class="section"><a href="ch09.html#drop_view-section">DROP VIEW</a></span></dt><dt><span class="section"><a href="ch09.html#explain-section">EXPLAIN PLAN</a></span></dt><dt><span class="section"><a href="ch09.html#grant-section">GRANT</a></span></dt><dt><span class="section"><a href="ch09.html#insert-section">INSERT</a></span></dt><dt><span class="section"><a href="ch09.html#revoke-section">REVOKE</a></span></dt><dt><span class="section"><a href="ch09.html#rollback-section">ROLLBACK</a></span></dt><dt><span class="section"><a href="ch09.html#savepoint-section">SAVEPOINT</a></span></dt><dt><span class="section"><a href="ch09.html#script-section">SCRIPT</a></span></dt><dt><span class="section"><a href="ch09.html#select-section">SELECT</a></span></dt><dt><span class="section"><a href="ch09.html#set_autocommit-section">SET AUTOCOMMIT</a></span></dt><dt><span class="section"><a href="ch09.html#collation-section">SET DATABASE COLLATION</a></span></dt><dt><span class="section"><a href="ch09.html#N1208B">SET CHECKPOINT DEFRAG</a></span></dt><dt><span class="section"><a href="ch09.html#set_ignorecase-section">SET IGNORECASE</a></span></dt><dt><span class="section"><a href="ch09.html#set_initialschema-section">SET INITIAL SCHEMA </a></span></dt><dt><span class="section"><a href="ch09.html#set_logsize-section">SET LOGSIZE</a></span></dt><dt><span class="section"><a href="ch09.html#set_password-section">SET PASSWORD</a></span></dt><dt><span class="section"><a href="ch09.html#set_property-section">SET PROPERTY</a></span></dt><dt><span class="section"><a href="ch09.html#set_refint-section">SET REFERENTIAL INTEGRITY</a></span></dt><dt><span class="section"><a href="ch09.html#set_schema-section">SET SCHEMA</a></span></dt><dt><span class="section"><a href="ch09.html#set_scriptformat-section">SET SCRIPTFORMAT</a></span></dt><dt><span class="section"><a href="ch09.html#set_table_index-section">SET TABLE INDEX</a></span></dt><dt><span class="section"><a href="ch09.html#set_table_readonly-section">SET TABLE READONLY</a></span></dt><dt><span class="section"><a href="ch09.html#set_table_source-section">SET TABLE SOURCE</a></span></dt><dt><span class="section"><a href="ch09.html#set_write_delay-section">SET WRITE DELAY</a></span></dt><dt><span class="section"><a href="ch09.html#shutdown-section">SHUTDOWN</a></span></dt><dt><span class="section"><a href="ch09.html#update-section">UPDATE</a></span></dt></dl></dd><dt><span class="section"><a href="ch09.html#schemanaming-section">Schema object naming</a></span></dt><dt><span class="section"><a href="ch09.html#datatypes-section">Data Types</a></span></dt><dt><span class="section"><a href="ch09.html#N12364">SQL Comments</a></span></dt><dt><span class="section"><a href="ch09.html#stored-section">Stored Procedures / Functions</a></span></dt><dt><span class="section"><a href="ch09.html#N12395">Built-in Functions and Stored Procedures</a></span></dt><dt><span class="section"><a href="ch09.html#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="N11BFC"></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
    used.</p><p><tt class="literal">[{ B | C }]</tt> means either B or C may optionally be
    used, or nothing at all.</p><p><tt class="literal">(</tt> and <tt class="literal">)</tt> are the actual
    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="N11C19"></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<sup>[<a href="#ftn.posthyper" name="posthyper">1</a>]</sup></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<sup>[<a href="#ftn.posthyper">1</a>]</sup></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="N11C38"></a>ALTER SCHEMA<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">ALTER SCHEMA &lt;schemaname&gt; RENAME TO &lt;newname&gt;;</pre><p>Renames the 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<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ADD [COLUMN] &lt;columnname&gt; <a href="ch09.html#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;])}] |
    [[NOT] NULL] [IDENTITY] [PRIMARY KEY]
    [BEFORE &lt;existingcolumn&gt;];</pre><p>Adds the column to the end of the column list. The optional BEFORE
      &lt;existingcolumn&gt; can be used to specify the name of an existing
      column so that the new column is inserted in a position just before the
      &lt;existingcolumn&gt;.</p><p>It accepts a <a href="ch09.html#columnDef-entry">columnDefinition</a> as in a CREATE TABLE command. If NOT NULL
      is specified and the table is not empty, then a default value must be
      specified. In all other respects, this command is the equivalent of a
      column definition statement in a CREATE TABLE statement.</p><p>If an SQL view includes a SELECT * FROM &lt;tablename&gt; in its
      select statement, the new column is added to the view. This is a
      non-standard feature which is likely to change in the future.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; DROP [COLUMN] &lt;columnname&gt;;</pre><p>Drops the column from the table. Will drop any single-column
      primary key or unique constraint on the column as well. The command will
      not work if there is any multiple key constraint on the column or the
      column is referenced in a check constraint or a foreign key.</p><p>It will also fail if an SQL view includes the column.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ALTER COLUMN &lt;columnname&gt; RENAME TO &lt;newname&gt; </pre><p>Changes a column name.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ALTER COLUMN &lt;columnname&gt; SET DEFAULT &lt;defaultvalue&gt;};</pre><p>Adds the specified default value to the column. Use NULL to remove
      a default.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ALTER COLUMN &lt;columnname&gt; SET [NOT] NULL</pre><p>Sets or removes a NOT NULL constraint for the column.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ALTER COLUMN &lt;columnDefinition&gt;;</pre><p>This form of ALTER TABLE ALTER COLUMN accepts a <a href="ch09.html#columnDef-entry">columnDefinition</a> as in a CREATE
      TABLE command, with the following restrictions.</p><div class="itemizedlist"><p class="title"><b>Restrictions</b></p><ul type="disc"><li>The column must be already be a PK column to accept an
        IDENTITY definition.</li><li>If the column is already an IDENTITY column and there is no
        IDENTITY definition, the existing IDENTITY attribute is
        removed.</li><li>The default expression will be that of the new definition,
        meaning an existing default can be dropped by ommission, or a new
        default added.</li><li>The NOT NULL attribute will be that of the new definition
        (similar to previous item).</li><li>Depending on the type of change, the table may have to be
        empty for the command to work. It always works when the type of change
        is possible in general and the individual existing values can all be
        converted.</li></ul></div><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ALTER COLUMN &lt;columnname&gt;
    RESTART WITH &lt;new sequence value&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
      check constraint can reference only the row being inserted or
      updated.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; ADD [CONSTRAINT &lt;constraintname&gt;] UNIQUE (&lt;column list&gt;);</pre><p>Adds a unique constraint to the table. This will not work if there
      is already a unique constraint covering exactly the same &lt;column
      list&gt;.</p><p>This will work only if the values of the column list for the
      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
      constraint syntax as when the primary key is specified in a table
      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;)
    [ON {DELETE | UPDATE} {CASCADE | SET DEFAULT | SET NULL}];</pre><p>Adds a foreign key constraint to the table, using the same
      constraint syntax as when the foreign key is specified in a table
      definition.</p><p>This will fail if for each existing row in the referring table, a
      matching row (with equal values for the column list) is not found in the
      referenced tables.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; DROP CONSTRAINT &lt;constraintname&gt;;</pre><p>Drop a named unique, check or foreign key constraint from the
      table.</p><pre class="programlisting">ALTER TABLE &lt;tablename&gt; RENAME TO &lt;newname&gt;;</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="alter_user-section"></a>ALTER USER<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">ALTER USER &lt;username&gt; SET PASSWORD &lt;password&gt;;</pre><p>Changes the password for an existing user. Password must be double
      quoted. Use "" for an empty password.</p><p>DBA's may change users' base default schema name with the comand
      <pre class="programlisting">ALTER USER &lt;username&gt; SET INITIAL SCHEMA &lt;schemaname&gt;;</pre>
      This is the schema which database object names will resolve to for this
      user, unless overridden as explained in <a href="ch09.html#schemanaming-section" title="Schema object naming">Schema object naming</a>. For
      reasons of backwards compatibility, the initial schema value will not be
      persisted across database shutdowns until HSQLDB version 1.8.1. (I.e.,
      INITIAL SCHEMA settings will be lost upon database shutdown with HSQLDB
      versions lower than version 1.8.1).</p><p>Only an administrator may use these commands.</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="ch09.html#expression-section" title="SQL Expression">Expression</a>;</pre><p>Any expression can be called like a stored procedure, including,
      but not only Java stored procedures or functions. This command returns a
      ResultSet with one column and one row (the result) just like a SELECT
      statement with one row and one column.</p><p>See also: <a href="ch09.html#stored-section" title="Stored Procedures / Functions">Stored Procedures / Functions</a>, <a href="ch09.html#expression-section" title="SQL Expression">SQL Expression</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="checkpoint-section"></a>CHECKPOINT</h3></div></div><div></div></div><pre class="programlisting">CHECKPOINT [DEFRAG<sup>[<a href="#ftn.posthyper">1</a>]</sup>];</pre><p>Closes the database files, rewrites the script file, deletes the
      log file and opens the database.</p><p>If DEFRAG is specified, this command also shrinks the .data file
      to its minimal size.</p><p>See also: <a href="ch09.html#shutdown-section" title="SHUTDOWN">SHUTDOWN</a>, <a href="ch09.html#set_logsize-section" title="SET LOGSIZE">SET LOGSIZE</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="commit-section"></a>COMMIT</h3></div></div><div></div></div><pre class="programlisting">COMMIT [WORK];</pre><p>Ends a transaction and makes the changes permanent.</p><p>See also: <a href="ch09.html#rollback-section" title="ROLLBACK">ROLLBACK</a>, <a href="ch09.html#set_autocommit-section" title="SET AUTOCOMMIT">SET AUTOCOMMIT</a>, <a href="ch09.html#set_logsize-section" title="SET LOGSIZE">SET LOGSIZE</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="connect-section"></a>CONNECT</h3></div></div><div></div></div><pre class="programlisting">CONNECT USER &lt;username&gt; PASSWORD &lt;password&gt;;</pre><p>Connects to the database as a different user. Password should be
      double quoted. Use "" for an empty password.</p><p>See also: <a href="ch09.html#grant-section" title="GRANT">GRANT</a>,
      <a href="ch09.html#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 static Java function to be used as a
          <a href="ch09.html#stored-section" title="Stored Procedures / Functions">Stored Procedure</a>.
          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><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
          The CREATE ALIAS command just defines the alias.
          It does not validate existence of the target method or its 
          containing class.
          To validate the alias, use it.
      </p></div><p>See also: <a href="ch09.html#call-section" title="CALL">CALL</a>,
      <a href="ch09.html#stored-section" title="Stored Procedures / Functions">Stored Procedures / Functions</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create_index-section"></a>CREATE INDEX</h3></div></div><div></div></div><pre class="programlisting">CREATE [UNIQUE] INDEX &lt;index&gt; ON &lt;table&gt; (&lt;column&gt; [DESC] [, ...]) [DESC];</pre><p>Creates an index on one or more columns in a table.</p><p>Creating an index on searched columns may improve performance. The
      qualifier DESC can be present for command compatibility with other
      databases but it has no effect. Unique indexes can be defined but this
      is deprecated. Use UNIQUE constraints instead. The name of an index must
      be unique within the whole database.</p><p>See also: <a href="ch09.html#create_table-section" title="CREATE TABLE">CREATE TABLE</a>, <a href="ch09.html#drop_index-section" title="DROP INDEX">DROP INDEX</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create_role-section"></a>CREATE ROLE<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">CREATE ROLE &lt;rolename&gt;;</pre><p>Creates the named role with no members. Requires Administrative
      privileges.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11D63"></a>CREATE SCHEMA<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">CREATE SCHEMA &lt;schemaname&gt; AUTHORIZATION &lt;grantee&gt;
    [&lt;createStatement&gt; [&lt;grantStatement&gt;] [...];</pre><p>Creates the named schema, with ownership of the specified
      <span class="emphasis"><em>authorization</em></span>. The authorization grantee may be a
      database user or a role.</p><p>Optional (nested) CREATE and GRANT statements can be given only
      for new objects in this new schema. Only the last nested statement
      should be terminated with a semicolon, because the first semicolon
      encountered after "CREATE SCHEMA" will end the CREATE SCHEMA command. In
      the example below, a new schema, ACCOUNTS, is created, then two tables
      and a view are added to this schma and some rights on these objects are
      granted.<div class="informalexample"><pre class="programlisting">    CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA
        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>
        Note that this example consists of one CREATE SCHEMA statement which
        is terminated by a semicolon.
    </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<sup>[<a href="#ftn.posthyper">1</a>]</sup></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
      value is 0 and the increment 1. Negative values are not allowed. If a
      sequence goes beyond Integer.MAXVALUE or Long.MAXVALUE, the next result
      is determined by 2's complement arithmetic.</p><p>The next value for a sequence can be included in SELECT, INSERT
      and UPDATE statements as in the following example:</p><p>
        <div class="informalexample"><pre class="programlisting">SELECT [...,] NEXT VALUE FOR &lt;sequencename&gt; [, ...] FROM &lt;tablename&gt;;</pre></div>
      </p><p>In the proposed SQL 200n and in the current version, there is no
      way of retreiving the last returned value of a sequence.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create_table-section"></a>CREATE TABLE</h3></div></div><div></div></div><pre class="programlisting">CREATE [MEMORY | CACHED | [GLOBAL] TEMPORARY | TEMP <sup>[<a href="#ftn.posthyper">1</a>]</sup> | TEXT<sup>[<a href="#ftn.posthyper">1</a>]</sup>] TABLE &lt;name&gt;
    ( &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
      memory. If the database is all-in-memory, both MEMORY and CACHED forms
      of CREATE TABLE return a MEMORY table while the TEXT form is not
      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="ch09.html#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;])}] |
    [[NOT] NULL] [IDENTITY] [PRIMARY KEY]</pre><p>Default values that are allowed are constant values or
            certain SQL datetime functions.</p><div class="itemizedlist"><p class="title"><b>Allowed Default Values in Column Definitions</b></p><ul type="disc"><li><p>For character column, a single-quoted string or NULL.
                The only SQL function that can be used is CURRENT_USER.</p></li><li><p>For datetime columns, a single-quoted DATE, TIME or
                TIMESTAMP value or NULL. Or a datetime SQL function such as
                CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, TODAY, NOW.
                Each function is allowed for a certain datetime type.</p></li><li><p>For BOOLEAN columns, the literals FALSE, TRUE,
                NULL.</p></li><li><p>For numeric columns, any valid number or NULL.</p></li><li><p>For binary columns, any valid hex string or NULL.</p></li></ul></div><p>Only one identity column is allowed in each table. Identity
            columns are autoincrement columns. They must be of INTEGER or
            BIGINT type and are automatically primary key columns (as a
            result, multi-column primary keys are not possible with an
            IDENTITY column present). Using the long SQL syntax the (START
            WITH &lt;n&gt;) clause specifies the first value that will be
            used. The last inserted value into an identity column for a
            connection is available using the function IDENTITY(), for example
            (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;]
    UNIQUE ( &lt;column&gt; [,&lt;column&gt;...] ) |
    PRIMARY KEY ( &lt;column&gt; [,&lt;column&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}]<sup>[<a href="#ftn.posthyper">1</a>]</sup> |
    CHECK(&lt;search condition&gt;)<sup>[<a href="#ftn.posthyper">1</a>]</sup></pre><p>Both ON DELETE and ON UPDATE clauses can be used in a single
            foreign key definition.</p></dd><dt><span class="term">search condition</span></dt><dd><p>A search condition is similar to the set of conditions in a
            WHERE clause. In the current version of HSQLDB, the conditions for
            a CHECK constraint can only reference the current row, meaning
            there should be no SELECT statement. Sample table definitions with
            CHECK constraints are in 
            <tt class="literal">TestSelfCheckConstraints.txt</tt>. This file is in
            the /hsqldb/testrun/hsqldb/ directory of the zip.</p></dd><dt><span class="term">General syntax limitations</span></dt><dd><p>HSQLDB databases are initially created in a legacy mode that
            does not enforce column size and precision. You can set the
            property: <tt class="code">sql.enforce_strict_size=true</tt> to enable this
            feature. When this property has been set, Any supplied column size
            and precision for numeric and character types (CHARACTER and
            VARCHAR) are enforced. Use the command, <tt class="code">SET PROPERTY
            "sql.enforce_strict_size" TRUE</tt> once before defining the
            tables.</p><p>NOT NULL constraints can be part of the column definition
            only. Other constraints cannot be part of the column definition
            and must appear at the end of the column definition list.</p><p>TEMPORARY TABLE contents for each session (connection) are
            emptied by default at each commit or rollback. The optional
            qualifier ON COMMIT PRESERVE ROWS can be used to keep the rows
            while the session is open. The default is ON COMMIT DELETE
            ROWS.</p></dd></dl></div><p>See also: <a href="ch09.html#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<sup>[<a href="#ftn.posthyper">1</a>]</sup></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
      <tt class="classname">org.hsqldb.Trigger</tt> interface e.g.
      "mypackage.TrigClass". It is the fire method of this class that is
      invoked when the trigger event occurs. You should provide this class,
      which can have any name, and ensure that this TriggerClass is present in
      the classpath which you use to start hsqldb.</p><p>Since 1.7.2 the implementation has been changed and enhanced. When
      the 'fire' method is called, it is passed the following
      arguments:</p><div class="informalexample"><pre class="programlisting">    fire (String name, String table, Object row1[], Object row2[])
</pre></div><p>where 'row1' and 'row2' represent the 'before' and 'after' states
      of the row acted on, with each column being a member of the array. The
      mapping of members of the row arrays to database types is specified in
      <a href="ch09.html#datatypes-section" title="Data Types">Data Types</a>. For
      example, BIGINT is represented by a <tt class="filename">java.lang.Long
      Object</tt>. Note that the number of elements in the row arrays
      could be 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
      row.</p><p>If the trigger method wants to access the database, it must
      establish its own JDBC connection. This can cause data inconsistency and
      other problems so it is not recommended. The
      <tt class="literal">jdbc:default:connection:</tt> URL is not currently
      supported.</p><p>Implementation note:</p><p>If QUEUE 0 is specified, the fire method is execued in the same
      thread as the database engine. This allows trigger action to alter the
      data that is about to be stored in the database. Data can be checked or
      modified in BEFORE INSERT / UPDATE + FOR EACH ROW triggers. All table
      constraints are then enforced by the database engine and if there is a
      violation, the action is rejected for the SQL command that initiated the
      INSERT or UPDATE. There is an exception to this rule, that is with
      UPDATE queries, referential integrity and cascading actions resulting
      from ON UPDATE CASCASE / SET NULL / SET DEFAULT are all performed prior
      to the invocation of the trigger method. If an invalid value that breaks
      referential integrity is inserted in the row by the trigger method, this
      action is not checked and results in inconsistent data in the
      table.</p><p>Alternatively, if the trigger is used for external communications
      and not for checking or altering the data, a queue size larger than zero
      can be specified. This is in the interests of not blocking the
      database's main thread as each trigger will run in a thread that will
      wait for its firing event to occur. When this happens, the trigger's
      thread calls TriggerClass.fire. There is a queue of events waiting to be
      run by each trigger thread. This is particularly useful for 'FOR EACH
      ROW' triggers, when a large number of trigger events occur in rapid
      succession, without the trigger thread getting a chance to run. If the
      queue becomes full, subsequent additions to it cause the database engine
      to suspend awaiting space in the queue. Take great care to avoid this
      situation if the trigger action involves accessing the database, as
      deadlock will occur. This can be avoided either by ensuring the QUEUE
      parameter makes a large enough queue, or by using the NOWAIT parameter,
      which causes a new trigger event to overwrite the most recent event in
      the queue. The default queue size is 1024. Note also that the timing of
      trigger method calls is not guaranteed, so applications should implement
      their own synchronization measures if necessary.</p><p>With a non-zero QUEUE parameter, if the trigger methods modifies
      the 'row2' values, these changes may or may not affect the database and
      will almost certainly result in data inconsistency.</p><p>Please refer to the code for <a href="../src/org/hsqldb/Trigger.html" target="_top">
          <tt class="classname">org.hsqldb.sample.Trigger</tt>
        </a> and <a href="../src/org/hsqldb/sample/TriggerSample.html" target="_top">
          <tt class="classname">org.hsqldb.sample.TriggerSample</tt>
        </a> for more information on how to write a trigger class.</p><p>See also: <a href="ch09.html#drop_trigger-section" title="DROP TRIGGER">DROP TRIGGER</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create_user-section"></a>CREATE USER</h3></div></div><div></div></div><pre class="programlisting">CREATE USER &lt;username&gt; PASSWORD &lt;password&gt; [ADMIN];</pre><p>Creates a new user or new administrator in this database. Password
      must be double quoted. Empty password can be made using "". You can
      change a password afterwards using a <a href="ch09.html#alter_user-section" title="ALTER USER">ALTER USER<sup>[<a href="#ftn.posthyper">1</a>]</sup></a> command.</p><p>Only an administrator can do this.</p><p>See also: <a href="ch09.html#connect-section" title="CONNECT">CONNECT</a>, <a href="ch09.html#grant-section" title="GRANT">GRANT</a>, <a href="ch09.html#revoke-section" title="REVOKE">REVOKE</a>. <a href="ch09.html#alter_user-section" title="ALTER USER">ALTER USER<sup>[<a href="#ftn.posthyper">1</a>]</sup></a>,</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create_view-section"></a>CREATE VIEW<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">CREATE VIEW &lt;viewname&gt;[(&lt;viewcolumn&gt;,..) AS SELECT ... FROM ... [WHERE Expression]
[ORDER BY orderExpression [, ...]]
[LIMIT &lt;limit&gt; [OFFSET &lt;offset&gt;]];</pre><p>A view can be thought of as either a virtual table or a stored
      query. The data accessible through a view is not stored in the database
      as a distinct object. What is stored in the database is a SELECT
      statement. The result set of the SELECT statement forms the virtual
      table returned by the view. A user can use this virtual table by
      referencing the view name in SQL statements the same way a table is
      referenced. A view is used to do any or all of these functions:</p><div class="itemizedlist"><ul type="disc"><li><p>Restrict a user to specific rows in a table. For example,
          allow an employee to see only the rows recording his or her work in
          a labor-tracking table.</p></li><li><p>Restrict a user to specific columns. For example, allow
          employees who do not work in payroll to see the name, office, work
          phone, and department columns in an employee table, but do not allow
          them to see any columns with salary information or personal
          information.</p></li><li><p>Join columns from multiple tables so that they look like a
          single table.</p></li><li><p>Aggregate information instead of supplying details. For
          example, present the sum of a column, or the maximum or minimum
          value from a column.</p></li></ul></div><p>Views are created by defining the SELECT statement that retrieves
      the data to be presented by the view. The data tables referenced by the
      SELECT statement are known as the base tables for the view. In this
      example, is a view that selects data from three base tables to present a
      virtual table of commonly needed data:</p><div class="informalexample"><pre class="programlisting">    CREATE VIEW mealsjv AS
      SELECT m.mid mid, m.name name, t.mealtype mt, a.aid aid,
             a.gname + ' ' + a.sname author, m.description description,
             m.asof asof
        FROM meals m, mealtypes t, authors a
       WHERE m.mealtype = t.mealtype
        AND m.aid = a.aid;</pre></div><p>You can then reference mealsjv in statements in the same way you
      would reference a table:</p><div class="informalexample"><pre class="programlisting">    SELECT * FROM mealsjv;</pre></div><p>A view can reference another view. For example, mealsjv presents
      information that is useful for long descriptions that contain
      identifiers, but a short list might be all a web page display needs. A
      view can be built that selects only specific mealsjv columns:</p><div class="informalexample"><pre class="programlisting">    CREATE VIEW mealswebv AS SELECT name, author FROM mealsjv;</pre></div><p>The SELECT statement in a VIEW definition should return columns
      with distinct names. If the names of two columns in the SELECT statement
      are the same, use a column alias to distinguish between them. A list of
      new column names can always be defined for a view.</p><div class="informalexample"><pre class="programlisting">    CREATE VIEW aview (new_name, new_author) AS
      SELECT name, author
      FROM mealsjv</pre></div><p>See also: <a href="ch09.html#expression-section" title="SQL Expression">SQL Expression</a>, <a href="ch09.html#select-section" title="SELECT">SELECT<sup>[<a href="#ftn.posthyper">1</a>]</sup></a>, <a href="ch09.html#drop_view-section" title="DROP VIEW">DROP VIEW<sup>[<a href="#ftn.posthyper">1</a>]</sup></a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="delete-section"></a>DELETE</h3></div></div><div></div></div><pre class="programlisting">DELETE FROM table [WHERE Expression];</pre><p>Removes rows in a table.</p><p>See also: <a href="ch09.html#expression-section" title="SQL Expression">SQL Expression</a>, <a href="ch09.html#insert-section" title="INSERT">INSERT</a>, <a href="ch09.html#select-section" title="SELECT">SELECT<sup>[<a href="#ftn.posthyper">1</a>]</sup></a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="disconnect-section"></a>DISCONNECT</h3></div></div><div></div></div><pre class="programlisting">DISCONNECT;</pre><p>Closes this connection. It is not required to call this command
      when using the JDBC interface: it is called automatically when the
      connection is closed. After disconnecting, it is not possible to execute
      other queries (including CONNECT) with this connection.</p><p>See also: <a href="ch09.html#connect-section" title="CONNECT">CONNECT</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drop_index-section"></a>DROP INDEX</h3></div></div><div></div></div><pre class="programlisting">DROP INDEX index [IF EXISTS];</pre><p>Removes the specified index from the database. Will not work if
      the index backs a UNIQUE of FOREIGN KEY constraint.</p><p>See also: <a href="ch09.html#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="N11ED7"></a>DROP ROLE<sup>[<a href="#ftn.posthyper">1</a>]</sup></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
      itself.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drop_sequence-section"></a>DROP SEQUENCE<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">DROP SEQUENCE &lt;sequencename&gt; [IF EXISTS] [RESTRICT | CASCADE];</pre><p>Removes the specified sequence from the database. When IF EXIST is
      used, the statement returns without an error if the sequence does not
      exist. The RESTRICT option is in effect by default, meaning that DROP
      will fail if any view reference the sequence. Specify the
      <tt class="literal">CASCADE</tt> option to silently drop all dependent
      database objects.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N11EF1"></a>DROP SCHEMA<sup>[<a href="#ftn.posthyper">1</a>]</sup></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
      option is in effect by default, meaning that DROP will fail if any
      objects such as tables or sequences have been defined in the schema.
      Specify the <tt class="literal">CASCADE</tt> option to silently drop all
      database objects in the schema.</p><p>Requires Administrative privileges.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drop_table-section"></a>DROP TABLE</h3></div></div><div></div></div><pre class="programlisting">DROP TABLE &lt;table&gt; [IF EXISTS] [RESTRICT | CASCADE];</pre><p>Removes a table, the data and indexes from the database. When IF
      EXIST is used, the statement returns without an error even if the table
      does not exist.</p><p>The RESTRICT option is in effect by default, meaning that DROP
      will fail if any tables or views refer to this table. Specify the
      <tt class="literal">CASCADE</tt> option to silently drop all dependent views,
      and to drop any foreign key constraint that links this table with other
      tables.</p><p>See also:</p><p><a href="ch09.html#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="drop_trigger-section"></a>DROP TRIGGER</h3></div></div><div></div></div><pre class="programlisting">DROP TRIGGER &lt;trigger&gt;;</pre><p>Removes a trigger from the database.</p><p>See also: <a href="ch09.html#create_trigger-section" title="CREATE TRIGGER">CREATE TRIGGER<sup>[<a href="#ftn.posthyper">1</a>]</sup></a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drop_user-section"></a>DROP USER</h3></div></div><div></div></div><pre class="programlisting">DROP USER &lt;username&gt;;</pre><p>Removes a user from the database.</p><p>Only an administrator do this.</p><p>See also: <a href="ch09.html#create_user-section" title="CREATE USER">CREATE USER</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drop_view-section"></a>DROP VIEW<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">DROP VIEW &lt;viewname&gt; [IF EXISTS] [RESTRICT | CASCADE];</pre><p>Removes a view from the database. When IF EXIST is used, the
      statement returns without an error if the view does not exist. The
      RESTRICT option is in effect by default, meaning that DROP will fail if
      any other view refers to this view. Specify the
      <tt class="literal">CASCADE</tt> option to silently drop all dependent
      views.</p><p>See also: <a href="ch09.html#create_view-section" title="CREATE VIEW">CREATE VIEW<sup>[<a href="#ftn.posthyper">1</a>]</sup></a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="explain-section"></a>EXPLAIN PLAN</h3></div></div><div></div></div><pre class="programlisting">EXPLAIN PLAN FOR { SELECT ... | DELETE ... | INSERT ... | UPDATE ..};</pre><p>EXPLAIN PLAN FOR can be used with any query to get a detailed list
      of the elements in the execution plan.</p><p>This list includes the indexes used for performing the query and
      can be used to optimise the query or to add indexes to tables.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="grant-section"></a>GRANT</h3></div></div><div></div></div><pre class="programlisting">GRANT { SELECT | DELETE | INSERT | UPDATE | ALL } [,...]
ON { table | CLASS "package.class" } TO &lt;grantee&gt;;</pre><pre class="programlisting">GRANT &lt;rolename&gt; [,...] TO &lt;grantee&gt;<sup>[<a href="#ftn.posthyper">1</a>]</sup>;</pre><p>&lt;grantee&gt; is either a user name, a role name, or
      <tt class="literal">PUBLIC</tt>. <tt class="literal">PUBLIC</tt> means <span class="emphasis"><em>all
      users</em></span>.</p><p>The first form of the GRANT command assigns privileges to a
      grantee for a table or for a class. To allow a user to call a 
      <a href="ch09.html#stored-section" title="Stored Procedures / Functions">Store Procedure</a> static
      function, 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.Math.abs" TO PUBLIC;</pre></div></p><div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Warning</h3><p>
          Even though the command is GRANT ALL ON CLASS, you must
          specify a static <span class="emphasis"><em>method name</em></span>.  You are actually
          granting access to a static method, not to a class.
      </p></div><p>The second form of the GRANT command gives the specified
      &lt;grantee&gt; membership in the specified role.</p><p>Requires Administrative privileges.</p><p>See also: <a href="ch09.html#revoke-section" title="REVOKE">REVOKE</a>, <a href="ch09.html#create_user-section" title="CREATE USER">CREATE USER</a>, <a href="ch09.html#create_role-section" title="CREATE ROLE">CREATE ROLE<sup>[<a href="#ftn.posthyper">1</a>]</sup></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 [,...] )]
{ VALUES(<a href="ch09.html#expression-section" title="SQL Expression">Expression</a> [,...]) | <a href="ch09.html#select-section" title="SELECT">SelectStatement</a>};</pre><p>Adds one or more new rows of data into a table.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="revoke-section"></a>REVOKE</h3></div></div><div></div></div><pre class="programlisting">REVOKE { SELECT | DELETE | INSERT | UPDATE | ALL } [,...]
ON { table | CLASS "package.class" } FROM &lt;grantee&gt;;</pre><pre class="programlisting">REVOKE &lt;rolename&gt; [,...] FROM &lt;grantee&gt;<sup>[<a href="#ftn.posthyper">1</a>]</sup>;</pre><p>&lt;grantee&gt; is either a user name, a role name, or
      <tt class="literal">PUBLIC</tt>. <tt class="literal">PUBLIC</tt> means <span class="emphasis"><em>all
      users</em></span>.</p><p>The first form of the REVOKE command withdraws privileges from a
      grantee for a table or for a class.</p><p>The second form of the REVOKE command withdraws membership of the
      specified &lt;grantee&gt; from the specified role.</p><p>Both forms require Administrative privileges.</p><p>See also: <a href="ch09.html#grant-section" title="GRANT">GRANT</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="rollback-section"></a>ROLLBACK</h3></div></div><div></div></div><pre class="programlisting">ROLLBACK [TO SAVEPOINT &lt;savepoint name&gt;<sup>[<a href="#ftn.posthyper">1</a>]</sup> |  WORK}];</pre><p>ROLLBACK used on its own, or with WORK, undoes changes made since
      the last COMMIT or ROLLBACK.</p><p><tt class="literal">ROLLBACK TO SAVEPOINT &lt;savepoint name&gt;</tt>
      undoes the change since the named savepoint. It has no effect if the
      savepoint is not found.</p><p>See also: <a href="ch09.html#commit-section" title="COMMIT">COMMIT</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="savepoint-section"></a>SAVEPOINT<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">SAVEPOINT &lt;savepoint name&gt;;</pre><p>Sets up a SAVEPOINT for use with ROLLBACK TO SAVEPOINT.</p><p>See also: <a href="ch09.html#commit-section" title="COMMIT">COMMIT</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="script-section"></a>SCRIPT</h3></div></div><div></div></div><pre class="programlisting">SCRIPT ['file'];</pre><p>Creates an SQL script describing the database. If the file is not
      specified, a result set containing only the DDL script is returned. If
      the file is specified then this file is saved with the path relative to
      the machine where the database engine is located.</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="select-section"></a>SELECT<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">SELECT [{LIMIT &lt;offset&gt; &lt;limit&gt; | TOP &lt;limit&gt;}<sup>[<a href="#ftn.posthyper">1</a>]</sup>][ALL | DISTINCT]
{ selectExpression | table.* | * } [, ...]
[INTO [CACHED | TEMP  | TEXT]<sup>[<a href="#ftn.posthyper">1</a>]</sup> newTable]
FROM tableList
[WHERE <a href="ch09.html#expression-section" title="SQL Expression">Expression</a>]
[GROUP BY Expression [, ...]]
[HAVING Expression]
[{ UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT] } |
INTERSECT [DISTINCT] } selectStatement]
[ORDER BY orderExpression [, ...]]
[LIMIT &lt;limit&gt; [OFFSET &lt;offset&gt;]];</pre><p>Retrieves information from one or more tables in the
      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]<sup>[<a href="#ftn.posthyper">1</a>]</sup>] Expression) } [[AS] label]</pre><p>If DISTINCT is specified, only one instance of several
            equivalent values is used in the aggregate function. Except
            COUNT(*), all aggregate functions exclude NULL values. The type of
            the returned value for SUM is subject to deterministic widenning
            to ensure lossless results. The returned value type for COUNT is
            INTEGER, for MIN, MAX and AVG it is the same type as the column,
            for SOME and EVERY it is BOOLEAN. For VAR_POP, VAR_SAMP,
            STDDEV_POP and STDDEV_SAMP statistical functions, the type is
            always DOUBLE. These statistical functions do not allow ALL or
            DISTINCT qualifiers.</p><p>If CROSS JOIN is specified no ON expression is allowed for
            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
            then discards the first n rows (OFFSET) and returns the first m
            rows of the remaining result set (LIMIT). Special cases: LIMIT 0 m
            is equivalent to TOP m or FIRST m in other RDBMS's; LIMIT n 0
            discards the first n rows and returns the rest of the result
            set.</p></dd><dt><span class="term">LIMIT m OFFSET n</span></dt><dd><p>This form is used at the end of the SELECT statement. The
            OFFSET term is optional.</p></dd><dt><span class="term">TOP m</span></dt><dd><p>Equivalent to LIMIT 0 m.</p></dd><dt><span class="term">UNION and other set operations</span></dt><dd><p>Multiple SELECT statements joined with UNION, EXCEPT and
            INTERSECT are possible. Each SELECT is then treated as a term, and
            the set operation as an operator in an expression. The expression
            is evaluated from left to right but INTERSECT takes precedence
            over the rest of the operators and is applied first. You can use
            parentheses around any number of SELECT statements to change the
            evaluation order.</p></dd></dl></div><p>See also: <a href="ch09.html#insert-section" title="INSERT">INSERT</a>, <a href="ch09.html#update-section" title="UPDATE">UPDATE</a>, <a href="ch09.html#delete-section" title="DELETE">DELETE</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_autocommit-section"></a>SET AUTOCOMMIT</h3></div></div><div></div></div><pre class="programlisting">SET AUTOCOMMIT { TRUE | FALSE };</pre><p>Switches on or off the connection's auto-commit mode. If switched
      on, then all statements will be committed as individual transactions.
      Otherwise, the statements are grouped into transactions that are
      terminated by either <a href="ch09.html#commit-section" title="COMMIT">COMMIT</a> or
      <a href="ch09.html#rollback-section" title="ROLLBACK">ROLLBACK</a>. By default, new
      connections are in auto-commit mode. This command should not be used
      directly. Use the JDBC equivalent method,
      Connection.setAutoCommit(boolean autocommit).</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="collation-section"></a>SET DATABASE COLLATION<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">SET DATABASE COLLATION &lt;double quoted collation name&gt;;</pre><p>Each database can have its own collation. Sets the collation from
      the set of collations in the source for org.hsqldb.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="N1208B"></a>SET CHECKPOINT DEFRAG<sup>[<a href="#ftn.posthyper">1</a>]</sup></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
      abandoned space in the .data file. When a CHECKPOINT is performed either
      as a result of the .log file reaching the limit set by "SET LOGSIZE
      size", or by the user issuing a CHECKPOINT command, the amount of space
      abandoned during the session is checked and if it is larger than size, a
      CHECKPOINT DEFRAG is performed instead of a checkpoint.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_ignorecase-section"></a>SET IGNORECASE</h3></div></div><div></div></div><pre class="programlisting">SET IGNORECASE { TRUE | FALSE };</pre><p>Disables (ignorecase = true) or enables (ignorecase = false) the
      case sensitivity of text comparison and indexing for new tables. By
      default, character columns in new databases are case sensitive. The
      sensitivity must be switched before creating tables. Existing tables and
      their data are not affected. When switched on, the data type VARCHAR is
      set to <tt class="literal">VARCHAR_IGNORECASE</tt> in new tables.
      Alternatively, you can specify the <tt class="literal">VARCHAR_IGNORECASE</tt>
      type for the definition of individual columns. So it is possible to have
      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_initialschema-section"></a>SET INITIAL SCHEMA <sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><p>Users may change their base default schema name with the comand
      <pre class="programlisting">SET INITIAL SCHEMA &lt;schemaname&gt;;</pre>
      This is the schema which database object names will resolve to for the
      current user, unless overridden as explained in <a href="ch09.html#schemanaming-section" title="Schema object naming">Schema object naming</a>. For
      reasons of backwards compatibility, the initial schema value will not be
      persisted across database shutdowns until HSQLDB version 1.8.1. (I.e.,
      INITIAL SCHEMA settings will be lost upon database shutdown with HSQLDB
      versions lower than version 1.8.1).</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.
      The database will be closed and opened (just like using CHECKPOINT) if
      the .log file gets over this limit, and so the .log file will shrink. 0
      means no limit.</p><p>See also: <a href="ch09.html#checkpoint-section" title="CHECKPOINT">CHECKPOINT</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_password-section"></a>SET PASSWORD</h3></div></div><div></div></div><pre class="programlisting">SET PASSWORD &lt;password&gt;;</pre><p>Changes the password of the currently connected user. Password
      must be double quotedEmpty password can be set using "".</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_property-section"></a>SET PROPERTY<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">SET PROPERTY &lt;double quoted name&gt; &lt;value&gt;;</pre><p>Sets a database property. Properties that can be set using this
      command are either boolean or integral and are listed in the <a href="ch04.html" title="Chapter&nbsp;4.&nbsp;Advanced Topics">Advanced Topics</a> chapter.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_refint-section"></a>SET REFERENTIAL INTEGRITY</h3></div></div><div></div></div><pre class="programlisting">SET REFERENTIAL_INTEGRITY { TRUE | FALSE };</pre><p>This commands enables / disables the referential integrity
      checking (foreign keys). Normally it should be switched on (this is the
      default) but when importing data (and the data is imported in the
      'wrong' order) the checking can be switched off.</p><div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Warning</h3><p>Note that when referential integrity is switched back on, no
        check is made that the changes to the data are consistent with the
        existing referential integrity constraints. You can verify consistency
        using SQL queries and take appropriate actions.</p></div><p>Only an administrator may do this.</p><p>See also: <a href="ch09.html#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="set_schema-section"></a>SET SCHEMA<sup>[<a href="#ftn.posthyper">1</a>]</sup></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
      session schema is to provide a default schema name for schema objects
      that do not have the schema name specified explicitly in the SQL
      command, or by association with another object of known schema. For
      example, if you run <tt class="literal">SELECT * FROM atbl;</tt>, HSQLDB will
      look for the table or view named <tt class="literal">atbl</tt> in the
      session's current schema.</p><p>Session schemas last only for the duration of the current session.
      When a new JDBC session is obtained, the new session will have the
      default schema.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_scriptformat-section"></a>SET SCRIPTFORMAT<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">SET SCRIPTFORMAT {TEXT | BINARY | COMPRESSED};</pre><p>Changes the format of the script file. BINARY and COMPRESSED
      formats are slightly faster and more compact than the default TEXT.
      Recommended only for very large script files.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_table_index-section"></a>SET TABLE INDEX</h3></div></div><div></div></div><pre class="programlisting">SET TABLE tableName INDEX 'index1rootPos index2rootPos ... ';</pre><p>This command is only used internally to store the position of
      index roots in the .data file. It appears only in database script files;
      it should not be used directly.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_table_readonly-section"></a>SET TABLE READONLY<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">SET TABLE &lt;tablename&gt; READONLY {TRUE | FALSE};</pre><p>Sets the table as read only.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set_table_source-section"></a>SET TABLE SOURCE<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">SET TABLE &lt;tablename&gt; SOURCE &lt;file and options&gt; [DESC];</pre><p>For details see the <a href="ch06.html" title="Chapter&nbsp;6.&nbsp;Text Tables">Text Tables</a> chapter.</p><p>This command is used exclusively with TEXT tables to specify which
      file is used for storage of the data. The optional DESC qualifier
      results in the text file indexed from the end and opened as readonly.
      The &lt;file and options&gt; argument is a double quoted string that
      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
            characters</p></dd><dt><span class="term">all_quoted = { true | false }</span></dt><dd><p>default is false. If true, adds double quotes around all
            fields.</p></dd><dt><span class="term">encoding = &lt;encoding name&gt;</span></dt><dd><p>character encoding for text and character fields, for
            example, encoding=UTF-8</p></dd><dt><span class="term">ignore_first = { true | false }</span></dt><dd><p>default is false. If true ignores the first line of the
            file</p></dd><dt><span class="term">cache_scale= &lt;numeric value&gt;</span></dt><dd><p>exponent to calculate rows of the text file in cache.
            Default is 8, equivalent to nearly 800 rows</p></dd><dt><span class="term">cache_size_scale = &lt;numeric value&gt;r</span></dt><dd><p>exponent to calculate average size of each row in cache.
            Default is 8, equivalent to 256 bytes per row.</p></dd><dt><span class="term">fs = &lt;unquoted character&gt;</span></dt><dd><p>field separator</p></dd><dt><span class="term">vs = &lt;unquoted character&gt;</span></dt><dd><p>varchar separator</p></dd><dt><span class="term">lvs = &lt;unquoted character&gt;</span></dt><dd><p>long varchar separator</p></dd></dl></div><div class="variablelist"><p class="title"><b>Special indicators for Hsqldb Text Table separators</b></p><dl><dt><span class="term">\semi</span></dt><dd><p>semicolon</p></dd><dt><span class="term">\quote</span></dt><dd><p>quote</p></dd><dt><span class="term">\space</span></dt><dd><p>space character</p></dd><dt><span class="term">\apos</span></dt><dd><p>apostrophe</p></dd><dt><span class="term">\n</span></dt><dd><p>newline - Used as an end anchor (like $ in regular
            expressions)</p></dd><dt><span class="term">\r</span></dt><dd><p>carriage return</p></dd><dt><span class="term">\t</span></dt><dd><p>tab</p></dd><dt><span class="term">\\</span></dt><dd><p>backslash</p></dd><dt><span class="term">\u####</span></dt><dd><p>a Unicode character specified in hexadecimal</p></dd></dl></div><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_write_delay-section"></a>SET WRITE DELAY<sup>[<a href="#ftn.posthyper">1</a>]</sup></h3></div></div><div></div></div><pre class="programlisting">SET WRITE_DELAY {{ TRUE | FALSE } | &lt;seconds&gt; | &lt;milliseconds&gt; MILLIS};</pre><p>This controls the frequency of file sync for the log file. When
      WRITE_DELAY is set to FALSE or 0, the sync takes place immediately at
      each COMMIT. WRITE_DELAY TRUE performs the sync once every 20 seconds
      (which is the default). A numeric value can be specified instead.</p><p>The purpose of this command is to control the amount of data loss
      in case of a total system crash. A delay of 1 second means at most the
      data written to disk during the last second before the crash is lost.
      All data written prior to this has been synced and should be
      recoverable.</p><p>A write delay of 0 impacts performance in high load situations, as
      the engine has to wait for the file system to catch up.</p><p>To avoid this, you can set write delay down to 10 milliseconds. In
      practice, a write delay of 100 milliseconds provides better than
      99.9999% reliability with an average one system crash per day, or
      99.99999% with an average one system crash per 6 days.</p><p>Each time a SET WRITE_DELAY is issued with any value, a sync is
      immediately performed.</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="shutdown-section"></a>SHUTDOWN</h3></div></div><div></div></div><pre class="programlisting">SHUTDOWN [IMMEDIATELY | COMPACT | SCRIPT<sup>[<a href="#ftn.posthyper">1</a>]</sup>];</pre><p>Closes the current database.</p><div class="variablelist"><p class="title"><b>Varieties of the SHUTDOWN command</b></p><dl><dt><span class="term">SHUTDOWN</span></dt><dd><p>Performs a checkpoint to creates a new .script file that has
            the minimum size and contains the data for memory tables only. It
            then backs up the .data file containing the CACHED TABLE data in
            zipped format to the .backup file and closes the database.</p></dd><dt><span class="term">SHUTDOWN IMMEDIATELY</span></dt><dd><p>Just closes the database files (like when the Java process
            for the database is terminated); this command is used in tests of
            the recovery mechanism. This command should not be used as the
            routine method of closing the database.</p></dd><dt><span class="term">SHUTDOWN COMPACT</span></dt><dd><p>Writes out a new .script file which contains the data for
            all the tables, including CACHED and TEXT tables. It then deletes
            the existing text table files and the .data file before rewriting
            them. After this, it backs up the .data file in the same way as
            normal SHUTDOWN. This operation shrinks all files to the minimum
            size.</p></dd><dt><span class="term">SHUTDOWN SCRIPT</span></dt><dd><p>Similar to SHUTDOWN COMPACT but after writing the script and
            deleting the existing files, it does not rewrite the .data and
            text table files. After SHUTDOWN SCRIPT, only the .script and
            .properties file remain. At the next startup, these files are
            processed and the .data and .backup files are created. This
            command in effect performs part of the job of SHUTDOWN COMPACT,
            leaving the other part to be performed automatically at the next
            startup.</p><p>This command produces a full script of the database which
            can be edited for special purposes prior to the next
            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="ch09.html#select-section" title="SELECT">SELECT<sup>[<a href="#ftn.posthyper">1</a>]</sup></a>, <a href="ch09.html#insert-section" title="INSERT">INSERT</a>, <a href="ch09.html#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="schemanaming-section"></a>Schema object naming</h2></div></div><div></div></div><p><span class="emphasis"><em>Schema</em></span> objects are database objects that are
    always scoped to a specific schema. Each schema has a namespace. There may
    be multiple schema objects of the same name, each in the namespace of a
    different schema. A particular schema object may nearly always be uniquely
    identified using the notation <tt class="literal">schemaname.objectname</tt>.
    All HSQLDB database objects are schema objects, other than the
    following.</p><table summary="Simple list" border="0" class="simplelist"><tr><td>
        <p>Users</p>
      </td></tr><tr><td>
        <p>Roles</p>
      </td></tr><tr><td>
        <p>Store Procedure Java Classes</p>
      </td></tr><tr><td>
        <p>HSQL Aliases</p>
      </td></tr></table><p>Our current Java-class-based Triggers are not fully schema
    objects. However, we are in the process of implementing SQL-conformant
    triggers which will encompass our Java-class-based triggers. When this
    work is completed, HSQLDB triggers will be schema objects.</p><p>Sequences are schema objects with creation and removal permission
    governed by schema authorization (as described hereafter), but GRANT
    and REVOKE command do not work yet for sequences.
    In a future version of HSQLDB, sequence GRANTs and REVOKEs will work 
    similarly to the current GRANT and REVOKE commands for table access.
    </p><p>Most of the time, you do not need to specify the schema for the
    desired schema object, because the implicit schema is usually the only one
    that can be used. For example, when creating an index, the target schema
    will default to that of the table which is the target of the index. Named
    constraints are an extreme example of this. There is never a need to
    specify a schema name for a constraint, since constraint names are only
    specified in a CREATE or ALTER TABLE command, and the schema must be that
    of the target table. If the implicit schema is not determined by a related
    object, then the default comes from your JDBC session's current schema
    setting. The session schema value will be your login user's
    <span class="emphasis"><em>initial schema</em></span>, or whatever you last set it to with
    <a href="ch09.html#set_schema-section" title="SET SCHEMA">SET SCHEMA<sup>[<a href="#ftn.posthyper">1</a>]</sup></a> in your
    <span class="emphasis"><em>current</em></span> JDBC session with the SET SCHEMA command.
    (Your initial schema is "PUBLIC" unless changed with the <a href="ch09.html#alter_user-section" title="ALTER USER"> ALTER USER SET INITIAL SCHEMA</a> or the
    <a href="ch09.html#set_initialschema-section" title="SET INITIAL SCHEMA ">SET INITIAL SCHEMA <sup>[<a href="#ftn.posthyper">1</a>]</sup></a> command).</p><p>In addition to namespace scoping, there are permission aspects to
    the schema of a database object. The <span class="emphasis"><em>authorization</em></span> of
    a schema is a role or user that is basically the
    <span class="emphasis"><em>owner</em></span> of the schema. Only a user with the DBA role
    (an admin user) or the owner of a schema may create objects, or modify the
    DDL of objects, in the namespace of that schema. In this way, a schema
    authorization is said to "own" the objects of that schema. A schema
    authorization/owner can be a role or a user (even a role with no members).
    The two schemas automatically created when a database is initialized are
    both owned by the role <span class="emphasis"><em>DBA</em></span>.</p><p>An important implication to database objects being
    <span class="emphasis"><em>owned</em></span> by the schema owner is, if a non-DBA database
    user is to have permission to create any database object, they must have
    ownership of a schema. To allow a user to create (or modify DDL of)
    objects in their own personal schema, you would create a new schema with
    that user as the authorization. To allow a non-DBA user to share create
    and DDL privileges in some schema, you would create this schema with
    a role as the authorization, then GRANT this role to all of the desired
    users.</p><p>The INFORMATION_SCHEMA is a system defined schema that contains
    the system tables for the database. This schema is read-only. When a
    database is created, a shema named PUBLIC is automatically created as the
    default schma. This schema has the authorization DBA. You can change the
    name of this schema. If all non-system schemas are dropped from a
    database, an empty PUBLIC schema is created again. So each database always
    has at least one non-system schema.</p></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="N12272"></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> |
            <tt class="classname">java.lang.Integer</tt></td></tr><tr><td align="left">DOUBLE [PRECISION] | FLOAT</td><td align="left">as Java type</td><td align="left"><tt class="literal">double</tt> |
            <tt class="classname">java.lang.Double</tt></td></tr><tr><td align="left">VARCHAR</td><td align="left">as Integer.MAXVALUE</td><td align="left"><tt class="classname">java.lang.String</tt></td></tr><tr><td align="left">VARCHAR_IGNORECASE</td><td align="left">as Integer.MAXVALUE</td><td align="left"><tt class="classname">java.lang.String</tt></td></tr><tr><td align="left">CHAR | CHARACTER</td><td align="left">as Integer.MAXVALUE</td><td align="left"><tt class="classname">java.lang.String</tt></td></tr><tr><td align="left">LONGVARCHAR</td><td align="left">as Integer.MAXVALUE</td><td align="left"><tt class="classname">java.lang.String</tt></td></tr><tr><td align="left">DATE</td><td align="left">as Java type</td><td align="left"><tt class="classname">java.sql.Date</tt></td></tr><tr><td align="left">TIME</td><td align="left">as Java type</td><td align="left"><tt class="classname">java.sql.Time</tt></td></tr><tr><td align="left">TIMESTAMP | DATETIME</td><td align="left">as Java type</td><td align="left"><tt class="classname">java.sql.Timestamp</tt></td></tr><tr><td align="left">DECIMAL</td><td align="left">No limit</td><td align="left"><tt class="classname">java.math.BigDecimal</tt></td></tr><tr><td align="left">NUMERIC</td><td align="left">No limit</td><td align="left"><tt class="classname">java.math.BigDecimal</tt></td></tr><tr><td align="left">BOOLEAN | BIT</td><td align="left">as Java type</td><td align="left"><tt class="literal">boolean</tt> |
            <tt class="classname">java.lang.Boolean</tt></td></tr><tr><td align="left">TINYINT</td><td align="left">as Java type</td><td align="left"><tt class="literal">byte</tt> |
            <tt class="classname">java.lang.Byte</tt></td></tr><tr><td align="left">SMALLINT</td><td align="left">as Java type</td><td align="left"><tt class="literal">short</tt> |
            <tt class="classname">java.lang.Short</tt></td></tr><tr><td align="left">BIGINT</td><td align="left">as Java type</td><td align="left"><tt class="filename">long</tt> |
            <tt class="classname">java.lang.Long</tt></td></tr><tr><td align="left">REAL</td><td align="left">as Java type</td><td align="left"><tt class="literal">double</tt> |
            <tt class="classname">java.lang.Double</tt><sup>[<a href="#ftn.posthyper">1</a>]</sup></td></tr><tr><td align="left">BINARY</td><td align="left">as Integer.MAXVALUE</td><td align="left"><tt class="literal">byte[]</tt></td></tr><tr><td align="left">VARBINARY</td><td align="left">as Integer.MAXVALUE</td><td align="left"><tt class="literal">byte[]</tt></td></tr><tr><td align="left">LONGVARBINARY</td><td align="left">as Integer.MAXVALUE</td><td align="left"><tt class="literal">byte[]</tt></td></tr><tr><td align="left">OTHER | OBJECT</td><td align="left">as Integer.MAXVALUE</td><td align="left"><tt class="classname">java.lang.Object</tt></td></tr></tbody></table></div><p>The uppercase names are the data types names defined by the SQL
    standard or commonly used by RDMS's. The data types in quotes are the Java
    class names - if these type names are used then they must be enclosed in
    quotes because in Java names are case-sensitive. Range indicates the
    maximum size of the object that can be stored. Where Integer.MAXVALUE is
    stated, this is a theoretical limit and in practice the maximum size of a
    VARCHAR or BINARY object that can be stored is dictated by the amount of
    memory available. In practice, objects of up to a megabyte in size have
    been successfully used in production databases.</p><p>The recommended Java mapping for the JDBC datatype FLOAT is as a
    Java type "double". Because of the potential confusion it is recommended
    that DOUBLE is used instead of FLOAT.</p><p>VARCHAR_IGNORECASE is a special case-insensitive type of VARCHAR.
    This type is not portable.</p><p>In table definition statements, HSQLDB accepts size, precision and
    scale qualifiers only for certain types: CHAR(s), VARCHAR(s), DOUBLE(p),
    NUMERIC(p), DECIMAL(p,s) and TIMESTAMP(p).</p><p>TIMESTAMP(p) can take only 0 or 6 as precision. Zero indicates no
    subsecond part. Without the precision, the default is 6.</p><p>By default specified precision and scale for the column is simply
    ignored by the engine. Instead, the values for the corresponding Java
    types are always used, which in the case of DECIMAL is an unlimited
    precision and scale. If a size is specified, it is stored in the database
    definition but is not enforeced by default. Once you have created the
    database (before adding data), you can add a database property value to
    enforce the sizes:</p><pre class="programlisting">    SET PROPERTY "sql.enforce_strict_size" true</pre><p>This will enforce the specified size and pad CHAR fields with spaces
    to fill the size. This complies with SQL standards by throwing an
    exception if an attempt is made to insert a string longer than the maximum
    size. It also results in all DECIMAL values conforming to the specified
    precision and scale.</p><p>CHAR and VARCHAR and LONGVARCHAR columns are by default compared and
    sorted according to POSIX standards. See the <a href="ch09.html#collation-section" title="SET DATABASE COLLATION">SET DATABASE COLLATION<sup>[<a href="#ftn.posthyper">1</a>]</sup></a> section above to
    modify this behavior. The property
    <tt class="literal">sql.compare_in_locale</tt> is no longer supported. Instead,
    you can define a collation to be used for all character
    comparisons.</p><p>Columns of the type OTHER or OBJECT contain the serialized form of a
    Java Object in binary format. To insert or update such columns, a binary
    format string (see below under Expression) should be used. Using
    PreparedStatements with JDBC automates this transformation.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N12364"></a>SQL Comments</h2></div></div><div></div></div><table summary="Simple list" border="0" class="simplelist"><tr><td>
        <tt class="literal">-- SQL style line comment</tt>
      </td></tr><tr><td>
        <tt class="literal">// Java style line comment</tt>
      </td></tr><tr><td>
        <tt class="literal">/* C style line comment */</tt>
      </td></tr></table><p>All these types of comments are ignored by the database.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="stored-section"></a>Stored Procedures / Functions</h2></div></div><div></div></div><p>Stored procedures are static Java functions that are called 
    directly from
    the SQL language or using an alias. Calling Java functions (directly or
    using the alias) requires that the Java class can be reached by the
    database (server). The syntax is:</p><pre class="programlisting">    "java.lang.Math.sqrt"(2.0)</pre><p>This means the package must be provided, and the name must be
    written as one word, and inside " because otherwise it is converted to
    uppercase (and not found).</p><p>An alias can be created using the command CREATE ALIAS:</p><pre class="programlisting">    CREATE ALIAS SQRT FOR "java.lang.Math.sqrt";</pre><p>When an alias is defined, then the function can be called
    additionally using this alias:</p><pre class="programlisting">    SELECT SQRT(A) , B FROM MYTABLE;</pre><p>Only static java methods can be used as stored procedures. If,
    within the same class, there are overloaded methods with the same number
    of arguments, then the first one encountered by the program will be used.
    If you want to use Java library methods, it is recommended that you create
    your own class with static methods that act as wrappers around the Java
    library methods. This will allow you to control which method signature is
    used to call each Java library method.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N12395"></a>Built-in Functions and Stored Procedures</h2></div></div><div></div></div><div class="variablelist"><p class="title"><b>Numerical built-in Functions / Stored Procedures</b></p><dl><dt><span class="term">ABS(d)</span></dt><dd><p>returns the absolute value of a double value</p></dd><dt><span class="term">ACOS(d)</span></dt><dd><p>returns the arc cosine of an angle</p></dd><dt><span class="term">ASIN(d)</span></dt><dd><p>returns the arc sine of an angle</p></dd><dt><span class="term">ATAN(d)</span></dt><dd><p>returns the arc tangent of an angle</p></dd><dt><span class="term">ATAN2(a,b)</span></dt><dd><p>returns the tangent of a/b</p></dd><dt><span class="term">BITAND(a,b)</span></dt><dd><p>return a &amp; b</p></dd><dt><span class="term">BITOR(a,b)</span></dt><dd><p>returns a | b</p></dd><dt><span class="term">CEILING(d)</span></dt><dd><p>returns the smallest integer that is not less than d</p></dd><dt><span class="term">COS(d)</span></dt><dd><p>returns the cosine of an angle</p></dd><dt><span class="term">COT(d)</span></dt><dd><p>returns the cotangent of an angle</p></dd><dt><span class="term">DEGREES(d)</span></dt><dd><p>converts radians to degrees</p></dd><dt><span class="term">EXP(d)</span></dt><dd><p>returns e (2.718...) raised to the power of d</p></dd><dt><span class="term">FLOOR(d)</span></dt><dd><p>returns the largest integer that is not greater than d</p></dd><dt><span class="term">LOG(d)</span></dt><dd><p>returns the natural logarithm (base e)</p></dd><dt><span class="term">LOG10(d)</span></dt><dd><p>returns the logarithm (base 10)</p></dd><dt><span class="term">MOD(a,b)</span></dt><dd><p>returns a modulo b</p></dd><dt><span class="term">PI()</span></dt><dd><p>returns pi (3.1415...)</p></dd><dt><span class="term">POWER(a,b)</span></dt><dd><p>returns a raised to the power of b</p></dd><dt><span class="term">RADIANS(d)</span></dt><dd><p>converts degrees to radians</p></dd><dt><span class="term">RAND()</span></dt><dd><p>returns a random number x bigger or equal to 0.0 and smaller
          than 1.0</p></dd><dt><span class="term">ROUND(a,b)</span></dt><dd><p>rounds a to b digits after the decimal point</p></dd><dt><span class="term">ROUNDMAGIC(d)</span></dt><dd><p>solves rounding problems such as 3.11-3.1-0.01</p></dd><dt><span class="term">SIGN(d)</span></dt><dd><p>returns -1 if d is smaller than 0, 0 if d==0 and 1 if d is
          bigger than 0</p></dd><dt><span class="term">SIN(d)</span></dt><dd><p>returns the sine of an angle</p></dd><dt><span class="term">SQRT(d)</span></dt><dd><p>returns the square root</p></dd><dt><span class="term">TAN(A)</span></dt><dd><p>returns the trigonometric tangent of an angle</p></dd><dt><span class="term">TRUNCATE(a,b)</span></dt><dd><p>truncates a to b digits after the decimal point</p></dd></dl></div><div class="variablelist"><p class="title"><b>String built-in Functions / Stored Procedures</b></p><dl><dt><span class="term">ASCII(s)</span></dt><dd><p>returns the ASCII code of the leftmost character of s</p></dd><dt><span class="term">BIT_LENGTH(str)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>returns the length of the string in bits</p></dd><dt><span class="term">CHAR(c)</span></dt><dd><p>returns a character that has the ASCII code c</p></dd><dt><span class="term">CHAR_LENGTH(str)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>returns the length of the string in characters</p></dd><dt><span class="term">CONCAT(str1,str2)</span></dt><dd><p>returns str1 + str2</p></dd><dt><span class="term">DIFFERENCE(s1,s2)</span></dt><dd><p>returns the difference between the sound of s1 and s2</p></dd><dt><span class="term">HEXTORAW(s1)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>returns translated string</p></dd><dt><span class="term">INSERT(s,start,len,s2)</span></dt><dd><p>returns a string where len number of characters beginning at
          start has been replaced by s2</p></dd><dt><span class="term">LCASE(s)</span></dt><dd><p>converts s to lower case</p></dd><dt><span class="term">LEFT(s,count)</span></dt><dd><p>returns the leftmost count of characters of s) - requires
          double quoting - use SUBSTRING() instead</p></dd><dt><span class="term">LENGTH(s)</span></dt><dd><p>returns the number of characters in s</p></dd><dt><span class="term">LOCATE(search,s,[start])</span></dt><dd><p>returns the first index (1=left, 0=not found) where search is
          found in s, starting at start</p></dd><dt><span class="term">LTRIM(s)</span></dt><dd><p>removes all leading blanks in s</p></dd><dt><span class="term">OCTET_LENGTH(str)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>returns the length of the string in bytes (twice the number of
          characters)</p></dd><dt><span class="term">RAWTOHEX(s1)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>returns translated string</p></dd><dt><span class="term">REPEAT(s,count)</span></dt><dd><p>returns s repeated count times</p></dd><dt><span class="term">REPLACE(s,replace,s2)</span></dt><dd><p>replaces all occurrences of replace in s with s2</p></dd><dt><span class="term">RIGHT(s,count)</span></dt><dd><p>returns the rightmost count of characters of s</p></dd><dt><span class="term">RTRIM(s)</span></dt><dd><p>removes all trailing spaces</p></dd><dt><span class="term">SOUNDEX(s)</span></dt><dd><p>returns a four character code representing the sound of
          s</p></dd><dt><span class="term">SPACE(count)</span></dt><dd><p>returns a string consisting of count spaces</p></dd><dt><span class="term">SUBSTR(s,start[,len])</span></dt><dd><p>alias for substring</p></dd><dt><span class="term">SUBSTRING(s,start[,len])</span></dt><dd><p>returns the substring starting at start (1=left) with length
          len</p></dd><dt><span class="term">UCASE(s)</span></dt><dd><p>converts s to upper case</p></dd><dt><span class="term">LOWER(s)</span></dt><dd><p>converts s to lower case</p></dd><dt><span class="term">UPPER(s)</span></dt><dd><p>converts s to upper case</p></dd></dl></div><div class="variablelist"><p class="title"><b>Date/Time built-in Functions / Stored Procedures</b></p><dl><dt><span class="term">CURDATE()</span></dt><dd><p>returns the current date</p></dd><dt><span class="term">CURTIME()</span></dt><dd><p>returns the current time</p></dd><dt><span class="term">DATEDIFF(string, datetime1, datetime2)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>returns the count of units of time elapsed from datetime1 to
          datetime2. The string indicates the unit of time and can have the
          following values 'ms'='millisecond',
          'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month',
          'yy' = 'year'. Both the long and short form of the strings can be
          used.</p></dd><dt><span class="term">DAYNAME(date)</span></dt><dd><p>returns the name of the day</p></dd><dt><span class="term">DAYOFMONTH(date)</span></dt><dd><p>returns the day of the month (1-31)</p></dd><dt><span class="term">DAYOFWEEK(date)</span></dt><dd><p>returns the day of the week (1 means Sunday)</p></dd><dt><span class="term">DAYOFYEAR(date)</span></dt><dd><p>returns the day of the year (1-366)</p></dd><dt><span class="term">HOUR(time)</span></dt><dd><p>return the hour (0-23)</p></dd><dt><span class="term">MINUTE(time)</span></dt><dd><p>returns the minute (0-59)</p></dd><dt><span class="term">MONTH(date)</span></dt><dd><p>returns the month (1-12)</p></dd><dt><span class="term">MONTHNAME(date)</span></dt><dd><p>returns the name of the month</p></dd><dt><span class="term">NOW()</span></dt><dd><p>returns the current date and time as a timestamp) - use
          CURRENT_TIMESTAMP instead</p></dd><dt><span class="term">QUARTER(date)</span></dt><dd><p>returns the quarter (1-4)</p></dd><dt><span class="term">SECOND(time)</span></dt><dd><p>returns the second (0-59)</p></dd><dt><span class="term">WEEK(date)</span></dt><dd><p>returns the week of this year (1-53)</p></dd><dt><span class="term">YEAR(date)</span></dt><dd><p>returns the year</p></dd><dt><span class="term">CURRENT_DATE<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>returns the current date</p></dd><dt><span class="term">CURRENT_TIME<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>returns the current time</p></dd><dt><span class="term">CURRENT_TIMESTAMP<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>returns the current timestamp</p></dd></dl></div><div class="variablelist"><p class="title"><b>System/Connection built-in Functions / Stored Procedures</b></p><dl><dt><span class="term">DATABASE()</span></dt><dd><p>returns the name of the database of this connection</p></dd><dt><span class="term">USER()</span></dt><dd><p>returns the user name of this connection</p></dd><dt><span class="term">CURRENT_USER</span></dt><dd><p>SQL standard function, returns the user name of this
          connection</p></dd><dt><span class="term">IDENTITY()</span></dt><dd><p>returns the last identity values that was inserted by this
          connection</p></dd></dl></div><div class="variablelist"><p class="title"><b>System built-in Functions / Stored Procedures</b></p><dl><dt><span class="term">IFNULL(exp,value)</span></dt><dd><p>if exp is null, value is returned else exp) - use COALESCE()
          instead</p></dd><dt><span class="term">CASEWHEN(exp,v1,v2)</span></dt><dd><p>if exp is true, v1 is returned, else v2) - use CASE WHEN
          instead</p></dd><dt><span class="term">CONVERT(term,type)</span></dt><dd><p>converts exp to another data type</p></dd><dt><span class="term">CAST(term AS type)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>converts exp to another data type</p></dd><dt><span class="term">COALESCE(expr1,expr2,expr3,...)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>if expr1 is not null then it is returned else, expr2 is
          evaluated and if not null it is returned and so on</p></dd><dt><span class="term">NULLIF(v1,v2)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>if v1 equals v2 return null, otherwise v1</p></dd><dt><span class="term">CASE v1 WHEN...<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>
            <tt class="literal">CASE v1 WHEN v2 THEN v3 [ELSE v4] END</tt>
          </p><p>when v1 equals v2 return v3 [otherwise v4 or null if there is
          no ELSE]</p></dd><dt><span class="term">CASE WHEN...<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>
            <tt class="literal">CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4]
            END</tt>
          </p><p>when expr1 is true return v1 [optionally repeated for more
          cases] [otherwise v4 or null if there is no ELSE]</p></dd><dt><span class="term">EXTRACT<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>
            <tt class="literal">EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}
            FROM &lt;datetime value&gt;)</tt>
          </p></dd><dt><span class="term">POSITION (... IN ..)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>
            <tt class="literal">POSITION(&lt;string expression&gt; IN &lt;string
            expression&gt;)</tt>
          </p><p>if the first string is a sub-string of the second one, returns
          the position of the sub-string, counting from one; otherwise
          0</p></dd><dt><span class="term">SUBSTRING(... FROM ... FOR ...)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>
            <tt class="literal">SUBSTRING(&lt;string expression&gt; FROM &lt;numeric
            expression&gt; [FOR &lt;numeric expression&gt;])</tt>
          </p></dd><dt><span class="term">TRIM( LEDING ... FROM ...)<sup>[<a href="#ftn.posthyper">1</a>]</sup></span></dt><dd><p>
            <tt class="literal">TRIM([{LEADING | TRAILING | BOTH}] FROM &lt;string
            expression&gt;)</tt>
          </p></dd></dl></div><p>See also: <a href="ch09.html#call-section" title="CALL">CALL</a>,
    <a href="ch09.html#create_alias-section" title="CREATE ALIAS">CREATE ALIAS</a>.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="expression-section"></a>SQL Expression</h2></div></div><div></div></div><pre class="programlisting">[NOT] condition [{ OR | AND } condition]</pre><div class="variablelist"><p class="title"><b>Components of SQL Expressions</b></p><dl><dt><span class="term">condition</span></dt><dd><pre class="programlisting">   { value [|| value]
  | value { = | &lt; | &lt;= | &gt; | &gt;= | &lt;&gt; | != } value
  | value IS [NOT] NULL
  | EXISTS(selectStatement)
  | value BETWEEN value AND value
  | value [NOT] IN ( {value [, ...] | selectStatement } )
  | value [NOT] LIKE value [ESCAPE] value }</pre></dd><dt><span class="term">value</span></dt><dd><pre class="programlisting">   [+ | -] { term [{ + | - | * | / | || } term]
  | ( condition )
  | function ( [parameter] [,...] )
  | selectStatement giving one value
  | {ANY|ALL} (selectStatement giving single column)</pre></dd><dt><span class="term">term</span></dt><dd><pre class="programlisting">   { 'string' | number | floatingpoint
  | [table.]column | TRUE | FALSE | NULL }</pre></dd><dt><span class="term">sequence</span></dt><dd><pre class="programlisting">   NEXT VALUE FOR &lt;sequence&gt;</pre><p>HSQLDB does not currently enforce the SQL 200n proposed rules
          on where sequence generated values are allowed to be used. In
          general, these values can be used in insert and update statements
          but not in CASE statements, order by clauses, search conditions,
          aggregate functions, or grouped queries.</p></dd><dt><span class="term">string</span></dt><dd><p>Strings in HSQLDB are Unicode strings. A string starts and
          ends with a single ' (singlequote). In a string started with '
          (singlequote) use '' (two singlequotes) to create a '
          (singlequote).</p><p>String contatenation should be performed with the standard SQL
          operator || rather than the non-standard + operator.</p><p>The LIKE keyword uses '%' to match any (including 0) number of
          characters, and '_' to match exactly one character. To search for
          '%' or '_' itself an escape character must also be specified using
          the ESCAPE clause. For example, if the backslash is the escaping
          character, '\%' and '\_' can be used to find the '%' and '_'
          characters themselves. For example, SELECT .... LIKE '\_%' ESCAPE
          '\' will find the strings beginning with an underscore.</p></dd><dt><span class="term">name</span></dt><dd><p>The character set for quoted identifiers (names) in HSQLDB is
          Unicode.</p><p>A unquoted identifier (name) starts with a letter and is
          followed by any number of ASCII letters or digits. When an SQL
          statement is issued, any lowercase characters in unquoted
          identifiers are converted to uppercase. Because of this, unquoted
          names are in fact ALL UPPERCASE when used in SQL statements. An
          important implication of this is the for accessing columns names via
          JDBC DatabaseMetaData: the internal form, which is the ALL UPPERCASE
          must be used if the column name was not quoted in the CREATE TABLE
          statement.</p><p>Quoted identifiers can be used as names (for tables, columns,
          constraints or indexes). Quoted identifiers start and end with "
          (one doublequote). A quoted identifier can contain any Unicode
          character, including space. In a quoted identifier use "" (two
          doublequotes) to create a " (one doublequote). With quoted
          identifiers it is possible to create mixed-case table and column
          names. Example:</p><div class="informalexample"><pre class="programlisting">    CREATE TABLE "Address" ("Nr" INTEGER,"Name" VARCHAR);
    SELECT "Nr", "Name" FROM "Address";</pre></div><p>The equivalent quoted identifier can be used for an unquoted
          identifer by converting the identifier to all uppercase and quoting
          it. For example, if a table name is defined as Address2 (unquoted),
          it can be referred to by its quoted form, "ADDRESS2", as well as
          address2, aDDress2 and ADDRESS2. Quoted identifiers should not be
          confused with SQL strings.</p><p>Quoting can sometimes be used for identifiers, aliases or
          functions when there is an ambiguity. For example:</p><div class="informalexample"><pre class="programlisting">    SELECT COUNT(*) "COUNT" FROM MYTABLE;
    SELECT "LEFT"(COL1, 2) FROM MYTABLE;</pre></div><p>Although HSQLDB 1.8.0 does not force unquoted identifiers to
          contain only ASCII characters, the use of non-ASCII characters in
          these identifiers does not comply with SQL standards. Portability
          between different JRE locales could be an issue when accented
          characters (or extended unicode characters) are used in unquoted
          identifiers. Because native Java methods are used to convert the
          identifier to uppercase, the result may vary not be expected in
          different locales. It is recommended that accented characters are
          used only in quoted identifiers.</p><p>When using JDBC DatabaseMetaData methods that take table,
          column, or index identifiers as arguments, treat the names as they
          are registered in the database. With these methods, unquoted
          identifiers should be used in all-uppercase to get the correct
          result. Quoted identifiers should be used in the exact case
          combination as they were defined - no quote character should be
          included around the name. JDBC methods that return a result set
          containing such identifiers return unquoted identifiers as
          all-uppercase and quoted identifiers in the exact case they are
          registered in the database (a change from 1.6.1 and previous
          versions).</p><p>Please also note that the JDBC getXXX(String columnName)
          methods interpret the columnName as case-independent. This is a
          general feature of JDBC and not specific to HSQLDB.</p></dd><dt><span class="term">password</span></dt><dd><p>Passwords must be double quoted and used consistently.
          Passwords are case insensitive only for backward compatibility. This
          may change in future versions.</p></dd><dt><span class="term">values</span></dt><dd><div class="itemizedlist"><ul type="disc"><li><p>A DATE literal starts and ends with ' (singlequote), the
              format is yyyy-mm-dd (see
              <tt class="classname">java.sql.Date</tt>.</p></li><li><p>A TIME liteal starts and ends with ' (singlequote), the
              format is hh:mm:ss (see
              <tt class="classname">java.sql.Time</tt>).</p></li><li><p>A TIMESTAMP or DATETIME literal starts and ends with '
              (singlequote), the format is yyyy-mm-dd hh:mm:ss.SSSSSSSSS (see
              <tt class="classname">java.sql.Timestamp</tt>).</p></li></ul></div><p>When specifying default values for date / time columns in
          CREATE TABLE statements, or in SELECT,INSERT, and UPDATE statements,
          special SQL functions: NOW, SYSDATE, TODAY, CURRENT_TIMESTAMP,
          CURRENT_TIME and CURRENT_DATE (case independent) can be used. NOW is
          used for TIME and TIMESTAMP columns, TODAY is used for DATE columns.
          The data and time variants CURRENT_* are SQL standard versions and
          should be used in preference to others. Example:</p><pre class="programlisting">    CREATE TABLE T(D DATE DEFAULT CURRENT_DATE);
    CREATE TABLE T1(TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP);</pre><p>Binary data starts and ends with ' (singlequote), the format
          is hexadecimal. '0004ff' for example is 3 bytes, first 0, second 4
          and last 255 (0xff).</p></dd></dl></div><p>Any number of commands may be combined. With combined commands, ';'
    (semicolon) must be used at the end of each command to ensure data
    integrity, despite the fact that the engine may understand the end of
    commands and not return an error when a semicolon is not used.</p></div><div class="footnotes"><br><hr align="left" width="100"><div class="footnote"><p><sup>[<a href="#posthyper" name="ftn.posthyper">1</a>] </sup>These features were added by HSQL Development Group since
          April 2001</p></div></div></div><div class="navfooter"><hr><table summary="Navigation footer" width="100%"><tr><td align="left" width="40%"><a accesskey="p" href="ch08.html"><img src="navicons/prev.gif" alt="Prev"></a>&nbsp;</td><td align="center" width="20%"><a accesskey="u" href="index.html"><img src="navicons/up.gif" alt="Up"></a></td><td align="right" width="40%">&nbsp;<a accesskey="n" href="apa.html"><img src="navicons/next.gif" alt="Next"></a></td></tr><tr><td valign="top" align="left" width="40%">Chapter&nbsp;8.&nbsp;SqlTool&nbsp;</td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="navicons/home.gif" alt="Home"></a></td><td valign="top" align="right" width="40%">&nbsp;Appendix&nbsp;A.&nbsp;Building HSQLDB</td></tr></table></div></body></html>