Copy split-HSQL databases to dual-boot Windows 11/Xubuntu 22.04

English
#1

I trying to move HSQLDB split databases from Windows to Xubuntu.

I’m getting this error message

Error code: 1000

The driver class “org.hsqldb.jdbcDriver” could not be loaded.The additional driver class path is “file:///D:/LO%20Databases/Corsham%20Library/driver/hsqldb.jar”. at /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/JConnection.cxx:686

Searching through the cases already noted under this site then this request seems to be a follow-on from the case recorded under Test DB gets error "The driver class 'org.hsqldb.jdbcDriver' could not be loaded" - #3 by Ratslinger.

Following the guidance in that case I have gone into the Xubuntu terminal (Cntrl/Alt/T) and then

cd /media/nick/Shared/LO\ Databases/Corsham\ Library/
cp Corsham\ Library.odb xxx.zip

From the File Manager GUI then extracted the xxx.zip file and found the content.xml in the root directory.

Looking at the content.xml file I can see the line
<db:connection-resource xlink:href=“jdbc:hsqldb:file:////media/nick/Shared/LO Databases/Corsham Library/database/Corsham_Lib;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false” xlink:type=“simple”/>

I can also see this entry
db:data-source-setting-value
file:///D:/LO%20Databases/Corsham%20Library/driver/hsqldb.jar
</db:data-source-setting-value>

I’m guessing that this entry needs to be changed to
db:data-source-setting-value
file:////media/nick/Shared/LO Databases/Corsham Library/driver/hsqldb.jar
</db:data-source-setting-value>

Is this as simple as
1, Copy the .odb file (I already have that copy)
2, Rename .odb file to .zip
3, Extract the .zip file
4, Amend the text as above (if I have permissions to the file)
5, Create an new .zip archive
6, Rename the .zip file to the old .odb fle name?

You can find below some more background.

Any help would be appreciated.

Thanks

Nick

Background
The files were copied off the source Windows machine onto a USB stick and then copied from there onto a dual-boot Windows/Xubuntu machine with some storage shared between the two operating systems on tat machine. The databases have been put onto this shared partition.

The key steps were
1, Copy the database folders onto a USB stick
2, Copy those folders off the USB stick onto a shared partition on the dual-boot machine
3, In LibreOffice
Update the connection string string under LibreOffice Base Edit>Database>Properties
Register the database
Open the .odb file and then
Selecting Tables

I then get this message.

Error code: 1000

The driver class “org.hsqldb.jdbcDriver” could not be loaded.The additional driver class path is “file:///D:/LO%20Databases/Corsham%20Library/driver/hsqldb.jar”. at /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/JConnection.cxx:686

The reference to the jar file doesn’t appear in neither the script file nor the properties file under /media/nick/Shared/LO Databases/Corsham Library/database/.

You can find some further information about the environment below. You can see below the connection string under LibreOffice Base Edit>Database>Properties for each machine/operating system.

Environment
The database folders, with the .odb files, have been copied to a dual-boot machine with partitions for
Windows
Xubuntu 22.04
A shared drive (S: and /media/nick/Shared) that both operating systems can see. I have updated the same Writer files on this shared drive from both operating systems.

On the source Windows 10 Pro machine the LibreOffice settings are

    Version: 7.6.5.2 (X86_64) / LibreOffice Community

    Build ID: 38d5f62f85355c192ef5f1dd47c5c0c0c6d6598b

    CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win

    Locale: en-GB (en_GB); UI: en-GB

    Calc: CL threaded


    Also 

        LibreOffice Tools>Options>

            Under LibreOffice>Advanced (Alt/F12) Class Path - empty

            Under LibreOffice Base the database is registered


        LibreOffice Base Edit>Database>Properties

               Connection string  jdbc: hsqldb:file:///D:\LO Databases\Corsham Library\database/Corsham_Lib;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

                JDBC Driver Class org.hsqldb.jdbcDriver

        LibreOffice Base Edit>Database>Connections

                Database type: JDBC

                  On the next page the driver-specific box is empty and the JDBC driver class is org.hsqldb.jdbcDriver

On the target dual-boot machine

Version: 7.6.6.3 (X86_64) / LibreOffice Community
Build ID: d97b2716a9a4a2ce1391dee1765565ea469b0ae7
CPU threads: 8; OS: Linux 6.5; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-US
Calc: threaded

LibreOffice Tools>Options>
    Under LibreOffice>Advanced (Alt/F12) Class Path - empty
    Under LibreOffice Base the database is registered with the path "/media/nick/Shared/LO Databases/Corsham Library/Corsham Library.odb"

LibreOffice Base Edit>Database>Properties

    Connection string  jdbc: 

    hsqldb:file:////media/nick/Shared/LO Databases/Corsham Library/database/Corsham_Lib;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
            
    JDBC Driver Class org.hsqldb.jdbcDriver

LibreOffice Base Edit>Database>Connections

                Database type: JDBC

            On the next page the driver-specific box is empty and the JDBC driver class is org.hsqldb.jdbcDriver
#2

Install my little macro suite FreeHSQLDB.
Call Tools>Macros>MyMacros>FreeHSQLDB>FreeHSQLDB>Main
A dialog pops up where you can pick the database’s script file and the driver hsqldb.jar. Both, the database files and the driver, need to be accessible from both bootable systems.

On both systems you need different versions of the Base document, so repeat the procedure on Windows and LInux.

#3

Villeroy,

Thank you for your quick reply.

I installed that .odt file and got it to run (not in quite the way that you suggested but got the dialogue box up ).

When I ran it again the box for the the jar file box had “file:///media/nick/Shared/LO%20Databases/Corsham%20Library/driver/hsqldb.jar”. Which suggested that it applied an update when I ran it.

When I try to select Tables from the .odb file I now get a different error message.

The error message is now "SQL Status: S1000
Error code: -25

error in script file line: /media/nick/Shared/LO Databases/Corsham Library/database/Corsham_Lib 1 at /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:173".

This might need to be the subject of a new request to this site.

Regards

#4

You may have upgraded the database if you pointed to a hsqldb.jar newer than the one you used to use.
You find the database version in the properties file.
You find the driver version in META.INF/MANIFEST.MF after opening the jar file with an archive manager (it’s just a zip file).
Possibly, you need to restore your database from a backup.

#5

Villeroy,

From the manifest file (file:///media/nick/Shared/LO%20Databases/Corsham%20Library/DBExtract/META-INF/manifest.xml) I can see
<manifest:file-entry manifest:full-path=“driver/hsqldb.jar” manifest:media-type=""/>
<manifest:file-entry manifest:full-path=“driver/sqltool.jar” manifest:media-type=""/>.

The contents of the driver folder are
nick@Lenovo-ideapad-330S-14IKB:/media/nick/Shared/LO Databases/Corsham Library/driver$ ls
hsqldb.jar sqltool.jar

Given this the jar files would seem to be the correct version (or am I missing something?).

I then opened up that hsqdb.jar file and looked at the META.INF/MANFEST.MF file. The versions given were
Implementation-Version: 2.3.2
Bundle-Version: 2.3.2
Specification-Version: 2.3.2

I’ve checked these against the hsqldb.jar file copied from the source Windows machine and these numbers match. In short I don’t think that I have upgraded the database.

In that comment that you picked up “Which suggested that it applied an update when I ran it.” I was trying to say that your macro had worked in that it seems to have updated the path for the hsqldb.jar file, so had replaced the text “D:\LO Databases\Corsham Library\database/Corsham_Lib” with “/media/nick/Shared/LO Databases/Corsham Library/database/Corsham_Lib”.

#6

Looking more closely at that error message it includes “error in script file line: /media/nick/Shared/LO Databases/Corsham Library/database/Corsham_Lib 1” I looked at Line 1 of the script file for the database.

Line 1 says “SET DATABASE NAME HSQLDB8BD367B3FA”.

This was the line that created previous error messages, which I fixed, also after reviewing this web site, by upgrading my version of LibreOffice from 7.3 to 7.6.

#7

This may or may not be relevant as when I go to Edit>Database>Properties and select Test Class for the JDBC driver “org.hsqldb.jdbcDriver” I’m getting the message that the driver could not be loaded. Of course this could be another symptom of the real problem.

#8

OK, let’s try from the other end. Open your database document and run the following macro code. You can copy the little report from the message box.
Ensure that the paths to database and driver are correct. If so, and if you did not change the HSQL version, I’m clueless.
Once you get the error about SET DATABASE NAME, the next thing I would try is to simply delete that line from the script file after backing up the script file.

Sub ShowDatabaseProperties
doc = ThisComponent
ds = doc.DataSource
settings = ds.Settings
s = "Doc: "& doc.URL & chr(10) _
& "Data Source: "& ds.URL  & chr(10) _
& "JavaDriverClassPath: "& settings.JavaDriverClassPath  & chr(10) _
& "JavaDriverClass: "& settings.JavaDriverClass
MsgBox s
End Sub
#9

Could you please avoid spaces in paths?

No, with spaces it’s file:////media/nick/Shared/LO%20Databases/Corsham%20Library/driver/hsqldb.jar
as far as the Base document is concerned.
The path within the connection URL jdbc:hsql:file:… does not follow the standard. It can have spaces and even backslashes. I’m not sure if spaces masked with %20 actually work, since I avoid spaces in paths.

#10

Villeroy,

I removed the spaces from the folder and file names.

Here is the information reported from that macro.
ODB file path : file:///media/nick/Shared/LODatabases/CorshamLibrary/CorshamLibrary.odb
Data Source URL: jdbc:hsqldb:file:////media/nick/Shared/LODatabases/CorshamLibrary/database/Corsham_Lib;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
JavaDriverClassPath: file:///media/nick/Shared/LODatabases/CorshamLibrary/driver/hsqldb.jar
JavaDriverClass: org.hsqldb.jdbcDriver

Regards

Nick

#11 
ls /media/nick/Shared/LODatabases/CorshamLibrary/database/Corsham_Lib*

should return the names of your database backend files Corsham_Lib.script, Corsham_Lib.properties, Corsham_Lib.data.
and ls /media/nick/Shared/LODatabases/CorshamLibrary/driver/hsqldb.jar should return that file with no error.
org.hsqldb.jdbcDriver is definitively correct.
By any chance, do you have any class path to some hsqldb.jar registered in the LibreOffice Java options? If so, remove that one.
If you still get any errors, could you try some older backup of your database backend?

#12

1, To return the names of my database backend files
Corsham_Lib.script,
Corsham_Lib.properties,
Corsham_Lib.data.

nick@Lenovo-ideapad-330S-14IKB:~$ ls -l /media/nick/Shared/LODatabases/CorshamLibrary/database/Cor
-rwxrwxrwx 1 nick nick 16384 Mar 11 16:19 /media/nick/Shared/LODatabases/CorshamLibrary/database/Corsham_Lib.data
-rwxrwxrwx 1 nick nick 88 Mar 11 16:19 /media/nick/Shared/LODatabases/CorshamLibrary/database/Corsham_Lib.properties
-rwxrwxrwx 1 nick nick 2483 Apr 24 16:19 /media/nick/Shared/LODatabases/CorshamLibrary/database/Corsham_Lib.script

There is also /media/nick/Shared/LODatabases/CorshamLibrary/database/Corsham_Lib.tmp.

nick@Lenovo-ideapad-330S-14IKB:~$ ls -l /media/nick/Shared/LODatabases/CorshamLibrary/database/Corsham_Lib.tmp/
total 0

2, Show that the hsqldb.jar is in the correct folder

nick@Lenovo-ideapad-330S-14IKB:~$ ls -l /media/nick/Shared/LODatabases/CorshamLibrary/driver/hsqldb.jar
-rwxrwxrwx 1 nick nick 1473091 Nov 15 15:34 /media/nick/Shared/LODatabases/CorshamLibrary/driver/hsqldb.jar

3, The class path
Under LibreOffice Tools> Options> LibreOffice-Advanced> Class Path the entry under “Assigned folders and archives” is empty.

Regards

#13

Also, following the advise about user profiles given at JDBC Driver Could Not Be Loaded - #2 by Ratslinger, I restarted LibreOffice is safe mode and tried to open the tables in the database.

I still got the error message
SQL Status: S1000
Error code: -25

error in script file line: /media/nick/Shared/LODatabases/CorshamLibrary/database/Corsham_Lib 1 at /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:173

#14

Can you upload the database files somewhere?

#15

Villeroy, I’ll have a go.

#16

As suggested by Villeroy I copied the .opd files and the database and driver folders to another location and then re-ran the FreeHSQLDB macro for each .opd file.

This allowed me to access the tables for each .opd file.

I did have a problem with empty tables and this was solved by copying across the correct data files.

Thank you to Villeroy for your help on this.

As far as I’m concerned this tracker can be closed as “Solved”.

#17

Are you LibreOffice in a container? Snap, AppImage or alike? From their container, these apps can not see all devices such as drives mounted on /media. In this case, you need to configure the container application or install LO as a Debian package.

#18

Villeroy,

To install LO I downloaded a tar.gz file and unpacked it and then ran dpkg against all the .deb files.

Here are the outputs from the macro that you sent
ODB file path : file:///media/nick/Shared/SharedLODBs/CorshamLibrary/CorshamLibrary.odb
Data Source URL: jdbc:hsqldb:file:////media/nick/Shared/SharedLODBs/CorshamLibrary/database/Corsham_Lib;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
JavaDriverClassPath: file:///media/nick/Shared/SharedLODBs/CorshamLibrary/driver/hsqldb.jar
JavaDriverClass: org.hsqldb.jdbcDriver

You may be pointing to the cause of another problem that I have. I’m trying to list out all the properties and methods for a database. The macro is failing and the cause seems to be that it doesn’t recognise the object ThisDatabaseDocument. I’ve tried using ThisComponent but cannot work out from the BASIC help pages a valid command.

#19

Well, then your database on a shared drive should work with the adequate file permissions. Do you have similar problems on the Windows side?

#20

Windows works well.

Removing the spaces from the path and file names may have helped. Another option I did something wrong in the original copy from USB or afterwards.

Either way the databases seem to the working and I’m looking at BASIC commands to extract some information about them.

Thanks, again, for your help.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.