15

In the documentation of the HSQLDB is a command line statement to start a HSQLDB server (HSQLDB Doc). But there is this "file:mydb" property, so I assume its not in memory-only mode.

How do I run a memory-only HSQLDB server?

I ran the following but get no clue.

java -cp ../lib/hsqldb.jar org.hsqldb.Server -?
mtyson
  • 8,196
  • 16
  • 66
  • 106
Juri Glass
  • 88,173
  • 8
  • 33
  • 46

3 Answers3

16

It took around 2 days for me to figure out on how to start a server in-memory and then access from outside. Hope this will save someone's time.

Server server = new Server();
server.setDatabaseName(0, "mainDb");
server.setDatabasePath(0, "mem:mainDb");
server.setDatabaseName(1, "standbyDb");
server.setDatabasePath(1, "mem:standbyDb");
server.setPort(9001); // this is the default port
server.start();

When you have to access the in-memory database for any CRUD, here is what you need to do :-

String url="jdbc:hsqldb:hsql://192.168.5.1:9001/mainDb";
Class.forName("org.hsqldb.jdbc.JDBCDriver");
Connection conn = DriverManager.getConnection(url, "SA", "");

where 192.168.5.1 is the server ip where HSQL is running. To connect to the standbyDb, replace mainDb with standbyDb in the first line. Once you get the connection, you can perform all database related operations.

To connect to the server from remote using DatabaseManagerSwing, here is what you need to do.

Download hsqldb-x.x.x jar and copy it to a folder (x.x.x is the version) open a terminal or command prompt and cd to the folder and run

java -cp hsqldb-x.x.x.jar org.hsqldb.util.DatabaseManagerSwing

Select "HSQL Database Engine Server" from the Type drop down and give "jdbc:hsqldb:hsql://192.168.5.1:9001/mainDb" as the URL. This will connect you to the remote HSQL in-memory Server instance.

Happy Coding !!
DbManagerSwing UI

Shajee Lawrence
  • 171
  • 1
  • 4
9

use java -cp .\hsqldb-1.8.0.10.jar org.hsqldb.Server -database.0 mem:aname

In memory mode is specified by the connection url - so if you want, you can just have a server.properties file in the same directory, and set the connection url to use the mem protocol - or if you are using hsqldb in another application that allows you to specify the connection url such as jdbc, specify jdbc:hsqldb:mem:aname.

Chii
  • 14,540
  • 3
  • 37
  • 44
0

I believe the file is used to load up the db into memory, and then persist when Server stops. I don't think the file is accessed while you're running.

It's been awhile since I've used HSQLDB (or H2), but I'm pretty sure that's how it works.

Todd R
  • 18,236
  • 8
  • 31
  • 39
  • If you load from a file, it does run it principally in memory, and it's extremely quick, but if you make changes it'll eventually flush them back to the disk, which is probably not what you want. To fix this, you need to set the hsqldb 'files_readonly' option. Doing this in the connection string isn't valid, but you can do it in the DB properties file: This first time you run the above, it'll create mydb.properties if it doesn't already exist. Add a new line saying 'hsqldb.files_readonly=true' to the bottom of that, and you're done. – Tim Perry Mar 13 '12 at 15:36