HyperSQL User Guide

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

Listener / 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

Basic 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

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

MVCC

Choosing the Transaction Model

Schema and Database Change

Simultaneous Access to Tables

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 and 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

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

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 Returned Values

Syntax Elements

Literals

References, etc.

Value Expression

Predicates

Other Syntax Elements

Data Access Statements

Table

Query Specification

Table Expression

Table Primary

Joined Table

Selection

Projection

Computed Columns

Naming

Grouping Operations

Aggregation

Set Operations

Query Expression

Ordering

Slicing

Data Change Statements

Delete Statement

Truncate Statement

Insert Statement

Update Statement

Merge Statement

8. SQL-Invoked Routines

SQL Language Routines (PSM)

Routine Statements

Compound Statement

Variables

Handlers

Assignment Statement

Select Statement : Single Row

Formal Parameters

Iterated Statements

Conditional Statements

Return Statement

Control Statements

Routine Polymorphism

Returning Data From Routines

Java Language Routines (SQL/JRT)

Polymorphism

Java Language Procedures

Legacy Support

SQL Language Aggregate Functions

Definition of Aggregate Functions

SQL PSM Aggregate Functions

Java Aggregate Functions

Routine Definition

Routine Characteristics

9. Triggers

Overview

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

Array Functions

General Functions

System Functions

11. System Management and Deployment Issues

Mode of Operation and Tables

Mode of Operation

Tables

Large Objects

Deployment context

Readonly Databases

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

Embedding a Database Listener

Using HyperSQL Without Logging

Server Databases

Upgrading Databases

Upgrading From Older Versions

Manual Changes to the *.script File

Backward Compatibility Issues

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

Statement Level Monitoring

Internal Event Monitoring

Server Operation Monitoring

Statements

12. Properties

Connections

Connection properties

Database Properties in Connection URL and Properties

13. HyperSQL Network Listeners

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 a Database

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

A. Lists of Keywords

List of SQL Standard Keywords

List of SQL Keywords Disallowed as HyperSQL Identifiers

B. Building HyperSQL Jars

Purpose

Building with Ant

Obtaining Ant

Building Hsqldb with Ant

Building for Older JDKs

Building with IDE's

Hsqldb CodeSwitcher

Building documentation

C. HyperSQL with OpenOffice.org

HyperSQL with OpenOffice.org

Using OpenOffice.org as a Database Tool

Converting .odb files to use with HyperSQL Server

D. HyperSQL File Links

SQL Index

General Index