2

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 and never set any access stuff.

PS

I have also updated Hibernate to 3.6.

mP.
  • 18,002
  • 10
  • 71
  • 105

3 Answers3

1

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!

Community
  • 1
  • 1
Barry Pitman
  • 3,087
  • 1
  • 24
  • 32
0

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.

fredt
  • 24,044
  • 3
  • 40
  • 61
  • Hi THanx for HSQL - Would it be possible to include the actual offending columns, the from and to type, and perhaps data in the exception message. – mP. Feb 03 '11 at 21:35
  • This my be improved in the future. But as you are using Hibernate, you can turn on verbose reporting of statement execution in Hibernate and locate the offending statements. – fredt Feb 03 '11 at 22:42
  • Another possibe cause is the existence of multiple HSQLDB jars, as the type convertion of SA to Long looks somehow impossible. – fredt Feb 03 '11 at 22:50
  • many times these exceptions happen during the startup phase of hibernate. I only have one hsqldb.jar on the cp. – mP. Feb 18 '11 at 13:06
  • You can always send the HSQLDB project a test case which includes some of your startup and shows the problem. – fredt Feb 18 '11 at 14:04
0

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());
Community
  • 1
  • 1
idle
  • 1,117
  • 9
  • 17