Good night to all. I’m very new in DB creation (studied some things but 30 years ago) and this is my first attempt to create a functional Database with a purpose.
I’m using HSQLDB 2.7.4 connected via JDBC. Windows 11, LO 25.2.5.2.
The problem is: I was using a Form to put some data in the DESENHO table. Got an error, go to crash report and reopen the DB. Table DESENHO got corrupted since then (there’s no way to enter any record).
I’ve deleted the Table, recreated with all the relations, but it gives the same error and corrupts the Table.
Can you give me any advice, please?
Thank you!
PDM274.zip (75.9 KB)
Could you tell the error message?
It simply goes down after I try to fill the first record. I write some stuff but as soon as it goes to the 2nd record it crashes.
After recover, DESENHO table no longer allows to insert records, shows no lines at all.
[EDIT]
The .log:
/*C2*/SET SCHEMA PUBLIC
INSERT INTO DESENHO VALUES(0,'9114','Senfim',NULL,NULL,0,1,FALSE,NULL,NULL)
COMMIT
Recreate all the DB from scratch from DUMP.sql
Exactly the same error. Libreoffice goes down with Document recovery window. After that Table DESENHO get unusable.
Might be this one: 146568 – LO fails to apply HSQL 2.6 privileges
Couldn’t test it, because I don’t use external HSQLDB.
I’ll ask for other advise.
I’m Using Libreoffice Base as a frontend (as you know better than me) to this DB plan, a simple PDM (Product Data Management) for engineering files. Using Libreoffice with the amazing Forms and Reports that it can generate.
Can you advise me of other external DB type, more stable with Libreoffice?
I’ve installed jdbcDriverOOo an can connect to a lot of DB types.
I read lots of information but could not decide, there’s a ton of DB types.
Thank you!
According to bug-report and the discussion at OpenOffice.org you can use the Version 2.5.x of HSQLDB without problems.
Apache OpenOffice Community Forum - HSQLDB MULTIUSER, GRANTING not working in HSQLDB 2.6.1 - (View topic)
Other options would be Firebird as it is also directly brought with LibreOffice (option for embedded version). A good database is also MariaDB, as it replaced MySQL in most Linux-distributions and it is quite common on Web-Servers or NAS.
.
PostgresSQL or Sqlite I’d use, if there are other reasons to chose them.
I prefer Firebird as an external file database for databases, which only need one user. It could be created directly in LibreOffice (Connection to external database → create …).
If I need a database for more than one user at the same time I use server database: MariaDB or PostgreSQL would both be connected to LibreOffice by direct connections provided by LibreOffice. So you won’t need JDBC or ODBC.
Made a test, reconfiguring all the DB for SQLite and, for my surprise, has much fewer data types than HSQLDB. Also, Libreoffice cannot handle Table relations.
Will I find this kind of situations with Firebird, or MariaDB?
Thank you!
There are internal drivers for MariaDB and Firebird. This drivers support to handle table relations in Tools → Relationships.
There is no special driver for SQLite. So you have to use ODBC or JDBC instead.
I downloaded PDM274.zip from the initial post; hsqldb-2.7.4.zip from HSQLDB (HyperSQL Database Engine (HSQLDB) - Browse Files at SourceForge.net). Unpacked both to separate directories. Added C:\Path\To\hsqldb-2.7.4\hsqldb\lib\hsqldb.jar to LibreOffice’s Java Class Paths. Opened the ODB, and Edit->Database->Connection Type. There I choose JDBC, enter hsqldb:file:///C:/path/to/PDM274/PDM274;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false as connection string (without jdbc:, which is already shown there in the dialog), org.hsqldb.jdbcDriver as JDBC driver class, test class (OK), test connection (OK), save. Open the DESENHO table (empty, doesn’t allow adding records). Execute SQL from “The .log” - it adds the record OK, but the table is still not editable from the UI.
I never see a crash. I use Version: 25.8.0.2 (X86_64)
Build ID: 80a8bc2ef75d415a197e282da0ebf917315d5e24
CPU threads: 24; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Vulkan; VCL: win
Locale: de-DE (en_US); UI: en-US
Calc: CL threaded
What I noticed, is that in the original ODB, the connection string started with sdbc:.
Thank you all!
Will make more experiences.
https://www.mediafire.com/file/8cfm7n7fw1wdk8g/PDM.zip/file
I was able to copy the database tables and forms into an embedded hsqldb, then converted to 2.4.1 and 2.7.4.
The new document contains a macro which connects to the database in folder “database” using hsql-2.7.4.jar in folder “driver”.
Thank you so much Villeroy!
Lack of knowledge from my part as this things.
After knowing that 2.7.2 version is not working well with Libreoffice, go to configure a 2.5.1 DB. Will see what headaches the future reserve. 
Thank you again!