## How To / FAQ

How to Install Hypersonic SQL
How to Run and Compile Hypersonic SQL
How to Create a new database
How to start programming JDBC / Hypersonic SQL
Where to get more Documentation
How to use Hypersonic SQL inside JBuilder
How to Upgrade from an old version

Frequently Asked Questions

### How to Install Hypersonic SQL

- Download the hsqldb.zip file in the download page to your local computer.
- Uncompress the files. Any program that can handle zip files should work (there have been some problems reported using and old version of WinZip): WinZip or PKZIP from PKWARE. If you have trouble uncompressing the file and / or if you have installed a JDK you may also use the command line tool jar: open a command prompt, change to the directory where the file hsqldb.zip is, and type:
  jar -xf hsqldb.zip
- Browse the documentation. Start with 'index.html'.

### How to Run and Compile Hypersonic SQL

A compiled version of Hypersonic SQL is included in the download, but if you want to re-compile it or if you want to compile Java source code, you will need a Java compiler.

Command line tools are included in the free JDK (Java Development Kit) from Sun. The JDK is required to run the 'application' examples.

A free and very fast Java Compiler is jikes, originally from IBM, now Open Source. The batch files included in the Hypersonic SQL download uses jikes (but this may be changed easily).

### How to Create a new database

A new database is created automatically if it does not yet exist. Just connect to the not-yet-existing database using the user 'sa' and an empty password.

### How to start programming JDBC / Hypersonic SQL

Hypersonic SQL comes with documentation, example applets and source code that can help programers that are new to JDBC programming.

An example application is 'FindFile', a tool to search files. The source code of this demo application is well documented, and the functionality is kept low, so it should be easy to understand what is going on just reading the source code.

Because the source code of all other tools and applets is also included in the download, more advanced programers will find one or the other hint also there. The applications and applets that come with Hypersonic SQL should be a good start.

### Where to get more Documentation

Hypersonic SQL has a standard JDBC interface that is provided by most databases. The JDBC interface is well documented in the JDK. Additionally, Hypersonic SQL specific JDBC documentation is included in the download.

There are also many books available about JDBC programming.

For people that are interested in the database engine design of Hypersonic SQL: There is currently not much documentation about the Hypersonic SQL kernel architecture, but the source code is quite easy to read.

### How to use Hypersonic SQL inside JBuilder

To use Hypersonic SQL at design-time in JBuilder, for example to connect to a database, you need to add the Hypersonic SQL JDBC driver to the JBuilder environment.

Copy the file hsql.jar to the path ...\JBuilder<whatever_version_you_have>\lib (where also the file jbcl2.0.jar is). Close JBuilder (this is important), then change the file ...\JBuilder<whatever_version_you_have>\JBuilder.INI using a text editor. Add to the group [Java_Global], item IDEClassPath=, the path to hsqldb.jar. The IDEClassPath will then look about like this: IDEClassPath=..\lib\jbuilder.zip; (other files) ;..\lib\pvcs.zip;..\lib\hsqldb.jar;

Start the WebServer from command line. From a another command line, you may start the DatabaseManager and connect to this database.

Then start JBuilder, open your project. You will need to add the hsqldb.jar file also to the libraries of your project (project properties, Java libraries). Add a 'Database' control to your project, set the Connection. In the dialog 'connection' you will not be able to browse the driver or the URL, but you can set it manually. Set the URL to jdbc:hsqldb:http://localhost and then you can set the driver class to org.hsqldb.jdbcDriver. Set the user name to 'sa' and test the connection.

You can add now your QueryDataSets and other controls as you like.

### How to Upgrade from an old version to the current version

An upgrade is required when updating from version an older version to a newer version Upgrade works like this (the old database is called 'olddb' and the new 'newdb'):

- Backup the old database.
- Create a script of the old database with the old tools. Run the command: SCRIPT 'newdb.script' in the QueryTool or DatabaseManager of the old database version.
- Install the new version.
- Create a new database in the same directory as the 'newdb.script' with the name 'newdb'. The script file will be executed automatically.

## Frequently Asked Questions

- Can Hypersonic SQL handle multithreading?

  The database itself is currently not multithreaded (*), but it is multithreading-safe. Application that are multithreading can use the database, but all requests are executed one after the other, this means only one at a time. (*) there is a second thread for saving the transaction log to disk when WRITE_DELAY is turned on.

- What is the limitations of the database (size of columns, number of tables, rows...)?

  There is no limitation. Number of columns, tables, indexes, size of columns and so on is limited only by the memory.

- My database runs out of memory / How much memory does a database need?

  If only memory tables (CREATE TABLE or CREATE MEMORY TABLE) are used then the database is limited by the memory. About 500 bytes are required for each record. If you use CREATE CACHED TABLE, then the size of this table is not limited by the memory (because it is saved to disk). Indexes of cached tables are also saved to disk.

- What is the biggest known Hypersonic SQL database?

  Tests have been made with 4'000'000 records using the SelfTest application (insert, update, delete). The size of the database is then over 400 MB. For this tests the main memory was limited to 16 MB. The current size limit of a Hypersonic SQL database is currently 2 GB (the biggest integer value). If this is a problem, the limit is easy to change.

- How to create a new database?

  A new database will be created automatically if it does not yet exist.

- Is a GPL version available / may I use Hypersonic SQL in a commerical product?

  Hypersonic SQL is Open Source. The copyright of Hypersonic SQL is almost the same as for Apache, and is very near the LGPL license. The difference to GPL is that Hypersonic SQL may be used in commercial products for free.

- After the program is finished, almost all statements are in the *.script file, and the *.data file is almost empty. Why?

  This is by design. The statements that make up the database are saved in the *.script file (mostly CREATE and INSERT statements). Only data of cached tables (CREATE CACHED TABLE) are stored in the *.data file. Also all data manipulation operations are stored in the *.script file (mostly UPDATE, DELETE) for security. When the database is closed (Connection.close) then the *.script file is re-created. After this only CREATE and INSERT statements are in the *.script file. When the database is started, all statements of the *.script file are executed first and new statements are appended to this file.

If you have or need more information about something, please send a mail to hsqldb@users.sourceforge.net.