Apache OpenOffice Community Forum

Hello forum,

I'm trying to create a JDBC connection to an existing ODB file with embedded HSQLDB.
So far I've been able to load the driver "org.hsqldb.jdbcDriver", connect to the database with "jdbc:hsqldb:file:c:\db\test.odb;default_schema=true"
However, I cannot open tables with Base.

Is it possible?

Run this macro against your embedded HSQL: [Python] Macro to extract and reconnect embedded HSQLDB.
If you provide a database driver hsqldb.jar in a subfolder "driver", that one will be used, otherwise it will use the older hsqldb.jar which resides in your office installation folder.

Macro? No way to connect directly without unzip ODB content?

OK, you are right. Today I did a complete rewrite of that macro. Now you can use it to extract and connect or re-connect to an existing set of database files. Before uploading I have to run a lot of tests when I'm back home.
The new macro detects if there is an embedded database in the document archive and if there are matching files in the database directory. If both are found, the macro prompts you to decide if you want to reconnect the existing db or replace the existing with the embedded one (if you want to start again with the embedded version).

What you can do anyway:
1. Use my FreeHSQLDB v.0.3, point to the script file and to the hsqldb.jar. If you want to use the hsqldb.jar which is shipped with your office suite, you will find it in <installation_path>/classes/hsqldb.jar
I don't understand why, but the shipped hsqldb.jar can not be found unless you explicitly declare its path in the database document.
2. Alternatively, you can backup your existing database files, run the current version of the extraction macro, close everything and restore the backup files.

After hours of testing and debugging with OpenOffice and LibreOffice, I release the latest version of my ExtractHSQL.py. Now it prompts for 4 possible actions depending if there is an embedded HSQLDB, an extracted HSQLDB, both or none. This version can be used to reconnect a Base document with external files having the same base name as the document in a subdirectory "database".
In other words: It connects MyDatabase.odb to ./database/MyDatabase.* one way or the other.

I especially apreciate your valuable time and knowledge spent in writing this OXT.
I install and run it. As you see, connection URL must be set with an *.script
But there is no such file except my *.odb

Every HSQLDB consists of one *.script file and one *.properties file at least. The * stands for the database name.
When embedded in a document, these files are archived as database/script and database/properties without any base name. If you can not find any *.script file, I guess you forgot to give a name to your manually extracted database files.

http://www.hsqldb.org/doc/1.8/guide/ch01.html#N100F3

Mr.Dandy wrote:No way to connect directly without unzip ODB content?

Anyway, I give up this.

You can not connect to odb files. An odb file may contain a HSQL database but it is no database. JDBC drivers work with jdbc databases but not with odb containers.

Understood but I would find the MS-Access "attached table" concept.

You can do something similar with external forms on stand-alone Writer or Calc documents. A parent form can be linked to some database's record set and one of its subforms can be linked to some other database's record set. This way you can for instance show all rows in a spreadsheet that have a corresponding entry in some other type of database if the linked field types are compatible to each other.