No AUTO_INCREMENT in DatabaseMetaData.getTypeInfo()

[prrvchr]

Hi all,

It seems that no DataType returned by DatabaseMetaData.getTypeInfo() has the AUTO_INCREMENT column set to true.

Normally it seems that DataType: INTEGER, BIGINT, TINYINT, SMALLINT, NUMERIC, DECIMAL should be autoincrement and have AUTO_INCREMENT column set to true.

This prevents me from being able to handle auto-increments in creating tables in LibreOffice Base... any workaround?

[Evgenij Ryazanov]

Hello.

Identity columns don't have any dedicated data types in the SQL Standard and it the most of database systems. When you declare an identity column you must specify both data type (BIGINT or INTEGER, for example) and identity clause (GENERATED BY DEFAULT AS IDENTITY etc.)

Few database systems have special data types with implicit identity characteristics due to historic reasons. PostgreSQL has serial data types (smallserial = serial2, serial = serial4, bigserial = serial8). Normally identity columns should be used in modern versions of PostgreSQL instead of these legacy types, but at least JDBC drivers have something to report here. PgJDBC reports only smallserial, serial, and bigserial data types in getTypeInfo() with TRUE in AUTO_INCREMENT column.

H2 doesn't have special types (compatibility modes have some) and returns FALSE for all data types.

HSQLDB doesn't have special types, but it returns TRUE for TYNYINT, SMALLINT, INTGER, BIGINT, DECIMAL and NUMERIC. These data types may be used for identity columns, but an additional clause must be specified in their definitions.

Derby doesn't have special types, it returns TRUE for SMALLINT, INTEGER, and BIGINT, so situation is the same. It also returns NULL for LOB data types, it looks like a bug.

It means some drivers (PgJDBC, H2) assume that TRUE in this column indicates a special data type with implicit identity characteristics, some other drivers (HSQLDB, Derby) assume that TRUE indicates a data type that may be used for an identity column with additional identity generation clauses.

Why this deviation exists? In the SQL Standard, INFORMATION_SCHEMA is described in a separate book with more than 300 pages and we usually can say where it was implemented correctly and where it isn't correct. But JDBC metadata is only described in its own Javadoc and these descriptions are too brief and obscure. In many cases it isn't possible to determine what they exactly mean, so developers of JDBC drivers need to guess or they can try to copy behavior of some other driver.

I think we can change our implementation to return TRUE for TINYINT, SMALLINT, INTEGER, BIGINT, and NUMERIC data types.

[prrvchr]

Hi Evgenij,

I just saw that Katzyn has already committed the correction.

Maybe you know where I can find the corrected H2 jar archive?

And maybe you can help me to finish the integration of auto increment in Base?
Apparently H2 does not support a command like:

CREATE TABLE "PUBLIC"."PUBLIC"."Table1" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, "Name" VARCHAR(100), PRIMARY KEY ("ID"))

If so, I can create the table then the index in two separate commands:
CREATE TABLE "PUBLIC"."PUBLIC"."Table1" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, "Name" VARCHAR(100))

ALTER TABLE "PUBLIC"."PUBLIC"."Table1" ADD INDEX .....

I don't know the second command...
Thanks.

[prrvchr]

This problem has been solved and I would particularly like to thank @Katzyn and @Evgenij for their help and their reactivity...