We help IT Professionals succeed at work.

How to create a persistence HSQL database?

Ahmet Ekrem SABAN
on
I am new to HSQLDB and want to create a persistent database so that when opening HSQLDB, the already created tables with their contents exist. I read about HSQLDB in the documentation, but cannot find the information. My current HSQLDB.bat is the following:
cd %HSQLDB%
java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

Open in new window

When running the batch, the following dialogue opens:
Opening dialogue of HSQLDBNote that the URL filed contains jdbc:hsqldb:mem:..
After [OK], I call two scripts, one creating two tables, the other filling them with test data. But reopening HSQLDB does not read the tables with their data.
What is missing, what is wrong? A SHUTDOWN does not change anything. I also replaced mem with file in the URL, but could not make the database persistent. What did I oversee in the HSQLDB guide?

Edit: Thank you, David! I started the database with the line you proposed & got the following reply:
C:\Program Files\hsqldb-2.5.0\hsqldb\lib>java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:hsqldb/mydb --dbname.0 xdb
[Server@77a567e1]: Startup sequence initiated from main() method
[Server@77a567e1]: Could not load properties from file
[Server@77a567e1]: Using cli/default properties only
[Server@77a567e1]: Initiating startup sequence...
[Server@77a567e1]: Server socket opened successfully in 15 ms.
Aug. 03, 2019 3:57:25 NACHM. org.hsqldb.persist.Logger logInfoEvent
INFO: Checkpoint start
Aug. 03, 2019 3:57:25 NACHM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose start
Aug. 03, 2019 3:57:25 NACHM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose synched
Aug. 03, 2019 3:57:25 NACHM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose script done
Aug. 03, 2019 3:57:25 NACHM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose end
Aug. 03, 2019 3:57:25 NACHM. org.hsqldb.persist.Logger logInfoEvent
INFO: Checkpoint end - txts: 1
[Server@77a567e1]: Database [index=0, id=0, db=file:hsqldb/mydb, alias=xdb] opened successfully in 223 ms.
[Server@77a567e1]: Startup sequence completed in 238 ms.
[Server@77a567e1]: 2019-08-03 13:57:25.733 HSQLDB server 2.5.0 is online on port 9001
[Server@77a567e1]: To close normally, connect and execute SHUTDOWN SQL
[Server@77a567e1]: From command line, use [Ctrl]+[C] to abort abruptly

Open in new window


Now, I want to access the database. No dialogue window opened. How can I interact with the database? From Java, JDBC gives means to access an HSQLDB:
        final String database = "jdbc:hsqldb:mem:hsql://localhost:9001/mydb;ifexists=true";
        final String user = "SA", password = "";
         
        try {
            // Create database connection
            connection = DriverManager.getConnection(database, user, password);
             
            // Create and execute statement
            final Statement statement = connection.createStatement();

            final ResultSet resultSet =  statement.executeQuery("select NAME from USERS");
             
            // Loop through the data and print all artist names
            while (resultSet.next()) {
                System.out.println("Customer Name: " + resultSet.getString("NAME"));
            }

Open in new window


The message I get is

Aug. 03, 2019 4:05:50 NACHM. org.hsqldb.persist.Logger logSevereEvent
SEVERE: could not reopen database
org.hsqldb.HsqlException: Database does not exists: hsql://localhost:9001/mydb
	at hsqldb/org.hsqldb.error.Error.error(Unknown Source)
	at hsqldb/org.hsqldb.error.Error.error(Unknown Source)
	at hsqldb/org.hsqldb.persist.Logger.open(Unknown Source)
	at hsqldb/org.hsqldb.Database.reopen(Unknown Source)
	at hsqldb/org.hsqldb.Database.open(Unknown Source)
	at hsqldb/org.hsqldb.DatabaseManager.getDatabase(Unknown Source)
	at hsqldb/org.hsqldb.DatabaseManager.newSession(Unknown Source)
	at hsqldb/org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source)
	at hsqldb/org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source)
	at hsqldb/org.hsqldb.jdbc.JDBCDriver.connect(Unknown Source)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
	at hSQLDBAccess/hsqldbaccess.JDBCHSQLDBExample.getCustomers(JDBCHSQLDBExample.java:22)
	at hSQLDBAccess/hsqldbaccess.JDBCHSQLDBExample.main(JDBCHSQLDBExample.java:57)

Database does not exists: hsql://localhost:9001/mydb

Open in new window


Obviously, the "database does not exist" or is accessible with other means.
Comment
Watch Question

David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Docs aren't clear about how to accomplish this.

My first thought was what you've already done... likely something like this (from docs)...

java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

Open in new window


https://sourceforge.net/projects/hsqldb/support will likely be your starting point for HyperSQL questions.
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Performance Consideration: Best to performance test HyperSQL against something like MariaDB (10.4.6+) to compare speeds.

If your application will have many connections + constant SQL statements running... you may find MariaDB will be a better choice.
CERTIFIED EXPERT
Top Expert 2016

Commented:
You should probably be working with a file url not a mem one:

http://hsqldb.org/doc/2.0/guide/running-chapt.html
Ahmet Ekrem SABANSenior IT consultant

Author

Commented:
Thank you for your reply! CEHJ, I tried
        final String database = "jdbc:hsqldb:file:hsql://localhost:9001/hsqldb/mydb;ifexists=true";

Open in new window

and
        final String database = "jdbc:hsqldb:file:hsql://localhost:9001/mydb;ifexists=true";

Open in new window


but couldn't access the database:
org.hsqldb.HsqlException: Database does not exists: hsql://localhost:9001/mydb

Open in new window

Aug. 03, 2019 4:15:32 NACHM. org.hsqldb.persist.Logger logSevereEvent
SEVERE: could not reopen database
org.hsqldb.HsqlException: Database does not exists: hsql://localhost:9001/hsqldb/mydb

Open in new window


David Favor, I edited the question, but I still cannot access the DB over JDBC.
CERTIFIED EXPERT
Top Expert 2016

Commented:
What operating system are you using?
Ahmet Ekrem SABANSenior IT consultant

Author

Commented:
Microsoft Windows 10 Home, 64-bit.
CERTIFIED EXPERT
Top Expert 2016

Commented:
Try

        final String database = "jdbc:hsqldb:file:///C:/Users/YourName/yourdbfile;ifexists=true";

Open in new window

Ahmet Ekrem SABANSenior IT consultant

Author

Commented:
Thank you, CEHJ! The line made access to the DB possible:
        final String database = "jdbc:hsqldb:file:///C:/Program Files/hsqldb-2.5.0/hsqldb/data/mydb;ifexists=true";

Open in new window


Is it possible to access the database from another point? Currently, the database is empty:
Aug. 04, 2019 10:28:00 VORM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose start
Aug. 04, 2019 10:28:00 VORM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose synched
Aug. 04, 2019 10:28:00 VORM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose script done
Aug. 04, 2019 10:28:00 VORM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose end
user lacks privilege or object not found: USERS

Open in new window


The "user lacks privilege or object not found" is true, as I had no chance to execute the SQL batch files, which brings us to my question: How can I made the database "remember" my tables?
Senior IT consultant
Commented:
I got a tip from another site to set the Type: to HSQL Database Engine Standalone and added as the file jdbc:hsqldb:file:///C:/Program Files/hsqldb-2.5.0/hsqldb/data/dbname. This made the content of the database persistent. After closing the database, I could access from my IDE the table:
        final String database = "jdbc:hsqldb:file:///C:/Program Files/hsqldb-2.5.0/hsqldb/data/mydb;ifexists=true";
        final String password = "", user = "SA";
         
        try {
            // Create database connection
            connection = DriverManager.getConnection(database, user, password);
             
            // Create and execute statement
            final Statement statement = connection.createStatement();

            final ResultSet resultSet =  statement.executeQuery("select NAME from USER");
             
            // Loop through the data and print all artist names
            while (resultSet.next()) {
                System.out.println("Customer Name: " + resultSet.getString("NAME"));
            }
             
            // Clean up
            resultSet.close();
            statement.close();

Open in new window


which resulted in the following output:
Aug. 04, 2019 10:47:21 VORM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose start
Aug. 04, 2019 10:47:21 VORM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose synched
Aug. 04, 2019 10:47:21 VORM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose script done
Aug. 04, 2019 10:47:21 VORM. org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose end
Customer Name: Max Mustermann

Open in new window

CERTIFIED EXPERT
Top Expert 2016

Commented:
Thank you, CEHJ! The line made access to the DB possible:
Glad i could help. Therefore wondering why you didn't award points ...

added as the file jdbc:hsqldb:file:///C:/Program Files/hsqldb-2.5.0/hsqldb/data/dbname.
You shouldn't be using the installation directory tree to place your data files

        final String database = "jdbc:hsqldb:file:hsql://localhost:9001/hsqldb/mydb;ifexists=true";

Open in new window

btw the above might not be quite right if you need to create the db. It will only connect if one already exists. Delete the semicolon and everything thereafter if that's not the behaviour you want