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

You might face this exception if you are trying execute any query OR stored procedure, and parameter are not set in correct order. It may happen that accidentally you are setting a String type column to a Integer type column.

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

Solution

To fix this error, make sure you are mapping values to correct columns in query/ stored procedure. Best way is to define column name and values in 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 !!