[h2] Re: No AUTO_INCREMENT in DatabaseMetaData.getTypeInfo()

[prrvchr]

This problem has been solved and I would particularly like to thank @
*Katzyn* and @*Evgenij* for their help and their reactivity...
Le lundi 4 juillet 2022 à 12:20:17 UTC+2, prrvchr a écrit :

> 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.
>
> Le lundi 4 juillet 2022 à 04:06:33 UTC+2, Evgenij Ryazanov a écrit :
>
>> 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.
>>
>

-- 
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/01ef2f8f-44ff-4183-b8e6-1f2656cb31f7n%40googlegroups.com.

[h2] Re: No AUTO_INCREMENT in DatabaseMetaData.getTypeInfo()

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

Le lundi 4 juillet 2022 à 04:06:33 UTC+2, Evgenij Ryazanov a écrit :

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

-- 
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/6373be3a-7a47-4462-9de2-ec05c3c80003n%40googlegroups.com.

[h2] Re: No AUTO_INCREMENT in DatabaseMetaData.getTypeInfo()

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

-- 
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/e009e47a-381a-4c49-b909-dc039a7c34a9n%40googlegroups.com.