Re: [h2] Statement.execute throws Syntax error (206/210)

[Helmut Leininger]

Thanks for your explanation. Obviously, syntax check has been more lenient 
in previous releases.

Evgenij Ryazanov schrieb am Donnerstag, 3. Februar 2022 um 01:34:40 UTC+1:

> Hello!
>
> INT GENERATED BY DEFAULT AS IDENTITY should be used for identity column of 
> INT (INTEGER) data type. Additional NOT NULL clause isn't needed, but may 
> be specified. H2 intentionally supports this standard-compliant syntax in 
> all compatibility modes to have possibility to use the same syntax in all 
> of them.
>
> INT IDENTITY is a syntax from SQL Server, H2 supports it only in 
> MSSQLSERVER and LEGACY compatibility modes. You can't use in in any other 
> mode.
> https://h2database.com/html/features.html#compatibility
>
> LEGAGY mode is based on REGULAR modes, but it is less restrictive and 
> provides better compatibility with H2 1.4.200. You can use it if you can't 
> change definition of your table.
>

-- 
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/6ffc9208-d6b6-4953-978e-94afcc387324n%40googlegroups.com.

Re: [h2] Statement.execute throws Syntax error (206/210)

[Evgenij Ryazanov]

Hello!

INT GENERATED BY DEFAULT AS IDENTITY should be used for identity column of 
INT (INTEGER) data type. Additional NOT NULL clause isn't needed, but may 
be specified. H2 intentionally supports this standard-compliant syntax in 
all compatibility modes to have possibility to use the same syntax in all 
of them.

INT IDENTITY is a syntax from SQL Server, H2 supports it only in 
MSSQLSERVER and LEGACY compatibility modes. You can't use in in any other 
mode.
https://h2database.com/html/features.html#compatibility

LEGAGY mode is based on REGULAR modes, but it is less restrictive and 
provides better compatibility with H2 1.4.200. You can use it if you can't 
change definition of your table.

-- 
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/852e6d79-dae1-4009-945a-4c583e964044n%40googlegroups.com.

Re: [h2] Statement.execute throws Syntax error (206/210)

[Noel Grandin]

You can specify a column as either INT or IDENTITY but not both.

On Wed, 2 Feb 2022 at 20:38, Helmut Leininger  wrote:

> This was not the case in 200
>
> Syntax error in SQL statement "create table persons (mstb_per_no int
> [*]identity not null, mstb_father int, mstb_mother int, mstb_last_edit
> date, mstb_dsid int, mstb_ref_id int, mstb_reference varchar(12),
> mstb_spoulast int, mstb_scbuff varchar(10), mstb_pbirth varchar(30),
> mstb_pdeath varchar(30), mstb_relate int, mstb_relatefo int, mstb_tt
> varchar(1), mstb_sex varchar(1), mstb_living varchar(1), mstb_birthorder
> varchar(2), mstb_multibirth varchar(1), mstb_adopted varchar(1),
> mstb_ance_int varchar(1), mstb_desc_int varchar(1))"; expected "ARRAY,
> INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL,
> AUTO_INCREMENT, DEFAULT, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT,
> CONSTRAINT, COMMENT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES,
> AUTO_INCREMENT, ,, )"; SQL statement:
>
> create table persons (mstb_per_no int identity not null, mstb_father int,
> mstb_mother int, mstb_last_edit date, mstb_dsid int, mstb_ref_id int,
> mstb_reference varchar(12), mstb_spoulast int, mstb_scbuff varchar(10),
> mstb_pbirth varchar(30), mstb_pdeath varchar(30), mstb_relate int,
> mstb_relatefo int, mstb_tt varchar(1), mstb_sex varchar(1), mstb_living
> varchar(1), mstb_birthorder varchar(2), mstb_multibirth varchar(1),
> mstb_adopted varchar(1), mstb_ance_int varchar(1), mstb_desc_int
> varchar(1)) [42001-210]
>
> Sorry for possible double  posting (this is my first try in this group)
>
> --
> 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/f64c2b8d-9e77-4d40-acc5-649a5ebdf311n%40googlegroups.com
> 
> .
>

-- 
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/CAFYHVnUyL7FpEEWDD%3Dn0E0xC1V34ebtXgLn%2BL6R71whw%3DZPsvg%40mail.gmail.com.

[h2] Statement.execute throws Syntax error (206/210)

[Helmut Leininger]

This was not the case in 200

Syntax error in SQL statement "create table persons (mstb_per_no int 
[*]identity not null, mstb_father int, mstb_mother int, mstb_last_edit 
date, mstb_dsid int, mstb_ref_id int, mstb_reference varchar(12), 
mstb_spoulast int, mstb_scbuff varchar(10), mstb_pbirth varchar(30), 
mstb_pdeath varchar(30), mstb_relate int, mstb_relatefo int, mstb_tt 
varchar(1), mstb_sex varchar(1), mstb_living varchar(1), mstb_birthorder 
varchar(2), mstb_multibirth varchar(1), mstb_adopted varchar(1), 
mstb_ance_int varchar(1), mstb_desc_int varchar(1))"; expected "ARRAY, 
INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, 
AUTO_INCREMENT, DEFAULT, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, 
CONSTRAINT, COMMENT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, 
AUTO_INCREMENT, ,, )"; SQL statement:

create table persons (mstb_per_no int identity not null, mstb_father int, 
mstb_mother int, mstb_last_edit date, mstb_dsid int, mstb_ref_id int, 
mstb_reference varchar(12), mstb_spoulast int, mstb_scbuff varchar(10), 
mstb_pbirth varchar(30), mstb_pdeath varchar(30), mstb_relate int, 
mstb_relatefo int, mstb_tt varchar(1), mstb_sex varchar(1), mstb_living 
varchar(1), mstb_birthorder varchar(2), mstb_multibirth varchar(1), 
mstb_adopted varchar(1), mstb_ance_int varchar(1), mstb_desc_int 
varchar(1)) [42001-210]

Sorry for possible double  posting (this is my first try in this group)

-- 
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/f64c2b8d-9e77-4d40-acc5-649a5ebdf311n%40googlegroups.com.