| HyperSQL User Guide |
|  | Preface

# HyperSQL User Guide

## HyperSQL Database Engine (HSQLDB) 2.3

#### Edited by ### The HSQL Development Group #### Edited by ### Blaine Simpson The HSQL Development Group #### Edited by ### Fred Toussi The HSQL Development Group Copyright 2002-2013 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. 2013-07-08 15:32:59+0100 |

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. Closing old ResultSet when Statement is reused
12.7. Column Names in JDBC ResultSet
12.8. Creating New Database
12.9. Automatic Shutdown
12.10. Validity Check Property
12.11. SQL Keyword Use as Identifier
12.12. SQL Keyword Starting with the Underscore or Containing Dollar Characters
12.13. Reference to Columns Names
12.14. String Size Declaration
12.15. Type Enforcement in Comparison and Assignment
12.16. Foreign Key Triggered Data Change
12.17. Use of LOB for LONGVAR Types
12.18. Concatenation with NULL
12.19. NULL in Multi-Column UNIQUE Constraints
12.20. Truncation or Rounding in Type Conversion
12.21. Decimal Scale of Division and AVG Values
12.22. Support for NaN values
12.23. Sort order of NULL values
12.24. Sort order of NULL values with DESC
12.25. String comparison with padding
12.26. Case Insensitive Varchar columns
12.27. DB2 Style Syntax
12.28. MSSQL Style Syntax
12.29. MySQL Style Syntax
12.30. Oracle Style Syntax
12.31. PostgreSQL Style Syntax
12.32. Default Table Type
12.33. Transaction Control Mode
12.34. Default Isolation Level for Sessions
12.35. Transaction Rollback in Deadlock
12.36. Time Zone and Interval Types
12.37. Opening Database as Read Only
12.38. Opening Database Without Modifying the Files
12.39. Huge database files and tables
12.40. Event Logging
12.41. SQL Logging
12.42. Temporary Result Rows in Memory
12.43. Rows Cached In Memory
12.44. Rows Cached In Memory
12.45. Size of Rows Cached in Memory
12.46. Size Scale of Disk Table Storage
12.47. Size Scale of LOB Storage
12.48. Compression of BLOB and CLOB data
12.49. Internal Backup of Database Files
12.50. Use of Lock File
12.51. Logging Data Change Statements
12.52. Automatic Checkpoint Frequency
12.53. Automatic Defrag at Checkpoint
12.54. Compression of the .script file
12.55. Logging Data Change Statements Frequency
12.56. Logging Data Change Statements Frequency
12.57. Use of NIO for Disk Table Storage
12.58. Use of NIO for Disk Table Storage
12.59. Recovery Log Processing
12.60. Default Properties for TEXT Tables
12.61. Forcing Garbage Collection
12.62. Crypt Property For LOBs
12.63. Cipher Key for Encrypted Database
12.64. Crypt Provider Encrypted Database
12.65. Cipher Specification for Encrypted Database
12.66. Logging Framework
12.67. Text Tables
12.68. 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. SQL Log Example
11.6. 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: 5206 $

|  | Preface