[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.

exceptions-notes
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 !!

Was this post helpful?

Join 7000+ Awesome Developers

Get the latest updates from industry, awesome resources, blog updates and much more.

* We do not spam !!

Leave a Comment

HowToDoInJava

A blog about Java and related technologies, the best practices, algorithms, and interview questions.