HsqlException: data exception

Question

I am using hsqldb version 2.2.5 in my application sometimes I am getting

org.hsqldb.HsqlException: data exception: string data, right truncation.

So I want to know what are the possible reasons for that. I am not inserting any data like longvarchar in a varchar column.

http://sourceforge.net/tracker/index.php?func=detail&aid=2993445&group_id=23316&atid=378131

I searched above link but could not get proper feedback.

Given below the exception stack This exception is not happening frequently.

So what could be the reason for that and how to set the data type length in script file to increase at run time ?

java.sql.SQLException: data exception: string data, right truncation
    at org.hsqldb.jdbc.Util.sqlException(Util.java:255)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(JDBCPreparedStatement.java:4659)
    at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(JDBCPreparedStatement.java:311)
    at com.dikshatech.agent.db.NodesRuntimeTable.persistData(NodesRuntimeTable.java:151)
    at com.dikshatech.agent.jobs.WorkFlowJob.execute(WorkFlowJob.java:108)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:216)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
Caused by: org.hsqldb.HsqlException: data exception: string data, right truncation
    at org.hsqldb.error.Error.error(Error.java:134)
    at org.hsqldb.error.Error.error(Error.java:104)
    at org.hsqldb.types.CharacterType.castOrConvertToType(CharacterType.java:523)
    at org.hsqldb.types.CharacterType.convertToType(CharacterType.java:638)
    at org.hsqldb.StatementDML.getInsertData(StatementDML.java:921)
    at org.hsqldb.StatementInsert.getResult(StatementInsert.java:124)
    at org.hsqldb.StatementDMQL.execute(StatementDMQL.java:190)
    at org.hsqldb.Session.executeCompiledStatement(Session.java:1344)
    at org.hsqldb.Session.execute(Session.java:997)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(JDBCPreparedStatement.java:4651)

I solved by changing varchar() to CHAR VARYING which is defult varchar(32768) it chages dynamically . — Anil, Oct 03 '11 at 09:31

score 21 · Answer 1 · answered Sep 27 '11 at 10:51

The maximum size of a VARCHAR column is user-defined. If the inserted data is larger than this, an exception is thrown. The example below defines a table with a VARCHAR(100) column, which limits the size to 100 characters.

CREATE TABLE T (ID INT, DATA VARCHAR(100))

You can use a database manager and execute the SCRIPT command to see all your table definitions and their column size. Alternatively, SELECT * FROM INFORMATION_SCHEMA.COLUMNS shows the characteristics of each column.

You can use the ALTER TABLE table_name ALTER COLUMN col_name SET DATA TYPE to increase the size of an existing column.

score 11 · Answer 2 · answered Dec 17 '16 at 16:49

For Hibernate/HSQLDB automatically generated schema via @Column annotation on @Entity field of type String you might need to provide length atrribute. Otherwise the length will default to 255 and long input will not fit:

@Lob
@Column(name="column_name", length = 1000)
private String description;

score 9 · Answer 3 · answered May 14 '14 at 14:38

Your field length is not large enough. I used the LONGVARCHAR data type to fix this error.

CREATE TABLE "DEMO_TABLE" ("ID" NUMBER(19,0), "MESSAGE" LONGVARCHAR);

WARNING: Rant follows...

Yep, the error message java.sql.SQLException: data exception: string data, right truncation... makes total sense only after you know what's wrong. Occasionally I find a clear, well-written error message, meant to inform users. The time it takes to write one will be returned 100 fold (or more depending on usage), but usually to others. Hence, there is too little incentive for most to spend the time. It can however come back to benefit the product, as with the Spring Framework which has generally superior error messages.

I'm sure stackoverflow.com does not mind. Poor error messages likely drive people here every minute of every day!

score 4 · Answer 4 · answered May 23 '13 at 11:20

I encountered this error while using Hibernate with HSQLDB. Instead of the usual String field, the offender was a serializable field.

Hibernate mapping file was

<hibernate-mapping package="in.fins.shared">
     <class name="Data">
            <id name="id" column="id">
                  <generator class="uuid" />
            </id>
            <property name="date" column="Date" />
            <property name="facts" column = "facts" type="serializable" />
      </class>
</hibernate-mapping>

For facts field, which is set to serializable, Hibernate creates a column of type VARBINARY with maximum length 255 in HSQLDB. As serialized object size was more than this size data exception: string data, right truncation was thrown by HSQLDB.

Changing the facts column to Blob with sql-type attribute resolves the problem.

   <property name="facts" type="serializable">
          <column name="facts" sql-type="blob" />
   </property>

Same problem here. If anyone is curious the equivalent annotation would be `@Column(columnDefinition = "blob")` — Stefan Haberl, Feb 28 '14 at 13:01

score 1 · Answer 5 · answered Oct 07 '12 at 12:44

I actually faced the same problem, and got fixed relatively quickly. In my case I've declared a DB table column column like this: description VARCHAR(50), but I was trying to insert a longer string/text in there, and that caused the exception.

Hope this will help you :)

score 1 · Answer 6 · answered Jun 01 '16 at 13:00

I had the same problem as you describe while testing with HSQLDB.

I'm using hibernate as JPA implementation and this is my mapping class:

@Column (name = "file")
private byte[] file;

In production I'm using PostgreSQL and the problem don't shown up, but with HSQL I had to add the @Type annotation in my mapping to solve that error:

@Column (name = "file")
@Type(type = "org.hibernate.type.MaterializedBlobType")
private byte[] file;

There are many implementations of types. You can take a look at hibernate-core jar, inside the package org.hibernate.type and pick some that matches your mappings.

score -1 · Answer 7 · answered Dec 13 '12 at 15:48

This error occurs in some scenario's but in the following scenario it is difficult to retrieve the cause, assume following scenario: Assume the following entity

@Entity
public class Car {
   private String name;
   @ManyToOne
   @JoinColumn(name = "ownerId")
   private Owner owner;
        ...

When the annotation '@ManyToOne' would be forgotten, but the annotation ' @JoinColumn(name = "ownerId")' would be present! This error would occur, which doesn't really indicate the real issue.

HsqlException: data exception

7 Answers7

Linked