Search code examples
hibernateprepared-statementhsqldb

HSQLDB: user lacks privilege or object not found error

I'm looking at some old code and am running into a problem running one of the unit tests.

The code is:

String sql = "insert into customers" + 
            "(name, age, date, id) " +
            " values " + 
            " (?, ?, ?, CUSTOMERS_SEQ.NEXTVAL)";

        if (LOG.isDebugEnabled()) LOG.debug("doInsert: SQL query = " + sql);

        int count;
        PreparedStatement pStmt = null;
        try {
            // insert!
            pStmt = conn.prepareStatement(sql); // fails over here
            //... do other stuff
        }
        //...more things

But, when trying to prepare the statement it throws the error

user lacks privilege or object not found: CUSTOMERS_SEQ.NEXTVAL.

I was reading around about the error itself and couldn't seem to find anything. Weirdly, when I locally run the query Select CUSTOMERS_SEQ.NEXTVAL from CUSTOMERS it works okay.

Any and all help would be greatly appreciated.

Solution

  • The expression CUSTOMERS_SEQ.NEXTVAL is not a SQL Standard expression, but an Oracle syntax one. You need to enable Oracle syntax compatibility with the statement below:

    SET DATABASE SQL SYNTAX ORA TRUE

    Or include the sql.syntax_ora=true on the connection URL.

    When this compatibility mode is enabled, the INSERT statement should work (it does work with HSQLDB 2.4.0)

    The equivalent SQL Standard expression is :

    NEXT VALUE FOR CUSTOMERS_SEQ