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

English
#1

Ubuntu 22.04
LibreOffice
Version: 7.3.2.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 2; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-NZ (en_NZ.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.2-0ubuntu2
Calc: threaded

I have two split databases created using Split_HSQLDB_2.3.2_Wizard_v3d.odb

When I try to open those split databases I get the message:

The connection to the data source “electricity_db” could not be established.
error in script file line 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE] ./connectivity/source/drivers/jdbc/Object.cxx:175

In the electicity.script file, line 1:
SET DATABASE UNIQUE NAME HSQLDB7F6901B26D

I can restart LibreOffice in safe mode, which allows one to open the above or another split database file without complaint.

Here suggests the problem is with my user profile:
https://wiki.documentfoundation.org/UserProfile#GNU.2FLinux

But I have tried the manual steps there to force LibreOffice to create a new user profile and the problem persists. Only using safe mode itself seems to allow me to open the split database files.

In Safe Mode, if I reset to factory settings–either Reset settings and user interface modifications, or Reset entire user profile–then Apply Changes and Restart, I can open the split database files without problem, but only so long as that instance of LibreOffice stays open, i.e. closing/quitting LibreOffice and re-opening the database files returns one to the error message behaviour.

There’s an earlier query with this problem/title here: error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]

That seems to have been resolved by cleaning all LibreOffice before reinstalling. I tried that but am still stuck with the same problem.

Thanks for any help.

1 Like
#2

Open the script file and remove the line.

#3

OK, if I remove that first line the error message becomes:

The connection to the data source “electricity_db” could not be established.
error in script file line 1 Unexpected token GC, requires COLLATION in statement [SET DATABASE GC] ./connectivity/source/drivers/jdbc/Object.cxx:175

The first line in electricty.script has now become:

SET DATABASE GC 0

i.e. that was previously the second line.

Is LibreOffice expecting a COLLATION statement of some kind in the first line? If so, what syntax?

#4

Just make a backup of your script file and try out line by line. So far I could remove these lines without negative effect. An explicit COLLATION is not required. You need it when you want some special sort orders for strings.

#5

OK, I’ve pared the script file down to the following:

CREATE USER SA PASSWORD DIGEST ‘d41d8cd98f00b204e9800998ecf8427e’
ALTER USER SA SET LOCAL TRUE
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE CACHED TABLE PUBLIC.“t_prices”(“ID” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,“start_date” DATE,“end_date” DATE,“peak_price” DECIMAL(100,4),“off_peak_shoulder_price” DECIMAL(100,4),“night_price” DECIMAL(100,4),“daily_fixed_charge” DECIMAL(100,4))
ALTER TABLE PUBLIC.“t_prices” ALTER COLUMN “ID” RESTART WITH 1
CREATE CACHED TABLE PUBLIC.“t_meters”(“ID” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,“start_date” DATE,“end_date” DATE,“peak_usage” DECIMAL(100,2),“off_peak_shoulder_usage” DECIMAL(100,2),“night_usage” DECIMAL(100,2),“hour_of_power_usage” DECIMAL(100,2))
ALTER TABLE PUBLIC.“t_meters” ALTER COLUMN “ID” RESTART WITH 3
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
SET TABLE PUBLIC.“t_prices” INDEX ‘19 0 1’
SET TABLE PUBLIC.“t_meters” INDEX ‘13 0 3’
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)

Each time I get an error message. As above the error message says:

error in script file line: 1 Unexpected token: d41d8cd98f00b204e9800998ecf8427e in statement [‘d41d8cd98f00b204e9800998ecf8427e’] ./connectivity/source/drivers/jdbc/Object.cxx:175

If I remove that first line it complains:

error in script file line: 1 User not found: SA in statement [ALTER USER SA] ./connectivity/source/drivers/jdbc/Object.cxx:175

I have tried removing all lines down to the line that begins:

CREATE CACHED TABLE PUBLIC.“t_prices”…

i.e. the SQL that creates my tables. I don’t think I would have a usable database without those. Each time I get some sort of error message. In that last case I get:

error in script file line: 5 invalid schema name: SYSTEM_LOBS in statement [ALTER SEQUENCE SYSTEM_LOBS.LOB_ID] ./connectivity/source/drivers/jdbc/Object.cxx:175

so simply removing lines from the top of my script file doesn’t seem to solve the problem.

Error moving split database to new computer (error in script file line: 1 Unexpected token UNIQUE)
#6

I tried to create a new split database using the Split_HSQLDB_2.3.2_Wizard_v3d.odb file. After defining a table same as in my above example, when trying to save that table, I see the error message:

Error while saving the table design

Unexpected token: GENERATED in statement [CREATE TABLE “t_prices” (“ID” INTEGER NOT NULL GENERATED] ./connectivity/source/drivers/jdbc/Object.cxx:175

I do not face any errors if I simply try to create a database without cloning the split database wizard file, i.e. just a normal “embedded” LO base database. But I do not want to use embedded database files due to the risk of corruption/files becoming unusable.

#12

I had the same issue on ubuntu 20.04. Solved using the LO .deb version 7.3.5.2

#13

now with version LO 7.3.7.2 I’m getting the same error again… :frowning:

#7

Of course you do this with a copy of your original embedded database.
Make another copy and try this macro of mine: [Python] Macro to extract and reconnect embedded HSQLDB (View topic) • Apache OpenOffice Community Forum
Put your embedded DB in a directory, add a subdirectory “driver” where you put your hsqldb.jar. NOT the latest version! It has to be before 2.5. Install the macro and run it.

#8

I grabbed hsqldb-2.4.1.jar and used it, but renamed it to hsqldb.jar because otherwise LO base doesn’t recognise it.

As per here: Apache OpenOffice Community Forum - [Python] Macro to extract and reconnect embedded HSQLDB - (View topic)
I installed libreoffice-script-provider-py via terminal, i.e. following instructions here: How to install libreoffice-script-provider-python ubuntu package on Ubuntu 20.04/Ubuntu 18.04/Ubuntu 19.04/Ubuntu 16.04

Your ExtractHSQL.py.odt file created a python3 script here:

[Home] /.config/libreoffice/4/user/Scripts/python/pyDBA/ExtractHSQL.py

Opening either of my two LO base split database files previously created using Split_HSQLDB_2.3.2_Wizard_v3d.odb

I open the menu:

Tools → Macros → Organize Macros → Python

select the Main method in your python script in the pyDBA sub-directory, and press the Run button.

It produces an error dialog:

ExtractHSQL.py: No database found.
Essential HSQL files missing in document and file system. Nothing to connect with.

Here’s one of my database folder structures:

/electricity_db/
… electricity_db.odb
… /driver/
… … hsqldb.jar
… … sqltool.jar
… /database/
… … electricity.data
… … electricity.properties
… … electricity.script

I think that matches the structure shown here:
[more than 2 links in post not allowed on this site:] https ://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=86071

except that I have never had any file named “electricity.backend” in the database subdirectory. In one case I have a file called “mydb.backup” in that database subdirectory, but I still got the same above error message from running your python script.

The only change to the above structure of files has been replacing the hsqldb.jar file that was created/generated when I first used Split_HSQLDB_2.3.2_Wizard_v3d.odb to create this split database, when running Ubuntu 21.10 earlier this year, with the hsqldb-2.4.1.jar file I downloaded earlier today.

Thank you for all your help to date–much appreciated.

#9

Ah, I should have read your post more carefully, sorry. I don’t have an embedded db, I have two db created as split db, but which now only work if run in safe mode, after upgrading from Ubuntu 21.10 to 22.04 and the LibreOffice that comes with it: 7.3.2.2–see full info. in first post above.

Just out of curiosity, I tried your python script again, but instead of choosing Main I instead tried to revert the db from split to embedded. When I select the “Revert_To_Embedded” method and press the “Run” button I also get an error message:

Revert_To_Embedded: No databse [sic]
This document container does not contain any database folder.
Nothing to do.

Same result with my second split database.

#10

And I took for granted that you have one :wink: simply because every Base connection with HSQL must have started as an embedded DB which of course is a wrong assumption.

If there is no HSQLDB embedded in your database document then there is nothing to extract, nothing to reconnect and nothing to remove. My macro could be used to connect a set of database files with the document.
No, sorry. I don’t know what to do with those error messages. Until now I could always fix these errors by deleting the offending line mentioned in the error message from the script file.

#11

I’ve solved the above problem by getting a slightly newer version of LibreOffice from their download page–for Ubuntu download the .deb packages. It’s not yet in the Ubuntu software updates but presumably will arrive in time.

Version: 7.3.3.2 / LibreOffice Community
Build ID: d1d0ea68f081ee2800a922cac8f79445e4603348
CPU threads: 2; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-NZ (en_NZ.UTF-8); UI: en-US
Calc: threaded

And after removing all of the fomer LO (7.3.2.2) bits & pieces–see here: apt - How to uninstall LibreOffice? - Ask Ubuntu

I installed the new version, 7.3.3.2, and there are now no problems opening and using my previously created split database files.

My sincere thanks to Villeroy for all their kind patience and suggestions, and not just here but for so many others as well.

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