UNSIGNED and array problem in H2 query

34 views
Skip to first unread message

Ronit

unread,
Jul 18, 2022, 4:04:57 AMJul 18
to H2 Database
Hello 

I have these key words in the query 

Actual : CREATE TABLE STUDENT(S_PK varchar PRIMARY KEY, S_id BIGINT UNSIGNED NOT NULL UNIQUE)
But when exception is thrown it shows the 
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement

"CREATE TABLE STUDENT(S_PK varchar PRIMARY KEY, S_id BIGINT [*]UNSIGNED NOT NULL UNIQUE)"

It happens with USER and Array as well

For USER I have added "NON_KEYWORDS=USER" to the jdbc connection string and it solves the problem but what should I do for UNSIGNED and Array


Thanks

Evgenij Ryazanov

unread,
Jul 18, 2022, 4:36:55 AMJul 18
to H2 Database
Hello.

Where did you find that syntax? There are no unsigned numeric data types in the SQL Standard and in the most of database systems including the H2. This is a feature of MySQL. H2 silently accepts UNSIGNED only in MySQL and MariaDB compatibility modes, but this declaration is ignored by H2 anyway.
Also the real MySQL doesn't have the ARRAY data type, so if you use H2 as an replacement of MySQL for unit tests, you shouldn't try to use it.

If you use H2 as a primary database, you can use standard-compliant ARRAY data type safely:
You cannot declare your column as UNSIGNED, but you can add a check constraint to it if you wish.

Ronit

unread,
Jul 18, 2022, 4:55:50 AMJul 18
to H2 Database
Thanks for responding ,  I am migration my code from H2 1.3.176 to H2 2.1.214
These are the queries that are already being used in the original code . But now  as I have upgraded the version of H2 it started to throw this exception .

Thanks you.
Reply all
Reply to author
Forward
0 new messages