A confusing point about INFORMATION_SCHEMA of H2Database 2.x

[泠恒谦]

- Hi everyone, I'm migrating H2Database 1.x to 2.x. But I found a strange problem, and I can't find relevant information on the mailing list.

- On H2Database 1.x, I use `SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=?` to check the metadata of the table. But on H2Database 2.x, `TYPE_NAME` has been removed. I'm not sure how I should change this SQL, the migration guide in the documentation doesn't provide much information.

- But I checked https://github.com/h2database/h2database/blob/version-2.1.214/h2/src/main/org/h2/jdbc/meta/DatabaseMetaLocal.java#L299 , `TYPE_NAME` still seems to be Exists. But the documentation does remove information about this column.

- I would be grateful for any possible reply.

[Evgenij Ryazanov]

Hi!

DatabaseMetaLocal is not related to INFORMATION_SCHEMA. It is a data source for implementation of java.sql.DatabaseMetaData.

[Evgenij Ryazanov]

You can read data type name and other parameters from the COLUMNS table:

https://h2database.com/html/systemtables.html#information_schema_columns

DATA_TYPE column now contains the name as required by the SQL Standard.

For ARRAY data types there is an additional table with definitions of elements:

https://h2database.com/html/systemtables.html#information_schema_element_types

For row value data types you need another additional table with definitions of fields:

https://h2database.com/html/systemtables.html#information_schema_fields

All these tables in H2 2.*.* are compliant with the SQL Standard.

Non-standard ENUM data types have an own non-standard table with possible values:

https://h2database.com/html/systemtables.html#information_schema_enum_values

In old versions of H2 INFORMATION_SCHEMA.COLUMNS.DATA_TYPE was incorrectly used for JDBC type code. H2 2.*.* doesn't report JDBC-specific information in the INFORMATION_SCHEMA, but you can read it from JDBC database metadata, if you wish.

[泠恒谦]

- Thank you very much for your reply! 

- With such a change, I wonder if it is possible for me to continue to view the documentation for H2Database 1.x at a documentation address somewhere? I kind of want to compare the two different versions of the documentation, but I pay attention Go to https://github.com/h2database/h2database.github.io and there is no Git Tag for H2Database 1.x.

[Evgenij Ryazanov]

You need to download the complete distribution of the version you need in a ZIP archive:

https://h2database.com/html/download-archive.html

They contain documentation in PDF and HTML format.

But INFORMATION_SCHEMA wasn't documented properly in old versions, so you cannot compare old and new implementation in that way.

You can try to compare new and old implementations in the sources:

https://github.com/h2database/h2database/blob/version-2.1.214/h2/src/main/org/h2/table/InformationSchemaTable.java

https://github.com/h2database/h2database/blob/version-2.1.214/h2/src/main/org/h2/table/InformationSchemaTableLegacy.java

Legacy implementation is provided by H2 Server to client processes with outdated drivers.