Copyright HSQLDB Development Group. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. If you notice any mistakes in this document, please email the author listed at the beginning of the chapter.

If you have problems with the procedures themselves, please use the HSQLDB support facilities which are listed at http: You may be reading this document right now at http: I hereby call the document distribution from which you are reading this, your current distro.

If you want a different format of the same version of the document you are reading now, then you should try your current distro. If you want the latest production version, you should try http: Sometimes, distributions other than http: So, if you can't access the format that you want in your current distro, you have no choice but to use the newest production version at http: Alternate formats of this document.

Copyright Fred Toussi. Additional permission is granted to the HSQLDB Development Group to distribute this document with or without alterations under the terms of the HSQLDB license. Different commands are used to run each program. The HSQLDB RDBMS and JDBC Driver provide the core functionality. The rest are general-purpose database tools that can be used with any database engine that has a JDBC driver. All tools can be run in the standard way for archived Java classes. In the following example the AWT version of the Database Manager, the hsqldb.

Some tools, such as the Database Manager or SQL Tool, can use command line arguments or entirely rely on them. You can add the command line argument -? Database Manager features a graphical user interface and can be explored interactively. HSQLDB can be run in a number of different ways.

In general these are divided into Server Modes and In-Process Mode also called Standalone Mode. A different sub-program from the jar is used to run HSQLDB in each mode. Each HSQLDB database consists of between 2 to 5 files, all named the same but with different extensions, located in the same directory. For example, the database named "test" consists of the following files:. The properties files contains general settings about the database. The script file contains the definition of tables and other database objects, plus the data for non-cached tables.

The log file contains recent changes to the database. The data file contains the data for cached tables and the backup file is a zipped backup of the last known consistent state of the data file. All these files are essential and should never be deleted. If the database has no cached tables, the test. In addition to those files, HSQLDB database may link to any formatted text files, such as CSV lists, anywhere on the disk.

While the "test" database is operational, a test. This file is removed at a normal SHUTDOWN. Otherwise with abnormal shutdown this file is used at the next startup to redo the changes. This is deleted at a normal SHUTDOWN. In some circumstances, a test. When the engine closes the database at a shutdown, it creates temporary files with the extension. Server modes provide the maximum accessibility. The database engine runs in a JVM and listens for connections from programs on the same computer or other computers on the network.

Several different programs can connect to the server and retrieve or update information. Applications programs clients connect to the server using the HSQLDB JDBC driver. In most server modes, the server can serve up to 10 databases that are specified at the time of running the server.

Server modes can use preset properties or command line arguments as detailed in the Advanced Topics chapter. There are three server modes, based on the protocol used for communications between the client and server. This is the preferred way of running a database server and the fastest one.

A proprietary communications protocol is used for this mode. A command similar to those used for running tools and described above is used for running the server. The following example of the command for starting the server starts the server with one default database with files named "mydb. The command line argument -? This mode is used when access to the computer hosting the database server is restricted to the HTTP protocol.

The only reason for using the Web Server mode is restrictions imposed by firewalls on the client or server machines and it should not be used where there are no such restrictions.

The HSQLDB Web Server is a special web server that allows JDBC clients to connect via HTTP. To run a web server, replace the main class for the server in the example command line above with the following:.

This uses the same protocol as the Web Server. It is used when a separate servlet engine or application server such as Tomcat or Resin provides access to the database. The Servlet Mode cannot be started independently from the servlet engine. The Servlet class, in the HSQLDB jar, should be installed on the application server to provide the connection. The database is specified using an application server property.

Refer to the source file org. Both Web Server and Servlet modes can only be accessed using the JDBC driver at the client end. They do not provide a web front end to the database. The Servlet mode can serve only a single database. Please note that you do not normally use this mode if you are using the database engine in an application server.

Once an HSQLDB server is running, client programs can connect to it using the HSQLDB JDBC Driver contained in hsqldb. A common example is connection to the default port used for the hsql protocol on the same machine:.

Note in the above connection URL, there is no mention of the database file, as this was specified when running the server. Instead, the value defined for dbname. Also, see the Advanced Topics chapter for the connection URL when there is more than one database per server instance. When HSQLDB is run as a server, network access should be adequately protected.

Source IP addresses may be restricted by use of TCP filtering or firewall programs, or standalone firewalls. If the traffic will cross an unprotected network such as the Internetthe stream should be encrypted for example by VPN, ssh tunneling, or TLS using the SSL enabled HSQLS and HTTPS variants of the server and web server modes.

Only secure passwords should be used-- most importantly, the password for the default system user should be changed from the default empty string.

If you are purposefully providing data to the public, then the wide-open public network connection should be used exclusively to access the public data via read-only accounts.

These considerations also apply to HSQLDB servers run with the HTTP protocol. This mode runs the database engine as part of your application program in the same Java Virtual Machine. For most applications this mode can be faster, as the data is not converted and sent over the network. The main drawback is that it is not possible by default to connect to the database from outside your application.

As a result you cannot check the contents of the database with external tools such as Database Manager while your application is running. The recommended way of using the in-process mode in an application is to use an HSQLDB Server instance for the database while developing the application and then switch to In-Process mode for deployment.

An In-Process Mode database is started from JDBC, with the database file path specified in the connection URL. For example, if the database name is testdb and its files are located in the same directory as where the command to run your application was issued, the following code is used for the connection:.

The database file path format can be specified using forward slashes in Windows hosts as well as Linux hosts. So relative paths or paths that refer to the same directory on the same drive can be identical.

When using relative paths, these paths will be taken relative to the directory in which the shell command to start the Java Virtual Machine was executed. Refer to Javadoc for jdbcConnection for more details. It is possible to run HSQLDB in a way that the database is not persistent and exists entirely in random access memory.

As no information is written to disk, this mode should be used only for internal processing of application data, in applets or certain special applications. This mode is specified by the mem: You can also run a memory-only server instance by specifying the same URL in the server. This usage is not common and is limited to special applications where the database server is used only for exchanging information between clients, or for non-persistent data. All databases running in different modes can be closed with the SHUTDOWN command, issued as an SQL query.

When SHUTDOWN is issued, all active transactions are rolled back. A special form of closing the database is via the SHUTDOWN COMPACT command. This command rewrites the. This command should be issued periodically, especially when lots of inserts, updates or deletes have been performed on the cached tables. Changes to the structure of the database, such as dropping or modifying populated CACHED tables or indexes also create large amounts of unused file space that can be reclaimed using this command.

In the above examples each server serves only one database and only one in-memory database can be created. However, from version 1. These capabilities are covered in the Advanced Topics chapter. When a server instance is started, or when a connection is made to an in-process database, a new, empty database is created if no database exists at the given path.

This feature has a side effect that can confuse new users. If a mistake is made in specifying the path for connecting to an existing database, a connection is nevertheless established to a new database.

In this case, if the database does not exist, the getConnection method will throw an exception. Once a connection is established to a database in any mode, JDBC methods are used to interact with the database. The Javadoc for jdbcConnectionjdbcDriverjdbcDatabaseMetadatajdbcResultSetjdbcStatementand jdbcPreparedStatement list all the supported JDBC methods together with information that is specific to HSQLDB.

JDBC methods are broadly divided into: The database access methods use SQL commands to perform actions on the database and return the results either as a Java primitive type or as an instance of the java. You can use Database Manager or other Java database access tools to explore your database and update it with SQL commands. These programs use JDBC internally to submit your commands to the database engine and to display the results in a human readable format.

The SQL dialect used in HSQLDB is as close to the SQL92 and SQLn standards as it has been possible to achieve so far in a small-footprint database engine. The full list of SQL commands is in the SQL Syntax chapter. TEMP tables are not written to disk and last only for the lifetime of the Connection object. The contents of each TEMP table is visible only from the Connection that was used to populate it; other concurrent connections to the database will have access to their own copies of the table.

The definition of the table persists but each new connections sees its own copy of the table, which is empty at the beginning.

When the connection commits, the contents of the table are cleared by default. If the table definition statements includes ON COMMIT PRESERVE ROWS, then the contents are kept when a commit takes place.

Memory tables are the default type when the CREATE TABLE command is used. The script file is read the next time the database is opened, and the MEMORY tables are recreated with all their contents. So unlike TEMP table, the default, MEMORY tables are persistent. CACHED tables are created with the CREATE CACHED TABLE command. Only part of their data or indexes is held in memory, allowing large tables that would otherwise take up to several hundred megabytes of memory.

Another advantage of cached tables is that the database engine takes less time to start up when a cached table is used for large amounts of data. The disadvantage of cached tables is a reduction in speed. Do not use cached tables if your data set is relatively small. In an application with some small tables and some large ones, it is better to use the default, MEMORY mode for the small tables.

TEXT tables are supported since version 1. You can specify an existing CSV file, such as a dump from another database or program, as the source of a TEXT table.

Alternatively, you can specify an empty file to be filled with data by the database engine. TEXT tables are efficient in memory usage as they cache only part of the text data and all of the indexes. The Text table data source can always be reassigned to a different file if necessary. Two commands are needed to set up a TEXT table as detailed in the Text Tables chapter.

With memory-only databases see aboveboth MEMORY table and CACHED table declarations are treated as declarations for non-persistent memory tables. TEXT table declarations are not allowed in this mode.

Binary Search Tree Animation by Y. Daniel Liang

HSQLDB supports PRIMARY KEY, NOT NULL, UNIQUE, CHECK and FOREIGN KEY constraints. In addition, it supports UNIQUE or ordinary indexes. This support is fairly comprehensive and covers multi-column constraints and indexes, plus cascading updates and deletes for foreign keys. HSQLDB creates indexes internally to support PRIMARY KEY, UNIQUE and FOREIGN KEY constraints: Because of this, you should not create duplicate user-defined indexes on the same column sets covered by these constraints.

This would result in unnecessary memory and speed overheads. See the discussion in the SQL Issues chapter for more information. Indexes are crucial for adequate query speed. When queries joining multiple tables are used, there must be an index on each joined column of each table.

When range or equality conditions are used e. Indexes have no effect on ORDER BY clauses or some LIKE conditions. As a rule of thumb, HSQLDB is capable of internal processing of queries at overrows per second. Any query that runs into several seconds should be checked and indexes should be added to the relevant columns of the tables if necessary.

The SQL syntax supported by HSQLDB is essentially that specified by the SQL Standard 92 and n. Not all the features of the Standard are supported and there are some proprietary extensions. The main changes are. The supported commands are listed in the SQL Syntax chapter. For a well written basic guide to SQL with examples you can consult PostgreSQL: Introduction and Concepts by Bruce Momjian, which is available on the web. Most of the SQL coverage in the book applies also to HSQLDB. There are some differences in keywords supported by one and not the other engine OUTER, OID's, etc.

The relevant classes are thoroughly documented. See the JavaDoc for org. Many questions repeatedly asked in Forums and mailing lists are answered in this guide. If you want to use HSQLDB with your application, you should read this guide. This means where a feature of the standard is supported, e. Many features of SQL92 and 99 up to Advanced Level are supported and there is support for most of SQL Foundation and several optional features of this standard.

However, certain features of the Standards are not supported so no claim is made for full support of any level of the standards. The SQL Syntax chapter of this guide SQL Syntax lists all the keywords and syntax that is supported. When writing or converting existing SQL DDL Data Definition Language and DML Data Manipulation Language statements for HSQLDB, you should consult the supported syntax and modify the statements accordingly.

Several words are reserved by the standard and cannot be used as table or column names. For example, the word POSITION is reserved as it is a function defined by the Standards with a similar role as String. HSQLDB does not currently prevent you from using a reserved word if it does not support its use or can distinguish it.

For example BEGIN is a reserved words that is not currently supported by HSQLDB and is allowed as a table or column name. You should avoid the use of such words as future versions of HSQLDB are likely to support the words and will reject your table definitions or queries. The full list of SQL reserved words is in the source of the org. HSQLDB also supports some keywords and expressions that are not part of the SQL standard as enhancements.

Expressions such as SELECT TOP 5 FROM. They are supported by a unique index on the primary key column s specified and no extra hidden column is maintained for these indexes. According to the SQL standards, a unique constraint on a single column means no two values are equal unless one of them is NULL.

This means you can have one or more rows where the column value is NULL. A unique constraint on multiple columns c1, c2, c3. Each single column taken by itself can have repeat values. The following example satisfies a UNIQUE constraint on the two columns:. Column values which satisfy a 2-column UNIQUE constraint. A row, in which the value for any of the UNIQUE constraint columns is NULL, can always be added to the table.

So multiple rows can contain the same values for the UNIQUE columns if one of the values is NULL. You should use a UNIQUE constraint instead. A foreign key can also be specified to reference a target table without naming the target column s.

In this case the primary key column s of the target table is used as the referenced column s. Each pair of referencing and referenced columns in any foreign key should be of identical type.

When a foreign key is declared, a unique constraint or primary key must exist on the referenced columns in the primary key table. A non-unique index is automatically created on the referencing columns. There must be a UNIQUE constraint on columns p1,p2 in the table named "parent". A non-unique index is automatically created on columns c1, c2 in the table named "child".

Columns p1 and c1 must be of the same type INTEGER. Columns p2 and c2 must be of the same type VARCHAR. HSQLDB does not use indexes to improve sorting of query results. But indexes have a crucial role in improving query speed. If no index is used in a query on a single table, such as a DELETE query, then all the rows of the table must be examined. With an index on one of the columns that is in the WHERE clause, it is often possible to start directly from the first candidate row and reduce the number of rows that are examined.

Indexes are even more important in joins between multiple tables. FROM t1 JOIN t2 ON t1. If there is no index index on t2. Whereas with an index, a matching row can be found in a fraction of the time. If the query also has a condition on t1, e. So if t1 and t2 each contain 10, rows, the query without indexes involves checking , row combinations. With an index on t2. With the additional index on t2. Indexes are automatically created for primary key and unique columns.

Otherwise you should define an index using the CREATE INDEX command.

Note that in HSQLDB a unique index on multiple columns can be used internally as a non-unique index on the first column in the list. So you do not need to specify an extra index if you require one on the first column of the list. You need NOT declare additional individual indexes on those columns unless you use queries that search only on a subset of the columns. For example, rows of a table that has a PRIMARY KEY or UNIQUE constraint on three columns or simply an ordinary index on those columns can be found efficiently when values for all three columns are specified in the WHERE clause.

FROM t1 WHERE t1. As a result of the improvements to multiple key indexes, the order of declared columns of the index or constraint has less affect on the speed of searches than before.

If the column that contains more diverse values appears first, the searches will be slightly faster. A multi-column index will not speed up queries on the second or third column only. The first column must be specified in the JOIN. ON or WHERE conditions. Query speed depends a lot on the order of the tables in the JOIN.

ON or FROM clauses. For example the second query below should be faster with large tables provided there is an index on TB. The reason is that TB. COL3 can be evaluated very quickly if it applies to the first table and there is an index on TB. The general rule is to put first the table that has a narrowing condition on one of its columns. An index is added to a view when it is joined to a table or another view.

Using WHERE conditions to join tables is likely to reduce execution speed. For example the following query will generally be slow, even with indexes:. The query implies TA. COL2 but does not explicitly set this condition. If TA and TB each contain rows, combinations will be joined with TC to apply the column conditions, even though there may be indexes on the joined columns.

With the JOIN keyword, the TA. COL2 condition has to be explicit and will narrow down the combination of TA and TB rows before they are joined with TC, resulting in much faster execution with larger tables:. The query can be speeded up a lot more if the order of tables in joins are changed, so that TC.

In the above example the engine automatically applies TC. COL1 will be used if present and will speed up the query. Using joins and setting up the order of tables for maximum performance applies to all areas. For example, the second query below should generally be much faster if there are indexes on TA.

The second query turns MAX TB. COL2 into a single row table then joins it with TA. With an index on TA. COL1this will be very fast. The first query will test each row in TA and evaluate MAX TB. COL2 again and again. Table columns of all types supported by HSQLDB can be indexed and can feature in comparisons. Types can be explicitly converted using the CONVERT library function, but in most cases they are converted automatically.

Previous versions of HSQLDB featured poor handling of arithmetic operations. TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL without a decimal point are supported integral types and map to byte, short, int, long and BigDecimal in Java. The SQL type dictates the maximum and minimum values that can be held in a field of each type.

DECIMAL and NUMERIC are mapped to java. BigDecimal and can have very large numbers of digits. TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL without a decimal point are fully interchangeable internally, and no data narrowing takes place. Depending on the types of the operands, the result of the operations is returned in a JDBC ResultSet in any of related Java types: IntegerLong or BigDecimal.

This type is deterministically based on the query, not on the actual rows returned. The type does not change when the same query that returned one row, returns many rows as a result of adding more data to the tables. If the SELECT statement refers to a simple column or function, then the return type is the type corresponding to the column or the return type of the function. Integer and the second column is java. Long and BigDecimal values, generated as a result of uniform type promotion for all the return values.

There is no built-in limit on the size of intermediate integral values in expressions. As a result, you should check for the type of the ResultSet column and choose an appropriate getXXXX method to retrieve it. Alternatively, you can use the getObject method, then cast the result to java. Number and use the intValue or longValue methods on the result. When the result of an expression is stored in a column of a database table, it has to fit in the target column, otherwise an error is returned.

In SQL statements, numbers with a decimal point are treated as DECIMAL unless they are written with an exponent. When a REAL, FLOAT or DOUBLE all synonymous is part of an expression, the type of the result is DOUBLE. Otherwise, when no DOUBLE value exists, if a DECIMAL or NUMERIC value is part an expression, the type of the result is DECIMAL. The result can be retrieved from a ResultSet in the required type so long as it can be represented.

This means DECIMAL values can be converted to DOUBLE unless they are beyond the Double. Similar to integral values, when the result of an expression is stored in a table column, it has to fit in the target column, otherwise an error is returned. The distinction between DOUBLE and DECIMAL is important when a division takes place. When the terms are DECIMAL, the result is a value with a scale number of digits to the right of the decimal point equal to the larger of the scales of the two terms.

With a DOUBLE term, the scale will reflect the actual result of the operation. Without division operations, DECIMAL values represent exact arithmetic; the resulting scale is the sum of the scales of the two terms when multiplication is performed. REAL, FLOAT and DOUBLE values are all stored in the database as java. These values can be submitted to the database via JDBC PreparedStatement methods and are returned in ResultSet objects.

The primary representation of BOOLEAN column is 'true' or 'false' either as the boolean type or as strings when used from JDBC. This type of column can also be initialised using values of any numeric type. In this case 0 is translated to false and any other value such as 1 is translated to true. NULL values are treated as undefined. This improvement affects queries that contain NOT IN. See the test text file, TestSelfNot. For comparison purposes and in indexes, any two Java Objects are considered equal unless one of them is NULL.

You cannot search for a specific object or perform a join on a column of type OTHER. Please note that HSQLDB is not an object-relational database.

Java Objects can simply be stored internally and no operations should be performed on them other than assignment between columns of type OTHER or tests for NULL. But WHERE object1 IS NOT NULL is perfectly acceptable. So please use columns of type OTHER only to store your objects and nothing else. For example INTEGER 8 is no longer acceptable. The qualifiers are still ignored unless you set a database property.

The precision and scale qualifiers are also enforced for DECIMAL and NUMERIC types. TIMESTAMP can be used with a precision of 0 or 6 only. Casting a value to a qualified CHARACTER type will result in truncation or padding as you would expect. The SEQUENCE keyword was introduced in 1.

Corresponding SQL n syntax for IDENTITY columns has also been introduced. Each table can contain one auto-increment column, known as the IDENTITY column. An IDENTITY column is always treated as the primary key for the table as a result, multi-column primary keys are not possible with an IDENTITY column present. Support has also been added for BIGINT identity columns.

As a result, an IDENTITY column is simply an INTEGER or BIGINT column with its default value generated by a sequence generator. The IDENTITY function returns the last value inserted into any IDENTITY column by this connection.

Use CALL IDENTITY ; as an SQL statement to retrieve this value. Both types of call to IDENTITY must be made before any additional update or insert statements are issued on the database. The SQL n syntax and usage is different from what is supported by many existing database engines.

Sequences are created with the CREATE SEQUENCE command and their current value can be modified at any time with ALTER SEQUENCE. This expression can be used for inserting and updating table rows. You can also use it in select statements. For example, if you want to number the returned rows of a SELECT in sequential order, you can use:.

Numbering returned rows of a SELECT in sequential order. Please note that the semantics of sequences is not exactly the same as defined by SQL n. For example if you use the same sequence twice in the same row insert query, you will get two different values, not the same value as required by the standard. This means that during the lifetime of a transaction, other connections to the database can see the changes made to the data.

Transaction support works well in general. Reported bugs concerning transactions being committed if the database is abruptly closed have been fixed. However, the following issues may be encountered only with multiple connections to a database using transactions:. If two transactions modify the same row, no exception is raised when both transactions are committed. This can be avoided by designing your database in such a way that application data consistency does not depend on exclusive modification of data by one transaction.

You can set a database property to cause an exception when this happens. INSERT COLUMN or DROP COLUMN command results in changes to the table structure, the current session is committed. If an uncommitted transaction started by another connections has changed the data in the affected table, it may not be possible to roll it back after the ALTER TABLE command.

It is recommended to use these ALTER commands only when it is known that other connections are not using transactions. In recent versions leading to 1. These are listed in the SQL Syntax chapter, in.

Functions and expressions such as POSITIONSUBSTRINGNULLIFCOALESCECASE ELSE, ANY, ALL etc. Other enhancements may not be very obvious in the documentation but can result in changes of behaviour from previous versions. Most significant among these are handling of NULL values in joins null columns are no longer joined and OUTER joins the results are now correct.

You should test your applications with the new version to ensure they do not rely on past incorrect behaviour of the engine. The engine will continue to evolve in future versions towards full SQL standard support, so it is best not to rely on any non-standard feature of the current version. This chapter explains how to quickly install, run, and use HSQLDB on UNIX. HSQLDB has lots of great optional features. I intend to cover very few ib report on stock market them.

I do intend to cover what I think is the most common UNIX setup: To run a multi-user database with permament data persistence. By the latter I mean that data is stored to disk so that the data will persist across database shutdowns and startups. I also cover how to run HSQLDB as a system daemon. You want the current version. This will be the highest numbered version under the plain black "hsqldb" heading.

See if there's a distribution for the current HSQLDB version in the format that you want. If you want an rpm, you should still find out the current version of HSQLDB as described in the previous paragraph.

Then click "hsqldb" in the "free section" of http: Hopefully, the JPackage folk will document what JVM versions their rpm will support currently they document this neither on their site nor within the package itself. I really can't document how to download from a site that is totally beyond my control. It could very well happen that some of the file formats which I discuss below are not in fact offered.

If so, then we have not gotten around to building them. This package is only for use by a Solaris super-user. It's a System V package. Download then uncompress the package with uncompress or gunzip uncompress filename.

Z You can read about the package by running pkginfo -l -d filename. This is a Linux rpm package. Suse users may want to keep Yast aware of installed packages by running rpm through Yast: Extract the zip file to the parent directory of the new HSQLDB home.

All the files in the zip archive will be extracted to underneath a new hsqldb directory. Take a look at the files you installed. Under hsqldb for zip file installations. Otherwise, use the utilities for forex review broker packaging system. The most important file of the hsqldb system is hsqldb. If the description of your distribution says that the hsqldb. Otherwise you need to build a new hsqldb.

If you followed the instructions above and you still don't know what Java version your hsqldb. If how to make origami flower using money still doesn't help, then you can just try your hsqldb. To use the supplied hsqldb. Otherwise build a new hsqldb.

If you don't already have Ant, download the latest stable binary version from http: You can rename the directory after the extraction if you wish. Make sure that the bin directory under your Ant home is in your search path. Run the following command. See the Building HSQLDB appendix if you want to build anything other than hsqldb. If you installed from an OS-specific package, you may already have a database instance and server pre-configured.

See if your package includes a file named server. If you do, then I suggest that you still read this section while how to report stock options exercised poke around, in order to understand your setup. Select a UNIX user to run the database as. If this database is for the use of multiple users, or is a production system or to emulate a production systemyou should dedicate a UNIX user for this purpose.

In my trading strategies ebook, I use the user name hsqldb.

If the account doesn't exist, then create it. Since the value of the first database server. Set the path to whatever you want relative paths will be relative to the directory containing the properties file. You can read about how to specify other database instances of various types, and how to make settings for the listen port and many other things, in the Option pricing simplified approach Topics chapter.

This will start the Server process in the background, and will create your new database instance "db0". Continue on when you see the message containing HSQLDB server We will be using the "localhost-sa" sample urlid definition from the config file.

The JDBC URL for this urlid is jdbc: That is the URL for binary option strategies 4 adoption default database instance of a HSQLDB Server running on the default port of the local host. You can read about URLs to connect to other instances and other servers in the Advanced Topics chapter. If security is of any concern to you at all, then you should change the privileged password in the database.

Use the command SET PASSWORD command to change SA's password. If you changed the SA password, then you need to fix the password in the sqltool. You can, of course, also access the database with any JDBC client program. See the First JDBC Client Example appendix. You will need to modify your classpath to include hsqldb.

You can also use the other HSQLDB client programs, such as org. DatabasManagerSwinga graphical client with a similar purpose to SqlTool. You can use any normal UNIX account to run the JDBC clients, including SqlToolas long as the account has read access to the hsqldb. See the SqlTool chapter about where to put sqltool. Connect to the database as SA or any other Administrative user and run CREATE USER to create new accounts for your database instance. HSQLDB accounts are database-instance-specific, not Server -specific.

For the current version of HSQLDB, only users with Role of DBA may create or own database objects. DBA members have privileges to do anything. Non-DBAs may be granted some privileges, but may never create or own database objects. Before long, non-DBAs will be able to create objects if they have permission to do so in the target schema. When you first create a hsqldb database, it has only one database user-- SA, a DBA account, with an empty string password.

You should set a password as described above. You can create as many additional users as you wish. To make a user a DBA, you can use the "ADMIN" option to the CREATE USER command, or GRANT the DBA Role to the account after creating it. If you create a user without the ADMIN tag and without granting the DBA role to them this user will be able to read the data dictionary tables, but will be able unable to create or own his own objects.

He will have only the rights which the pseudo-user PUBLIC has. To give him more permissions, even rights to read objects, you can GRANT permissions for specific objects, grant Roles which encompass a set of permissionsor grant the DBA Role itself. Since only people with a database account may do anything at all with the database, it is often useful to permit other database users to view the data in your tables. To optimize performance, reduce contention, and minimize administration, it is often best to grant SELECT to PUBLIC on any object that needs to be accessed by multiple database users with the significant exception of any data which you want to keep secret.

Do a clean database shutdown when you are finished with the database instance. You need to connect up as SA or some other Admin user, of course. You can, of course, run HSQLDB through inittab on System V UNIXes, but usually an init script 60 second binary options system online trading strategies more convenient and manageable.

This section explains how to set up and use our UNIX init script. Our init script is only for use by root. That is not to say that the Server will run as root-- it usually should not. The main purpose of the init script is to start up a Server with the database instances specified in your server. These urlids must all have entries in a sqltool. If, due to firewall issues, you want to run a WebServer instead of a Server, then make sure you have a healthy WebServer with a webserver. After you have the init script set up, root can use it anytime maverick fx trading review start or stop HSQLDB.

The primary design criterion of the init script is portability. Offsetting these limitations, this one script does it's intended job great on the UNIX varieties I have tested, and can easily be modified to accommodate other UNIXes. While you don't have tight integration with OS-specific daemon administration guis, etc.

After that's working, you can customize the JVM that is run by running additional Servers in it, weizmann forex ltd baroda your own application in it embeddingor even overriding HSQLDB behavior with your own overriding classes. Look at the comment towards the top of the init collapse stock market 1929 which lists recommended locations for the configuration file for various UNIX platforms.

Edit the config file according to the instructions in it. If you copy the file, make sure to use chmod to restrict permissions on the new copy. The init script now enforces permissions on this file. X that you have defined, set a property of name server. X to the urlid for an Administrative user for that database instance. Make sure to add a urlid for each and every database instance. If you don't then the init script will never know about databases that become inaccessible and will give false diagnostics.

For this example, you would need to define the urlid localhostdb1 in your sqltool. Verify that the init script works. Notice that you can run. Re-run the script with each of the possible arguments to really test it good. If anything doesn't work right, then see the Troubleshooting the Init Script section. Tell your OS to run the init script upon system startup and shutdown.

For good UNIXes that use System V style init, you must set up hard links or soft links either manually or with management tools such as chkconfig or insserv or Gui's like run level editors. This paragraph is for Mac OS X users only.

Now copy the file StartupParameters. See the man page for SystemStarter. Hard to believe, but the Mac people tell me that during system shutdown the Startup Items don't run at all.

Therefore, if you don't want your data corrupted, make sure to run "SystemStarter stop Hsqldb" before shutting down your Mac. Follow the examples in the config file to add additional classes to the server JVM's classpath and to execute additional classes in your JVM. Do a ps to look for processes containing the string hsqldband try to connect to the database from any client.

If the init script starts up your database successfully, but incorrectly reports that it has not, then your problem is with specification of urlid s or SqlTool setup. If your database really did not start, then skip to the next paragraph. Verify that the urlid s listed in the server. If your database really is not starting, then verify that you can su to the database owner account and start the database.

The command su USERNAME -c Therefore, if you try to tighten up security by disabling this user's login shell, you will break the init script. If these possibilities don't pan out, then debug the init script or seek help, as described below. To debug the init script, run it in verbose mode to see exactly what is happening and perhaps manually run the steps that are suspect. This document covers system related issues. For issues related to SQL see the SQL Issues chapter.

The normal method of accessing an HSQLDB database is via the JDBC Connection interface. An introduction to different methods of providing database services and accessing them can be found in the SQL Issues chapter. Details and examples of how to connect via JDBC are provided in our JavaDoc for jdbcConnection. The common driver identifier is jdbc: 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: The file path specifies the database file. In the above examples the first one refers to a set of mydb. The second and third examples refer to absolute paths on the host machine.

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.

The following example lines in server. The old form for the server URL, e. In the example below, the database files lists. Each new JDBC Connection to a database can specify connection properties. The properties user and password are always required. This property is used for compatibility with other JDBC driver implementations. When true the defaultResultSet. When false, the above method returns the same value as ResultSet.

When a ResultSet is used inside a user-defined stored procedure, the default, true, is always used for this property. Has an effect only with mem: When true, will not create a new database if one does not already exist for the URL. When false the defaulta new mem: Setting the property to true is useful when troubleshooting as no database is created if the URL is malformed. This mimics the behaviour of 1. 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 digimon masters online best way to get money if used with subsequent, simultaneous 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 conisides with the web currency traders await uk growth forecasts being shut down.

In addition, when a connection to an in-process database creates a new database, or opens an existing database i. However, for new databases, it is recommended to use the SET PROPERTY command for such settings.

HSQLDB relies on a set of properties files for different settings. In all properties files, values are case-sensitive. All values apart from names of files or pages are required in lowercase e. Properties files for running the servers are not created automatically. You should create your own files that contain server. The properties file for each database is generated by the database engine. This file can be edited after closing the database.

The digit 0 is incremented for the second database and so on. Values for the server. All the above values can be specified on the command line to start the server by omitting the server. If you want to start the server from within your application, as opposed to the command line or batch files, you should create an instance of Server or Web Server, then assign the properties in the form of a String and start the Server.

An example of this can be found in the org. If you have existing custom properties files, change the values to the new naming convention. Note the use of digits at the best way to make money with scrap metal of server. Properties that can be modified via SET PROPERTY are indicated in the table below. Only the user-defined values listed below should ever be modified.

Changing any other value could result in unexpected malfunction in database operations. Most of these values have been introduced for the new features since 1. When true, the database cannot be modified in use.

This setting can be changed to true if the database is to be opened from a CD. Prior to changing this setting, the database range oscillator forex indicator be closed with the SHUTDOWN COMPACT command to ensure consistency and compactness of the data.

When true, data in MEMORY tables can be modified and new MEMORY tables can be added. However, these changes are not saved when the database is shutdown. CACHED and TEXT tables are always readonly when this setting is true.

This property can be set to 8 to increase the size limit of the. The property can be set with the SQL command as opposed to changing the value in the properties file when the database has no CACHED tables e. 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. TIMESTAMP 0 and TIMESTAMP 6 are also allowed in order to specify the subsecond resolution of the values. When false defaultstores the exact string that is inserted. Setting this property to true will raise an exception when such a write is attempted SET PROPERTY. The value can range between This result value is multiplied by the maximum number of rows defined by hsqldb.

The default results in bytes per row. This default, combined with the default number of rows, results in approximately 50MB of the. The value is the size in megabytes that the. A checkpoint and rewrites the. The value can be changed via the SET LOGSIZE nnn SQL command. 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. This should not be set when the database engine is acting as a server inside an exclusive JVM. The setting can be useful when the database is used in-process with the application with some Java Runtime Environments JRE's.

Some JRE's increase the size of the memory heap before doing any automatic garbage collection.

This setting would prevent indikator forex gratis akurat unnecessary enlargement of the heap. Typical values for this setting would probably be between 10, toWhen HSQLDB is compiled m91/30 replacement stock run in Java 1.

If the data file is larger than MB 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. The CREATE TABLE command binary search tree applet 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.

The default level 0 indicates no logging. Level 1 results in events related to persistence to be logged, including any failures. The events are logged in a file ending with. Properties that override the database engine defaults for newly created text tables.

When connecting to an in-process database creates a new database, or opens an existing database i. All files belonging to a database should reside in the same directory. If the line exists in a. See the SET DATABASE COLLATION [ 2 ] command.

When HSQLDB is used in OpenOffice. The properties and values are:. There are some database properties that are set with dedicated SQL commands beginning with SET. The default is TRUE and indicates that the changes to the database that have been logged are synched to the file system once every 20 seconds. FALSE indicates there is no delay and at how to make origami flower using money commit a file synch operation is performed.

Numeric values from 0 can also be specified for the synch delay. The purpose of this command is to control the amount of data loss in case of a total system crash. A delay of 1 second means at most the data written to disk during the last second before the crash is lost. All data written prior to this has been synced and should be recoverable.

This setting should be specified on the basis of the reliability of the hardware used for running the database engine, the type of disk system used, the possibility of binary search tree applet failure etc. Also the nature of the data stored should be considered. In general, when the system is very reliable, the setting can be left to the default.

If it is not very reliable, or the data is critical a setting of 1 or 2 seconds would suffice. Only in the worst case boxing day sales victoria secret canada or with the most critical data should a setting of 0 or FALSE be specified as this will slow the engine down to the speed at which the file synch operation can be performed by the disk subsystem.

Values down to 10 millisconds can be specified by adding MILLIS to the command, but in practice a delay of milliseconds provides The engine writes out a log of all the changes to the database as they occur. The log is never reused unless there is an abnormal termination, i.

The default maximum size of the. This operation will save the other database files in a consistent state and delete the old log. A value of 0 indicates no limit for the. Should i buy merryweather stock gta rows in CACHED tables are updated or deleted, the spaces are mostly reused.

However, in time, some unused spaces are left in the. The numeric value is the number of megabytes of recorded empty spaces in the. Low values result in more frequent DEFRAG operations. A value of 0 indicates no automatic DEFRAG is performed. The default is megabytes of lost space. This is TRUE by default. If bulk data needs to be loaded into the database, this property can be set FALSE for the duration of bulk load operation. This allows loading data for related tables in any order.

The property should be set TRUE after bulk load. If the loaded data is not guaranteed to conform to the referential integrity constraints, SQL queries should be run after loading to identify and modify any non-conforming rows.

HSQLDB has many modes of operation and features that allow it to be used in very different scenarios. Levels of memory usage, speed and accessibility by different applications are influenced by how HSQLDB is deployed. The decision to run HSQLDB as a separate server process or as an in-process database should be based on the following:.

When HSQLDB is run as a server on a separate machine, it is isolated from hardware failures and crashes on the hosts running the application. When HSQLDB is run as a server on the same machine, it is isolated from application crashes and memory leaks.

Server connections are slower than in-process connections due to the overhead of streaming the data for each JDBC call. TEXT tables are designed for special applications where the data has to be in an interchangeable format, such as CSV. TEXT tables should not be used for routine storage of data. MEMORY tables and CACHED tables are generally used for data storage. The difference between the two is as follows:.

The data for all MEMORY tables is read from the. In contrast the data for cached tables is not read into memory until the table is accessed. Furthermore, only part of the data for each CACHED table is held in memory, allowing tables with more data than can be held in memory.

When the database is shutdown in the normal way, all the data for MEMORY tables is written out to the disk. In comparison, the data in CACHED tables that has changed is written out at shutdown, plus a compressed backup of all the data in all cached tables. The size and capacity of the data cache for all the CACHED tables is configurable. This makes it earn cash surfing to allow all the data in CACHED tables to be cached in memory.

In this case, speed of access is good, but slightly slower than MEMORY tables. For normal applications it is recommended that MEMORY tables are used for small amounts of data, leaving CACHED tables for large data sets. For special applications in which speed vega of a go options binary options paramount and a large amount of free memory is available, MEMORY tables can be used for large tables as well.

The first table to contain the normal fields and the second table to contain the large object plus an identity field. Using this method has two benefits. An example of two tables and a select query that exploits the separation between the two follows:.

The inner SELECT finds the required rows without reference to the LOBTABLE and when it has found all the rows, retrieves the required large objects from the LOBTABLE. The files used for storing HSQLDB database data are all in the same directory. New files are always created and deleted by the database engine. Two simple principles must be observed:. The Java process running HSQLDB must have full privileges on the directory where the files are stored. This include create and delete privileges.

The file system must have enough spare room both for the 'permanent' and 'temporary' files. The temporary file created at the time of a SHUTDOWN COMPACT can be equal in size to the. Memory used by the program can be thought of as two distinct pools: In addition, when transactions are used, memory is utilised for storing the information needed for a rollback.

The memory used for a MEMORY table is the sum pakistan forex market memory used by each row. Each MEMORY table row is a Java object that has 2 int or reference variables. It contains an array of objects for the fields in the earnest money contract real estate form. Each field is an object such as IntegerLongStringetc.

In addition each index on the table adds a node object to the row. Each node object has 6 int or reference variables. As a result, a table with just one column of type INTEGER will have four objects per row, with a total of 10 variables of 4 bytes each - currently taking up 80 bytes per row.

Beyond this, each extra column in the table adds at least a few broker forex forex forex forex info knowforex.info trading trading to the size of each row. The memory used for a result set row has fewer overheads fewer variables and no index nodes but still uses a lot of memory.

All the rows in the result set are built in memory, so very large result sets may not be possible. In server mode databases, the result set memory is released from the server once the database server has returned the result set.

In-process databases release the memory when the application program how to make robux on roblox fast the java.

Binary Search Tree

Server modes require additional memory for returning result sets, as they convert the full result set into an array of bytes which is then transmitted to the client. When UPDATE and DELETE queries are performed on CACHED tables, the full set of rows that are affected, including those affected due to ON UPDATE actions, is held in memory for the duration of the operation. This means it may not be possible to perform deletes or updates involving very large numbers of rows of CACHED tables.

Such operations should be performed in smaller sets. Transactions that span hundreds of modification to data will take up a lot of memory until the next COMMIT or ROLLBACK clears the list.

Most JVM implementations allocate up to a maximum amount of memory usually 64 MB by default. This amount is generally not adequate when large memory tables are used, or when the average size of rows in cached tables is larger than a few hundred bytes. The maximum amount of allocated memory can be set on the java For example, with Sun JVM version 1. In most circumstances, this reduces the memory footprint still further as fewer copies of the most frequently-used objects are kept in memory.

With CACHED tables, the data is stored on disk and only up to a maximum number of rows are held in memory at any time. As any random subset of the rows in any of the CACHED tables can be held in the cache, the amount of memory needed by cached rows can reach the sum of the rows containing the largest field data.

For example if a table withrows contains 40, rows with 1, bytes of data in each row and 60, rows with bytes in each, the cache can grow to contain nearly 50, rows, including all the 40, larger rows. An additional property, hsqldb. This puts a limit in bytes on the total size of rows that are cached. When the default values is used for both properties, the limit on the total size of rows is approximately 50MB.

This is the size of binary images of the rows and indexes. It translates to more actual memory, typically times, used for the cache because the data is represented by Java objects. If memory is limited, the hsqldb. In the example above, if the hsqldb. This will allow the number of cached rows to reach 50, small rows, but only 12, of the larger rows. In all running modes server or in-process multiple connections to the database engine are supported.

In-process standalone mode supports connections from the client in the same Java Virtual Machine, while server modes support connections over the network from several different clients. Connection number of nyse trading days per year software can be used to connect to the database but it is not generally necessary.

With other database engines, connection pools are used for reasons that may not apply to HSQLDB. To allow new queries to be performed while a time-consuming query is being performed in the background. This is not possible with HSQLDB 1. This capability is under development and will be introduced in a future version. To limit the maximum number of simultaneous connections to the database for performance reasons.

With HSQLDB this can be useful only if your application is designed in a way that opens and closes connections for each small task. To control transactions in a multi-threaded application. This can be useful with HSQLDB as well. For example, in a web application, a transaction may involve some processing between the queries or user action across web pages. A separate connection should be used for each HTTP session so that the work can be committed when completed or rolled back otherwise.

NO TITLE

Although this usage cannot be applied to most other database engines, HSQLDB is perfectly capable of handling over simultaneous HTTP sessions as individual JDBC connections. An application that is not should i buy a timeshare in aruba multi-threaded and transactional, such as an application for recording user login and logout actions, does not need more than one connection.

The connection can stay open indefinitely and reopened only when it is dropped due to network problems. When using an in-process database with versions prior to 1. This is not necessary since 1. An explicit SHUTDOWN command, with or fact about the stock market crash an argument, is required to close the database.

When using a server database and to some extent, an in-process databasecare must be taken to avoid creating and dropping JDBC Connections too frequently. Failure to observe this will result in unsuccessful connection attempts when the application is under heavy load. Any database not produced with the release version of HSQLDB 1. This includes databases created with the RC versions of 1. The instructions under the Upgrading Using the SCRIPT Command section should be followed in all cases.

Once a database is upgraded to 1. There may be some potential legacy issues in the upgrade which should be resolved by editing the. To upgrade from 1. The upgrade is then complete. To upgrade from older version database files 1. If there is any error in the. In all versions of HSQLDB and Hypersonic 1.

You can export a script file using the old version of the database engine and open the script as a database with 1. Issue the SCRIPT command, for example SCRIPT 'newversion. If there is any inconsistency in the data, the script line number is reported on the console and the opening process is aborted.

Edit and correct any problems in the newversion. Use the guidelines in the next section Manual Changes to the. Use a programming editor that is capable of handling very large files and does not wrap long lines of text. However, if an old database cannot be opened due to data inconsistencies, or the use of index or column names that are not compatible with 1.

The following changes can be applied so long as they do not affect the integrity of existing data. A unique index can always be converted into a normal index. A non-unique index can only be converted into a unique index if the table data for the column s is unique in each row. A not-null constraint can always be removed. It can only be added if the table data for the column has no null values. A primary key constraint can be removed or added. It cannot be removed if there is a foreign key referencing the column s.

Some changes to column types are possible. For example an INTEGER column can be changed to BIGINT, or DATE, TIME and TIMESTAMP columns can be changed to VARCHAR.

The data for each database consists of up to 5 files in the same directory. These should be backed up together. Normal backup methods, such as archiving the files in a compressed bundle can be used. Copyright Bob Preston and Fred Toussi. Text Table support for HSQLDB was originally developed by Bob Preston independently from the Project. Subsequently Bob joined the Project and incorporated this feature into version 1. In a nutshell, Text Tables are CSV or other delimited files treated as SQL tables.

Any ordinary CSV or other delimited file can be used. The full range of SQL queries can be performed on these files, including SELECT, INSERT, UPDATE and DELETE. Indexes and unique constraints can be set up, and foreign key constraints can be used to enforce referential integrity between Text Tables themselves or with conventional tables. HSQLDB with Text Table support is the only comprehensive solution that employs the power of SQL and the universal reach of JDBC to handle data stored in text files and will have wide-ranging use way beyond the currently established Java realm of HSQLDB.

We aimed to finalise the DDL for Text Tables so that future releases of HSQLDB use the same DDL scripts. We aimed to support Text Tables as GLOBAL TEMPORARY or GLOBAL BASE tables in the SQL domain. In addition, a SET command specifies the file and the separator character that the Text table uses:. Text Tables cannot be created in memory-only databases databases that have no script file.

A Temporary Text table has the scope and the lifetime of the SQL session a JDBC Connection. Reassigning a Text Table definition to a new file has implications in the following areas:. Constraints, including foreign keys referencing this table, are kept intact.

It is the responsibility of the administrator to ensure their integrity. At this point any violation of NOT NULL, UNIQUE or PRIMARY KEY constrainst are caught and the assignment is aborted.

However, foreign key constraints are not checked at the time of assignment or reassignment of the source file. Empty fields are treated as NULL. These are fields where there is nothing or just spaces between the separators. The default field separator is a comma. A different field separator can be specified within the SET TABLE SOURCE statement.

For example, to change the field separator for the table mytable to a vertical bar, place the following in the SET TABLE SOURCE statement, for example:.

The following example shows how to change the default separator to the pipeVARCHAR separator to the period. Place the following within the SET TABLE SOURCE statement, for example:. Furthermore, HSQLDB provides csv file support with three additional boolean options: This option is used when the first line of the file contains column headings. The quoted option default true uses quotes only when necessary to distinguish a field that contains the separator character.

It can be set to false to prevent the use of quoting altogether and treat quote characters as normal characters. These options may be specified within the SET TABLE SOURCE statement:. The quote character is doubled when used inside a string. With this option, it is not possible to insert any string containing the separator into the table, as it would become impossible to distinguish from a separator.

While reading an existing data source file, the program treats each individual field separately. It determines that a field is quoted only if the first character is the quote character.

It interprets the rest of the field on this basis. The character encoding for the source file is ASCII by default. To support UNICODE or source files preprared with different encodings this can be changed to UTF-8 or any other encoding.

Finally, HSQLDB provides the ability to read a text file from the bottom up and making them READ ONLY, by placing the keyword "DESC" at the end of the SET TABLE SOURCE statement:. This feature provides functionality similar to the Unix tail command, by re-reading the file each time a select is executed.

Using this feature sets the table to read-only mode. Text table source files are cached in memory. The maximum number of rows of data that are in memory at any time is controlled by the textdb.

The default value for textdb. The property can also be set for individual text tables:. Text tables may be disconnected from their underlying data source, i. You can explicitly disconnect a text table from its file by issuing the following statement: SET TABLE mytable SOURCE OFF. Subsequently, mytable will be empty and read-only. However, the data source description will be preserved, and the table can be re-connected to it with SET TABLE mytable SOURCE ON.

When a database is opened, if the source file for an existing text table is missing the table remains disconnected from its data source, but the source sescription is preserved. This allows the missing source file to be added to the directory and the table re-connected to it with the above command. File locations are restricted to below the directory that contains the database, unless the textdb.

The table name is converted into the file name by replacing all the non-alphanumeric characters with the underscore character, conversion into lowercase, and adding the ".

An existing table source file may include CHARACTER fields that do not begin with the quote character but contain instances of the quote character.

These fields are read as literal strings. Alternatively, if any field begins with the quote character, then it is interpreted as a quoted string that should end with the quote character and any instances of the quote character within the string is doubled. When any field containing the quote character or the separator is written out to the source file by the program, the field is enclosed in quote character and any instance of the quote character inside the field is doubled.

Inserts or updates of CHARACTER type field values are allowed with strings that contains the linefeed or the carriage return character. ALTER TABLE commands that add or drop columns are not supported with non-empty text tables. This is a working SQL file which imports a pipe-delimited text file from the database's file directory into an existing normal table.

You can edit a copy of this file and use it directly with SqlToolor you can use the SQL therein as a model using any SQL client at all. The instructions in this document are liable to change at any time.

In particular, we will be changing the method to supply the server-side certificate password. This is probably possible with IBM's Java, but I don't think anybody has attempted to run HSQLDB with TLS under IBM's Java, and I'm sure that nobody in the HSQLDB Development Group has documented how to set up the environment.

Client-side users will not be able to use the https: JDBC protocol because the https protocol handler is not implemented in 2. A JKS keystore containing a private keyin order to run a server. If you are running the server side, then you'll need to run a HSQLDB Server or WebServer. It doesn't matter if the underlying database instances are new, and it doesn't matter if you are making a new Server configuration or encrypting an existing Server configuration.

You can turn encryption on and off at will. You need a HSQLDB jar file that was built with JSSE present. If you got your HSQLDB 1. If you build your own jar file with Java 1. At this time, the latter will only work for clients running with Java 1.

If the server you wish to connect to is using a certificate approved by your default trust keystores, then there is nothing else to do. If not, then you need to tell Java to "trust" the server cert. It's a slight over-simplification to say that if the server certificate was purchased, then you are all set; if somebody "signed their own" certificate by self-signing or using a private ca certificate, then you need to set up trust.

First, you need to obtain the cert only the "public" part of it. Since in most cases, if you want to trust a non-commercial cert, you probably have access to the server keystore, I'll show an example of how to get what you need from the server-side JKS keystore. You may already have an X cert for your server. If you have a server keystore, then you can generate a X cert like this. Exporting certificate from the server's keystore. Now, you need to add this cert to one of the system trust keystores or to a keystore of your own.

See the Customizing Stores section in JSSERefGuide. You can put private keystores anywhere you want to. The following command will add the cert to an existing keystore, or create a new keystore if client. Unless your OS can't stop other people from writing to your files, you probably do not want to set a password on the trust keystore.

If you added the cert to a system trust store, then you are finished. Otherwise you will need to specify your custom trust keystore to your client program. The generic way to set the trust keystore is to set the sytem property javax. Specifying your own trust store to a JDBC client. The hostname in your database URL must match the Common Name of the server's certificate exactly. That means that if a site certificate is admc.

If you want more details on anything, see JSSERefGuide. Get yourself a JKS keystore containing a private key. Then set the system property javax. In this example, I'm using a server. Specifying a password on the command-line is definitely not secure. It's really only appropriate when untrusted users do not have any access to your computer. If there is any user demand, we will have a more secure way to supply the password before long.

If you are running Java 4. If you agree to the terms and meet the requirements, download the domestic or global JSSE software. All you need from the software distro is the three jar files. There are two main ways to do this. Either you can use a certificate signed by a certificate authority, or you can make your own. One thing that you need to know in both cases is, the Common Name of the cert has to be the exact hostname that JDBC clients will use in their database URL.

I'm not going to tell you how to get a CA-signed SSL certificate. That is well documented at many other places. Assuming that you have a standard pem-style private key certificate, here's how you can use openssl and the program DERImport to get it into a JKS keystore. Because I have spent a lot of time on this document already, I am just giving you an example. Make sure to set the password of the key exactly the same as the password for the keystore! You need the program DERImport. Do some internet searches to find DERImport.

If DERImport has become difficult to obtain, I can write a program to do the same thing-- just let me know. Run man keytool or see the Creating a Keystore section of JSSERefGuide.

If you are using a private server certificate, make sure to also set the trust store filepath as shown in the sample init script configuration file. The cautionary warning above still applies. The password will be visible to any minimally competent local UNIX user who wants to see it. This document explains how to use SqlTool, the main purpose of which is to read your SQL text file or stdin, and execute the SQL commands therein against a JDBC database.

There are also a great number of features to facilitate both interactive use and automation. The following paragraphs explain in a general way why SqlTool is better than any existing tool for text-mode interactive SQL work, and for automated SQL tasks. Two important benefits which SqlTool shares with other pure Java JDBC tools is that users can use a consistent interface and syntax to interact with a huge variety of databases-- any database which supports JDBC; plus the tool itself runs on any Java platform.

As far as I know, SqlTool is the only production-ready, pure Java, command-line, generic JDBC client. Several databases come with a command-line client with limited JDBC abilities usually designed for use with their specific database.

SqlTool is purposefully not a Gui tool like Toad or DatabaseManager. There are many use cases where a Gui SQL tool would be better. Where automation is involved in any way, you really need a text client to at least test things properly and usually to prototype and try things out.

A command-line tool is really better for executing SQL scripts, any form of automation, direct-to-file fetching, and remote client usage. To clarify this last, if you have to do your SQL client work on a work server on the other side of a VPN connection, you will quickly appreciate the speed difference between text data transmission and graphical data transmission, even if using VNC or Remote Console. Another case would be where you are doing some repetitive or very structured work where variables or language features would be useful.

SqlTool starts up very quickly, and it takes up a tiny fraction of the RAM required to run a comparably complex Gui like Toad. SqlTool is superior for interactive use because over many years it has evolved lots of features proven to be efficient for day-to-day use. Unlike server-side language features, the same feature set works for any database server.

algorithm - How do you validate a binary search tree? - Stack Overflow

Database access details may be supplied on the command line, but day-to-day users will want to centralize JDBC connection details into a single, protected RC file. You can put connection details username, password, URL, and other optional settings for scores of target databases into your RC file, then connect to any of them whenever you want by just giving SqlTool the ID "urlid" for that database.

When you Execute SqlTool interactively, it behaves by default exactly as you would want it to. If errors occur, you are given specific error messages and you can decide whether to roll back your session. You can easily change this behavior to auto-commit, exit-upon-error, etc. You can import or export delimiter-separated-value files. When you Execute SqlTool with a SQL script, it behaves by default exactly as you would want it to.

If any error is encountered, the connection will be rolled back, then SqlTool will exit with an error exit value. If you wish, you can detect and handle error or other conditions yourself. For scripts expected to produce errors like many scripts provided by database vendorsyou can have SqlTool continue-upon-error. For SQL script-writers, you will have access to portable scripting features which you've had to live without until now.

You can use variables set on the command line or in your script. You can handle specific errors based on the output of SQL commands or of your variables. You can chain SQL scripts, invoke external programs, dump data to files, use prepared statements, Finally, you have a procedural language with ifforeachwhilecontinueand break statements.

SqlTool runs on any Java 1. I haven't run it with a non-Sun JVM in years like Blackdown, IBM, JRockit, etc. Some of the examples below use quoting which works exactly as-is for any Bourne-compatible UNIX shell. Only line-continuation would need to be changed for C-compatible UNIX shells.

I have not yet tested these commands on Windows, and I doubt whether the quoting will work just like this though it is possible. SqlTool is still a very useful tool even if you have no quoting capability at all.

If you are using SqlTool from a HSQLDB distribution before version 1. This document is now updated for the current versions of SqlTool and SqlFile at the time I am writing this versions and correspondingly, SqlFile is the class which does most of the work for SqlTool. Therefore, if you are using a version of SqlTool or SqlFile that is more than a couple revisions greater, you should find a newer version of this document.

The imprecision is due to content-independent revision increments at build time, and the likelihood of one or two behavior-independent bug fixes after public releases. The startup banner will report both versions when you run SqlTool interactively. Dotted version numbers of SqlTool and SqlFile are older than and This guide covers SqlTool bundled with series 1.

This section lists changes to SqlTool since the last major release of HSQLDB which may effect the portability of SQL scripts. For this revision of this document, this list consists of script-impacting changes made to SqlTool after the final 1.

I'm specifically not listing changes to interactive-only commands ": The reason for limiting the change list to only portability- impacting changes is that a list of all enhancements since just 1. Although it doesn't effect scripts, I will mention a significant recent change to interactive commands. Special and PL commands are not stored to the edit buffer and to command history, so they can be recalled and edited just like SQL commands.

If you are using an Oracle database server, it will commit your current transaction if you cleanly disconnect, regardless of whether you have set auto-commit or not. This will occur if you exit SqlTool or any other client in the normal way as opposed to killing the process or using Ctrl-C, etc.

This is mentioned in this section only for brevity, so I don't need to mention it in the main text in the many places where auto-commit is discussed. This behavior has nothing to do with SqlTool. It is a quirk of Oracle. If you want to use SqlTool, then you either have an SQL text file, or you want to interactively type in SQL commands. If neither case applies to you, then you are looking at the wrong program.

Copy the file sqltool. This file will work as-is for a Memory Only database instance; or if your target is a HSQLDB Server running on your local computer with default settings and the password for the "sa" account is blank the sa password is blank when new HSQLDB database instances are created. Edit the file if you need to change the target Server URL, username, password, character set, JDBC driver, or TLS trust store as documented in the RC File Authentication Setup section.

You could, alternatively, use the --inlineRc command-line switch to specify your connection parameters as documented in the Using Inline RC Authentication section. Find out where your hsqldb. Note that you don't need to worry about setting the CLASSPATH when you use the -jar switch to java.

For the filepaths, you can use whatever wildcards your operating system shell supports. The urlid mem in these commands is a key into your RC file, as explained in the RC File Authentication Setup section. Since this is a Memory Only database, you can use SqlTool with this urlid immediately with no database setup whatsoever however, you can't persist any changes that you make to this database.

At the end of this section, I explain how you can load some sample data to play with, if you want to. SqlTool does not commit SQL changes by default. This leaves it to the user's disgression whether to commit or rollback their modifications.

If you put a file named auto. To use a JDBC Driver other than the HSQLDB driver, you can't use the -jar switch because you need to modify the classpath. You must add the hsqldb. The latter can be accomplished by either using the "--driver" switch, or setting "driver" in your config file. The RC File Authentication Setup section. Here's an example of the first method after you have set the classpath appropriately.

If the tables of query output on your screen are all messy because of lines wrapping, the best and easiest solution is usually to resize your terminal emulator window to make it wider. If you are using SqlTool to connect to a HSQLDB network server or any non-HSQLDB database, you may prefer to use the jar file hsqltool. These alternative jar files contain all of SqlTool without stuff you don't need, but you will have to follow a simple procedure to generate these jars. See the Using hsqltool.

There are many SQL types which SqlTool being a text-based program can't display properly. When you run a query that returns any of these, SqlTool will save the very first such value obtained to the binary buffer and will not display any output from this query. You can then save the binary value to a file, as explained in the Storing and retrieving binary files section.

There are other types, such as BINARYwhich JDBC can make displayable by using ResultSet. Another restriction which all text-based database clients have is the practical inability for the user to type in binary data such as photos, audio streams, and serialized Java objects. This is also explained in the Storing and retrieving binary files section. Desktop shortcuts and quick launch icons are useful, especially if you often run SqlTool with the same set of arguments.

It's really easy to set up several of them-- one for each way that you invoke SqlTool i. One typical setup is to have one shortcut for each database account which you normally use use a different urlid argument in each shortcut's Target specification.

Desktop icon setup varies depending on your Desktop manager, of course. I'll explain how to set up a SqlTool startup icon in Windows XP. Linux and Mac users should be able to take it from there, since it's easier with the common Linux and Mac desktops. Navigate to where your good JRE lives. For recent Sun JRE's, it installs to C: Leave the path to java.

Beginning with a space, enter the command-line that you want run. If you want a quick-launch icon instead of or in addition to a desktop shortcut icon, click and drag it to your quick launch bar. You may or may not need to edit the Windows Toolbar properties to let you add new items. If you want some sample database objects and data to play with, execute the sampledata. To separate the sample data from your regular data, you can put it into its own schema by running this before you import: For memory-only databases, you'll need to run this every time that you run SqlTool.

For other persistent databases, the data will reside in your database until you drop the tables. RC file authentication setup is accomplished by creating a text RC configuration file. In this section, when I say configuration or config file, I mean an RC configuration file. RC files can be used by any JDBC client program that uses the org. RCData class-- this includes SqlTool, DatabaseManager, DatabaseManagerSwing.

You can use it for your own JDBC client programs too. The default location is sqltool. If you have any doubt about where your home directory is, just run SqlTool with a phony urlid and it will tell you where it expects the configuration file to be.

The config file consists of stanza s like this: These four settings are required for every urlid. There are optional settings also, which are described a couple paragraphs down. The URL may contain JDBC connection properties. You can have as many blank lines and comments like This comment. Use whatever facilities are at your disposal to protect your configuration file. It should be readable, both locally and remotely, only to users who run programs that need it. You can also put the following optional settings into a urlid stanza.

The setting will, of course, only apply to that urlid. Property and SqlTool command-line switches override settings made in the configuration file. Inline RC authentication setup is accomplished by using the --inlineRc command-line switch on SqlTool. The url and user elements are required.

The rest are optional. Use the --driver switch instead of --inlineRc to specify a JDBC driver class. Here is an example of invoking SqlTool to connect to a standalone database.

For security reasons, you cannot specify a non-empty password as an argument. You will be prompted for a password as part of the login process. This procedure will allow users of a legacy version of HSQLDB to use all of the new features of SqlTool. You will also get the new versions of the DatabaseManagers! This procedure works for distros going back to 1. Follow the instructions in the See the Using hsqltool. For now on, whenever you are going to run SqlTool, make sure that you have this hsqldbutil.

You can't run SqlTool with the "-jar" switch because the -jar switch doesn't permit setting your own class path. Here's a UNIX example where somebody wants to use the new SqlTool with their older HSQLDB database, as well as with Postgresql and a local application. Do read the The Bare Minimum section before you read this section. You run SqlTool interactively by specifying no SQL filepaths on the SqlTool command line.

What happens when SqlTool is run interactively using all default settings. SqlTool starts up and connects to the specified database, using your SqlTool configuration file as explained in the RC File Authentication Setup section. SqlTool displays a banner showing the SqlTool and SqlFile version numbers and describes the different command types that you can give, as well as commands to list all of the specific commands available to you. You can't nest commands or comments.

You can only start new commands and comments after the preceding statement has been terminated. Remember that if you're running SqlTool interactively, you can terminate an SQL statement without executing it by entering a blank line.

Special Commands, Edit Buffer Commands and PL Commands always consist of just one line. Any of these commands or comments may be preceded by space characters. These rules do not apply at all to Raw Mode. Raw mode is for use by advanced users when they want to completely bypass SqlTool processing in order to enter a chunk of text for direct transmission to the database engine. When you are typing into SqlTool, you are always typing part of the immediate command.

You execute the immediate command by hitting ENTER after a semi-colon for SQL commands or by just hitting ENTER after any other non-empty command-- see next section about this distinction. The edit buffer usually contains a copy of the last command executed, and you can always view it with the: If you never use any: If you want to repeat commands or edit previous commands, you will need to work with the edit buffer.

The immediate command contains whatever and exactly what you type. The command history and edit buffer may contain any type of command other than comments and: Hopefully an example will clarify the difference between the immediate command and the edit buffer. If you type in the edit buffer Substitution command ": Above, we said that if you enter an SQL command, one SQL command corresponds to one SqlTool command. This is the most typical usage, however, you can actually put multiple SQL statements into one SQL command.

See the Chunking section to see why you may want to chunk SQL commands, how, and the implications. The command is not terminated when you hit ENTER, like most OS shells.

You terminate SQL Statements with either ";" at the end of a line, or with a blank line. In the former case, the SQL Statement will be executed against the SQL database and the command will go into the edit buffer and SQL command history for editing or viewing later on. In the former case, execute against the SQL database means to transmit the SQL text to the database engine for execution. In the latter case you end an SQL Statement with a blank linethe command will go to the edit buffer and SQL history, but will not be executed but you can execute it later from the edit buffer.

See the note immediately above about multiple SQL statements in one SqlTool command. Blank lines are only interpreted this way when SqlTool is run interactively. In SQL files, blank lines inside of SQL statements remain part of the SQL statement. In the case of the first line, you will be appending to an empty SQL statement. More detailed explanation of PL variables and the other PL features, with examples, are covered in the SqlTool Procedural Language section.