Hola,

I'm using Hibernate 2.3 w sqlserver 2000. I have created a named query which also has some converting, eg. select convert(varchar, entrytime, 120) from test. The query is quite large and I only use it for selection purposes.

It works fine against real database, but I get stuck using hsqldb (1.8) when unit testing. I guess the named query isn't converted to some dialect before it's executed, and hsqldb does not understand this convert.

Does anyone have experience w solving this?

Thanx
Lino

What is the hibernate exception you are getting?

This error is what I get on the fixture (the hibernate.config.hbm2ddl.auto is set to "create-drop"):
ERROR [main] util.JDBCExceptionReporter - Wrong data type: INSERTDATE in statement [SELECT (...)]
- the "INSERTDATE" is a datetime column that is converted in the query to varchar.
- I replaced the actual query with (...)

I could manually create the table, and drop it afterwards;
onSetUpBeforeTransaction() {
jdbcTemplate.execute("CREATE TABLE TEST (TESTID INT, SOMEDATE DATETIME)");
jdbcTemplate.execute("SELECT CONVERT(VARCHAR, SOMEDATE) FROM TEST");

org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SELECT CONVERT(VARCHAR, SOMEDATE) FROM TEST]; SQL state [37000]; error code [-16]; Wrong data type: SOMEDATE in statement [SELECT CONVERT(VARCHAR, SOMEDATE]; nested exception is java.sql.SQLException: Wrong data type: SOMEDATE in statement [SELECT CONVERT(VARCHAR, SOMEDATE]
java.sql.SQLException: Wrong data type: SOMEDATE in statement [SELECT CONVERT(VARCHAR, SOMEDATE]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

If I would use SELECT CONVERT(SOMEDATE, VARCHAR) FROM TEST instead the query would execute, but I cannot convert it into the format that I want.

I was thinking that the one solution would be to do the SELECT and do the conversions to the formats that I want later.