Chapter 14. Properties

Fred Toussi

The HSQL Development Group

$Revision: 6491 $

Copyright 2002-2022 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.

2022-10-20

Table of Contents

Connection URL
Variables in Connection URL
Connection Properties
Properties for Individual Connections
Properties for the Database
SQL Conformance Properties
Database Operations Properties
Database File and Memory Properties
Crypt Properties
System Properties

Connection URL

The normal method of accessing a HyperSQL catalog is via the JDBC Connection interface. An introduction to different methods of providing database services and accessing them can be found in the Running and Using HyperSQL chapter. Details and examples of how to connect via JDBC are provided in our JavaDoc for JDBCConnection.

A uniform method is used to distinguish between different types of connection. The common driver identifier is jdbc:hsqldb: followed by a protocol identifier (mem: file: res: hsql: http: hsqls: https:) then followed by host and port identifiers in the case of servers, then followed by database identifier. Additional property / value pairs can be appended to the end of the URL, separated with semicolons.

Table 14.1. Memory Database URL

Driver and ProtocolHost and Port ExampleDatabase Example
jdbc:hsqldb:mem:
not available
accounts

Lowercase, single-word identifier creates the in-memory database when the first connection is made. Subsequent use of the same Connection URL connects to the existing DB. Multiple in-memory databases can be created with different database names. For example, first connections to jdbc:hsqldb:mem:db1, jdbc:hsqldb:mem:db2, and jdbc:hsqldb:mem:accounts create three completely separate databases in the Java virtual machine. A second connection to jdbc:hsqldb:mem:accounts connects to the database created by the first connection. A database stays in memory until the SQL command, SHUTDOWN is executed.

The old form for the URL, jdbc:hsqldb:. creates or connects to the same database as the new form for the URL, jdbc:hsqldb:mem:.


Table 14.2. File Database URL

Driver and ProtocolHost and Port ExampleDatabase Example
jdbc:hsqldb:file:
not available
accounts
/opt/db/accounts
C:/data/mydb

The file path specifies the database files. It should consist of a relative or absolute path to the directory containing the database files, followed by a '/' and the database name. In the above examples the first one, jdbc:hsqldb:file:accounts refers to a set of accounts.* files in the directory where the java command for running the application was issued. The second and third examples refer to absolute paths on the host machine: For example, jdbc:hsqldb:file:/opt/db/accounts refers to files named accounts.* in the directory /opt/db which contain the accounts database.


Table 14.3. Resource Database URL

Driver and ProtocolHost and Port ExampleDatabase Example
jdbc:hsqldb:res:
not available
/adirectory/dbname

Database files can be loaded from one of the jars specified as part of the Java command the same way as resource files are accessed in Java programs. The /adirectory above stands for a directory in one of the jars.


Table 14.4. Server Database URL

Driver and ProtocolHost and Port ExampleDatabase Example
jdbc:hsqldb:hsql:
jdbc:hsqldb:hsqls:
jdbc:hsqldb:http:
jdbc:hsqldb:https:
//localhost
//192.0.0.10:9500
//dbserver.somedomain.com
/accounts
/enrolments
/quickdb

The host and port specify the IP address or host name of the server and an optional port number. The database to connect to is specified by an alias. This alias is a lowercase string defined in the server.properties file to refer to an actual database on the file system of the server or a transient, in-memory database on the server. The alias for a database can have a different name to its memory or file name. The lines in server.properties or webserver.properties define the database aliases listed above and accessible to clients to refer to different file and in-memory databases. In the example below, the file: database named mydb is made accessible with the alias enrolments.

server.database.1=file:/opt/db/mydb

server.dbname.1=enrolments

The old form for the server URL, e.g., jdbc:hsqldb:hsql//localhost connects to the same database as the new form for the URL, jdbc:hsqldb:hsql//localhost/ where the alias is a zero length string.


Variables in Connection URL

If the database part of a file: database begins with ~/ or ~\ the tilde character is replaced with the value of the system property "user.home" resulting in the database being created or accessed in this directory, or one of its subdirectories. In the examples below, the database files for mydb and filedb are located in the user's home directory.

 jdbc:hsqldb:file:~/mydb
 jdbc:hsqldb:file:~/filedb;shutdown=true

If the database URL contains a string in the form of ${propname} then the sequence of characters is replaced with the system property with the given name. For example, you can use this in the URL of a database that is used in a web application and define the system property, "propname" in the web application properties. In the example below, the string ${mydbpath} is replaced with the value of the property, mydbpath

 jdbc:hsqldb:file:${mydbpath}

Connection Properties

There are two types of connection properties: properties for individual connections, and properties for the whole database.

The properties for individual connections apply only to the connection that uses them and can be different for different connections. These properties can be used when connecting to in-process and server databases.

The properties for the database apply to the whole database. These properties have an effect only if used for the first connection to an in-process file: or mem: database. For the connection that creates a new database all the user-defined database properties can be specified as URL properties.

A few of the properties for the database can be applied to an existing database when the database is reopened after a shutdown.

When running a server, these properties are not used on the connection URL but can be appended to the database path URL in server.properties or the server command line.

Almost all properties for the database listed in this chapter have corresponding SQL statements which can be used after connecting to the database. The SQL statement for each property is listed here.

Properties for Individual Connections

Each JDBC Connection to a database can specify connection properties. The properties user and password are always required. The following optional properties can also be used.

Connection properties are specified either by using a Java Properties object when establishing the connection via the JDBC method call below. Alternatively the property can be appended to the full Connection URL.

 DriverManager.getConnection (String url, Properties info);

Table 14.5. User and Password

NameDefaultDescription
userSAuser name

Standard property. This property is case sensitive. Example below:

 jdbc:hsqldb:file:enrolments;user=aUserName;password=pass
passwordempty stringpassword for the user

Standard property. This property is case sensitive. Example below:

 jdbc:hsqldb:file:enrolments;user=aUserName;password=3xLVz

For compatibility with other engines, a non-standard form of specifying user and password is also supported. In this form, user name and password appear at the end of the URL string, prefixed respectively with the question mark and the ampersand:

 jdbc:hsqldb:file:enrolments;create=false?user=aUserName&password=3xLVz

Table 14.6. Closing old ResultSet when Statement is reused

NameDefaultDescription
close_resultfalseclosing the old result set when a new ResultSet is created by a Statement

This property is used for compatibility with the JDBC specification. When true (the JDBC specification), a ResultSet that was previously returned by executing a Statement or PreparedStatement is closed as soon as the Statement is executed again.

The default is false as previous versions of HSQLDB did not close old result set. The user application should close old result sets when they are no longer needed and should not rely on auto-closing side effect of executing the Statement.

The default is false. When the property is true, the old ResultSet is closed when a Statement is re-executed. Example below:

 jdbc:hsqldb:hsql://localhost/enrolments;close_result=true

When a ResultSet is used inside a user-defined stored procedure, the default, false, is always used for this property.


Table 14.7. Column Names in JDBC ResultSet

NameDefaultDescription
get_column_nametruecolumn name in ResultSet

This property is used for compatibility with other JDBC driver implementations. When true (the default), ResultSet.getColumnName(int c) returns the underlying column name. This property can be specified differently for different connections to the same database.

The default is true. When the property is false, the above method returns the same value as ResultSet.getColumnLabel(int column) Example below:

 jdbc:hsqldb:hsql://localhost/enrolments;get_column_name=false

When a ResultSet is used inside a user-defined stored procedure, the default, true, is always used for this property.


Table 14.8. In-memory LOBs from JDBC ResultSet

NameDefaultDescription
memory_lobsfalselobs retrieved in full from server by ResultSet

This property can be set to retrieve lobs as fully in-memory objects by the JDBC driver. When false (the default), ResultSet methods for streaming BLOB and CLOB retrieve large lobs in chunks in order to limit memory use on the client. When true, the lob is returned fully as soon as it is streamed. This property can be specified differently for different connections to the same database.

The default is false.

 jdbc:hsqldb:hsql://localhost/enrolments;memory_lobs=true

Table 14.9. Empty batch in JDBC PreparedStatement

NameDefaultDescription
allow_empty_batchfalseexecuteBatch with empty batch

This property is used for compatibility with other JDBC driver implementations such as the PostgreSQL driver. By default PreparedStatement.executeBatch() throws an exception if addBatch() has not been called at all. Setting this property to true ignores the empty batch and returns an empty int[]. This property can be specified differently for different connections to the same database.

The default is false. Example below:

 jdbc:hsqldb:hsql://localhost/enrolments;allow_empty_batch=true

When a PreparedStatement is used inside a user-defined stored procedure, the default, false, is always used for this property.


Table 14.10. Automatic Shutdown

NameDefaultDescription
shutdownfalseshut down the database when the last connection is closed

Has an effect only with mem: and file: databases. If this property is true, when the last connection to a database is closed, the database is automatically shut down. The property takes effect only when the first connection is made to the database. This means the connection that opens the database. It has no effect if used with subsequent connections.

This command has two uses. One is for test suites, where connections to the database are made from one JVM context, immediately followed by another context. The other use is for applications where it is not easy to configure the environment to shutdown the database. Examples reported by users include web application servers, where the closing of the last connection coincides with the web app being shut down. Note the automatic shutdown happens in a background thread and the Connection.close() call returns before the shutdown is complete. This may cause an issue if the shutdown takes a long time to save the data and the user application (or unit test) immediately reopens the database while this is happening. In these contexts, use an explicit SHUTDOWN as an SQL statement.

 jdbc:hsqldb:file:enrolments;shutdown=true

Table 14.11. OpenOffice and Libre Office usage

NameDefaultDescription
default_schemafalseOpenOffice and LibreOffice connections

When HyperSQL is used with OpenOffice.org as an external database, the property "default_schema=true" must be set on the URL, otherwise the program will not operate correctly as it does with its built-in hsqldb instance.

The default is false.

 jdbc:hsqldb:hsql://localhost/enrolments;default_schema=true

Properties for the Database

Each database has several default settings (properties) that are listed in the System Management chapter. These properties can be changed via SQL commands after a connection is made to the database. It is also possible to specify most of these properties in the connection properties or as part of the URL string when the first connection is made to a new file: or mem: database. This allows the properties to be set without using any SQL commands. The corresponding SQL command is given for each property. For a server, these properties can be appended to the database path URL in server.properties or the server command line.

When connecting to an in-process database creates a new database, or opens an existing database (i.e. it is the first connection made to the database by the application), all the user-defined database properties listed in this section can be specified as URL properties.

Note the preferred method of setting database properties is by using a set of SQL statements. These statements can be used both for a new database or an existing database, unlike URL properties that are generally effective for new databases only.

If these properties are used for connection to an existing database, they are ignored.

The exceptions are the following property settings that are allowed for the first connection to an existing database (the connection which reopens the database): readonly=true, files_readonly=true, hsqldb.lock_file=false, hsqldb.sqllog=1-3, hsqldb.applog=1-3. These specific property / value pairs override the existing database properties. For example a normal database is opened as readonly, or the lock file is not created, or the sql log level is set to a value between 1 and 3.

Properties for database encryption and compressed .script file are also required on the first connection to an existing database.

Management of properties has changed since version 1.8. The old SET PROPERTY statement does not change a property and is ignored. The statement is retained to simplify application upgrades.

In the example URL below, two properties are set for the first connection to a new database.

 jdbc:hsqldb:file:enrolments;hsqldb.cache_rows=10000;hsqldb.nio_data_file=false

In the table below, database properties that can be used as part of the URL or in connection properties are listed. For each property that can also be set with an SQL statement, the statement is also given. These statements are described more extensively in the System Management chapter.

Table 14.12. Validity Check Property

NameDefaultDescription
check_propsfalsechecks the validity of the database properties

If the property is true, every database property that is specified on the URL or in connection properties is checked and if it is not used correctly, an error is returned.

this property cannot be set with an SQL statement

Table 14.13. Creating New Database Check Property

NameDefaultDescription
ifexistsfalseconnect only if database already exists

Has an effect only with mem: and file: database. When true, will not create a new database if one does not already exist for the URL.

When the property is false (the default), a new mem: or file: database will be created if it does not exist.

Setting the property to true is useful when troubleshooting as no database is created if the URL is malformed. Example below:

 jdbc:hsqldb:file:accounts;ifexists=true
createtruecreate the database if it does not exist

Similar to the ifexists property, but with opposite meaning.

Has an effect only with mem: and file: databases. When false, will not create a new database if one does not already exist for the URL.

When the property is true (the default), a new mem: or file: database will be created if it does not exist.

Setting the property to true is useful when troubleshooting as no database is created if the URL is malformed. Example below:

 jdbc:hsqldb:file:enrolments;create=false

SQL Conformance Properties

Table 14.14. Execution of Multiple SQL Statements etc.

NameDefaultDescription
sql.restrict_execfalsepreventS execution of multiple, concatenated SQL statements

This property, when set true, prevents execution of multiple, concatenated statements via Statement.execute() and other methods of java.sql.Statement. It also prevents the use of Statement.executeQuery() for any DDL or DML statement.

Legacy applications may contain such statements, for example "INSERT INTO T1 VALUES 1, 2, 3;DELETE FROM T2 WHERE C1 = 9"; therefore the default is false. Statements that are prepared with java.sql.PreparedStatement have been limited to single statements since HyperSQL 2.0.

It is recommended to set this property to TRUE and use single execution of statements.

SET DATABASE SQL RESTRICT EXEC { TRUE | FALSE }

Table 14.15. SQL Keyword Use as Identifier

NameDefaultDescription
sql.enforce_namesfalseenforcing SQL keywords

This property, when set true, prevents SQL keywords being used for database object names such as columns and tables.

SET DATABASE SQL NAMES { TRUE | FALSE }

Table 14.16. SQL Keyword Starting with the Underscore or Containing Dollar Characters

NameDefaultDescription
sql.regular_namestrueenforcing SQL keywords

This property, when set true, prevents database object names such as columns and tables beginning with the underscore or containing the dollar character.

SET DATABASE SQL REGULAR NAMES { TRUE | FALSE }

Table 14.17. Reference to Columns Names

NameDefaultDescription
sql.enforce_refsfalseenforcing column reference disambiguation

This property, when set true, causes an error when an SQL statement (usually a select statement) contains column references that can be resolved by more than one table name or alias. In effect forces such column references to have a table name or table alias qualifier.

SET DATABASE SQL REFERENCES { TRUE | FALSE }

Table 14.18. String Size Declaration

NameDefaultDescription
sql.enforce_sizetruesize enforcement of string columns

Conforms to SQL standards for size and precision of data types. When true, all VARCHAR column type declarations require a size. When the property is false and there is no size in the declaration, a default size is used. Note that all other types accept a declaration without a size, which is interpreted as a default size.

SET DATABASE SQL SIZE { TRUE | FALSE }

Table 14.19. Truncation of trailing spaces from string

NameDefaultDescription
sql.truncate_trailingtruetruncation of long strings with trailing spaces

When a string that is longer than the maximum size of a column is inserted, the default behaviour is to remove any trailing spaces until the length of the string equals the maximum size of the column. When this property is set to false, long strings are always rejected and an exception is raised.

SET DATABASE SQL TRUNCATE TRAILING { TRUE | FALSE }

Table 14.20. Type Enforcement in Comparison and Assignment

NameDefaultDescription
sql.enforce_typesfalseenforcing type compatibility

This property, when set true, causes an error when an SQL statements contains comparisons or assignments that are non-standard due to type mismatch. Most illegal comparisons and assignments will cause an exception regardless of this setting. This setting applies to a small number of comparisons and assignments that are possible, but not standard conformant, and were allowed in previous versions of HSQLDB.

SET DATABASE SQL TYPES { TRUE | FALSE }

Table 14.21. Foreign Key Triggered Data Change

NameDefaultDescription
sql.enforce_tdc_deletetrueenforcing triggered data change violation for deletes

The ON DELETE and ON UPDATE clauses of constraints cause data changes in rows in different tables or the same table. When there are multiple constraints, a row may be updated by one constraint and deleted by another constraint in the same operation. This is not allowed by default. Changing this property to false allows such violations of the Standard to pass without an exception. Used for porting from database engines that do not enforce the constraints.

SET DATABASE SQL TDC DELETE { TRUE | FALSE }
 
sql.enforce_tdc_updatetrueenforcing triggered data change violation for updates

The ON DELETE and ON UPDATE clauses of foreign key constraints cause data changes in rows in different tables or the same table. With multiple constraint, a field may be updated by two constraints and set to different values. This is not allowed by default. Changing this property to false allows such violations of the Standard to pass without an exception. Used for porting from database engines that do not enforce the constraints properly.

SET DATABASE SQL TDC UPDATE { TRUE | FALSE }

Table 14.22. Use of LOB for LONGVAR Types

NameDefaultDescription
sql.longvar_is_lobfalsetranslating longvarchar and longvarbinary to lob

This property, when set true, causes type declarations using LONGVARCHAR and LONGVARBINARY to be translated to CLOB and BLOB respectively. By default, they are translated to VARCHAR and VARBINARY.

SET DATABASE SQL LONGVAR IS LOB { TRUE | FALSE }

Table 14.23. Type of string literals in CASE WHEN

NameDefaultDescription
sql.char_literaltrueresult of CASE WHEN with strings of different lengths

This property, when set false, sets the type of all string literal to VARCHAR, as opposed to CHARACTER. This results in strings not being padded with spaces by CASE WHEN expressions.

SET DATABASE SQL CHARACTER LITERAL { TRUE | FALSE }

Table 14.24. Concatenation with NULL

NameDefaultDescription
sql.concat_nullstruebehaviour of concatenation involving one null

This property, when set false, causes the concatenation of a null and a not null value to return the not null value. By default, it returns null.

SET DATABASE SQL CONCAT NULLS { TRUE | FALSE }

Table 14.25. NULL in Multi-Column UNIQUE Constraints

NameDefaultDescription
sql.unique_nullstruebehaviour of multi-column UNIQUE constraints with null values

This property, when set false, causes multi-column unique constrains to be more restrictive for value sets that contain a mix of null and not null values.

SET DATABASE SQL UNIQUE NULLS { TRUE | FALSE }

Table 14.26. Truncation or Rounding in Type Conversion

NameDefaultDescription
sql.convert_trunctruebehaviour of type conversion from DOUBLE to integral types

This property, when set false, causes type conversions from DOUBLE to any integral type to use rounding. By default truncation is used.

SET DATABASE SQL CONVERT TRUNCATE { TRUE | FALSE }

Table 14.27. Decimal Scale of Division and AVG Values

NameDefaultDescription
sql.avg_scale0decimal scale of values returned by division and the AVG and MEDIAN aggregate functions

By default, the result of a division or an AVG or MEDIAN aggregate has the same type and scale as the aggregated value. For INTEGER types, the scale is 0. When this property is set to a value other than the default 0, then the scale is used if it is greater than the scale of the divisor or aggregated value. This property does not affect DOUBLE values. Values between 0 - 10 can be used for this property.

SET DATABASE SQL AVG SCALE <numeric value>

Table 14.28. Support for NaN values

NameDefaultDescription
sql.double_nantruebehaviour of expressions returning DOUBLE NaN

This property, when set false, causes division of DOUBLE values by Zero to return a Double.NaN value. By default an exception is thrown.

SET DATABASE SQL DOUBLE NAN { TRUE | FALSE }

Table 14.29. Sort order of NULL values

NameDefaultDescription
sql.nulls_firsttrueordering of NULL values

By default, nulls appear before not-null values when a result set is ordered without specifying NULLS FIRST or NULLS LAST. This property, when set false, causes nulls to appear by default after not-null values in result sets with ORDER BY

SET DATABASE SQL NULLS FIRST { TRUE | FALSE }

Table 14.30. Sort order of NULL values with DESC

NameDefaultDescription
sql.nulls_ordertrueordering of NULL values when DESC is used

By default, when an ORDER BY clause that does not specify NULLS FIRST or NULLS LAST is used, nulls are ordered according to the sql.nulls_first setting even when DESC is used after ORDER BY. This property, when set false, causes nulls to appear in the opposite position when DESC is used.

SET DATABASE SQL NULLS ORDER { TRUE | FALSE }

Table 14.31. String Comparison with Padding

NameDefaultDescription
sql.pad_spacetrueordering of strings with trailing spaces

By default, when two strings are compared, the shorter string is padded with spaces before comparison. When this property is set false, no padding takes place before comparison. Without padding, the shorter string is never equal to the longer one.

Before version 2.0, HSQLDB used NO PAD comparison. If you need the old behaviour, use this property when opening an older database.

SET DATABASE COLLATION <collation name> [ NO PAD | PAD SPACE ]

Table 14.32. Default Locale Language Collation

NameDefaultDescription
sql.compare_in_localefalseuse the default locale language collation

When this property is set true, the language of the default locale of the JVM is used as the default collation. This is applied to new databases only.

SET DATABASE COLLATION <collation name> 

Table 14.33. Case-Insensitive Varchar columns

NameDefaultDescription
sql.ignore_casefalsecase-insensitive VARCHAR

When this property is set true, all VARCHAR declarations in CREATE TABLE and other statements are assigned an Upper Case Comparison collation, SQL_TEXT_UCC. This is designed for compatibility with some databases that use case-insensitive comparison. It is better to specify the collation selectively for specific columns that require it.

SET DATABASE COLLATION SQL_TEXT_UCC

Table 14.34. Lowercase column identifiers in ResultSet

NameDefaultDescription
sql.lowercase_identfalseuse lowercase for unquoted column names in ResultSetMetaData

When this property is set true, the ResultSetMetaData will report the names of columns, their table and their schema in lowercase instead of uppercase when the names where not created as quoted identifiers. This setting is useful for limited compatibility with PostgreSQL and MySQL which have non-standard identifier cases.

SET DATABASE SQL LOWER CASE IDENTIFIER

Table 14.35. Storage of Live Java Objects

NameDefaultDescription
sql.live_objectfalsestorage of Java Objects in OTHER columns with or without serialization

By default when Java Objects are stored in a column of type OTHER, the objects are serialized. Setting this property to true results in the Object to be stored without serialization. This option is available in mem: database only.

SET DATABASE LIVE OBJECT

Table 14.36. Names of System Indexes Used for Constraints

NameDefaultDescription
sql.sys_index_namestruename of system generated indexes for constraints

HSQLDB automatically creates a system index for each PRIMARY KEY, UNIQUE and FOREIGN KEY constraint. If a constraint is not defined with a name, the system generates a name. By default, the names of these indexes will be the same as the constraint names. This helps associating the index name with the user-defined constraint name. When this property is false, the names of those indexes are generated the system as a string beginning with SYS_.

The default value for this property was false before version 2.7.0.

SET DATABASE SQL SYS INDEX NAMES { TRUE | FALSE }

Table 14.37. DB2 Style Syntax

NameDefaultDescription
sql.syntax_db2falsesupport for DB2 style syntax

This property, when set true, allows compatibility with some aspects of this dialect.

SET DATABASE SQL SYNTAX DB2 { TRUE | FALSE }

Table 14.38. MSSQL Style Syntax

NameDefaultDescription
sql.syntax_mssfalsesupport for MS SQL Server style syntax

This property, when set true, switches the arguments of the CONVERT function and also allow compatibility with some other aspects of this dialect.

SET DATABASE SQL SYNTAX MSS { TRUE | FALSE }

Table 14.39. MySQL Style Syntax

NameDefaultDescription
sql.syntax_mysfalsesupport for MySQL style syntax

This property, when set true, enables support for TEXT and AUTO_INCREMENT types and also allow compatibility with many other aspects of this dialect.

SET DATABASE SQL SYNTAX MYS { TRUE | FALSE }

Table 14.40. Oracle Style Syntax

NameDefaultDescription
sql.syntax_orafalsesupport for Oracle style syntax

This property, when set true, enables support for non-standard types. It also enables DUAL, ROWNUM, NEXTVAL and CURRVAL syntax and and also allow compatibility with some other aspects of this dialect.

SET DATABASE SQL SYNTAX ORA { TRUE | FALSE }

Table 14.41. PostgreSQL Style Syntax

NameDefaultDescription
sql.syntax_pgsfalsesupport for PostgreSQL style syntax

This property, when set true, enables support for TEXT and SERIAL types. It also enables NEXTVAL, CURRVAL and LASTVAL syntax and also allow compatibility with some other aspects of this dialect.

SET DATABASE SQL SYNTAX PGS { TRUE | FALSE }

Table 14.42. Maximum Iterations of Recursive Queries

NameDefaultDescription
sql.max_recursive256maximum number of iterations of a recursive query

Recursive queries terminate if they are not completed when the maximum number of iterations is reached. This is to avoid long-running queries that may never actually finish.

The default value is fine for most use-cases. You can change the default if you need to.

SET DATABASE SQL MAX RECURSIVE <count>

Database Operations Properties

Table 14.43. Default Table Type

NameDefaultDescription
hsqldb.default_table_typememorytype of table created with unqualified CREATE TABLE

The CREATE TABLE command results in a MEMORY table by default. Setting the value cached for this property will result in a cached table by default. The qualified forms such as CREATE MEMORY TABLE or CREATE CACHED TABLE are not affected at all by this property.

SET DATABASE DEFAULT TABLE TYPE { CACHED | MEMORY }

Table 14.44. Transaction Control Mode

NameDefaultDescription
hsqldb.txlocksdatabase transaction control mode

Indicates the transaction control mode for the database. The values, locks, mvlocks and mvcc are allowed.

SET DATABASE TRANSACTION CONTROL { LOCKS | MVLOCKS | MVCC }

Table 14.45. Default Isolation Level for Sessions

NameDefaultDescription
hsqldb.tx_levelread_commiteddatabase default transaction isolation level

Indicates the default transaction isolation level for each new session. The values, read_committed and serializable are allowed. Individual sessions can change their isolation level.

SET DATABASE DEFAULT ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }

Table 14.46. Transaction Rollback in Deadlock

NameDefaultDescription
hsqldb.tx_conflict_rollbacktrueeffect of deadlock or other conflicts on transaction

When a transaction deadlock or other unresolvable conflict is about to happen, the current transaction is rolled back and an exception is raised. When this property is set false, the transaction is not rolled back. Only the latest action that would cause the conflict is undone and an error is returned. The property should not be changed unless the application can quickly perform an alternative statement and complete the transaction. It is provided for compatibility with other database engines which do not roll back the transaction upon deadlock.

SET DATABASE TRANSACTION ROLLBACK ON CONFLICT { TRUE | FALSE }

Table 14.47. Transaction Rollback on Interrupt

NameDefaultDescription
hsqldb.tx_interrupt_rollbackfalseeffect of Thread interrupt on transaction

In an in-process database, when a thread in the user's application is executing an SQL statement and it is interrupted, the interrupt is cleared by HyperSQL. You can set this property to true to force a rollback of the transaction (only if the interrupt is detected). With this setting the interrupt is not cleared.

SET DATABASE TRANSACTION ROLLBACK ON INTERRUPT { TRUE | FALSE }

Table 14.48. Time Zone and Interval Types

NameDefaultDescription
hsqldb.translate_tti_typestrueusage of type codes for advanced interval types

If the property is true, the INTERVAL types are represented in JDBC methods of ResultSetMetaData and DatabaseMetaData as the VARCHAR type. The original type names are preserved.

JDBC does not have direct support for names and codes of INTERVAL types. From Java 8, getting and setting INTERVAL values is possible via getObject() and setObject() methods of ResultSet and PreparedStatement.

SET DATABASE SQL TRANSLATE TTI TYPES { TRUE | FALSE }

Table 14.49. Temporary Result Rows in Memory

NameDefaultDescription
hsqldb.result_max_memory_rows0storage of temporary results and tables in memory or on disk

This property can be set to specify how many rows of each results or temporary table are stored in memory before the table is written to disk. The default is zero and means data is always stored in memory. If this setting is used, it should be set above 1000.

SET DATABASE DEFAULT RESULT MEMORY ROWS <numeric value>

Database File and Memory Properties

Table 14.50. Opening Database as Read Only

NameDefaultDescription
readonlyfalsereadonly database - is used to open an existing file: database

This property is a special property that can be added manually to the .properties file, or included in the URL or connection properties. When this property is true, the database becomes readonly. This can be used with an existing database to open it for readonly operation.

this property cannot be set with an SQL statement - it can be used in the .properties file

Table 14.51. Opening Database Without Modifying the Files

NameDefaultDescription
files_readonlyfalsereadonly files database - is used to open an existing file: database

This property is used similarly to the hsqldb.readonly property. When this property is true, CACHED and TEXT tables are readonly but memory tables are not. Any change to the data is not persisted to database files.

this property cannot be set with an SQL statement - it can be used in the .properties file

Table 14.52. Event Logging

NameDefaultDescription
hsqldb.applog0application logging level - can also be used when opening an existing file: database

The default level 0 indicates no logging. Level 1 results in minimal logging, including any failures. Level 2 indicates all events, including ordinary events. LEVEL 3 adds details of some of the normal operations. The events are logged in a file ending with ".app.log".

SET DATABASE EVENT LOG LEVEL { 0 | 1 | 2 | 3}

Table 14.53. SQL Logging

NameDefaultDescription
hsqldb.sqllog0sql logging level - can also be used when opening an existing file: database

The default level 0 indicates no logging. Level 1 logs only commits and rollbacks. Level 2 logs all the SQL statements executed, together with their parameter values. Long statements and parameter values are truncated. Level 3 is similar to Level 2 but does not truncate long statements and values. The events are logged in a file ending with ".sql.log". This property applies to existing file: databases as well as new databases.

SET DATABASE EVENT LOG SQL LEVEL { 0 | 1 | 2 | 3}

Table 14.54. Table Spaces for Cached Tables

NameDefaultDescription
hsqldb.files_spacefalseuse of separate table spaces for each CACHED table

The default value is false, indicating table space management is not used. When the value is true at the time of creation of a new database, the directory structures are created inside the .data file and table space support is enabled

SET FILES SPACE { TRUE | FALSE }

Table 14.55. Huge database files and tables

NameDefaultDescription
hsqldb.large_datafalseenable huge database files - can also be used to open an existing file: database

By default, up to 2 billion rows can be stored in all disk-based CACHED tables. Setting this property to true increases the limit to 256 billion rows. This property is used as a connection property.

this property cannot be set with an SQL statement - it can be used as a connection property for the connection that opens the database

Table 14.56. Use of NIO for Disk Table Storage

NameDefaultDescription
hsqldb.nio_data_filetrueuse of nio access methods for the .data file

Setting this property to false will avoid the use of nio access methods, resulting in somewhat reduced speed. If the data file is larger than hsqldb.nio_max_size (default 256MB) when it is first opened (or when its size is increased), nio access methods are not used. Also, if the file gets larger than the amount of available computer memory that needs to be allocated for nio access, non-nio access methods are used.

SET FILES NIO { TRUE | FALSE }

Table 14.57. Use of NIO for Disk Table Storage

NameDefaultDescription
hsqldb.nio_max_size256nio buffer size limit

The maximum size of .data file in mega bytes that can use the nio access method. When the file gets larger than this limit, non-nio access methods are used. Values 64, 128, 256, 512, 1024, and larger multiples of 512 can be used. The default is 256MB.

SET FILES NIO SIZE <numeric value>

Table 14.58. Internal Backup of the .data File

NameDefaultDescription
hsqldb.inc_backuptrueincremental backup of data file - NOW OBSOLETE

As the contents of the .data file are modified during database operation, the original contents are backed up gradually. This allows fast checkpoint and shutdown.

With HSQLDB up to version 2.5 it was possible to set the property false in order to have the .data file backed up entirely at the time of checkpoint and shutdown.

From version 2.5.1, this property has no effect and backup is always incremental.

SET FILES BACKUP INCREMENT { TRUE | FALSE }

Table 14.59. Unused Space Recovery

NameDefaultDescription
hsqldb.cache_free_count512maximum number of unused space recovery - can also be used when opening an existing file: database

The default indicates 512 unused spaces are kept for later use. The value can range between 0 - 8096.

When rows are deleted, the space is recovered and kept for reuse for new rows. If too many rows are deleted, the smaller recovered spaces are lost and the largest ones are retained for later use. Normally there is no need to set this property.

When table space management is turned on (see hsqldb.files_space property) this property has little effect as unused spaces are always recovered.

this property cannot be set with an SQL statement

Table 14.60. Rows Cached In Memory

NameDefaultDescription
hsqldb.cache_rows50000maximum number of rows in memory cache

Indicates the maximum number of rows of cached tables that are held in memory.

The value can range between 100- 4 million. If the value is set via SET FILES CACHE ROWS then it becomes effective after the next database SHUTDOWN.

SET FILES CACHE ROWS <numeric value>

Table 14.61. Size of Rows Cached in Memory

NameDefaultDescription
hsqldb.cache_size10000memory cache size

Indicates the total size (in kilobytes) of rows in the memory cache used with cached tables. This size is calculated as the binary size of the rows, for example an INTEGER is 4 bytes. The actual memory size used by the objects is 2 to 4 times this value. This depends on the types of objects in database rows, for example with binary objects the factor is less than 2, with character strings, the factor is just over 2 and with date and timestamp objects the factor is over 3.

The value can range between 100 KB - 4 GB. The default is 10,000, representing 10,000 kilobytes. If the value is set via SET FILES then it becomes effective after the next database SHUTDOWN or CHECKPOINT.

SET FILES CACHE SIZE <numeric value>

Table 14.62. Size Scale of Disk Table Storage

NameDefaultDescription
hsqldb.cache_file_scale32unit used for storage of rows in the .data file

The default value corresponds to a maximum size of 64 GB for the .data file. This can be increased to 64, 128, 256, 512, or 1024 resulting in up to 2 TB GB storage. Settings below 32 in older databases are preserved until a SHUTDOWN COMPACT.

SET FILES SCALE <numeric value>

Table 14.63. Size Scale of LOB Storage

NameDefaultDescription
hsqldb.lob_file_scale32unit used for storage of lobs in the .lobs file

The default value represents units of 32KB. When the average size of individual lobs in the database is smaller, a smaller unit can be used to reduce the overall size of the .lobs file. Values 1, 2, 4, 8, 16, 32 can be used.

SET FILES LOB SCALE <numeric value>

Table 14.64. Compression of BLOB and CLOB data

NameDefaultDescription
hsqldb.lob_compressedfalseuse of compression for storage of blobs and clobs

The default value is false, indicating no compression. When the value is true at the time of creation of a new database, blobs and clobs are stored as compressed parts.

SET FILES LOB COMPRESSED { TRUE | FALSE }

Table 14.65. Use of Lock File

NameDefaultDescription
hsqldb.lock_filetrueuse of lock file - can also be used with an existing file: database

By default, a lock file is created for each file database that is opened for read and write. This property can be specified with the value false to prevent the lock file from being created. This usage is not recommended but may be desirable when flash type storage is used. This property applies to existing file: databases as well as new databases.

this property cannot be set with an SQL statement

Table 14.66. Logging Data Change Statements

NameDefaultDescription
hsqldb.log_datatruelogging data change

This property can be set to false when database recovery in the event of an unexpected crash is not necessary. A database that is used as a temporary cache is an example. Regardless of the value of this property, a checkpoint or shutdown still writes the .script file and saves the .data file in full, therefore persisting all the changes.

SET FILES LOG  { TRUE | FALSE }

Table 14.67. Automatic Checkpoint Frequency

NameDefaultDescription
hsqldb.log_size50size of log when checkpoint is performed

The value is the size (in megabytes) that the .log file can reach before an automatic checkpoint occurs. A checkpoint rewrites the .script file and clears the .log file.

SET FILES LOG SIZE <numeric value>

Table 14.68. Automatic Defrag at Checkpoint

NameDefaultDescription
hsqldb.defrag_limit0percentage of unused space causing a defrag at checkpoint

When a checkpoint is performed, the percentage of wasted space in the .data file is calculated. If the wasted space is above the specified limit, a defrag operation is performed. The default is 0, which means no automatic checkpoint. The numeric value must be between 0 and 100 and is interpreted as a percentage of the current size of the .data file. Positive values less than 25 are converted to 25.

SET FILES DEFRAG <numeric value>

Table 14.69. Compression of the .script file

NameDefaultDescription
hsqldb.script_format0compressed .script file

If the property is set with the value 3, the .script file is stored in compressed format. This is useful for large script files. The .script is no longer readable when the hsqldb.script_format=3 has been used.

This property cannot be set with an SQL statement

Table 14.70. Logging Data Change Statements Frequency

NameDefaultDescription
hsqldb.write_delaytruewrite delay performing fsync of log file entries

If the property is true, the default WRITE DELAY property of the database is used, which is 500 milliseconds. If the property is false, the WRITE DELAY is set to 0 seconds. The log is written to file regardless of this property. The property controls the fsync that forces the written log to be persisted to disk. The SQL command for this property allows more precise control over the property.

SET FILES WRITE DELAY {{ TRUE | FALSE } | <seconds value> | <milliseconds value> MILLIS

Table 14.71. Logging Data Change Statements Frequency

NameDefaultDescription
hsqldb.write_delay_millis500write delay for performing fsync of log file entries

If the property is used, the WRITE DELAY property of the database is set the given value in milliseconds. The property controls the fsync that forces the written log to be persisted to disk. The SQL command for this property allows the same level of control over the property.

SET FILES WRITE DELAY {{ TRUE | FALSE } | <seconds value> | <milliseconds value> MILLIS

Table 14.72. Recovery Log Processing

NameDefaultDescription
hsqldb.full_log_replayfalserecovery log processing

The .log file is processed during recovery after a forced shutdown. Out of memory conditions always abort the startup. Any other exception stops the processing of the .log file and by default, continues the startup process. If this property is true, the startup process is stopped if any exception occurs. Exceptions are usually caused by incomplete lines of SQL statements near the end of the .log file, which were not fully synced to disk when an abnormal shutdown occurred.

This property cannot be set with an SQL statement

Table 14.73. Default Properties for TEXT Tables

NameDefaultDescription
textdb.*0default properties for new text tables

Properties that override the database engine defaults for newly created text tables. Settings in the text table SET <tablename> SOURCE <source string> command override both the engine defaults and the database properties defaults. Individual textdb.* properties are listed in the Text Tables chapter.


Table 14.74. Forcing Garbage Collection

NameDefaultDescription
runtime.gc_interval0forced garbage collection - NOW OBSOLETE

No-op setting previously used to forces garbage collection each time a set number of result set row or cache row objects are created. This setting has no effect in version 2.5 or later,

SET DATABASE GC <numeric value>

Crypt Properties

Table 14.75. Crypt Property For LOBs

NameDefaultDescription
crypt_lobstrueencryption of lobs

With encrypted databases, if this property is true, the contents of the .lobs file are also encrypted. HyperSQL versions prior to 2.3.0 did not support encrypted lobs. Encrypted databases created with those versions must be opened with crypt_lobs=false on the URL when they contain lobs.

this property cannot be set with an SQL statement

Table 14.76. Cipher Key for Encrypted Database

NameDefaultDescription
crypt_keynoneencryption

The cipher key for an encrypted database.

this property cannot be set with an SQL statement

Table 14.77. Cipher Initialization Vector for Encrypted Database

NameDefaultDescription
crypt_ivnoneencryption

The initialization vector for an encrypted database. Optional feature introduced in version 2.5.0.

this property cannot be set with an SQL statement

Table 14.78. Crypt Provider Encrypted Database

NameDefaultDescription
crypt_providernoneencryption

The fully-qualified class name of the cryptography provider. This property is not used for the default security provider.

this property cannot be set with an SQL statement

Table 14.79. Cipher Specification for Encrypted Database

NameDefaultDescription
crypt_typenoneencryption

The cipher specification.

this property cannot be set with an SQL statement

System Properties

A few system properties are used by HyperSQL. These are set on the Java command line or by calling System.setProperty() from the user's program. They are not valid as URL or connection properties.

Table 14.80. Logging Framework

NameDefaultDescription
hsqldb.reconfig_loggingtrueconfiguring the framework logging

Setting this system property false avoids reconfiguring the framework logging system such as Log4J or java.util.Logging. If the property does not exist or is true, reconfiguration takes place.


Table 14.81. Text Tables

NameDefaultDescription
textdb.allow_full_pathfalsetext table file locations

Setting this system property true allows text table sources and files to be opened on all available paths. It also allows pure mem: databases to open such files. By default, only the database directory and its subdirectories are allowed. See the Text Tables chapter.


Table 14.82. Java Functions

NameDefaultDescription
hsqldb.method_class_namesnoneallowed Java classes

This property needs to be set with the names (including wildcards) of Java classes that can be used for routines based on Java static methods. See the SQL Invoked Routines chapter.



$Revision: 6621 $