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

`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. — , Sep 17 '13 at 06:13
I already mentioned this as a workaround. I'd rather not have do this throughout the various daos. Its to easy to miss a spot — Kyle, Sep 17 '13 at 06:15
I still don't understand why you want to do that in the first place. Comparing apples to oranges seems rather pointless. One possible workaround would be to cast the *column* to a character representation: `SELECT * FROM some_table WHERE cast(id as varchar(20)) = 'abc'`. But that would prevent the usage of an index on that column. — , Sep 17 '13 at 06:29

score 8 · Accepted Answer · edited Jun 25 '21 at 20:34

8

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.

edited Jun 25 '21 at 20:34

rogerdpack

62,887
36
269
388

answered Aug 16 '14 at 02:24

Kyle

1,019
1
10
19

Or change the column to a String :) – rogerdpack Jun 25 '21 at 20:38
You're right. I've never regretted making an ID colum a string – Kyle Jun 26 '21 at 02:12

score 0 · Answer 2 · answered Jul 01 '21 at 21:35

For me this

Caused by: java.sql.SQLDataException: data exception: invalid character value for cast in statement [select * from x where x.status = 'READY_FOR_WRITE']

meant I was using hibernate and by default it was creating status as an Integer instead of a string for an enum. Fix.

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

2 Answers2

Linked