CODEWITHSUNDEEP
javadb2hsqldbibatis
jimmy
jimmy

Reputation: 165

error using DATE function with HSQL

I have a project that uses SQL to access a DB2 database. I use the a HSQL database when running junit test cases on the code. The java project uses SQL via iBATIS.

I experience an error if HSQL encounters the DATE function, for example DATE(STATUS_CREATE_TS) any ideas on a solution?

--- Cause: java.sql.SQLException: user lacks privilege or object not found: DATE at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203) at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:268)

Thank you.

Upvotes: 2

Views: 1385

Answers (2)

Turophile
Turophile

Reputation: 3405

Have you tried the HSQL setting:

SET DATABASE SQL SYNTAX DB2 TRUE

As documented in http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html (Table 12.27).

Upvotes: 0

fredt
fredt

Reputation: 24352

This function is specific to DB2 and not supported directly by HSQLDB.

You can define the function for your tests as:

 create function date(ts timestamp) returns date return cast(ts as date);

Upvotes: 3

Related Questions