Column called index issue when upgrading to hsqldb 2.7.4 - Stack Overflow

I'm trying to upgrade from hsqldb 2.4.1 to 2.7.4. I have an alter statement of the form

ALTER TABLE abc ADD Index INT DEFAULT 0 NOT NULL

Executing this statement results in this exception

Caused by: org.hsqldb.HsqlException: unexpected token: Index
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
    at org.hsqldb.ParserBase.checkIsSimpleName(Unknown Source)
    at org.hsqldb.ParserDDLpileAlterTable(Unknown Source)
    at org.hsqldb.ParserDDLpileAlter(Unknown Source)
    at org.hsqldb.ParserCommandpilePart(Unknown Source)
    at org.hsqldb.ParserCommandpileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)

I tried setting the property sql.syntax_mys=true together with various changes to the alter statement but no luck. Any pointers please?

Also, are there any special steps to follow for upgrading from 2.4.1 to 2.7.4 apart from just swapping the jars and addressing each issue encountered?

Thanks.

I'm trying to upgrade from hsqldb 2.4.1 to 2.7.4. I have an alter statement of the form

ALTER TABLE abc ADD Index INT DEFAULT 0 NOT NULL

Executing this statement results in this exception

Caused by: org.hsqldb.HsqlException: unexpected token: Index
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
    at org.hsqldb.ParserBase.checkIsSimpleName(Unknown Source)
    at org.hsqldb.ParserDDL.compileAlterTable(Unknown Source)
    at org.hsqldb.ParserDDL.compileAlter(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)

I tried setting the property sql.syntax_mys=true together with various changes to the alter statement but no luck. Any pointers please?

Also, are there any special steps to follow for upgrading from 2.4.1 to 2.7.4 apart from just swapping the jars and addressing each issue encountered?

Thanks.

• hsqldb

Share Improve this question Follow asked Jan 2 at 21:27 QuasiGQuasiG 9388 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

The identifier Index is probably a reserved keyword in whatever underlying SQL flavor you are using. You could try to escape it using double quotes:

ALTER TABLE abc ADD "Index" INT DEFAULT 0 NOT NULL

But even if the above works, you might be forced to forever escape Index in quotes whenever you refer to it from your Hibernate code. A better approach would be to not name your columns using SQL keywords, e.g. instead use:

ALTER TABLE abc ADD IndexCol INT DEFAULT 0 NOT NULL

The word Index (or INDEX) can still be used as the name of the new column. It is only rejected in this particular form of ALTER TABLE.

Obviously you want to keep code changes to absolute minimum, without changing the queries that access the column. Use this:

ALTER TABLE abc ADD COLUMN Index INT DEFAULT 0 NOT NULL