Chapter 12. Properties

Fred Toussi

The HSQL Development Group

$Revision: 3626 $

Copyright 2002-2009 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.

$Date: 2010-06-05 07:49:07 -0400 (Sat, 05 Jun 2010) $

Table of Contents

Connections
Connection properties
Database Properties in Connection URL and Properties

Connections

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 SQL Language 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 12.1. HyperSQL URL Components

Driver and ProtocolHost and PortDatabase
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.

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:.

 
jdbc:hsqldb:file:
not available
mydb
/opt/db/accounts
C:/data/mydb

The file path specifies the database file. In the above examples the first one refers to a set of mydb.* files in the directory where the javacommand for running the application was issued. The second and third examples refer to absolute paths on the host machine.

 
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.
 
jdbc:hsqldb:hsql:
jdbc:hsqldb:hsqls:
jdbc:hsqldb:http:
jdbc:hsqldb:https:
//localhost
//192.0.0.10:9500
//dbserver.somedomain.com
/an_alias
/enrollments
/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 following example 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.

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.


Connection properties

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

Connection properties are specified either by establishing the connection via the method call below, or the property can be appended to the full Connection URL.

    DriverManager.getConnection (String url, Properties info);

Table 12.2. Connection Properties

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/enrollments;get_column_name=false

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

 
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:enrollments;ifexists=true
 
shutdownfalseshut down the database when the last connection is closed

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.

    jdbc:hsqldb:file:enrollments;shutdown=true

In addition, when a connection 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 can be specified as URL properties. This can be used to specify properties to enforce more strict SQL adherence, or to change cache_scale or similar properties before the database files are created. However, for new databases, it is recommended to use the SET PROPERTY command for such settings.

Database Properties in Connection URL and Properties

The database engine has several properties that are listed in the System Management and Deployment Issues chapter. These properties can be changed via SQL commands after a connection is made to the database. It is possible to specify all of these properties in the connection properties on 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.

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

In the example URL below, two properties are set for the first connection to a new database. If the properties are used for connection to an existing database, they are ignored.

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

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

Table 12.3. Database-specific Property File Properties

ValueDefaultDescription
check_propsfalsechecks the validity of the connection 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

 
crypt_lobsfalseencryption of lobs

If the property is true, the contents of the .lobs file is encrypted as well.

this property cannot be set with an SQL statement

 
crypt_keynoneencryption

The cipher key for an encrypted database

this property cannot be set with an SQL statement

 
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

 
crypt_typenoneencryption

The cipher specification.

this property cannot be set with an SQL statement

 
read_onlyfalsereadonly 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 property cannot be set with an SQL statement

files_read_onlyfalsereadonly files database

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

this property cannot be set with an SQL statement

hsqldb.log_datatruerecovery log

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, if there is a proper shutdown of the database, all the change data is stored.

this property cannot be set with an SQL statement

 
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 }

 
sql.enforce_sizetruetrimming and padding string columns.

This property is the same as sql.enforce_strict_size

sql.enforce_strict_sizetruesize enforcement and padding string columns

Conforms to SQL standards for size and precision of data types. When true, all CHARACTER, VARCHAR, NUMERIC and DECIMAL values that are in a row affected by an INSERT INTO or UPDATE statement are checked against the size specified in the SQL table definition. An exception is thrown if the value is too long. Also all CHARACTER values that are shorter than the specified size are padded with spaces.

SET DATABASE SQL SIZE { TRUE | FALSE }

 
sql.enforce_refsfalseenforcing column reference disambiguation

This property, when set true, causes an error when an SQL statements contains column references that can be resovled 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 }

 
runtime.gc_interval0forced garbage collection

This setting forces garbage collection each time a set number of result set row or cache row objects are created. The default, "0" means no garbage collection is forced by the program.

SET DATABASE GC <numeric value>

 
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 }

 
hsqldb.applog0application logging level

The default level 0 indicates no logging. Level 1 results in events related to persistence to be logged, including any failures. Level 2 indicates all events, including ordinary events. The events are logged in a file ending with ".app.log".

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

 
hsqldb.result_max_memory_rows0amount of result rows that are kept in memory

Sets the maximum number of rows of each result set and other internal temporary table that is held in memory.

SET DATABASE DEFAULT RESULT MEMORY ROWS <unsigned integer literal>

 
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 }

 
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-1,000,000. If the value is set via SET FILES then it becomes effective after the next database SHUTDOWN or CHECKPOINT.

The property is changed via the SET FILES CACHE ROWS nnn SQL command.

SET FILES CACHE ROWS <numeric value>

 
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-1,000,000. 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>

 
hsqldb.inc_backuptrueincremental backup of data file

During updates, the contents of the .data file are modified. When this property is true, the modified contents are backed up gradually. This causes a marginal slowdown in operations, but allows fast checkpoint and shutdown.

When the property is false, the .data file is backed up entirely at the time of checkpoint and shutdown. Up to version 1.8, HSQLDB supported only full backup.

SET FILES INCREMENT BACKUP { TRUE | FALSE }

 
hsqldb.lock_filetrueuse of lock file

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 cannot be set with an SQL statement

 
hsqldb.log_datatruelogging data change

When false is specified, no data logging takes place. A checkpoint or shutdown still rewrites the .script file and saves the .backup file according to the other settings.

SET FILES LOG  { TRUE | FALSE }

 
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>

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

When HyperSQL is compiled and run in Java 1.4 or higher, 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 256MB when it is first opened, 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.

If used before defining any CACHED table, it applies immediately, otherwise it comes into effect after a SHUTDOWN and restart or CHECKPOINT.

SET FILES NIO { TRUE | FALSE }

 
hsqldb.translate_dti_typestrueusage of type codes for advanced type datetime types

If the property is true, the datetime WITH TIME ZONE types and INTERVAL types are represented as JDBC datetime types without time zone and the VARCHAR type respectively.

this property cannot be set with an SQL statement

 
hsqldb.write_delaytruewrite delay for writing log file entries

If the property is true, the default WRITE DELAY property of the database is used, which is 1000 milliseconds. If the property is false, the WRITE DELAY is set to 0 seconds. The SQL command for this property allows more precise control over the property.

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

 
hsqldb.write_delay_millis1000write delay for writing log file entries

If the property is used, the WRITE DELAY property of the database is set the given value. 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

 
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.


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.

When HSQLDB is used in OpenOffice.org, some property values will have a different default. The properties and values are:

hsqldb.default_table_type=cached hsqldb.cache_rows=25000; hsqldb.cache_size=6000; hsqldb.log_size=10; hsqldb.nio_data_file=false; sql.enforce_strict_size=true


$Revision: 3601 $