Connect to another ODB through JDBC

Discuss the database features

Connect to another ODB through JDBC

Postby Mr.Dandy » Mon Jan 23, 2017 7:22 pm

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.3 - Windows 7
User avatar
Mr.Dandy
 
Posts: 295
Joined: Tue Dec 11, 2012 4:22 pm

Re: Connect to another ODB through JDBC

Postby Villeroy » Mon Jan 23, 2017 8:03 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Postby Mr.Dandy » Tue Jan 24, 2017 12:29 pm

Macro? No way to connect directly without unzip ODB content?
OpenOffice 4.1.3 - Windows 7
User avatar
Mr.Dandy
 
Posts: 295
Joined: Tue Dec 11, 2012 4:22 pm

Re: Connect to another ODB through JDBC

Postby Villeroy » Wed Jan 25, 2017 12:01 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Postby Villeroy » Wed Jan 25, 2017 10:05 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Postby Mr.Dandy » Wed Jan 25, 2017 11:06 am

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.3 - Windows 7
User avatar
Mr.Dandy
 
Posts: 295
Joined: Tue Dec 11, 2012 4:22 pm

Re: Connect to another ODB through JDBC

Postby Villeroy » Wed Jan 25, 2017 2:59 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Postby Mr.Dandy » Fri Mar 24, 2017 3:23 pm

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

Anyway, I give up this.
OpenOffice 4.1.3 - Windows 7
User avatar
Mr.Dandy
 
Posts: 295
Joined: Tue Dec 11, 2012 4:22 pm

Re: Connect to another ODB through JDBC

Postby Villeroy » Fri Mar 24, 2017 3:30 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect to another ODB through JDBC

Postby Mr.Dandy » Fri Mar 24, 2017 3:37 pm

Understood but I would find the MS-Access "attached table" concept.
OpenOffice 4.1.3 - Windows 7
User avatar
Mr.Dandy
 
Posts: 295
Joined: Tue Dec 11, 2012 4:22 pm

Re: Connect to another ODB through JDBC

Postby Villeroy » Sat Mar 25, 2017 11:59 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests