Test DB gets error "The driver class 'org.hsqldb.jdbcDriver' could not be loaded"

English
, , ,
#1

I am trying to set up a “test” database. I copy the existing .odb file with some related files and the \database and \driver subfolders to a new directory and rename the .odb file. When I try to do a Data > Refresh on a related .obt file, I get the above error. I tried changing the DataSource URL in the Database Properties to reflect the new name of the .odb file, but the change continually reverts to the original file name. The files are originally downloaded via the Dropbox client to my system. Is there someway this can be resolved?

JohnD

Register database
Base hsqldb.jdbc Driver can't load
error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]
#2

Does the error message start with “The connection to the data source “XXXXX” could not be established”? Also, not sure about “.obt file”? Could you explain that file type?

#3

Going to take a shot at answering this. The question is not exactly clear as to where or what is the actual problem. There are a number of ways to split a DB and this solution is because the split DB is not portable. I have seen this error message (combined with the one in my comment) often and here is a fix:

You only have to change two things to fix this. Using a BACKUP COPY (always protect yourself), open the .odb with an archive manager. Right click content.xml file and open with a text editor. Look for:

  data-source-setting-value>file:THE DIRECTORY CURRENTLY POINTED TO/hsqldb.jar

Example:

image description

and change it to the correct location of the driver directory of the .jar file. Save the change and update the file in the archive. Second, run the .odb and from the menu select Edit->Database->Properties... and make sure the directories in the Datasource URL are correct. Those two steps should fix the problem.

Edit 9/18/2017:

Hopefully this will clear up the problems and clarify many of the questions in the comments.

As stated above, there are many ways to create a split DB. Some automatically detect that the split DB has changed location and adjust accordingly. The above process is to fix those which don’t make this adjustment. If you can relocate your DB and it opens properly without a problem, then this issue can be ignored.

Saying that moving the DB itself is not a problem, let’s go on from there.

Dropbox. The link in one of the later comments actually contains two methods of use. One is to use the DB in the synced folder and the other is to move it to another until ready to save. This seems to be a matter of preference and I can see pros & cons for each.

Related Calc & Writer files. This is where I see the major portion of the problem. Data in these files is obtained by using the registered DB name. The fields set in the root content.xml files are set by this registered name and the table/query it comes from. Modifying this file to a different registered name is not recommended and can cause more problems than it may solve. Instead, each users’ system should utilize the same registered name. It can point to wherever the .odb is located on their system, but the name is key here.

So in your situation, you get the files from Dropbox. You move them to wherever on your system. We are using the basis here that moving the DB is not an issue and it automatically adjusts for its’ location. Insure the registered name on your system is the same as the one used in the related files and points to the .odb location on your system. In one of your comments you stated this was being used so you gave it a different name. If you do this you must change all the related files so they point to the different registered name. Pain. Better to just change your system to match these files.

At this point all should work.

It seems there are two things which should be of concern. Making sure the DB automatically adjusts its’ location when moved and that each system user has the same name for the registered DB. The actual location on each system can be different but the registered name is key here.

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

I don’t understand “open the .odb with an archive manager”. What archive manager? I tried the second part of your comment (“Edit->Database->Properties”), but it reverts back to the original DataBase name.

JohnD

#5

OK, I renamed it .zip, but there are a bunch of content.xml files. How do I find the real one?

JohnD

#6

There should only be one file named content.xml. This may be part of your problem, files in the .odb may be wrong. When first editing the archive, you should see folders for various items like forms, Dialogs, META-INF, reports and a couple of files: content.xml, mimetype, settings.xml. How many content.xml files do you have?

#7

My apologies. I should have been specific - the ‘content.xml’ file in root folder (should only be one).

Line 2 with the ‘hsqldb.jar’ file is the one causing the problem. Line one is also a problem but it gets its’ value from Edit->Database->Properties... . Both lines can be modified at the same time which eliminates the second step of editing the DB properties.

#8

There are numerous content.xml files. One in the root folder and one in each “Objxx” file included in the Forms and Reports subfolders. These other content.xml files do not contain a “file:” reference so they can be ignored.

The primary content.xml contains two “file:” references (I had to change the format of these in order to get them to be displayed here):

  1. "[db:data-source][db:connection-data][db:connection-resource xlink:href="jdbc:hsqldb:file:///C:\Users\{my dropbox folder}

  2. [db:data-source-setting-value>file:///C:/Users/{my dropbox folder]/driver/hsqld.jar…

I assume that I would need to change both of these to reflect the new folder?

JohnD

#9

@JohnD Please do not respond to answer with another answer. If reply longer, either use multiple comments or modify original question for further clarification.

#10

Sorry. This forum format is not the easiest to use. I did a direct download of the Dropbox files. The primary content.xml file was pointing to the Dropbox folder of the other person working on the files. I modified both “file” entries in that file, replaced the file in the .ODB structure and opened the revised file. I got the same error but it referenced the other person’s Dropbox folder. I searched all the .XML files in all folders, but could not find anything. Stumped.

JohnD

#11

I even looked in the hsqldb.jar file and found nothing there.

JohnD

#12

Couple of questions. Are you working from a linked Dropbox folder? After modifying the content.xml file, then after getting the error again, did you perhaps re-check that the changes made to the content.xml file were still there?

#13

Don’t understand - “I even looked in the hsqldb.jar file and found nothing there.”

#14

While waiting, here is what I did. Have Dropbox just for testing & answering questions. Linked folder. Connected to Dropbox. Copied to a new folder - database folder, driver folder & xxx.odb file. Opened xxx.odb with archive manager (Linux), modified both file names ( xxx.odb location & hsqldb.jar loaction), saved the changes. Opened the xxx.odb without any problem & independent of Dropbox files.

#15

In your original question you mention “…some related files.” What are these and how are they related?

#16

There are two Calc (.ods) sheets and a Writer (.odt) file. The problem I am having is trying to do a Data > Refresh to the Calc sheets. That is where I am getting the hsqld.jar error.

I am changing the extension from .odb to .zip, unzipping the file, modifying the content.xml file, rezipping, and changing it back to .odb. In the Edit>Database>Properties, if I try the “Test Class” or “Test Connection”, I receive an error: “The driver class org.hsqld.jdbcDriver could not be loaded”.

#17

Having this information is important. This is why it is important to either be concise in the question or answer questions asked. Original comment asked about .obt file. Now it is apparent it is an .odt file - misspelling. Original answer stated ‘…question is not exactly clear as to where or what is the actual problem’. This may help in future for you when asking questions. In all fairness, you did state in question error was on related file. However, I got stuck on the database side.

#18

When an embedded DB is split, the “Test Class” or “Test Connection” are no longer functional. Unfortunately, it returns an error instead of a better message. Now for the Writer/Calc problem. Data Sources in these are only obtainable via Registered databases. In your situation, you most likely have the data source pointed at the incorrect .odb file. From the menu go to Tools->Options then under LibreOffice Base->Databases insure the registered name points to the correct database.

#19

Depending upon your setup, you may actually have to set this as a new registered name. In that case the Data Sources in the documents would also need to be changed.

#20

I had renamed the .ODB file and registered it as LO would not allow more than one registered DB with the same name. That did not help. I unzipped the .ODS file and searched the content.xml, but no Data Source was in it. Looking at this now, it really won’t get us anywhere. There will eventually be several people working on these files. Having them updated in the Dropbox folder which is in an individual’s local User folder will just cause a nightmare.

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