Ask Your Question
0

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

asked 2017-09-12 14:59:37 +0200

johnpd gravatar image

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

edit retag flag offensive close merge delete

Comments

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?

Ratslinger gravatar imageRatslinger ( 2017-09-12 16:36:59 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2017-09-12 22:06:49 +0200

Ratslinger gravatar image

updated 2017-09-18 18:29:05 +0200

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 ... (more)

edit flag offensive delete link more

Comments

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

johnpd gravatar imagejohnpd ( 2017-09-13 13:41:54 +0200 )edit

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

JohnD

johnpd gravatar imagejohnpd ( 2017-09-13 14:18:41 +0200 )edit

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?

Ratslinger gravatar imageRatslinger ( 2017-09-13 17:12:57 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-09-15 17:38:38 +0200 )edit
0

answered 2017-09-15 13:35:22 +0200

johnpd gravatar image

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

edit flag offensive delete link more

Comments

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

Ratslinger gravatar imageRatslinger ( 2017-09-15 18:43:25 +0200 )edit

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

johnpd gravatar imagejohnpd ( 2017-09-16 03:34:07 +0200 )edit

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

JohnD

johnpd gravatar imagejohnpd ( 2017-09-16 03:37:41 +0200 )edit

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?

Ratslinger gravatar imageRatslinger ( 2017-09-16 03:47:28 +0200 )edit

Don't understand - "I even looked in the hsqldb.jar file and found nothing there."

Ratslinger gravatar imageRatslinger ( 2017-09-16 03:48:59 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-09-16 04:05:48 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-09-16 06:02:36 +0200 )edit

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".

johnpd gravatar imagejohnpd ( 2017-09-16 07:05:11 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-09-16 14:40:59 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-09-16 14:50:41 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-09-16 15:08:42 +0200 )edit

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.

johnpd gravatar imagejohnpd ( 2017-09-17 04:22:42 +0200 )edit

We need to have everyone set up the same folder structure on their computer (such as "C:\LO_DB"), have all changes made there, and then copy those files to their local Dropbox folder where they will be updated to the Dropbox server. That would eliminate any specific references to one person's computer. The question is how do we eliminate those references from the existing files? I found the .ODB references but not the .ODS ones.

johnpd gravatar imagejohnpd ( 2017-09-17 04:28:22 +0200 )edit

When using Dropbox use a synced folder and run from there. This keeps the the dropbox files up to date without moving things around. The registered name of the DB is retained locally in LO so it can be used across all LO modules. Using Dropbox and having multiple users updating the files requires co-ordination. No two can update simultaneously. View this post for one users solution.

Ratslinger gravatar imageRatslinger ( 2017-09-17 15:39:07 +0200 )edit

Just a further note of clarification. User A gets access to Dropbox files. User B also gets access. If they each move these from the Dropbox folder and work on them locally, when placed back in the Dropbox folder it will replace (not an update of data) the files. So User A replaces the files first, then user B replaces the files & user A's work is lost.

Ratslinger gravatar imageRatslinger ( 2017-09-17 15:46:36 +0200 )edit

Just looked at one of my Calc files with a DB link - it is in the root content.xml. I found my connections by doing a search on table/query name used. Registered database name is also in there.

Ratslinger gravatar imageRatslinger ( 2017-09-17 19:07:50 +0200 )edit

I understand the potential problem with multiple users accessing the same file and making individual changes. We would have to set up some process to allow only one person at a time to change a particular file. That is what the post you reference discusses. It also mentions setting up a separate folder where changes would be made and then copied to the Dropbox folder for updating to the Dropbox server. That is my thinking as well.

johnpd gravatar imagejohnpd ( 2017-09-18 11:21:54 +0200 )edit

I looked at the content.xml file for one of the Calc sheets in question and could not find any directory reference. How are you searching? He set up some macros in some of these. I looked under the LibreOffice Basic Macro and noticed that under the .odb reference was a "Standard" > "Embedded" section which indicates the the HSQLDB is "installed". Does this have any affect?

johnpd gravatar imagejohnpd ( 2017-09-18 11:57:29 +0200 )edit

Please see edit in my answer.

Ratslinger gravatar imageRatslinger ( 2017-09-18 18:29:37 +0200 )edit

Ratslinger,

We finally got it sorted out. I am not exactly sure what it was. It could be that I was renaming the .odb file but not the files in the "database" subfolder. I deleted everything I had (folders, files, registered names, etc.), re-downloaded the files from Dropbox, copied them to a new folder, and registered the database. I was then able to do the Data > Refresh. Thanks for your help.

johnpd gravatar imagejohnpd ( 2017-09-26 12:53:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-12 14:59:37 +0200

Seen: 1,236 times

Last updated: Sep 18 '17