[Solved] HsqlDB 2.51 in Base

Discuss the database features
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

[Solved] HsqlDB 2.51 in Base

Post by psilocybe »

Good morning all,

Just to find out if anyone is interested in using HsqlDB 2.51 under Base .... For consultation only for now.
If so, let me know, I have it in my boxes ...


HsqlDB is really a very good database...
Last edited by psilocybe on Thu Jan 14, 2021 1:10 am, edited 1 time in total.
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

I use it every day.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Hi Villeroy,

Can you tell me which driver you use for this ...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

hsqldb-2.5.1.jar from the hsqldb.org website with local databases (jdbc:hsqldb:file:///path/name;...)
-------------
and now I have updated my server (jdbc:hsql://192.168.1.1/name;...)
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: HsqlDB 2.51 in Base

Post by Villeroy »

Are you talking about embedded HSQL 2.5.1? Sorry, didn't get that. This would be a major improvement for the "playground databases". I will keep on recommending to drop any kind of embedded database, no matter how well it sees to work. The data safety is not as good as it should be. Nevertheless, having a full featured HSQL database in a single file has its use cases, particularly on this forum.

As far as I know, 2.5. can not convert 1.8 anymore. One would need some older version up to 2.4.x to convert 1.8.
For several years LO tried to implement embedded Firebird, but they failed because FB too different from HSQL and therefore the Base component is difficult to adjust.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Hi Villeroy,

If you hear 'embedded HSQL 2.5.1' like the ability to open Tables, Users in Base, then yes that's it ...

I don't know if I'm using it wrong, but I've never been able to use the hsqldb java driver in Base.
I believe it is during connection, after choosing JDBC Driver, the 'Test Class' button does not work...

And if you can get around this problem, there is a second problem which is that the com.sun.star.sdbc.XStatement does not work ...
On the other hand, the com.sun.star.sdbc.XPreparedStatement works well, so I wrote a wrapper that allows using HsqlDB in Base... (it's just a wrapper arround com.sun.star.sdbc.XConnection redirecting all XStatement calls to XPreparedStatement).

The counterpart is to have to go through ODBC like: odbc: hsqldb: file:///path/name, but this allows me to open the Tables and even the Users of the HsqlDB database in Base...

I can make it an extension if anyone is interested?
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

Since 11 years I open tables, views, manage users and groups of any HSQL database.
The trick how to use any hsqldb.jar is that you store the driver path in the document. You must not store the path to hsqldb.jar in the Java options.

Embedded HSQL 1.8: download/file.php?id=11250 (notice the status bar when opening the attached document).
Macro with GUI to connect a Base document to an external HSQL: FreeHSQLDB v.0.3 including a short snippet without GUI.
Python script to extract embedded HSQL and/or reconnect with external HSQL: [Python] Macro to extract and reconnect embedded HSQLDB
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: HsqlDB 2.51 in Base

Post by Villeroy »

https://www.mediafire.com/file/qpiuiq0s ... e.zip/file is a zip with an odb doc a HSQLDB and a driver. The document has an auto-install macro. Extract everything to a trusted directory and open the document.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

I just tested due_date_template and it works fine, I will have a look at what macros do...

On the other hand, I cannot manage the users: Tools -> User administration said: The database does not support user administration... and what to do in Base if we have to start from scratch...

Thank you...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

That particular macro corrects the path settings if necessary.
In a shortened version with hard coded strings the relevant lines are:

Code: Select all

Const cURL_Prefix = "jdbc:hsqldb:file:"
Const cJarPath = "C:\Drivers\hsqldb\lib\hsqldb-2.5.0.jar"
Const cClass = "org.hsqldb.jdbcDriver"
Const cURL_Path = "C:/Users/LoginName/HSQL/database/"
Const cURL_Options = ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"
on error resume next
   oDoc.CurrentController.ActiveConnection.close()
on error goto 0
oDataSource = oDoc.DataSource
oDataSource.URL = cURL_Prefix & cURL_Path & cURL_DBName & cURL_Options
With oDataSource.Settings
   .JavaDriverClass = cClass
   .JavaDriverClassPath = ConvertToURL(cJarPath)
End With
This closes any connection, concatenates the connection URL, sets the driver class and the driver path to be used with this particular databse.
User administration can be done with the sqltool.jar that is shipped with HSQL, with a tool like SQuirrelSQL or via office menu:Tools>SQL... This is admin work and does not require any sophisticated GUI.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Thanks for this shortened version of the macro.

It would be good to build a driver (com.sun.star.sdbc.XDriver) which will provide this workaround implicitly ...

Moreover, if this new Driver implements the layer (com.sun.star.sdbcx) then the administration of the users of the hsqldb database can be done in Base...

I will be closely interested in the feasibility of such a Driver ... because it will allow a connection to hsqldb directly in Base, and if the sdbcx layer is implemented even the administration of the hsqldb base ...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

No need to have another driver. JDBC is just fine with external Java databases. http://www.h2database.com/html/main.html is another excellent Java engine.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Hi Villeroy,

Sorry, but now I don't understand ...

You confirm to me that we cannot connect to an hsqldb database without adding a macro:
You must not store the path to hsqldb.jar in the Java options
Maybe you prefer the complicated to the simple ... or you certainly have good reasons ...

But for me the connection to hsqldb does not work ...

Because if it worked, when opened, Base should be able to create an hsqldb base... from GUI...

Do not mind, I will write this driver anyway: it is to serve the lambda user ;)
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

If you never use embedded HSQL (for instance to help users on this forum) then you may define a class path pointing to your own hsqldb.jar driver. If you use embedded HSQL from time to time and don't want to keep a special user profile, then you better define your HSQL2 driver in the document and keep the office configuration free of any HSQL class path.
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: HsqlDB 2.51 in Base

Post by Villeroy »

This is how one would convert an embedded HSQL 1.8 into a stand-alone HSQL manually with a minimum of scripting:
1) Extract the database folder
2) Rename all the database files (script, properties, data etc) to DB_NAME.script, DB_NAME.properties, DB_NAME.data etc.
3) menu:Edit>Database>Connection... Type JDBC and enter a valid URL such as jdbc:hsqldb:file:///path/DB_NAME
4) Enter the driver class org.hsqldb.jdbcDriver
Now this will not work because no driver can be found.
5a) Drop support for embedded HSQL and add a class path to some hsqldb.jar to the office configuration.
OR
5b) Keep support for embedded HSQL and add a class path to some hsqldb.jar stored in the document. This requires a one line macro because the GUI does not have any such input box. I stumbled upon property JavaDriverClassPath while browsing the API on connection settings.

Code: Select all

ThisComponent.DataSource.Settings.JavaDriverClassPath = "file:///path/hsqldb.jar"
If this Basic code is embedded in the odb:

Code: Select all

ThisDatabaseDocument.DataSource.Settings.JavaDriverClassPath = "file:///path/hsqldb.jar"
where your hsqldb.jar should be able to convert HSQL1 to HSQL2 which was the case before version 2.5.

My Python script does this with one macro call. It extracts, renames reconnects and uses any given driver/hsqldb.jar or (if missing) the one that is shipped with the office suite.
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: HsqlDB 2.51 in Base

Post by Villeroy »

Can you provide a HSQL driver that can be added to the global class path while keeping compatibility with embedded HSQL?
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Hi Villeroy,

Sorry for the wait, but I'm really busy right now...
Can you provide a HSQL driver that can be added to the global class path while keeping compatibility with embedded HSQL?
Subject to correct operation, the operation of the driver would be:
- Get the path and name of the hsqldb database to open (ie: file:///path/name)
- Check if an 'name.odb' file exists at the location of this path
- If it does not exist, create it with the correct parameters (ie: JavaDriverClass and JavaDriverClassPath). The hsqldb driver jar file could be stored in the installation directory of the extension providing this driver.
- And finally use this odb file to establish the connection...

Obviously all this remains to be tested, but it seems to me that this is what your simplified macro does... So no modification of global class path (no problem with compatibility with embedded HSQL, normally...)

I admit that at the moment I am a little busy and it will take a little time...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Hi all,

Here is an alpha version of the HsqlDB Driver.

The 'sdbcx' layer is not fully implemented but allows to see the users of the HsqlDB database.

I didn't have time to test on Windows, nor OpenOffice, but it works on Linux with LibreOffice

Obviously this is a first draft, and remains to be improved...

In addition, I would like to thank Villeroy for his participation, it allowed me to save a lot of time...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

In added the extension to LibreOffice 7.0.
It breaks all connections to my existing HSQL databases (embedded, file and server).
When I create a new database document for an existing external HSQLDB, I choose "HSQLDB driver" and then I never get the right URL. What exactly do I have to enter into the URL box.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Hi Villeroy,

I need to update the README to provide usage documentation ...

But quickly, to use you have to do:
  • File -> New -> Database
  • Choose: Connect to an existing Database
  • Select HsqlDB Driver and Next on the Wizard
  • In Datasource URL put: file:///home/user/hsqldb/testdb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
Only the sub-protocol file:// is currently supported...

If the testdb database exists then it connects otherwise it creates it and connects, normally ;) ...

What do you mean by:
It breaks all connections to my existing HSQL databases (embedded, file and server).
Do not use this extension/driver on a production database, it is only an alfa version ... (just to test)
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

Open any of my above linked databases and see. They do not connect anymore if your HSQL driver is installed.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

You're right, I can no longer open an embedded HsqlDB database on LibreOffice but it works fine under OpenOffice.

I have trouble explaining it because this new driver responds to the sdbc:hsqldb:* protocol which is not normally used by embedded HsqlDB which responds to the sdbc:embedded:hsqldb:* protocol...

Another big regression of LibreOffice ???
:(

If you don't install libreoffice-sdbc-hsqldb package, it's normal you can't open the embedded HsqlDB databases
Last edited by psilocybe on Fri Dec 25, 2020 1:16 pm, edited 2 times in total.
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

I confirm that existing HSQL keeps on working in AOO but when I connect an existing database to AOO, it connects successfully to a blank database.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

I just updated the driver so that it works on Windows 7 and everything works fine with LibreOffice 6.4.4.2(x64): new driver and embedded HsqlDB...
Strangely, the Url must be: file:///c:/path/name instead of file://c:/path/name

This is a LibreOffice bug on Linux ...

I will open a bug at LibreOffice although I think it will have no effect
:(
Last edited by psilocybe on Fri Dec 25, 2020 1:17 pm, edited 1 time in total.
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Hi Villeroy,

Could you confirm to me the versions of the OS and LibreOffice, for which the installation of the new HsqlDB driver breaks the connection to the embedded HsqlDB.

I would like to open a bug report the most complete possible ... ;)

Thank you in advance...

PS: I just saw that I have the same problem with my extension gContactOOo which installs a new database driver allowing to synchronize our Android contacts...
It seems that this is not related to the protocol used by the driver but to its installation ...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

Currently I have yesterday's version 0.0.1 which is 1583078 bytes in size. The 0.0.1 from the day before was 1583072 bytes in size.
I added the oxt to AOO 4.1.8 on Linux(x64) and LO7.0 on the same machine. I use the distribution's LO 6.0 for productive purpose.

1. OpenOffice 4.1.8
1.1. Loading embedded HSQL works (can open tables).
1.2. Loading file based HSQL works.
1.3. Loading server based HSQL works.
1.4.1. Now I create a database document /tmp/MyDB.odb pointing to the existing ~/hsql/database/MyDB using your Hsqldb driver.
1.4.2. That one works too (can open tables) but why does a "shadow document" ~/hsql/database/MyDB.odb appears out of nothing? It also appears in the document history.
The shadow document can not read tables
"Error code: -1

Receiver class org.hsqldb.jdbc.JDBCPreparedStatement does not implement the interface java.sql.CallableStatement defining the method to be called (org.hsqldb.jdbc.JDBCPreparedStatement is in unnamed module of loader java.net.URLClassLoader @7674c8f8; java.sql.CallableStatement is in module java.sql of loader 'platform'"
2. LibreOffice 7.0.
2.1. LO pops up the same error with any of the above documents when accessing the tables container:
SQL Status: HY000

The connection to the external data source could not be established. No SDBC driver was found for the URL 'sdbc:embedded:hsqldb'. /home/buildslave/source/libo-core/connectivity/source/commontools/dbexception.cxx:399

Java disabled. My mistake.

2.2 Now I create a database document /tmp/MyDB_LO7.odb pointing to the existing ~/hsql/database/MyDB using your Hsqldb driver. Same error when accessing the tables container. No shadow document "MyDB_LO7.odb" in the database folder.
Last edited by Villeroy on Thu Dec 24, 2020 11:32 am, edited 1 time in total.
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: HsqlDB 2.51 in Base

Post by Villeroy »

After removing the Hsql driver extension from LO7 my databases still don't work anymore (same error as with the extension). There is no harm done to the database nor to the documents. Something is broken in the user profile. Yes, I restarted the application.
Java disabled. My mistake.
Last edited by Villeroy on Thu Dec 24, 2020 11:32 am, edited 1 time in total.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

I am sorry for the inconvenience caused. :(

It is true that installing or uninstalling extensions can corrupt the user profile and require deleting or renaming the profile so that LO creates a new one on restart.
In any case, this has happened to me several times already ...

Thank you for your super detailed report... ;)
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

I HAVE TO BE SORRY. SOOO SORRY.
Some weeks ago I disabled Java for my experimental LO7 installation during a Java debate on this forum. No Java, no HSQL.

The strange shadow databases remain. There is one database copy (*.odb) for every new database I create with your driver.
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
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Good, at least you have not lost your user profile, if it has not been deleted to recreate one ... I'm happy ... :D
The strange shadow databases remain. There is one database copy (*.odb) for every new database I create with your driver.
Yes, this is normal, this odt file is created by the driver itself .. It allows me to connect with the correct parameters (ie: JavaDriverClass and JavaDriverClassPath)
It may be wise for the driver to create it but not to save it so that it does not appear in the document history (because saving this odb is only there for performance issues and I am not sure it has much effect) :?
Tell me what you think about it?

Edit: If the odb is created at each connection and not saved, then it becomes possible to support all the protocols supported by HsqlDB (namely: hsql, hsqls, http, https, mem, file, res)
This remains to be verified... :?
Last edited by psilocybe on Thu Dec 24, 2020 1:56 pm, edited 2 times in total.
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
Post Reply