HyperSQL User Guide Next
   Preface

HyperSQL User Guide

HyperSQL Database Engine 2.3.4

Edited by

The HSQL Development Group

Edited by

Blaine Simpson

The HSQL Development Group

Edited by

Fred Toussi

The HSQL Development Group

Copyright 2002-2016 Blaine Simpson, Fred Toussi and 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.

2016-05-15 15:56:47-0400

Table of Contents

Preface
Available formats for this document
1. Running and Using HyperSQL
Introduction
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
Short Guide to Data Types
Data Types and Operations
Numeric Types
Boolean Type
Character String Types
Binary String Types
Bit String Types
Lob Data
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
Synonyms
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
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 and Routines
Warning
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
Database Types
Tables
Large Objects
Deployment context
ACID, Persistence and Reliability
Atomicity, Consistency, Isolation, Durability
System Operations
Backing Up and Restoring Database Catalogs
Making Online Backups
Offline Backup Utility Syntax
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
Statements
System Operations
Database Settings
SQL Conformance Settings
Cache, Persistence and Files Settings
Authentication Settings
12. Compatibility With Other DBMS
Compatibility Overview
PostgreSQL Compatibility
MySQL Compatibility
Firebird Compatibility
Apache Derby Compatibility
Oracle Compatibility
DB2 Compatibility
MS SQLServer and Sybase Compatibility
13. Properties
Connection URL
Variables In Connection URL
Properties for Individual Connections
Properties for the Database
SQL Conformance Properties
Database Operations Properties
Database File and Memory Properties
Crypt Properties
System Properties
14. HyperSQL Network Listeners (Servers)
Listeners
HyperSQL Server
HyperSQL HTTP Server
HyperSQL HTTP Servlet
Server and Web Server Properties
Starting a Server from your Application
Shutting down 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
Making a Private-key Keystore
Automatic Server or WebServer startup on UNIX
Network Access Control
15. 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
16. 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
Application Development and Testing
Tweaking the Mode of Operation
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
13.1. Memory Database URL
13.2. File Database URL
13.3. Resource Database URL
13.4. Server Database URL
13.5. User and Password
13.6. Closing old ResultSet when Statement is reused
13.7. Column Names in JDBC ResultSet
13.8. Empty batch in JDBC PreparedStatement
13.9. Creating New Database
13.10. Automatic Shutdown
13.11. Validity Check Property
13.12. SQL Keyword Use as Identifier
13.13. SQL Keyword Starting with the Underscore or Containing Dollar Characters
13.14. Reference to Columns Names
13.15. String Size Declaration
13.16. Type Enforcement in Comparison and Assignment
13.17. Foreign Key Triggered Data Change
13.18. Use of LOB for LONGVAR Types
13.19. Type of string literals in CASE WHEN
13.20. Concatenation with NULL
13.21. NULL in Multi-Column UNIQUE Constraints
13.22. Truncation or Rounding in Type Conversion
13.23. Decimal Scale of Division and AVG Values
13.24. Support for NaN values
13.25. Sort order of NULL values
13.26. Sort order of NULL values with DESC
13.27. String comparison with padding
13.28. Case Insensitive Varchar columns
13.29. Storage of Live Java Objects
13.30. DB2 Style Syntax
13.31. MSSQL Style Syntax
13.32. MySQL Style Syntax
13.33. Oracle Style Syntax
13.34. PostgreSQL Style Syntax
13.35. Default Table Type
13.36. Transaction Control Mode
13.37. Default Isolation Level for Sessions
13.38. Transaction Rollback in Deadlock
13.39. Time Zone and Interval Types
13.40. Opening Database as Read Only
13.41. Opening Database Without Modifying the Files
13.42. Huge database files and tables
13.43. Event Logging
13.44. SQL Logging
13.45. Temporary Result Rows in Memory
13.46. Rows Cached In Memory
13.47. Rows Cached In Memory
13.48. Size of Rows Cached in Memory
13.49. Size Scale of Disk Table Storage
13.50. Size Scale of LOB Storage
13.51. Compression of BLOB and CLOB data
13.52. Internal Backup of Database Files
13.53. Use of Lock File
13.54. Logging Data Change Statements
13.55. Automatic Checkpoint Frequency
13.56. Automatic Defrag at Checkpoint
13.57. Compression of the .script file
13.58. Logging Data Change Statements Frequency
13.59. Logging Data Change Statements Frequency
13.60. Use of NIO for Disk Table Storage
13.61. Use of NIO for Disk Table Storage
13.62. Recovery Log Processing
13.63. Default Properties for TEXT Tables
13.64. Forcing Garbage Collection
13.65. Crypt Property For LOBs
13.66. Cipher Key for Encrypted Database
13.67. Crypt Provider Encrypted Database
13.68. Cipher Specification for Encrypted Database
13.69. Logging Framework
13.70. Text Tables
13.71. Java Functions
14.1. common server and webserver properties
14.2. server properties
14.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. Displaying DbBackup Syntax
11.3. Offline Backup Example
11.4. Listing a Backup with DbBackup
11.5. Restoring a Backup with DbBackup
11.6. SQL Log Example
11.7. Finding foreign key rows with no parents after a bulk import
14.1. Exporting certificate from the server's keystore
14.2. Adding a certificate to the client keystore
14.3. Specifying your own trust store to a JDBC client
14.4. Getting a pem-style private key into a JKS keystore
14.5. Validating and Testing an ACL file
15.1. example sqltool.rc stanza
16.1. Using CACHED tables for the LOB schema
16.2. MainInvoker Example
16.3. HyperSQL Snapshot Repository Definition
16.4. Sample Snapshot Ivy Dependency
16.5. Sample Snapshot Maven Dependency
16.6. Sample Snapshot Gradle Dependency
16.7. Sample Snapshot ivy.xml loaded by Ivyxml plugin
16.8. Sample Snapshot Groovy Dependency, using Grape
16.9. Sample Range Ivy Dependency
16.10. Sample Range Maven Dependency
16.11. Sample Range Gradle Dependency
16.12. Sample Range ivy.xml loaded by Ivyxml plugin
16.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: 5631 $

   Next
   Preface