[Solved] HsqlException: data exception: invalid character value for cast

1. Reason

We might face this exception if we are trying to execute any query OR stored procedure, and parameters are not set in the correct datatype. It may happen that accidentally we are setting a String type column to an Integer type column.

The exception will look like this:

Caused by: org.hibernate.exception.DataException: Error calling CallableStatement.getMoreResults
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:69)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
	at org.hibernate.result.internal.OutputsImpl.convert(OutputsImpl.java:96)
	at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:73)
	at org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(ProcedureOutputsImpl.java:49)
	at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:426)
	at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:378)
	at org.hibernate.jpa.internal.StoredProcedureQueryImpl.outputs(StoredProcedureQueryImpl.java:251)
	at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:234)
	... 44 more
Caused by: java.sql.SQLDataException: data exception: invalid character value for cast
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
	at org.hsqldb.jdbc.JDBCCallableStatement.fetchResult(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
	at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:69)
	... 49 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)
	at org.hsqldb.StatementDML.getInsertData(Unknown Source)
	at org.hsqldb.StatementInsert.getResult(Unknown Source)
	at org.hsqldb.StatementDMQL.execute(Unknown Source)
	at org.hsqldb.StatementCompound.executeBlock(Unknown Source)
	at org.hsqldb.StatementCompound.execute(Unknown Source)
	at org.hsqldb.StatementProcedure.executePSMProcedure(Unknown Source)
	at org.hsqldb.StatementProcedure.getProcedureResult(Unknown Source)
	at org.hsqldb.StatementProcedure.getResult(Unknown Source)
	at org.hsqldb.StatementDMQL.execute(Unknown Source)
	at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
	at org.hsqldb.Session.execute(Unknown Source)
	... 55 more

2. Solution

To fix this error, make sure we are mapping values to correct columns in the query or stored procedure and using the correct data type. The best way is to define column names and values in the query as well.

CREATE PROCEDURE ADD_EMPLOYEE_PROCEDURE (IN firstName VARCHAR(50), IN lastName VARCHAR(50), IN email VARCHAR(100), IN departmentId INTEGER)
     MODIFIES SQL DATA
BEGIN ATOMIC
     INSERT INTO employee (id, email, firstName, lastName, department_id ) VALUES (DEFAULT,  email, firstName, lastName, departmentId);
END$

Fixing the order will fix this issue.

Happy Learning !!