hsqldb

Column called index issue when upgrading to hsqldb 2.7.4


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.


Solution

  • 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