2

I am using an in-memory hsqldb database with a JDBC driver.

Now, I am looking for a way to persist this database for reloading after application reboot. I came up with the following options:

  1. Export .script file with sql command "SCRIPT < path > " (link)
  2. Log all statements to a log file.

Option 2 works, but it seems kind of ugly in my eyes. The script export for option 1 works too, but I seem to be unable to get the .script file back into an in-memory database.

I am thankful for any advice.

Community
  • 1
  • 1
user3726374
  • 583
  • 1
  • 4
  • 24
  • I'm a bit puzzled because if you know that you want to persist the database why not just change the connection URL from `mem:` to `file:` and specify the where you want the database to be stored? – Gord Thompson Jan 26 '15 at 22:19
  • I am using a mem database for performance reasons. – user3726374 Jan 27 '15 at 06:48
  • There is no speed penalty. You can turn off logging on a file: database and issue a CHECKPOINT for SHUTDOWN when you want to persist the .script file. – fredt Jan 31 '15 at 23:41
  • @fredt This is interesting, because I am definitely experiencing different speed (for reading from the database). If I only change the `DriverManager` connection string from jdbc:hsqldb:mem:xxx to jdbc:hsqldb:file:xxx, the speed reduces. I am using memory tables. Why could this be? – user3726374 Feb 01 '15 at 15:44
  • Connect to the database that you exported as script file with the files_readonly=true property. See http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#dpc_db_file_mem – fredt Feb 03 '15 at 03:22
  • Possible duplicate of [Any ideas for persisting H2 Database In-Memory mode transaction?](http://stackoverflow.com/questions/18165462/any-ideas-for-persisting-h2-database-in-memory-mode-transaction) – t0r0X Dec 09 '15 at 18:33

1 Answers1

2

The first option is correct.

After you export the database with the SCRIPT <path> statement, you can get it into an in-memory database.

You need to connect to the scripted database with a read-only file: URL

For example if you export the database to d:/dbfiles/mydb.script, you will get the mydb.script file in the named directory. To connect to this database, use file:d:/dbfiles/mydb;files_readonly=true.

There is absolutely no speed difference between the above method and a mem: database.

fredt
  • 24,044
  • 3
  • 40
  • 61