error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]

English
#1

Having previously created and used many times, ‘split’ databases using hsqldb.jar (ver 2.4.0), I now cannot gain access to the actual database tables; the above message appears. The same message is displayed when attempting to open a form, the form requiring access to the database tables.

I have ‘googled’ for a solution without success, the general consensus being that LO (along with hsqldb.jar) has been updated since being created with hsqldb.jar (ver 1.8.n)

It has been quite a while since I last used the databases, and LO has been upgraded (probably several times) in the meantime.

Any suggestions/guidance gratefully received.

Edit 24 Dec 2019 14:05 (CET)

Thank you for your help so far. I certainly didn’t intend to trivialise your investigations to date.

I cannot attach a copy of the Batteries.odb as it is 1.6 MB and the max upload size is 1048576K

Edit 28 Dec 2016 16:50 (CET)

Books.odb : Books.odb

Test.properties: Test.properties.doc

Error moving split database to new computer (error in script file line: 1 Unexpected token UNIQUE)
Error in script file line 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]
#2

Hello,

Have deleted the comment as have been able to duplicate the problem.

Had a split DB created with 2.x version (sorry don’t remember which). Downloaded & installed HSQLDB v2.4.1 (that is replaced hsqldb.jar file in driver folder). No problem. Did same for HSQLDB v2.4.0 and again no problem. Then tried HSQLDB v1.8.x and got the error message you have.

The hsqldb.jar file you have is incorrect. It is a version older that what was used to create the database. Place the correct file in the driver folder and you should be running again.

Edit 2019-12-22:

Don’t know if you copied the files correctly when posting. The .data and .properties files were reversed. Once I figured this out ran the test and had no problems. Here is some of the tables:

image description
image description893×638 101 KB

Except for reversing the mentioned file extensions, did not need to alter anything. This used HSQLDB v2.4.0

#3

OK, thanks for you reply.

I have ensured that both the hsqldb.jar and sqltool.jar are all at version 2.4.0, but the error persists. I guess you’d like to the relevant files, so I’ve attached them in the ‘Propose Your Solution’ section below.

#4

Yes, somehow the copying of the files had ‘swapped’ the two around!! However, they are correct in the original database, and I still get the above error message. Back to square 1!

#5

No this is not square 1. I have demonstrated that the files are intact and the error is on your side - again all pointing to an incorrect jar file or incorrect pointer to the jar file.

As it seems this is not personal/confidential information please post the Base .odb file. From what I have already I should be able to construct a working split db for you.

#6

@johnh009,

You must have some real bloat in that Base file. I have a 20 form, 15+ query Base file with around 1000 lines of code and it is less than 300kb. So here are your choices. Make a copy and eleminate some of that bloat and post it, or find another freeware site to post the full .odb (maybe something like DropBox), or try to repair yourself - see my answer in this post → Test DB gets error “The driver class ‘org.hsqldb.jdbcDriver’ could not be loaded”. Be sure to read answer & all comments in that post.

#7

@Ratslinger
My split databases have been created using the template and instructions from the the following link:

https://forum.openoffice.org/en/forum/viewtopic.php?f=83&t=61183

The template is 1.6 MB and a newly created database is the same size.

OK, I went through the info in the link you provided and the data is all as it should be.

As I have 4 split databases, all using the above template (that hasn’t been updated since April 2013), and all are displaying the same error, I’m wondering whether a LibreOffice update has caused some incompatibility. My system:
Linux 5.3.0-2-amd64
LibreOffice Version: 6.3.4.2.0+

#8

By what you posted don’t know which Linux OS you are using - there are quite a few.

The template you are using contains the Java file - most of the size you are experiencing. This is not needed once the initial process is completed.

Make a copy of the .odb and using the copy change the extension to .zip and open with Archive manager. On the first screen will be a folder driver - delete it. Close the archive, reset the extension to .odb and post the file.

Deleted last comment as you state all your split files are inoperative.

Have been using the same split template for years and did testing of your files with it on Ubuntu 18.04 Mate with TDF LO v 6.3.4.2 - no problems here.

#9

@Ratslinger

Sparky Linux, based on Debian Testing.

Something is definitely wrong, perhaps with my system. When I attempt to create a new split database (Test), using the above template, the .properties file shows that the HSQLDB engine is version 1.8!

I have attached the .odb file (Books) and the .properties (Test) files in the 1st question section above.

#10

@johnh009,

Don’t understand why you suddenly changed directions here and did not post Batteries .odb. May not matter though.

It is possible you have done something I have stated in various answers/comments should not be done - adding an hsqldb class path. From menu go to Tools->Options. Then under LibreOffice->Advanced click on Class Path button. If you see an item there for .../hsqldb.jar select it and press Remove. Close all open LO items and try your split DB once again.

#11

Sorry, in trying to fix Batteries.odb, I ahve done something that results in a different error message, even with the backup.odb I created beforehand! But that, for the time being, is another problem. Books.odb is one of the 4 that displays the original error message.

Yes, I removed all ‘Class Paths’ as per the link you posted a couple of comments back.

#12

Unfortunately unless I have direct access to your system there isn’t much else I can do remotely. Have offered the best solutions based upon the error message. Have reproduced both methods and given solutions to each.

One final possibility is to reset your user profile. See → LibreOffice user profile. Make sure you back up the user profile as noted in the post.

#13

@johnh009,

Now I am a bit confused. Went back and reviewed the link from my comment. Looked through it multiple times and, unless my eyes/mind are failing, I don’t see any mention of “Class Paths” there. Also can’t say I ever in any post have stated to remove ALL “Class Paths”. Some may be necessary for connecting to various databases. I personally have a few.

Could you please verify that setting in Options?

#14

OK, thank your time and suggestions, and for that pointer - a little progress I think! When starting in ‘Safe Mode’, I can see everything; the fact that they - the tables at least (I didn’t try modifying the form) - cannot be modified/edited, is I presume, normal in ‘Safe Mode’. When trying some of the options presented on the ‘Safe Mode’ dialogue window, nothing appears to make any difference, i.e. when starting again in normal mode, the same error message is displayed whenever access to the tables is attempted. So what to do now? Would a complete removal of the config file(s), or of LibreOffice (and then reinstallation) work?

#15

Not certain what you have done. You have not responded to my last comment and your comment also points to something in “Class Path”. Safe mode eliminates these.

As for the config file, have already mentioned to create a backup copy. The linked instructions explain what to do next. Best to follow those instructions. Don’t see that re-installing LO is of any benefit.

#16

@Ratslinger

Thanks again for your time and suggestions.

Sorry for the confusion (mine), re: “Class Path”, as you say, does not appear in the link you provided several comments back. But it does 4 comments back.

I tried all of the suggestions in the ‘Profile’ link, and as I said earlier, nothing fixed the problem. So I completely uninstalled LibreOffice and reinstalled - see my edit in the ‘Answer’ section.

#17

Batteries.data.doc
Batteries.properties.doc
Batteries.script.doc

If there are any other files that you require, please let me know. Thanks

As the problem seemed to point to a ‘profile’ problem, I completely removing all LibreOffice and all associated files (sudo apt-get autoremove), having first renamed the profile file (home/~/.config/libreoffice) and then reinstalled LibreOffice. The data can now be accessed.

#18

@johnh009,

Please only use answer to answer the original question. This would be better as an edit to original question.

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