Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[prrvchr]

Thanks Evgenij, I will try to do something with this request...
Le jeudi 23 juin 2022 à 12:09:07 UTC+2, Evgenij Ryazanov a écrit :

> I forgot to add TABLE_TYPE alias after CASE … END.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/d5a9da94-ce16-486c-8cc9-1311dddf34e4n%40googlegroups.com.

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[Evgenij Ryazanov]

I forgot to add TABLE_TYPE alias after CASE … END.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/95761609-b5d4-4ab4-8ae7-5bd80e21c5b7n%40googlegroups.com.

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[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.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7addbc0a-3f13-498b-a3cb-a99c3195f8b0n%40googlegroups.com.

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[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?

Le jeudi 23 juin 2022 à 09:58:11 UTC+2, prrvchr a écrit :

> 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...
>
>
> Le jeudi 23 juin 2022 à 09:48:38 UTC+2, Noel Grandin a écrit :
>
>>
>>
>> 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. 
>>
>>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/07ab04f9-4a5b-4e5f-9f0a-caca2da83609n%40googlegroups.com.

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[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...


Le jeudi 23 juin 2022 à 09:48:38 UTC+2, Noel Grandin a écrit :

>
>
> 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.
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/336fd720-fbeb-43f3-8c9b-f3fb364dbe47n%40googlegroups.com.

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[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.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/6822bf4a-4571-4976-7100-a9b4ee434f8a%40gmail.com.

[h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[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.



Le jeudi 23 juin 2022 à 02:32:34 UTC+2, Evgenij Ryazanov a écrit :

> 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. 
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/54e487ef-504c-4d5f-89f4-bbcd38024f99n%40googlegroups.com.

[h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

[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. 

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/fa165739-0914-416f-8f17-de35cfd18039n%40googlegroups.com.