$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
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 Protocol | Host and Port | Database | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
| not available |
|
||||||||||
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, |
||||||||||||
| not available |
|
||||||||||
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
|
||||||||||||
| not available |
|
||||||||||
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. |
||||||||||||
|
|
|
||||||||||
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
The old form
for the server URL, e.g.,
|
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_name | true | column name in ResultSet |
This property is used for
compatibility with other JDBC driver implementations. When true
(the default), The default is true. When the
property is false, the above method returns the same value as
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. |
||
ifexists | false | connect 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 |
||
shutdown | false | shut down the database when the last connection is closed |
If this property is
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.
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
Value | Default | Description |
---|---|---|
check_props | false | checks 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_lobs | false | encryption 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_key | none | encryption |
The cipher key for an encrypted database
this property cannot be set with an SQL statement |
||
crypt_provider | none | encryption |
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_type | none | encryption |
The cipher specification.
this property cannot be set with an SQL statement |
||
read_only | false | readonly 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_only | false | readonly 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_data | true | recovery 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_names | false | enforcing 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_size | true | trimming and padding string columns. |
This property is the same as sql.enforce_strict_size |
||
sql.enforce_strict_size | true | size 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_refs | false | enforcing 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_interval | 0 | forced 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_type | memory | type 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.applog | 0 | application 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_rows | 0 | amount 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.tx | locks | database 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_rows | 50000 | maximum 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 <numeric value> |
||
hsqldb.cache_size | 10000 | memory 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_backup | true | incremental 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_file | true | use 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_data | true | logging data change |
When
SET FILES LOG { TRUE | FALSE } |
||
hsqldb.log_size | 50 | size of log when checkpoint is performed |
The value is the size (in
megabytes) that the
SET FILES LOG SIZE <numeric value> |
||
hsqldb.nio_data_file | true | use of nio access methods for the .data file |
When HyperSQL is compiled
and run in Java 1.4 or higher, setting this property to
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_types | true | usage 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_delay | true | write 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_millis | 1000 | write 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.* | 0 | default properties for new text tables |
Properties that override the
database engine defaults for newly created text tables. Settings
in the text table |
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 $