H2 can no longer be used with LibreOffice Base facilities

[morciej]

After upgrading H2 from Ver.1.4.200 to Ver.2.0.206, LibreOffice only displays INFORMATION_SCHEMA and pretends PUBLIC does not exist. I can use SELECT * FROM PUBLIC.PERSON either manually or in a query, but only via explicit SQL statements. PUBLIC tables do not appear anywhere in the GUI.

My system is Linux Mint 20.3 Una/Focal
I am using LibreOffice Version: 6.4.7.2 Build ID: 1:6.4.7-0ubuntu0.20.04.2

[katzyn]

Please, report it as issue with LibreOffice in its own bugtracker:
https://wiki.documentfoundation.org/QA/BugReport

H2 definitely reports them in both INFORMATION_SCHEMA.TABLES and JDBC metadata. It looks like LibreOffice for a some weird reason ignores tables of BASE TABLE type. In the SQL Standard there are various table types: BASE TABLE, VIEW, GLOBAL TEMPORARY, LOCAL TEMPORARY, SYSTEM VERSIONED and database systems can also report other types due to their own reasons. LibreOffice should accept them all.

[morciej]

Thanks for the info. I reported it to them today at https://bugs.documentfoundation.org/show_bug.cgi?id=146673 and let's see what happens.

[katzyn]

https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getTableTypes--

Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".

JDBC (unlike SQL/Schemata in the SQL Standard) doesn't specify the exact expected types, it only provides some possible examples and these examples aren't aligned with the Standard. It means nobody should rely on them, but if a VIEW is returned it is reasonable to display this table as a view.

[morciej]

@katzyn or anyone connected to H2 should perhaps take part in the discussion on Libreoffice Bugzilla, because otherwise they will likely make H2 change its connection parameters as a work-around. And everyone who tries to use H2 with Base will just walk away thinking "it doesn't work". I am a retired techie, not a programmer, so I am not in position to say anything to them. I just am not a fan of making things work by doing non-obvious workarounds, tying things with a shoestring etc. because the obvious method doesn't work due to a bug.

[katzyn]

https://bugs.documentfoundation.org/show_bug.cgi?id=146673#c7