| HyperSQL User Guide |
|  | Preface

# HyperSQL User Guide

## HyperSQL Database Engine 2.7.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-2024 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.

2024-10-25

List of Tables

1. Available formats of this document
2.1. List of SQL types
4.1. TO_CHAR (number) format elements
4.2. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements
14.1. Memory Database URL
14.2. File Database URL
14.3. Resource Database URL
14.4. Server Database URL
14.5. User and Password
14.6. Closing old ResultSet when Statement is reused
14.7. Column Names in JDBC ResultSet
14.8. In-memory LOBs from JDBC ResultSet
14.9. Empty batch in JDBC PreparedStatement
14.10. Automatic Shutdown
14.11. OpenOffice and Libre Office usage
14.12. Validity Check Property
14.13. Creating New Database Check Property
14.14. Execution of Multiple SQL Statements etc.
14.15. SQL Keyword Use as Identifier
14.16. SQL Keyword Starting with the Underscore or Containing Dollar Characters
14.17. Reference to Columns Names
14.18. String Size Declaration
14.19. Truncation of trailing spaces from string
14.20. Type Enforcement in Comparison and Assignment
14.21. Foreign Key Triggered Data Change
14.22. Use of LOB for LONGVAR Types
14.23. Type of string literals in CASE WHEN
14.24. Concatenation with NULL
14.25. NULL in Multi-Column UNIQUE Constraints
14.26. Truncation or Rounding in Type Conversion
14.27. Decimal Scale of Division and AVG Values
14.28. Support for NaN values
14.29. Sort order of NULL values
14.30. Sort order of NULL values with DESC
14.31. String Comparison with Padding
14.32. Default Locale Language Collation
14.33. Case-Insensitive Varchar columns
14.34. Lowercase column identifiers in ResultSet
14.35. Storage of Live Java Objects
14.36. Names of System Indexes Used for Constraints
14.37. DB2 Style Syntax
14.38. MSSQL Style Syntax
14.39. MySQL Style Syntax
14.40. Oracle Style Syntax
14.41. PostgreSQL Style Syntax
14.42. Maximum Iterations of Recursive Queries
14.43. Default Table Type
14.44. Transaction Control Mode
14.45. Default Isolation Level for Sessions
14.46. Transaction Rollback in Deadlock
14.47. Transaction Rollback on Interrupt
14.48. Interval Types
14.49. Temporary Result Rows in Memory
14.50. Opening Database as Read Only
14.51. Opening Database Without Modifying the Files
14.52. Event Logging
14.53. SQL Logging
14.54. Table Spaces for Cached Tables
14.55. Huge database files and tables
14.56. Use of NIO for Disk Table Storage
14.57. Use of NIO for Disk Table Storage
14.58. Internal Backup of the .data File
14.59. Unused Space Recovery
14.60. Rows Cached In Memory
14.61. Size of Rows Cached in Memory
14.62. Size Scale of Disk Table Storage
14.63. Size Scale of LOB Storage
14.64. Compression of BLOB and CLOB data
14.65. Use of Lock File
14.66. Logging Data Change Statements
14.67. Automatic Checkpoint Frequency
14.68. Automatic Defrag at Checkpoint
14.69. Compression of the .script file
14.70. Logging Data Change Statements Frequency
14.71. Logging Data Change Statements Frequency
14.72. Recovery Log Processing
14.73. Default Properties for TEXT Tables
14.74. Forcing Garbage Collection
14.75. Crypt Property For LOBs
14.76. Cipher Key for Encrypted Database
14.77. Cipher Initialization Vector for Encrypted Database
14.78. Crypt Provider Encrypted Database
14.79. Cipher Specification for Encrypted Database
14.80. Logging Framework
14.81. Text Tables
14.82. Java Functions
15.1. common server and webserver properties
15.2. server properties
15.3. webserver properties
17.1. Settings List

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 hsqls: and https: 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. inserting the next sequence value into a table row
3.2. numbering returned rows of a SELECT in sequential order
3.3. using the last value of a sequence
3.4. Column values which satisfy a 2-column UNIQUE constraint
6.1. User-defined Session Variables
6.2. User-defined Temporary Session Tables
6.3. Setting Transaction Characteristics
6.4. Locking Tables
6.5. Rollback
6.6. Setting Session Characteristics
6.7. Setting Session Authorization
6.8. Setting Session Time Zone
11.1. Using CACHED tables for the LOB schema
11.2. Creating a system-versioned table
11.3. Displaying DbBackup Syntax
11.4. Offline Backup Example
11.5. Listing a Backup with DbBackup
11.6. Restoring a Backup with DbBackup
11.7. SQL Log Example
11.8. Finding foreign key rows with no parents after a bulk import
12.1. Using CACHED tables for the LOB schema
12.2. MainInvoker Example
12.3. Sample Range Ivy Dependency
12.4. Sample Range Maven Dependency
12.5. Sample Range Gradle Dependency
12.6. Sample Range ivy.xml loaded by Ivyxml plugin
12.7. Sample Range Groovy Dependency, using Grape
15.1. Exporting certificate from the server's keystore
15.2. Adding a certificate to the client keystore
15.3. Specifying your own trust store to a JDBC client
15.4. Getting a pem-style private key into a JKS keystore
15.5. Validating and Testing an ACL file
16.1. example sqltool.rc stanza
C.1. Buiding the standard HSQLDB jar file with Ant
C.2. Example source code before CodeSwitcher is run
C.3. CodeSwitcher command line invocation
C.4. Source code after CodeSwitcher processing

$Revision: 6787 $

|  | Preface