Connect to another ODB through JDBC

Discuss the database features
Post Reply
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Connect to another ODB through JDBC

Post by Mr.Dandy »

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?
OpenOffice 4.1.12 - Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Post by Villeroy »

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: Connect to another ODB through JDBC

Post by Mr.Dandy »

Macro? No way to connect directly without unzip ODB content?
OpenOffice 4.1.12 - Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Post by Villeroy »

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Post by Villeroy »

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: Connect to another ODB through JDBC

Post by Mr.Dandy »

I especially apreciate your valuable time and knowledge spent in writing this OXT.
I install and run it.
capture.png
As you see, connection URL must be set with an *.script
But there is no such file except my *.odb
OpenOffice 4.1.12 - Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Post by Villeroy »

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: Connect to another ODB through JDBC

Post by Mr.Dandy »

Mr.Dandy wrote:No way to connect directly without unzip ODB content?
Anyway, I give up this.
OpenOffice 4.1.12 - Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Post by Villeroy »

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: Connect to another ODB through JDBC

Post by Mr.Dandy »

Understood but I would find the MS-Access "attached table" concept.
OpenOffice 4.1.12 - Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Post by Villeroy »

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply