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 !!
Was this post helpful?
Let us know if you liked the post. That’s the only way we can improve.