HyperSQL User Guide Next
   Preface

HyperSQL User Guide

HyperSQL Database Engine (HSQLDB) 2.2

Edited by

The HSQL Development Group

Edited by

Blaine Simpson

The HSQL Development Group

Edited by

Fred Toussi

The HSQL Development Group

Copyright 2002-2011 The HSQL Development Group. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. You are not allowed to distribute or display this document on the web in an altered form.

2012-08-06 00:10:58+0100

Table of Contents

Preface
Available formats for this document
1. Running and Using HyperSQL
The HSQLDB Jar
Running Database Access Tools
A HyperSQL Database
In-Process Access to Database Catalogs
Server Modes
HyperSQL HSQL Server
HyperSQL HTTP Server
HyperSQL HTTP Servlet
Connecting to a Database Server
Security Considerations
Using Multiple Databases
Accessing the Data
Closing the Database
Creating a New Database
2. SQL Language
Standards Support
SQL Data and Tables
Temporary Tables
Persistent Tables
Lob Data
Short Guide to Data Types
Data Types and Operations
Numeric Types
Boolean Type
Character String Types
Binary String Types
Bit String Types
Storage and Handling of Java Objects
Type Length, Precision and Scale
Datetime types
Interval Types
Arrays
Array Definition
Array Reference
Array Operations
Indexes and Query Speed
Query Processing and Optimisation
Indexes and Conditions
Indexes and Operations
Indexes and ORDER BY, OFFSET and LIMIT
3. Sessions and Transactions
Overview
Session Attributes and Variables
Session Attributes
Session Variables
Session Tables
Transactions and Concurrency Control
Two Phase Locking
Two Phase Locking with Snapshot Isolation
Lock Contention in 2PL
Locks in SQL Routines and Triggers
MVCC
Choosing the Transaction Model
Schema and Database Change
Simultaneous Access to Tables
Viewing Sessions
Session and Transaction Control Statements
4. Schemas and Database Objects
Overview
Schemas and Schema Objects
Names and References
Character Sets
Collations
Distinct Types
Domains
Number Sequences
Tables
Views
Constraints
Assertions
Triggers
Routines
Indexes
Statements for Schema Definition and Manipulation
Common Elements and Statements
Renaming Objects
Commenting Objects
Schema Creation
Table Creation
Table Manipulation
View Creation and Manipulation
Domain Creation and Manipulation
Trigger Creation
Routine Creation
Sequence Creation
SQL Procedure Statement
Other Schema Object Creation
The Information Schema
Predefined Character Sets, Collations and Domains
Views in INFORMATION SCHEMA
Visibility of Information
Name Information
Data Type Information
Product Information
Operations Information
SQL Standard Views
5. Text Tables
Overview
The Implementation
Definition of Tables
Scope and Reassignment
Null Values in Columns of Text Tables
Configuration
Disconnecting Text Tables
Text File Usage
Text File Global Properties
Transactions
6. Access Control
Overview
Authorizations and Access Control
Built-In Roles and Users
Listing Users and Roles
Access Rights
Statements for Authorization and Access Control
7. Data Access and Change
Overview
Cursors And Result Sets
Columns and Rows
Navigation
Updatability
Sensitivity
Holdability
Autocommit
JDBC Overview
JDBC Parameters
JDBC and Data Change Statements
JDBC Callable Statement
JDBC Returned Values
Cursor Declaration
Syntax Elements
Literals
References, etc.
Value Expression
Predicates
Aggregate Functions
Other Syntax Elements
Data Access Statements
Select Statement
Table
Subquery
Query Specification
Table Expression
Table Primary
Joined Table
Selection
Projection
Computed Columns
Naming
Grouping Operations
Aggregation
Set Operations
With Clause and Recursive Queries
Query Expression
Ordering
Slicing
Data Change Statements
Delete Statement
Truncate Statement
Insert Statement
Update Statement
Merge Statement
Diagnostics and State
8. SQL-Invoked Routines
Routine Definition
Routine Characteristics
SQL Language Routines (PSM)
Advantages and Disadvantages
Routine Statements
Compound Statement
Table Variables
Variables
Cursors
Handlers
Assignment Statement
Select Statement : Single Row
Formal Parameters
Iterated Statements
Iterated FOR Statement
Conditional Statements
Return Statement
Control Statements
Raising Exceptions
Routine Polymorphism
Returning Data From Procedures
Recursive Routines
Java Language Routines (SQL/JRT)
Polymorphism
Java Language Procedures
Java Static Methods
Legacy Support
Securing Access to Classes
User Defined Aggregate Functions
Definition of Aggregate Functions
SQL PSM Aggregate Functions
Java Aggregate Functions
9. Triggers
Overview
BEFORE Triggers
AFTER Triggers
INSTEAD OF Triggers
Trigger Properties
Trigger Event
Granularity
Trigger Action Time
References to Rows
Trigger Condition
Trigger Action in SQL
Trigger Action in Java
Trigger Creation
10. Built In Functions
Overview
String and Binary String Functions
Numeric Functions
Date Time and Interval Functions
Functions to Report the Time Zone.
Functions to Report the Current Datetime
Functions to Extract an Element of a Datetime
Functions for Datetime Arithmetic
Functions to Convert or Format a Datetime
Array Functions
General Functions
System Functions
11. System Management
Mode of Operation and Tables
Mode of Operation
Tables
Large Objects
Deployment context
Readonly Databases
ACID, Persistence and Reliability
Atomicity, Consistency, Isolation, Durability
Backing Up Database Catalogs
Making Online Backups
Making Offline Backups
Examining Backups
Restoring a Backup
Encrypted Databases
Creating and Accessing an Encrypted Database
Speed Considerations
Security Considerations
Monitoring Database Operations
External Statement Level Monitoring
Internal Statement Level Monitoring
Internal Event Monitoring
Log4J and JDK logging
Server Operation Monitoring
Database Security
Security Defaults
Authentication Control
Compatibility with Other RDBMS
PostgreSQL Compatibility
MySQL Compatibility
Firebird Compatibility
Apache Derby Compatibility
Oracle Compatibility
DB2 Compatibility
MS SQLServer and Sybase Compatibility
Statements
System Operations
Database Settings
SQL Conformance Settings
Cache, Persistence and Files Settings
Authentication Settings
12. Properties
Connection URL
Variables In Connection URL
Connection properties
Database Properties in Connection URL and Properties
SQL Conformance Properties
Database Operations Properties
Database File and Memory Properties
Crypt Properties
System Properties
13. HyperSQL Network Listeners (Servers)
Listeners
HyperSQL Server
HyperSQL HTTP Server
HyperSQL HTTP Servlet
Server and Web Server Properties
Starting a Server from your Application
Allowing a Connection to Open or Create a Database
Specifying Database Properties at Server Start
TLS Encryption
Requirements
Encrypting your JDBC connection
JSSE
Making a Private-key Keystore
Automatic Server or WebServer startup on UNIX
Network Access Control
14. HyperSQL on UNIX
Purpose
Installation
Setting up Database Catalog and Listener
Accessing your Database
Create additional Accounts
Shutdown
Running Hsqldb as a System Daemon
Portability of hsqldb init script
Init script Setup Procedure
Troubleshooting the Init Script
Upgrading
15. Deployment Guide
Memory and Disk Use
Table Memory Allocation
Result Set Memory Allocation
Temporary Memory Use During Operations
Data Cache Memory Allocation
Object Pool Memory Allocation
Lob Memory Usage
Disk Space
Managing Database Connections
Tweaking the Mode of Operation
Application Development and Testing
Embedded Databases in Desktop Applications
Embedded Databases in Server Applications
Mixed Mode : Embedding a HyperSQL Server (Listener)
Using HyperSQL Without Logging Data Change
Bulk Inserts, Updates and Deletes
Using NIO File Access
Server Databases
Upgrading Databases
Upgrading From Older Versions
Manual Changes to the *.script File
Backward Compatibility Issues
HyperSQL Dependency Settings for Applications
What version to Pull
Using the HyperSQL Snapshot Repository
Range Versioning
A. Lists of Keywords
List of SQL Standard Keywords
List of SQL Keywords Disallowed as HyperSQL Identifiers
Special Function Keywords
B. Building HyperSQL Jars
Purpose
Building with Gradle
Invoking a Gradle Build Graphically
Invoking a Gradle Build from the Command Line
Using Gradle
Building with Ant
Obtaining Ant
Building Hsqldb with Ant
Building for Older JDKs
Building with IDE Compilers
Hsqldb CodeSwitcher
Building Documentation
C. HyperSQL with OpenOffice
HyperSQL with OpenOffice
Using OpenOffice / LibreOffice as a Database Tool
Converting .odb files to use with HyperSQL Server
D. HyperSQL File Links
SQL Index
General Index

List of Tables

1. Available formats of this document
10.1. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements
12.1. Memory Database URL
12.2. File Database URL
12.3. Resource Database URL
12.4. Server Database URL
12.5. User and Password
12.6. Column Names in JDBC ResultSet
12.7. Creating New Database
12.8. Automatic Shutdown
12.9. Validity Check Property
12.10. SQL Keyword Use as Identifier
12.11. SQL Keyword Starting with the Underscore or Containing Dollar Characters
12.12. Reference to Columns Names
12.13. String Size Declaration
12.14. Type Enforcement in Comparison and Assignment
12.15. Foreign Key Triggered Data Change
12.16. Use of LOB for LONGVAR Types
12.17. Concatenation with NULL
12.18. NULL in Multi-Column UNIQUE Constraints
12.19. Truncation or Rounding in Type Conversion
12.20. Decimal Scale of Division and AVG Values
12.21. Support for NaN values
12.22. Sort order of NULL values
12.23. String comparison with padding
12.24. DB2 Style Syntax
12.25. MSSQL Style Syntax
12.26. MySQL Style Syntax
12.27. Oracle Style Syntax
12.28. PostgreSQL Style Syntax
12.29. Default Table Type
12.30. Transaction Control Mode
12.31. Default Isolation Level for Sessions
12.32. Transaction Rollback in Deadlock
12.33. Time Zone and Interval Types
12.34. Opening Database as Read Only
12.35. Opening Database Without Modifying the Files
12.36. Huge database files and tables
12.37. Temporary Result Rows in Memory
12.38. Event Logging
12.39. SQL Logging
12.40. Rows Cached In Memory
12.41. Rows Cached In Memory
12.42. Size of Rows Cached in Memory
12.43. Size Scale of Disk Table Storage
12.44. Size Scale of LOB Storage
12.45. Internal Backup of Database Files
12.46. Use of Lock File
12.47. Logging Data Change Statements
12.48. Automatic Checkpoint Frequency
12.49. Automatic Defrag at Checkpoint
12.50. Logging Data Change Statements Frequency
12.51. Logging Data Change Statements Frequency
12.52. Use of NIO for Disk Table Storage
12.53. Use of NIO for Disk Table Storage
12.54. Recovery Log Processing
12.55. Default Properties for TEXT Tables
12.56. Forcing Garbage Collection
12.57. Crypt Property For LOBs
12.58. Cipher Key for Encrypted Database
12.59. Crypt Provider Encrypted Database
12.60. Cipher Specification for Encrypted Database
12.61. Logging Framework
12.62. Text Tables
12.63. Java Functions
13.1. common server and webserver properties
13.2. server properties
13.3. webserver properties

List of Examples

1.1. Java code to connect to the local hsql Server
1.2. Java code to connect to the local http Server
1.3. Java code to connect to the local secure SSL hsql and http Servers
1.4. specifying a connection property to shutdown the database when the last connection is closed
1.5. specifying a connection property to disallow creating a new database
3.1. User-defined Session Variables
3.2. User-defined Temporary Session Tables
3.3. Setting Transaction Characteristics
3.4. Locking Tables
3.5. Rollback
3.6. Setting Session Characteristics
3.7. Setting Session Authorization
3.8. Setting Session Time Zone
4.1. inserting the next sequence value into a table row
4.2. numbering returned rows of a SELECT in sequential order
4.3. using the last value of a sequence
4.4. Column values which satisfy a 2-column UNIQUE constraint
11.1. Using CACHED tables for the LOB schema
11.2. Offline Backup Example
11.3. Listing a Backup with DbBackup
11.4. Restoring a Backup with DbBackup
11.5. Finding foreign key rows with no parents after a bulk import
13.1. Exporting certificate from the server's keystore
13.2. Adding a certificate to the client keystore
13.3. Specifying your own trust store to a JDBC client
13.4. Getting a pem-style private key into a JKS keystore
13.5. Validating and Testing an ACL file
14.1. example sqltool.rc stanza
15.1. Using CACHED tables for the LOB schema
15.2. MainInvoker Example
15.3. HyperSQL Snapshot Repository Definition
15.4. Sample Snapshot Ivy Dependency
15.5. Sample Snapshot Maven Dependency
15.6. Sample Snapshot Gradle Dependency
15.7. Sample Snapshot ivy.xml loaded by Ivyxml plugin
15.8. Sample Snapshot Groovy Dependency, using Grape
15.9. Sample Range Ivy Dependency
15.10. Sample Range Maven Dependency
15.11. Sample Range Gradle Dependency
15.12. Sample Range ivy.xml loaded by Ivyxml plugin
15.13. Sample Range Groovy Dependency, using Grape
B.1. Buiding the standard Hsqldb jar file with Ant
B.2. Example source code before CodeSwitcher is run
B.3. CodeSwitcher command line invocation
B.4. Source code after CodeSwitcher processing

$Revision: 4864 $

   Next
   Preface