hsql question on data being saved

Question

I am looking into HSQL (to embed in an app) and was expecting that the data would be saved in a myDB.data file in the filesystem
Instead after a clean shutdown (execute sql "shutdown", stop and shutdown server object) the only remaining files are myDB.properties and myDB.script and myDB.script has all the commands to recreate the data in memory. No myDB.data file exists
E.g. from myDB.script

CREATE MEMORY TABLE PUBLIC.DUMMYTABLE(ID INTEGER PRIMARY KEY,FIRSTNAME VARCHAR(20))

From myDB.properties:

version=2.2.4
modified=no

I thought I was using file db and not memory db.

Class.forName("org.hsqldb.jdbc.JDBCDriver");

HsqlProperties p = new HsqlProperties();  
p.setProperty("server.database.0", "file:./testDB");  
p.setProperty("server.dbname.0","myDB");  
p.setProperty("server.address","localhost");  
Server server = new Server(); server.setProperties(p);  
server.start();  
Connection connection = DriverManager.getConnection"jdbc:hsqldb:hsql://localhost:9001/myDB", "SA", "");    
PreparedStatement st = connection.prepareStatement("CREATE TABLE dummyTable (id INTEGER PRIMARY KEY, firstname VARCHAR(20))");    
st.executeUpdate();   
connection.prepareStatement("shutdown").execute();  
connection.close();  
server.stop();  
server.shutdown();

Answer 1

No, that's perfectly fine.

A memory table is one that is read completely into memory upon startup and persisted to a .script file upon shutdown.

If you don't want to keep all data in memory you have to create a CACHED table, that one is primarily stored on disk. Only parts are read into memory.

The default is "MEMORY" when you do not specify the table type when creating it.
http://hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html#sqlgeneral_tabletypes-sect#N1039D

You can also set the default table type (memory, cached) in the .properties file, so that when you execute a CREATE TABLE without specifying the type, that default type is created.

Again this is explained in the manual:
http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#N14DEE

Answer 2

If you use a file: database, HSQLDB should create .script and .properties (and possibly a .log if there is an unexpected shutdown) for your database, and these files will not be removed if the application is shut down. The .script file should have all of the INSERT statements necessary to repopulate your data.

If you use a mem: database, the files will not be written at all, and the data will not be saved between instances. From the guide:

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

The fact that the files exist and remain between application restarts is, in itself, the "file" database that you're looking for.

Edit:

To answer your comment,

But in which case is the *.data file used that the manual mentions

From the guide:

This file contains the (binary) data records for CACHED tables only.

See horse's answer for information about CACHED vs MEMORY tables. Regarding your questions about when you'd use CACHED tables, here's another snippet from the guide:

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.

Answer 3

HSQL 2.2.5 has some problem. Use hsqldb.jar from lib of HSQL DB version 2.2.9 In your program at the end write statement

org.hsqldb.DatabaseManager.closeDatabases(0);

It should persist your changes in file.