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 !!
Comments