Chapter 14. Properties

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 }

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 }

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 }

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 }

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 }

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 }

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 }

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 }

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 }

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 }

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 }

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 }

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 }

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 }

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>

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 }

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 }

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 }

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 ]

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> 

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

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

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

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 }

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

SET DATABASE SQL SYNTAX DB2 { TRUE | FALSE }

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 }

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 }

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 }

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 }

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>

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 }

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

SET DATABASE TRANSACTION CONTROL { LOCKS | MVLOCKS | MVCC }

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 }

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 }

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 }

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 4.3 does not have direct support for names and type codes of INTERVAL types. You can get and set INTERVAL values with java.time.Period (for MONTH and YEAR) and java.time.Duration (for the rest of interval types) via ResultSet.getObject() and PreparedStatement.setObject() methods.

SET DATABASE SQL TRANSLATE TTI TYPES { TRUE | FALSE }

This property can be set to specify how many rows of each result set 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>

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

This property is similar 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

The default level 0 indicates no logging. Level 1 and 2 result in minimal logging, including any failures. Level 3 indicates all events, including ordinary events. Level 4 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 | 4}

The default level 0 indicates no logging. Level 1 and 2 logs only commits and rollbacks. Level 3 logs all the SQL statements executed, together with their parameter values. Long statements and parameter values are truncated. Level 4 is similar to Level 3 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 | 4}

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 }

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

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 }

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>

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 }

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

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

The value can range between 100- 16 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>

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 can be just over 2 and with date and timestamp objects the factor is over 3.

The value can range between 100 KB - 16 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>

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>

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>

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 }

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

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 }

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. The special value 0 means no automatic checkpoint.

SET FILES LOG SIZE <numeric value>

When an automatic 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 defrag. 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>

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

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

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

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

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.

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>

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

The cipher key for an encrypted database.

this property cannot be set with an SQL statement

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

this property cannot be set with an SQL statement

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

The cipher specification.

this property cannot be set with an SQL statement