Search code examples
oracle-databasehibernatehsqldb

HSQLDB - Oracle to_char(integer) throws " Unexpected Token : )"

I'm doing integration testing in a Hsqldb. My production database is an Oracle Database.

Versions

  • Hibernate : 4.1.3.final.

  • Hsqldb : 2.3.3. ( I can't use the 2.3.4 because it can't run all my junit tests in one click.)

My problem

To create my test database, I followed this tutorial with minor modifications. Everything works fine except for the methods that use the TO_CHAR(integer) function from Oracle. For those methods, I got an unexpected token: )

This is the code that causes the exception

select ="select p.name, to_char(p.id) "
        + " from t_player p " +
        "inner join t_job job on j.id=p.id_job ";

This is a part of my unit test

public void testFindPlayer() throws ClassNotFoundException, SQLException {

    Class.forName("org.hsqldb.jdbcDriver");
    Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:DB", "sa", "");
    String syntax_ora = "SET DATABASE SQL SYNTAX ORA TRUE";
    PreparedStatement ps_ora = connection.prepareStatement(syntax_ora);
    ps_ora.execute();
    ps_ora.close();
    connection.close();

    List<String[]> actual_player = Player.findPlayer("Name");
    List<String[]> expected_player =  new ArrayList<String[]>(); 
    //etc..

This is the exception

Caused by: org.hsqldb.HsqlException: unexpected token: )
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
    at org.hsqldb.ParserDQL.readExpression(Unknown Source)
    at org.hsqldb.ParserDQL.readSQLFunction(Unknown Source)
    at org.hsqldb.ParserDQL.readColumnOrFunctionExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadValueExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpression(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)
    ... 41 more

How I tried to fix it

1) I try another function from Oracle that is to_char(number,'format'). Basically, I change the select part with the following:

select ="select p.name, to_char(p.id,'999999') "
    + " from t_player p " +
    "inner join t_job job on j.id=p.id_job ";

But then, I got this exception : incompatible data type in operation

2) I put the SET DATABASE SQL OSYNTAX part into comments

The same exceptions are raised.

Do you have an idea on how to fix this issue please?

Thank you for your reply.

Solution

  • HSQLDB's built-in TO_CHAR function supports only date and timestamp arguments.

    You can create a user-defined TO_CHAR function for numeric values. For example:

    CREATE FUNCTION TO_CHAR(param DECIMAL) RETURNS VARCHAR(20) 
    RETURN CAST(param AS VARCHAR(20))

CREATE FUNCTION TO_CHAR(param DECIMAL, format VARCHAR(20)) RETURNS VARCHAR(20) 
    RETURN CAST(param AS VARCHAR(20))

    Now TO_CHAR(p.id) will return the id as a string and the two-arge version also returns the same.