Confirming with

Version: 7.2.5.2 / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 8; OS: Mac OS X 12.1; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

I created a test DB prior to setting up the LO JDBC connection and added a few records to via the browser connection (as illustrated in the documentation provided with the driver JAR download package).

I then shutdown the H2 instance (otherwise you get an error when trying to set up the connection from within the ODB file).

Once I'd configured the ODB file, I could connect to the H2 instance, but it would only show the INFORMATION_SCHEMA in the UI, not any of the PUBLIC schema tables or information contained therein.

With the Query editor in SQL mode, it is possible to query all the results of the table by specifically naming the table in the query. Obviously, you can't use the UI to select a table for the query as none of the PUBLIC schema tables are displayed.

@Julien : thought you might be interested in this one - if not, please remove yourself.

I tried all of this with both a file mode ODB (single connection) and a server mode ODB (specifying a TCP connection string allowing multiple concurrent connections).

The results were identical, no PUBLIC schema tables displayed.

I don't know H2 database but since you use JDBC to connect, I search in this part on LO code.
I noticed 2 parameters (in connectivity/source/drivers/jdbc/JDriver.cxx):
193 "ImplicitCatalogRestriction"
194 "The catalog which should be used in getTables calls, when the caller passed NULL."
....
200 "ImplicitSchemaRestriction"
201 "The schema which should be used in getTables calls, when the caller passed NULL."
See https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/jdbc/JDriver.cxx?r=f790fb51#200

Also, reading http://www.h2database.com/html/features.html, perhaps it could be just a parameter to add in connection string?
eg, I see in this website:
jdbc:h2:<url>;<setting>=<value>[;<setting>=<value>...]
jdbc:h2:file:~/sample;TRACE_LEVEL_SYSTEM_OUT=3

So perhaps, using:
jdbc:h2:<url>;ImplicitSchemaRestriction=PUBLIC
may help?

How is this esoteric workaround communicated to novice-level users of Base like myself, without them having to google Libreoffice bugs specifically? I mean, I had no idea that "table types" are a thing, so how do I discover that they may not be allowed/be part of a regression/need an extra parameter added at H2 end etc.? I really would not bother to report this bug if it were just for my own benefit.

Hello!

This issue doesn't look like being related with schema name or something like it. Tables from INFORMATION_SCHEMA are also missing, as you can see on screenshot here:
https://github.com/h2database/h2database/issues/3351
LibreOffice can see only views, but this schema also contains various tables, the complete list of tables and views in this schema is here:
https://h2database.com/html/systemtables.html#information_schema_tables

DatabaseMetaData.getTables() in H2 returns the same type of tables as listed in INFORMATION_SCHEMA.TABLES.TABLE_TYPE.

H2 1.4 (incorrectly) returns TABLE, TABLE LINK, SYSTEM TABLE, or EXTERNAL for tables and VIEW for views in this table.

H2 2.0 by default returns BASE TABLE, GLOBAL TEMPORARY, or LOCAL TEMPORARY for tables and VIEW for views as required by SQL/Schemata part of the SQL Standard (Standard also has SYSTEM VERSIONED, but H2 2.0 doesn't support these tables).

DatabaseMetaData.getTableTypes() in both H2 1.4 and H2 2.0 correctly reports available table types.

JDBC (unlike the SQL Standard) doesn't require any specific table types, it only provides some possible examples and they look like being collected from few database systems with their vendor-specific extensions like SYNONYM.

I think LibreOffice should support tables of any types reported by getTableTypes(). Currently it somehow filters out tables with BASE TABLE type and possibly other valid types, but accepts TABLE and VIEW types. Such implementation doesn't look like being correct, because there are various possible compliant with JDBC types.

It's not a regression since it's the new H2 version which triggers a pb on LO. It's not a change in LO which triggered the pb for a same H2 version.
So I removed "regression" and "bibisectRequest"

(In reply to Maciej Morycinski from comment #6)
> How is this esoteric workaround communicated to novice-level users of Base
> like myself, without them having to google Libreoffice bugs specifically? I
> mean, I had no idea that "table types" are a thing, so how do I discover
> that they may not be allowed/be part of a regression/need an extra parameter
> added at H2 end etc.? I really would not bother to report this bug if it
> were just for my own benefit.

I don't know if it works or not, just suggest to give it a try. If it works these paramaters could be added in Base doc if they're not already indicated (I didn't check).

(In reply to Julien Nabet from comment #9)
> ...
> I don't know if it works or not, just suggest to give it a try. If it works
> these paramaters could be added in Base doc if they're not already indicated
> (I didn't check).

Forget it, I gave a try, it doesn't work.

can't help here=>uncc myself.

As do I.

Perhaps H2 and Libreoffice projects should stop advertising that these systems can work together, unless someone spends the time to find out exactly why, and publishes a workaround.

It is well known that H2 used to be a candidate for Base backend that has been rejected. Perhaps the resulting bad blood is still simmering between these two projects. Not really my problem. Since Base works with HSQLDB fine, I will just make a mental note "don't bother to use H2 with Base; mutual sabotage likely"

I am still looking for a database front end that would allow me to create a data entry form visually, but does not drag the whole machinery of Office with it.

Good luck!

(In reply to Maciej Morycinski from comment #12)

> It is well known that H2 used to be a candidate for Base backend that has
> been rejected. Perhaps the resulting bad blood is still simmering between
> these two projects. Not really my problem. Since Base works with HSQLDB
> fine, I will just make a mental note "don't bother to use H2 with Base;
> mutual sabotage likely"
> 

I don't believe that it is a question of mutual sabotage, nor bad blood, but rather a lack of Base development in general, and in particular anything Base-related that relies on Java.

The move to Firebird was motivated by the desire to lessen the impact of the dependency on Java in order to provide a functional embedded database solution (in addition to the server-based DB backends currently supported which may, or may not, rely on JDBC drivers, at least optionally).

Unfortunately, that move was neither completely thought out, nor implemented to equate to the old and now obsolete embedded HSQLDB 1.8.0, with the result that embedded Firebird still remains an "experimental" option, fraught with its own difficulties.

H2, being a Java DB, suffers from the same general fate as HSQLDB, or probably any other pure Java DB (Derby, etc).


With internal Java support in the LO code also being an unloved knave in the eyes of many developers, there isn't much interest among the code contributor community in developing, or even maintaining, the LO code base that relies on that Java functionality.

The fact that any of it (the Java stuff) still works is down to a very few developers.

I find this desperately dissatisfying too, but there is no changing the reality of the situation that unless someone comes along with deep pockets, coding experience in DB/Java environments, and/or a mind to make things better, then nothing will change much in the foreseeable future in this regard.

For those looking for a workaround, HsqlDB, H2 and Derby in a single extension: https://github.com/prrvchr/jdbcDriverOOo

Soon the high-level driver will allow the management of rights and users of the underlying database in Base.

Dear Maciej Morycinski,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug