Hsqldb User Guide
   Next

Hsqldb User Guide

The HSQLDB Development Group

Edited by

Blaine Simpson

Fred Toussi

Copyright 2002-2004 HSQLDB Development Group. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license.

$Date: 2004/07/18 03:48:05 $

Table of Contents

Introduction
Available formats for this document
1. Running and Using Hsqldb
Introduction
Running Tools
Running Hsqldb
Server Modes
Hsqldb Server
Hsqldb Web Server
Hsqldb Servlet
In-Process (Standalone) Mode
Memory-Only Databases
General
Closing the Database
Using Multiple Databases in One JVM
Creating a New Database
Using the Database Engine
Different Types of Tables
Constraints and Indexes
SQL Support
JDBC Support
2. SQL Issues
Purpose
SQL Standard Support
Constraints and Indexes
Primary Key Constraints
Unique Constraints
Unique Indexes
FOREIGN KEYS
Indexes and Query Speed
Where Condition or Join
Subqueries and Joins
Types and Arithmetic Operations
Integral Types
Other Numeric Types
Bit and Boolean Types
Storage and Handling of Java Objects
Type Size, Precision and Scale
Sequences and Identity
Identity Auto-Increment Columns
Sequences
Issues with Transactions
New Features and Changes
3. UNIX Quick Start
Purpose
Installation
Setting up Database Instance and Server
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
4. Advanced Topics
Purpose
Connections
Connection properties
Properties Files
Server and Web Server Properties
Starting a Server from your application
Individual Database Properties
JDBC Stream Based Methods
Managing Database Connections
Memory and Disk Use
Cache Memory Allocation
Upgrading and Managing Databases
Upgrading Using the SCRIPT Command
Manual Changes to the .script File
Backing Up Databases
5. Text Tables
The Implementation
Definition of Tables
Scope and Reassignment
Null Values in Columns of Text Tables
Configuration
Text File Issues
Text File Global Properties
Importing from a Text Table file
6. TLS
Requirements
Encrypting your JDBC connection
Client-Side
Server-Side
JSSE
Making a Private-key Keystore
CA-Signed Cert
Non-CA-Signed Cert
Automatic Server or WebServer startup on UNIX
7. SqlTool
Purpose
The Bare Minimum
Authentication Setup
Interactive
SQL History
Raw mode
Non-Interactive
Giving SQL on the Command Line
SQL Files
Optimally Compatible SQL Files
Comments
Special Commands and Buffer Commands in SQL Files
Automation
Getting Interactive Functionality with SQL Files
Character Encoding
Generating Text or HTML Reports
Procedural Language
Variables
Logical Expressions
Flow Control
Using hsqlsqltool.jar
Unit Testing SqlTool
8. SQL Syntax
Notational Conventions Used in this Chapter
SQL Commands
ALTER INDEX
ALTER SEQUENCE
ALTER TABLE
ALTER USER
CALL
CHECKPOINT
COMMIT
CONNECT
CREATE ALIAS
CREATE INDEX
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE USER
CREATE VIEW
DELETE
DISCONNECT
DROP INDEX
DROP SEQUENCE
DROP TABLE
DROP TRIGGER
DROP USER
DROP VIEW
GRANT
INSERT
REVOKE
ROLLBACK
SAVEPOINT
SCRIPT
SELECT
SET AUTOCOMMIT
SET IGNORECASE
SET LOGSIZE
SET PASSWORD
SET PROPERTY
SET REFERENTIAL INTEGRITY
SET SCRIPTFORMAT
SET TABLE INDEX
SET TABLE READONLY
SET TABLE SOURCE
SET WRITE DELAY
SHUTDOWN
UPDATE
Data Types
SQL Comments
Stored Procedures / Functions
Built-in Functions and Stored Procedures
SQL Expression
A. Building Hsqldb
Purpose
Building with Ant
Obtaining Ant
Building Hsqldb with Ant
Building with DOS Batch Files
Hsqldb CodeSwitcher
B. First JDBC Client Example
C. Hsqldb Database Files and Recovery
States
Procedures
Clean Shutdown
Startup
Repair
D. Running Hsqldb with OpenOffice.org
Introduction
Installing
Setting up OpenOffice.org
On Windows
On Linux
E. Hsqldb Test Utility
F. Database Manager
Brief Introduction
G. Transfer Tool
Brief Introduction

List of Tables

1. Alternate formats of this document
4.1. Hsqldb URL Components
4.2. Connection Properties
4.3. Hsqldb Server Properties Files
4.4. Property File Properties
4.5. Server Property File Properties
4.6. WebServer Property File Properties
4.7. Database-specific Property File Properties
8.1. Data Types

List of Examples

1.1. Java code to connect to the local Server above
2.1. Column values which satisfy a 2-column UNIQUE constraint
2.2. Query comparison
2.3. Numbering returned rows of a SELECT in sequential order
3.1. server.properties fragment
3.2. example sqltool.rc stanza
6.1. Exporting certificate from the server's keystore
6.2. Adding a certificate to the client keystore
6.3. Specifying your own trust store to a JDBC client
6.4. Running an Hsqldb server with TLS encryption
6.5. Getting a pem-style private key into a JKS keystore
7.1. Sample sqltool.rc File
7.2. Defining and using an alias (PL variable)
7.3. Piping input into SqlTool
7.4. Valid comment example
7.5. Invalid comment example
7.6. Simple SQL file using PL
7.7. SQL File showing use of most PL features
A.1. Buiding the standard Hsqldb jar file with Ant
A.2. Example source code before CodeSwitcher is run
A.3. CodeSwitcher command line invocation
A.4. Source code after CodeSwitcher processing
B.1. JDBC Client source code example
   Next
   Introduction