[Solved] Multiple user login with HSQLDB 1.8.0.10

[syp1977]

I have referred following regarding making multiple users and authentication of users for tables:
viewtopic.php?f=40&t=62725
My questions are:
1. How can we delete the user that we created?
2. What are the maximum number of users allowed?
3. How can we record the logins happen during the usage of database? like who has logged on during the time and what changes he has done in database.

Thank you in advance.
Best Regards

[Villeroy]

Refer to the documentation of your connected database. In case of HSQL you downloaded the full documentation together with the hsqldb.jar library and the hsql tools.

[F3K Total]

Hello
1.)

Code: Select all

Drop User <USERNAME>

2.) i just tested to create 10000 users, no problem, if you need more, tell me, i can try it, using a little script.
3.) can be done with TRIGGERS, here's an example Code:

Code: Select all

CREATE TRIGGER UPDATE_USER_TIMESTAMP_LOGTABLE
AFTER UPDATE ON "Persons_Data"
REFERENCING OLD ROW AS OLDROW NEW ROW AS NEWROW
FOR EACH ROW WHEN (COALESCE(OLDROW."TIMESTAMP",TIMESTAMP '1800-01-01 00:00:00') <> CURRENT_TIMESTAMP)
BEGIN ATOMIC
DECLARE NEW_ID INT;
SELECT ID INTO NEW_ID FROM "Persons_Data" WHERE ID=OLDROW.ID;
INSERT INTO "Logtable" ("TIMESTAMP","USER","FK_ID_PD") VALUES (CURRENT_TIMESTAMP,CURRENT_USER,NEW_ID);
END

This trigger reads the ID from the row which was UPDATED in table "Persons_Data", then creates a new row in "Logtable", containing the CURRENT_TIMESTAMP, CURRENT_USER and the ID as Foreign Key from "Persons_Data"

[syp1977]

Following error observed while using trigger:
Unexpected end of command: REFERENCING in statement [CREATE TRIGGER UPDATE_USER_TIMESTAMP_LOGTABLE
AFTER UPDATE ON "Persons_Data"
REFERENCING]

Much appreciated help.

[F3K Total]

You have to adjust the trigger to your belongings, or do you have a table called "Persons_Data"?

[syp1977]

Yes, I created Persons_data table with fields current time_stamp, User and ID

[RoryOF]

The table name and the sought name may be case sensitive.

[syp1977]

I have same field names and table name as mentioned in sql code.

[syp1977]

Hello F3K Total,
Let me clarify, I have made two tables:
1. Persons_Data, having fields ID and Name. Where ID is the one which is used to username for logins and Name is name of the individual
2. Logtable, having fields ID, TIMESTAMP, USER, FK_ID_PD

Does that clarify, why I am getting following error:
"Unexpected end of command: REFERENCING in statement [CREATE TRIGGER UPDATE_USER_TIMESTAMP_LOGTABLE
AFTER UPDATE ON "Persons_Data"
REFERENCING]"

Thanks in advance.

[F3K Total]

What Version of HSQLDB do you use?
If i remember right, in the "old version", shipped with the office, triggers don't work.
Can anyone confirm that?
Maybe you need to upgrade.

[syp1977]

Hi F3K Total,
I am using 2.6.1 version...
What version you recommend.

[F3K Total]

2.6.1. works, i tested it.
Load up a sample db with your problem, then we can have a look.

[F3K Total]

Here's another way, to test it

• create an empty HSQL Database version 2.6.1 and connect it to OpenOffice.
• Execute the following commands via Tools/SQL... to create the two tables Persons_Data and Logtable

  Code: Select all

  CREATE CACHED TABLE PUBLIC."Persons_Data"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"FON" VARCHAR(25) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"MAIL" VARCHAR(100) NOT NULL,"BD" DATE NOT NULL,"WORK" VARCHAR(40) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"ADDR" VARCHAR(50) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"CITY" VARCHAR(40) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"USER" VARCHAR(5),"TIMESTAMP" TIMESTAMP(0));
  CREATE CACHED TABLE PUBLIC."Logtable"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"FK_ID_PD" INTEGER,"USER" VARCHAR(10),"TIMESTAMP" TIMESTAMP,FOREIGN KEY("FK_ID_PD") REFERENCES PUBLIC."Persons_Data"("ID") ON DELETE CASCADE ON UPDATE CASCADE);
  CHECKPOINT DEFRAG

• Now execute this command, to create three triggers INSERT_USER_TIMESTAMP, UPDATE_USER_TIMESTAMP and UPDATE_USER_TIMESTAMP_LOGTABLE

  Code: Select all

  CREATE TRIGGER PUBLIC.INSERT_USER_TIMESTAMP BEFORE INSERT ON PUBLIC."Persons_Data" REFERENCING NEW ROW AS NEWROW FOR EACH ROW BEGIN ATOMIC SET NEWROW."USER"=CURRENT_USER;SET NEWROW."TIMESTAMP"=CURRENT_TIMESTAMP;END
  CREATE TRIGGER PUBLIC.UPDATE_USER_TIMESTAMP BEFORE UPDATE ON PUBLIC."Persons_Data" REFERENCING OLD ROW AS OLDROW NEW ROW AS NEWROW FOR EACH ROW WHEN (COALESCE(OLDROW."TIMESTAMP",TIMESTAMP '1800-01-01 00:00:00') <> CURRENT_TIMESTAMP) BEGIN ATOMIC SET NEWROW."USER"=CURRENT_USER;SET NEWROW."TIMESTAMP"=CURRENT_TIMESTAMP;END;
  CREATE TRIGGER PUBLIC.UPDATE_USER_TIMESTAMP_LOGTABLE AFTER UPDATE ON PUBLIC."Persons_Data" REFERENCING OLD ROW AS OLDROW NEW ROW AS NEWROW FOR EACH ROW WHEN (COALESCE(OLDROW."TIMESTAMP",TIMESTAMP '1800-01-01 00:00:00') <> CURRENT_TIMESTAMP) BEGIN ATOMIC DECLARE NEW_ID INT;SELECT ID INTO NEW_ID FROM PUBLIC."Persons_Data" WHERE ID=OLDROW.ID;INSERT INTO PUBLIC."Logtable"("TIMESTAMP","USER","FK_ID_PD")VALUES(CURRENT_TIMESTAMP,CURRENT_USER,NEW_ID);END;

• change to menue View/Refresh Tables
• open table Persons_Data and insert the first row, without the last two columns, save the row, and see how columns USER and TIMESTAMP will be inserted by trigger INSERT_USER_TIMESTAMP
• now wait a minute and then modify any data of that first row and store it, you will see, column TIMESTAMP will be changed by trigger UPDATE_USER_TIMESTAMP
• open table Logtable and see the first row, created by trigger UPDATE_USER_TIMESTAMP_LOGTABLE

R

[syp1977]

Hi,
Please find attached sample file.
I encountered error:"1: Unexpected token: COLLATE in statement [CREATE CACHED TABLE PUBLIC."Persons_Data"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"FON" VARCHAR(25) COLLATE]"

[F3K Total]

We can do nothing, as long the database files are missing
D:\Inventory - Copy\New Experiment\database - Copy\mydb
R

[syp1977]

Sorry for my ignorance.
PFA..link
https://wetransfer.com/downloads/306851 ... 254/6fc994
Regards,

[F3K Total]

Your kidding with me

Re: Multiple User login
Postby syp1977 » 30 Dec 2021, 07:19
Hi F3K Total,
I am using 2.6.1 version...
What version you recommend.

What you sent was not HSQL Version 2.6.1 but 1.8.0.10!
When i try to execute the trigger definition, i get the same error

1: Unexpected end of command: REFERENCING in statement [CREATE TRIGGER PUBLIC.INSERT_USER_TIMESTAMP BEFORE INSERT ON PUBLIC."Persons_Data" REFERENCING]

Changing to HSQLDB Version 2.6.1, no Problem!

Attached your db in Verion 2.6.1, containing the triggers.

R

[syp1977]

Sorry for being completely ignorant.
I have replaced the database file and I got following error:
"The connection to the data source "mydb_wizard" could not be established. error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]"

I have tried to follow viewtopic.php?f=40&t=84098, so as to update my existing HSQDB version. But it shows error:
"The connection to the data source "mydb_wizard" could not be established. The driver class 'org.hsqdb.jbcDriver' could not be loaded. The additional driver class path is 'file:///....../driver/hsqldb.jar'.

Let me know where I must have gone wrong.

[Villeroy]

The error message indicates that you try to access a HSQL2 database with a HSQL1 driver.

In the following I assume that you still have a copy of the original file with a working embedded HSQL.

1) Create a new directory and store a copy of the original file in that directory. Test if this database is working actually.
2) Download the attached Writer document from [Python] Macro to extract and reconnect embedded HSQLDB and put it in a trusted directory where documents are allowed to execute embedded macro code.
3) Open the Writer document, push the button, close the document.
4) Open the database document created in 1)
5) Call menu:Tools>Macros>Run>pyDBA>ExtractHSQLDB>Main [Run]. Notice the success messages, note any errors.
6) Test your modified database. Open some table, query, form, report.

Now you have a database document which is connected to the database in subdirectory "database" using the native HSQL 1.8 driver of your office suite. When either your database directory or the installation path of the office suite with the driver hsqldb.jar has moved, just repeat step 5). The macro will detect the extracted database and promts you to re-connect to that database. The only precondition is that your database document has the same name prefix as the related database files in the subdirectory (MyDB.odb belongs to database/MyDB.script, database/MyDB.properties, database/MyDB.data).
-----------
Upgrade from HSQL1 to HSQL2 works only with HSQL versions prior to 2.5. On the HSQL website you can find older versions. Just pick the latest version of the 2.4. branch: https://sourceforge.net/projects/hsqldb ... p/download It contains a subdirectory "lib" with a driver hsqldb.jar.
Add a subdirectory "driver" to the directory created in 1) and extract your hsqldb.jar into that directory.
Repeat step 5). This will detect the existing database files and driver and convert the external HSQL 1.8 database on the fly. After that, your database won't be compatible with HSQL1 anymore. So backup your database files!
From version 2.4.1 you may upgrade to 2.5.1. This can not be done directly because 2.5.x can not convert HSQL1 anymore.
From version 2.5.1 you may upgrade to 2.6.x. But you better don't because LO has a problem with the privilege system of HSQL 2.6.x. as described in HSQLDB MULTIUSER, GRANTING not working in HSQLDB 2.6.1

[syp1977]

Hello,
Sorry for such elongated answer..
I have tried following steps:
1) Created a new directory and copied mydb.backup, mydb.data, mydb.properties, mydb.script and mydb_wizard.odb. When I opened
It showed me error "hsqldb.jar not found" :
"Please add a copy of the HSQLDB engine (hsqldb.jar) to the current folder :
P:\SYP\database - Copy\database\
NOTE: This is necessary for proper wizard function, but additional benefits include :
* enhanced portability of the database-folder
* ensures database compatibility across computers and *Office installations
* guards against inadvertent upgrade of your database since the results are uncertain and irreversible
* hsqldb 2.x provides a built-in database management GUI accessible by clicking hsqldb.jar."

I clicked ignore and proceeded to click table, but it showed error "The driver class 'org.hsqldb.jdbcDriver' could not be loaded.The additional driver class path is 'file:///P:/SY/database%20-%20Copy/database/hsqldb.jar'."

Later I proceeded for following steps

2) Downloaded the attached Writer document from [Python] Macro to extract and reconnect embedded HSQLDB
3) Opened the Writer document, pushed the button, closed the document.
4) Opened the database document created in 1)

5) menu:Tools>Macros>Run>pyDBA>ExtractHSQLDB>Main [Run]. Got error "Essential HSQL files missing in document and file system. Nothing to connect with."

6) Copied hsqdb.jar 1.8 to the folder and repeat step 5) still I got same error "Essential HSQL files missing in document and file system. Nothing to connect with."

7) Downloaded hsqldb 2.4.1. Created folder "driver" in 1) and copied hsqldb.jar 2.4.1 into it and repeated step 5), got the same error "Essential HSQL files missing in document and file system. Nothing to connect with."

8) Once again run step 5)
"com.sun.star.uno.RuntimeException: Error during invoking function Main in module file:///C:/Users/Priti%20Shailesh%20Parab/AppData/Roaming/LibreOffice/4/user/Scripts/python/pyDBA/ExtractHSQL.py (<class 'NameError'>: name 'Null' is not defined
File "C:\Program Files\LibreOffice\program\pythonscript.py", line 915, in invoke
ret = self.func( *args )
File "C:\....................\LibreOffice\4\user\Scripts\python\pyDBA\ExtractHSQL.py", line 151, in Main
dba = DBA()
File "C:\....................\LibreOffice\4\user\Scripts\python\pyDBA\ExtractHSQL.py", line 62, in __init__
url = self.getURLStruct(self.odb.getURL())
File "C:\....................\LibreOffice\4\user\Scripts\python\pyDBA\ExtractHSQL.py", line 35, in getURLStruct
return x[0]==True and x[1] or Null
)"

9) Run sql as given by F3K
"CREATE CACHED TABLE PUBLIC."Persons_Data"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"FON" VARCHAR(25) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"MAIL" VARCHAR(100) NOT NULL,"BD" DATE NOT NULL,"WORK" VARCHAR(40) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"ADDR" VARCHAR(50) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"CITY" VARCHAR(40) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"USER" VARCHAR(5),"TIMESTAMP" TIMESTAMP(0));
CREATE CACHED TABLE PUBLIC."Logtable"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"FK_ID_PD" INTEGER,"USER" VARCHAR(10),"TIMESTAMP" TIMESTAMP,FOREIGN KEY("FK_ID_PD") REFERENCES PUBLIC."Persons_Data"("ID") ON DELETE CASCADE ON UPDATE CASCADE);
CHECKPOINT DEFRAG"
Got error
"1: Unexpected token: COLLATE in statement [CREATE CACHED TABLE PUBLIC."Persons_Data"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"FON" VARCHAR(25) COLLATE]"

Please let me know where I might have gone wrong.

[Villeroy]

Forget my sloppy macro code and upload your database document.

[syp1977]

Hello,
Please find below link..
https://wetransfer.com/downloads/306851 ... 254/6fc994
Regards,

[Villeroy]

I downloaded your archive and extracted it to a trusted directory. It works fine.
The auto-open macro connects the Base document to the mydb.* files through the HSQL1.8 driver in the same directory. I can open all forms and tables with no error message.

[syp1977]

Hi,

I downloaded your archive and extracted it to a trusted directory. It works fine.
The auto-open macro connects the Base document to the mydb.* files through the HSQL1.8 driver in the same directory. I can open all forms and tables with no error message.

I can also open all forms, tables and there is no error message.
But still I am not able to execute following SQL
"CREATE CACHED TABLE PUBLIC."Persons_Data"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"FON" VARCHAR(25) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"MAIL" VARCHAR(100) NOT NULL,"BD" DATE NOT NULL,"WORK" VARCHAR(40) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"ADDR" VARCHAR(50) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"CITY" VARCHAR(40) COLLATE INFORMATION_SCHEMA."English UCC" NOT NULL,"USER" VARCHAR(5),"TIMESTAMP" TIMESTAMP(0));
CREATE CACHED TABLE PUBLIC."Logtable"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"FK_ID_PD" INTEGER,"USER" VARCHAR(10),"TIMESTAMP" TIMESTAMP,FOREIGN KEY("FK_ID_PD") REFERENCES PUBLIC."Persons_Data"("ID") ON DELETE CASCADE ON UPDATE CASCADE);
CHECKPOINT DEFRAG"

I did replace hsqldb.jar of 1.8 to 2.4 and try above command, I got "1: unexpected token: INFORMATION_SCHEMA"

I was wondering where I might be going wrong?

[chrisb]

@syp1977,
1) i downloaded & extracted your db
2) i loaded mydb_wizard.odb
3) i hit Tables icon
4) i saved db
5) i closed db & shut down libreoffice
6) i deleted hsqldb.jar
7) i pasted hsqldb-2.3.4.jar & renamed as hsqldb.jar
8) i repeated steps 2, 3, 5
9) i deleted hsqldb.jar
10) i pasted hsqldb-2.6.0.jar & renamed as hsqldb.jar
11) i repeated steps 2, 3
12) i hit menu:Tools>SQL
13) i pasted the code in 2 blocks following the instructions of F3K Total » Thu Dec 30, 2021 6:12 pm
14) i hit menu:View>Refresh Tables
15) i opened the table "Persons_Data" & input one row of data. triggers work fine, absolutely no issues & here it is:

[syp1977]

Hello,
What I did is below:
1) Copied mydb.backup, mydb.data, mydb.properties, mydb.script and mydb_wizard.odb in different folder
2) Opened mydb_wizard.odb
3) Click on Tables icon
4) Ctrl-S
5) Closed the office
6) Deleted hsqldb.jar
7) Pasted hsqldb.jar of 2.4.1
8) Repeated steps 2, 3, 5
9) Deleted hsqldb.jar
10) Pasted hsqldb-jdk8.jar of 2.6.1 & renamed as hsqldb.jar
11) Repeated steps 2, 3
12) Menu:Tools>SQL
13) Pasted the code in of F3K Total
14) Menu:View>Refresh Tables
15) Opened the table "Persons_Data" & input one row of data. It really worked...
Thanks a lot ... all of you.. Thanks for the patience...