Junit HSQLDB - user lacks privilege or object not found - THIS_.oh-ordnbr

Question

I am getting an exception when my column name contains hyphen "-"

    Entity : this is the entity name.
    @Entity
    @Table(name = "RequestHeader")
    public class RequestHeader implements Serializable { 
    ....
    ....    
    @Column(name = "`oh-ordnbr`")
    private Integer ohOrdnbr;

Schema definition: This is the query for schema creation.

    CREATE MEMORY TABLE PUB.REQUESTHEADER(
           REQUESTID INTEGER,
           IMUSERID INTEGER,
           REQUESTDATE DATE,
           REQUESTTIME INTEGER,
           REQUESTSTATUS VARCHAR(19),
           REQUESTTYPE VARCHAR(22),
           HEADERINSTRUCTIONS VARCHAR(5150),
           DATEFORMAT VARCHAR(20),
           TIMEFORMAT VARCHAR(20),
           LANGUAGEID INTEGER,
           "OH-ORDNBR" INTEGER,
           "OH-TRCNSTAMP" INTEGER,
           ISPICKUPLIST BIT(1),
           CONSTRAINT "RQH-1" PRIMARY KEY(REQUESTID)
     );

The error is below:

Exception Stack: Error message which I have received by running the Junit.
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: THIS_.oh-ordnbr
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ExpressionColumn.checkColumnsResolved(Unknown Source)
at org.hsqldb.QueryExpression.resolve(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)

Could some one help me in fixing this?

Demi

Have you checked the documentation for HSQLDB? I think that column names in double quotes might be case sensitive. Do you really need the hyphens? I would change them to underscores, that is the SQL standard anyway... – Prate

@Prate Correct. Please post as an answer. Also THIS_ should not be used as table name. – Illation

Answer 1

The reason for the object not found error is the fact that the oh-ordnbr column is defined to be case sensitive (this is due to the double quotes you put around it).

You have two possible solutions:

1. Do not use dashes (hyphens) in your SQL. It is bad practice anyway, use underscores instead.

2. Update the JPA annotation as follows:

     @Column(name = "`OH-ORDNBR`")
     private Integer ohOrdnbr;
   

I strongly recommend using underscores instead of dashes, you never know what weirdness different JPA implementations might have when using the second solution.

Prate

Answer 2

Check your configuration file has correct provider, I fixed same issue by providing org.hibernate.ejb.HibernatePersistence provider.

Voroshilovsk

Answer 3

If HSQL Scripts are run via Java and if any new table query is followed by select/alter/update queries it always throws "user lacks privilege object". In reality the create queries after execution with Java Statements never gets commited into the DB and it is not persisted and we run our select/alter/update and it results in the exception. If we run the queries line by line and commit for each line, we could avoid this error.

Sprag