SELECT "issue_cat"."issue", "issue_cat"."cat", "cat"."catdescr" FROM "issue_cat", "cat" WHERE "issue_cat"."cat" = "cat"."cat"
Other relational databases allow editing of query results and addition of records resulting from queries such as this.
In this case, issue_cat's composite PK exists within the query results and the only other field is dependent upon the foreign key cat. It seems there is therefore enough information within the results for updates/additions to be made.
The LibreOffice team doesn't seem to agree this is a bug, and I am unsure if the above not being editable/addable-to would be considered expected behaviour for HSQLDB.
I would be grateful for any advice.
Last edit: ge79so 2016-06-08
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Are you using a recent version of HSQLDB? If not, please try with the latest version and report.You need to use the Server mode or split database mode.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks but I am unable to change the version used by LO Base(which is the latest verion available from Ubuntu 14.04 repos) - I would just like confirmation whether this should be possible, and if so, perhaps whether it is an LO or HSQLDB problem.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
LibreOffice Base (Version: 5.1.3.2, Build ID: 1:5.1.3~rc2-0ubuntu1~trusty1) uses HSQLDB.
With the table structure:
issue --< issue_cat >-- catissue(issue integer PK autoincrement, issuedescr text) cat(cat integer PK autoincrement, catdescr text) issue_cat(issue PK integer, cat PK integer)The following query's results are not editable:
SELECT "issue_cat"."issue", "issue_cat"."cat", "cat"."catdescr" FROM "issue_cat", "cat" WHERE "issue_cat"."cat" = "cat"."cat"Other relational databases allow editing of query results and addition of records resulting from queries such as this.
In this case,
issue_cat's composite PK exists within the query results and the only other field is dependent upon the foreign keycat. It seems there is therefore enough information within the results for updates/additions to be made.The LibreOffice team doesn't seem to agree this is a bug, and I am unsure if the above not being editable/addable-to would be considered expected behaviour for HSQLDB.
I would be grateful for any advice.
Last edit: ge79so 2016-06-08
Are you using a recent version of HSQLDB? If not, please try with the latest version and report.You need to use the Server mode or split database mode.
Thanks but I am unable to change the version used by LO Base(which is the latest verion available from Ubuntu 14.04 repos) - I would just like confirmation whether this should be possible, and if so, perhaps whether it is an LO or HSQLDB problem.
Actually it is NOT possible in any version of HSQLDB to use a query with a join to update a table. For how to use the new version see this:
https://forum.openoffice.org/en/forum/viewtopic.php?f=83&t=17567
OK thanks. Would you consider adding that capability? I think it would be very helpful to db developers.