After upgrade to HSQL 2.0.1.rc3 - Lots of org.hsqldb.HsqlException: data exception: ... cast complaints

I recently upgraded HSQL from 1.8.0.10 to 2.0 and then 2.0.1.rc3 without any changes in my code or test data. I have lots of tests which previously worked and now i am getting lots of exceptions involving "casting"...

Caused by: org.hsqldb.HsqlException: data exception: invalid character value for cast

Unfortunately the exception messages are poor and give me next to no clue which column etc is bad.

Other exceptions show that it is attempting to complain the sysadmin username to a long ??

Caused by: org.hsqldb.HsqlException: incompatible data type in conversion: from SQL type VARCHAR to java.lang.Long, value: SA
    at org.hsqldb.error.Error.error(Error.java:77)
    ... 54 more

The really strange this is i am using HSQL in memory mode and start it up empty开发者_Python百科 and never set any access stuff.

PS

I have also updated Hibernate to 3.6.

---

I just ran into a similar issue, after upgrading to HSQLDB 2.2.9, some unit tests started failing with this error message:

java.sql.SQLSyntaxErrorException: incompatible data type in conversion: from SQL type VARCHAR to java.math.BigDecimal, value: SA

The issue was only present when using DBUnit to manipulate datasets, there was no problem when using Hibernate. After turning up the JDBC logging, I found that DBUnit was failing after a select like this:

select id, user from MYTABLE order by id

// then, SQLSyntaxErrorException when DBUnit tried to retrieve the 'user' column:
BigDecimal userId = resultSet.getBigDecimal(2);

This answer led me to the cause: DBUnit does not escape the 'user' keyword by default, which explains why the error message has the value "SA", which is the default HSQLDB system user. However, DBUnit does let you configure escaping, which fixed the problem.

Finally the reason that Hibernate wasn't having problems was that its generated SQL is more explicit, e.g.

select mytable0.id, mytable0.user from MYTABLE mytable0 order by mytable0.id

---

Summary:

Find out which library isn't escaping keywords in SQL statements!

---

Auto casting behaviour has become more strict in version 2.x. This may account for the problems, but it is impossible to say where without seeing the actual table definitions and the statement executed by Hibernate. The extensive Hibernate test suite runs with very few (and unrelated) errors with the latest HSQLDB RC.

---

We ran into this same problem when packaging a custom version of hsqldb (required to fix this issue Hibernate/hsqldb 2 Cannot Hydrate Blob Column)

The current hsqldb source jars include an old version of org.hibernate which does not correctly identify the hsqldb version (ironic). If this source is included in your build then it may override the actual hibernate 3.6 version of the class.

Check inside of your hsqldb jar dependency and see if it includes any org.hibernate classes

Or try putting the following in your code to see where the dialect being used is coming from:

System.out.println(
    new HSQLDialect().getClass().getProtectionDomain().getCodeSource());