Org.hsqldb.HsqlException: data exception: invalid character value for translation

Question

I have a table in an HSQL database that has an identity (integer) column. I would like to support a query on a column using an arbitrary string (potentially non-numeric). However, the HSQL JDBC driver attempts to pass the query parameter to an integer and throws an exception. The Oracle driver seems to support this case.

Any ideas on changing this behavior in the hsql driver?

org.hsqldb: HSQLDB: 2.3.0

Table:

CREATE TABLE some_table(id IDENTITY NOT NULL); 

Request:

 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); 

An 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 

Answer 1

The problem is trying to compare a numeric column with a non-numeric string. The string is converted to a number before comparison and throws an exception.

Some work:

• Change the string to a long one. This is not really an option, since data sources other than hsql need to support arbitrary row identifiers.

• Tao can verify that the string is numeric, and only then make a request. This is acceptable, but I was hoping not to.