A confusing point about INFORMATION_SCHEMA of H2Database 2.x

38 views
Skip to first unread message

泠恒谦

unread,
Aug 4, 2022, 3:40:46 AMAug 4
to H2 Database
- 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

unread,
Aug 4, 2022, 3:48:04 AMAug 4
to H2 Database
Hi!

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

Evgenij Ryazanov

unread,
Aug 4, 2022, 4:05:42 AMAug 4
to H2 Database
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:

For row value data types you need another additional table with definitions of 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:

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.

泠恒谦

unread,
Aug 4, 2022, 5:30:31 AMAug 4
to H2 Database
- 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

unread,
Aug 4, 2022, 5:50:31 AMAug 4
to H2 Database
You need to download the complete distribution of the version you need in a ZIP archive:
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:

Legacy implementation is provided by H2 Server to client processes with outdated drivers.
Reply all
Reply to author
Forward
0 new messages