Created attachment 177421 [details] UI showing only INFORMATION_SCHEMA tables but not others After upgrading H2 from Ver.1.4.200 to Ver.2.0.206, I found that when connecting to an H2 database, LibreOffice only displays INFORMATION_SCHEMA, and does not show tables in the PUBLIC schema at all. As a result, I can only use SELECT * FROM PUBLIC.PERSON either manually or in a query, but PUBLIC tables do not appear anywhere in the GUI. It turns out that all tables of the BASE TABLE type are being ignored by Libreoffice. I reported it at https://github.com/h2database/h2database/issues/3351, and heard back that it is a KNOWN ISSUE. @katzyn commented that "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." Best, M
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.