HowToDoInJava

  • Python
  • Java
  • Spring Boot
  • Dark Mode
Home / Hibernate / HsqlException

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

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?

Let us know if you liked the post. That’s the only way we can improve.
TwitterFacebookLinkedInRedditPocket

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Comments are closed on this article!

Search Tutorials

Hibernate Tutorial

  • Hibernate – Introduction
  • Hibernate – Hello World
  • Hibernate – Get/Fetch
  • Hibernate – Persist
  • Hibernate – Merge & Refresh
  • Hibernate – Get Entity Reference
  • Hibernate – BLOB
  • Hibernate – Save Update
  • Hibernate – Persistence LifeCycle
  • Hibernate – SessionFactory
  • Hibernate – Entities Equality
  • Hibernate – Cascade Types
  • Hibernate – Lazy Loading
  • Hibernate – Criteria Queries
  • Hibernate – HQL
  • Hibernate – Named Query
  • Hibernate – Mappings
  • Hibernate – First Level Cache
  • Hibernate – Second Level Cache
  • Hibernate – EhCache Configuration
  • Hibernate – OSCache Configuration
  • Hibernate – C3P0 Connection Pool
  • Hibernate – In memory Database
  • Hibernate – Bean Validation
  • Hibernate – Validator CDI
  • UnexpectedTypeException

Hibernate Annotations

  • Hibernate – JPA 2 Annotations
  • Annotations Vs Mappings
  • Hibernate – @Immutable
  • Hibernate – @NaturalId
  • Hibernate – @OneToMany
  • Hibernate – @ManyToMany
  • Hibernate – @OneToOne

Meta Links

  • About Me
  • Contact Us
  • Privacy policy
  • Advertise
  • Guest and Sponsored Posts

Recommended Reading

  • 10 Life Lessons
  • Secure Hash Algorithms
  • How Web Servers work?
  • How Java I/O Works Internally?
  • Best Way to Learn Java
  • Java Best Practices Guide
  • Microservices Tutorial
  • REST API Tutorial
  • How to Start New Blog

Copyright © 2020 · HowToDoInjava.com · All Rights Reserved. | Sitemap

  • Sealed Classes and Interfaces