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

Question

I have a table in an HSQL database which has an identity(integer) column. I'd like to support querying against the column using an arbitrary string(potentially non-numeric). However, the HSQL JDBC driver tries to cast the query parameter to an integer and throws an exception. The Oracle driver seems to support this case fine.

Any ideas to alter this behavior in the hsql driver?

org.hsqldb:hsqldb:2.3.0

The table:

CREATE TABLE some_table(id IDENTITY NOT NULL);

The query:

final String query = "SELECT * FROM some_table WHERE id=?";

String id = "abc";
jdbcTemplate.query(query, new PreparedStatementSetter() {
    @Override
    public void setValues(PreparedStatement ps) throws SQLException {
        ps.setString(1, id);
    }
}, someMapper);

The exception:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT * FROM some_table WHERE id=?]; data exception: invalid character value for cast; nested exception is java.sql.SQLDataException: data exception: invalid character value for cast
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668)
    .
    .
    .
Caused by: java.sql.SQLDataException: data exception: invalid character value for cast
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.throwError(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.setString(Unknown Source)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
    at com.stackoverflow.SomeDao$2.setValues(SomeDao.java:39)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:644)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)
    ... 33 more
Caused by: org.hsqldb.HsqlException: data exception: invalid character value for cast
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.Scanner.convertToNumber(Unknown Source)
    at org.hsqldb.types.NumberType.convertToType(Unknown Source)
    ... 40 more

Solution

The problem comes from attempting to compare a numeric column to a non-numeric string. The string is attempt converted to a number prior to comparison and causes the cast exception to be thrown if it isn't a number as a string.

Some work arounds:

• Change the String to a long. This isn't really an option as non-hsql datasources need to support arbitrary string ids.

• The dao could check that the String is numeric and only then make the query. This is acceptable, but I was hoping not to have to do this.

Comments

[a_horse_with_no_name]

SELECT * FROM some_table WHERE id='abc' does not make any sense. Why would you want to search for characters in a column that is known to not contain any. If you can't convert the user input to a number, you can skip the whole database query and disply "Nothing found" to the user because you know there can't be any matches.

[rogerdpack]

Or change the column to a String :)

[Kyle]

You're right. I've never regretted making an ID colum a string