Accessing an HSQL file-based database inside a .jar

[Kurt Kopf]

I know this can be done, as I've head it alluded to many times: including an embedded read-only hsql db (via files) in a .jar file and accessing it.

This is how I have it set up:

My embedded db jar file contents:

com/company/example/db/
  db.tmp
  db.log
  db.new
  db.properties
  db.script

This gets successfully exported into my web app as "db.jar" in WEB-INF/lib/

My question is what is the JDBC url format to access this inside the generated jar file?
According to these sources:
http://hsqldb.org/doc/2.0/guide/running-chapt.html#running_inprocess-sect
http://hsqldb.org/doc/src/org/hsqldb/jdbc/jdbcConnection.html
http://download.oracle.com/javase/6/docs/api/java/lang/Class.html#getResourceAsStream(java.lang.String)

the URL should be:

jdbc:hsqldb:res:/com/company/example/db/db

But that doesn't work, or I wouldn't be posting here

I've tried every variation I could think of:

jdbc:hsqldb:res:com.company.example.db.db
jdbc:hsqldb:res:/com.company.example.db.db
jdbc:hsqldb:res:/db.jar/com.company.example.db.db

Plus replacing forward slashes with double backslashes (I'm on a windows 7 system at the moment), and every combination of slashes and dots I can think of. I haven't been able to find the specification for the "file_in_jar" notation mentioned in the HSQLDB documentation.

If I access the files that get compiled into the jar with a URL like this:

jdbc:hsqldb:file:C:\\workspace\\db\\src\\com\\company\\example\\db\\db

then it will work. Obviously this doesn't help me for deploying an application though.

I'm using hsqldb 2.0.0, java 1.6.0, and tomcat 6.

Thanks in advance for any help.

[Kurt Kopf]

Found some info in this thread:
http://sourceforge.net/projects/hsqldb/forums/forum/73674/topic/1481509

My database was actually "appDb" in camel-case, and apparently HSQLDB does not support camel-case in resource mode for some reason.

After fixing that, I was getting the following error...

20100805.165741.589 INFO  Logger (?) - Database closed
20100805.165741.591 FATAL Logger (?) - could not reopen database
org.hsqldb.HsqlException: file input/output error null exception message /com/company/example/db/db.script.new
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.scriptio.ScriptWriterBase.openFile(Unknown Source)
	at org.hsqldb.scriptio.ScriptWriterBase.<init>(Unknown Source)
	at org.hsqldb.scriptio.ScriptWriterText.<init>(Unknown Source)
	at org.hsqldb.persist.Log.writeScript(Unknown Source)
	at org.hsqldb.persist.Log.close(Unknown Source)

The solution to that was to modify db.properties (in the jar source). Somehow the "modified" flag had been set to true. Setting that back to false allowed the database to work. While I was in there, I set "readonly" to true for safety.

[Jan Cumps]

Thank you for posting the solution, Kurt.