No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[prrvchr]

Hi all,

It seems that the TABLE_TYPE returned by DatabaseMetaData.getTables() does not follow JDBC.

H2 only lists tables as BASE TABLE or VIEW, as a result, it is difficult or impossible to be able to differentiate the  SYSTEM TABLE from TABLE or VIEW.

Any idea to work around this problem?

Thanks.

[Evgenij Ryazanov]

Hello.

JDBC doesn't have any actual requirements for returned table types, the provided list is only an example.

H2 returns table types as required by the SQL Standard for INFORMATION_SCHEMA.TABLES.TABLE_TYPE column.

But each JDBC driver should return its own table types from DatabaseMetaData.getTableTypes() and H2 does it correctly.

I don't know what do you mean by system table and why you need to distinguish them from normal tables and views. The INFORMATION_SCHEMA and pg_catalog (it exists only when PostgreSQL compatibility mode was enabled) normally have only very special virtual tables, but they aren't expected to be found in other places.

You can use non-standard DB_OBJECT_SQL function, it definitely returns NULL for various special tables.

SELECT TABLE_SCHEMA, TABLE_NAME, DB_OBJECT_SQL('TABLE', TABLE_SCHEMA, TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES;

https://h2database.com/html/functions.html#db_object_sql

These is also non-standard INFORMATION_SCHEMA.TABLES.TABLE_CLASS column, but you should understand that names of some implementations may be changed at any moment, even it patch release, some implementations may appear only under certain conditions and it is possible to add an own implementation of a table with a custom table engine. 

[prrvchr]

Hi Evgenij,

I don't know what do you mean by system table and why you need to distinguish them from normal tables and views.

I don't know the JDBC specification, but  with all other driver: Hsqldb, Derby, smallSQL, all table and view in the INFORMATION_SCHEMA have SYSTEM TABLE as TABLE_TYPE.

I write a JDBC driver for LibreOffice/OpenOffice Base and Base use DataBaseMetaData.getTables() method to obtain the list of Table and View and uses the 4th argument to filter TABLE, VIEW and SYSTEM TABLE.

But since you are not using SYSTEM TABLE as the TABLE_TYPE for the table and view contained in INFORMATION_SCHEMA, any filtering is impossible.

[Noel Grandin]

On 2022/06/23 9:30 am, prrvchr wrote:
>
> /I don't know what do you mean by system table and why you need to distinguish them from normal tables and views./

Unfortunately, we cannot change this easily because of backwards compatibility.

What you can do, is to special case H2 in your JDBC driver.

All of the H2 system tables will have METADATA in the first column of the result set, and INFORMATION_SCHEMA in the
second column.

[prrvchr]

Hi Noel,

What you can do, is to special case H2 in your JDBC driver

I already have java services specific to each driver, but unfortunately for this problem, not being able to change how Base works, I can't do anything...

[prrvchr]

This is exactly what I wanted to avoid, but I can override the Databsae.getTables() method...

What would be the SQL query to obtain a resultset with SYSTEM TABLE as TABLE_TYPE for the table and view contained in INFORMATION_SCHEMA?

[Evgenij Ryazanov]

You can use something like that:

SELECT
    TABLE_CATALOG TABLE_CAT,
    TABLE_SCHEMA TABLE_SCHEM,
    TABLE_NAME,
    CASE WHEN TABLE_SCHEMA = 'INFORMATION_SCHEMA' THEN 'SYSTEM TABLE' WHEN TABLE_TYPE = 'BASE TABLE' THEN 'TABLE' ELSE TABLE_TYPE END,
    REMARKS,
    CAST(NULL AS VARCHAR) TYPE_CAT,
    CAST(NULL AS VARCHAR) TYPE_SCHEM,
    CAST(NULL AS VARCHAR) TYPE_NAME,
    CAST(NULL AS VARCHAR) SELF_REFERENCING_COL_NAME,
    CAST(NULL AS VARCHAR) REF_GENERATION
FROM INFORMATION_SCHEMA.TABLES;

You need to add WHERE clause when DatabaseMetaData.getTables() was called with filtration parameters.

[Evgenij Ryazanov]

I forgot to add TABLE_TYPE alias after CASE … END.

[prrvchr]

Thanks Evgenij, I will try to do something with this request...