HsqlException: Invalid Character Value for Cast [Solved]

HSQLDB throws an HsqlException with the data exception “invalid character value for cast” when it has to convert a text value into a number column, and the text is not a number. A cast is the conversion of a value from one SQL type to another, for example from VARCHAR “2” to INTEGER 2. The error has the standard SQLState 22018, and Hibernate wraps it in a DataException.

We meet the error when a value that arrives as text, such as a search term or a form field, goes to a number column.

The following example binds the pass number “A12” to the numeric zone column of a bus pass table in a native SQL query.

[main] WARN org.hibernate.orm.jdbc.error - HHH000247: ErrorCode: -3438, SQLState: 22018
[main] WARN org.hibernate.orm.jdbc.error - data exception: invalid character value for cast
org.hibernate.exception.DataException: JDBC exception executing SQL [data exception: invalid character value for cast] [select * from bus_pass where zone = ?]
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:53)
	...
	at org.hibernate.query.Query.getResultList(Query.java:121)
Caused by: java.sql.SQLDataException: data exception: invalid character value for cast
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.setString(Unknown Source)
	at org.hibernate.type.descriptor.jdbc.VarcharJdbcType$1.doBind(VarcharJdbcType.java:101)
	...
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)

Notice that HsqlException is the last cause in the chain, and the DataException on top shows the SQL that failed. Binding an int instead of the text fixes the query.

query.setParameter(1, "A12");   // zone is INTEGER: invalid character value for cast
query.setParameter(1, 2);       // [A12 (Lokesh, zone 2, MONTHLY, 2026-12-31)]

Next, we look at the four causes of the error and fix each of them.

1. Why HSQLDB Throws “Invalid Character Value for Cast”

Every column has a type, and every value we send must match it. When we send text to a non-text column, HSQLDB tries to convert it. The conversion works for “2”, but “A12” is not a number, so it fails. For a query parameter, HSQLDB converts the value inside setString(), so the error appears while Hibernate binds the parameters, before the SQL runs.

Our example is a city bus pass with Hibernate 7.4, Java 25 and HSQLDB 2.7.4, an in-memory database that many projects use in tests.

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private String passNumber;      // varchar, "A12"
private String holderName;      // varchar, "Lokesh"
private int zone;               // integer, 2
private LocalDate validUntil;   // date, 2026-12-31

@Enumerated(EnumType.STRING)
private PassType type;          // varchar, 'MONTHLY'

1.1. A String for a Numeric Column

The most common cause is a search value that we bind to the wrong column, or a number that arrives as text from a form. Two forms of the same mistake fail at different steps.

List<?> passes = em.createNativeQuery("select * from bus_pass where zone = ?", BusPass.class)
    .setParameter(1, "A12")
    .getResultList();
// DataException: JDBC exception executing SQL [data exception: invalid character value for cast] [select * from bus_pass where zone = ?]

List<?> literal = em.createNativeQuery("select * from bus_pass where zone = 'A12'", BusPass.class)
    .getResultList();
// DataException: Could not prepare statement [data exception: invalid character value for cast in statement [select * from bus_pass where zone = 'A12']] [...]

The same code works when the text holds a number, so the bug often shows up only with real user input.

Value bound to zone	Result
“A12”	invalid character value for cast
“2”	[A12 (Lokesh, zone 2, MONTHLY, 2026-12-31)]
2	[A12 (Lokesh, zone 2, MONTHLY, 2026-12-31)]

1.2. The Enum Mapping Does Not Match the Column

@Enumerated(EnumType.STRING) stores the enum name, such as ‘STUDENT’. The default, EnumType.ORDINAL, stores its position as a number (MONTHLY = 0, WEEKLY = 1, STUDENT = 2). If an older version of the app created the column for ORDINAL, and the entity now uses STRING, every insert sends a name to a number column.

create table bus_pass (..., type tinyint)

em.persist(new BusPass("C3", "Maria", 1, LocalDate.of(2026, 12, 31), PassType.STUDENT));
// insert into bus_pass (holderName,passNumber,type,validUntil,zone,id) values (?,?,?,?,?,default)
// DataException: Unable to bind parameter #3 - STUDENT [data exception: invalid character value for cast] [n/a]

Parameter #3 is type, the third column in the printed INSERT.

1.3. A Date String in the Wrong Format

HSQLDB converts a date string in the SQL format yyyy-MM-dd. A date typed as “30/11/2026” fails with a sibling error, invalid datetime format (SQLState 22007), in the same DataException.

List<?> expired = em.createNativeQuery("select * from bus_pass where validUntil < ?", BusPass.class)
    .setParameter(1, "30/11/2026")
    .getResultList();
// DataException: JDBC exception executing SQL [data exception: invalid datetime format] [select * from bus_pass where validUntil < ?]

    .setParameter(1, "2026-11-30")   // [B7 (Alex, zone 3, WEEKLY, 2026-10-31)]

1.4. Values in Another Order Than the Column List

A classic case of the error comes from a stored procedure whose INSERT lists the columns in one order and the values in another. An INSERT matches values to columns by position, not by name, so the holder name lands in the zone column.

CREATE PROCEDURE add_bus_pass(IN p_number VARCHAR(20), IN p_holder VARCHAR(50), IN p_zone INTEGER)
  MODIFIES SQL DATA
BEGIN ATOMIC
  INSERT INTO bus_pass (passNumber, zone, holderName) VALUES (p_number, p_holder, p_zone);
END

HSQLDB creates the procedure without a complaint, but the call fails when Hibernate 7.4 runs it.

Hibernate: {call add_bus_pass(?,?,?)}
org.hibernate.exception.DataException: Error calling CallableStatement.getMoreResults [data exception: invalid character value for cast] [org.hsqldb.jdbc.JDBCCallableStatement@7c7ff37a, parameters=[[C3], [Maria], [1]]]]
Caused by: org.hsqldb.HsqlException: data exception: invalid character value for cast
	...
	at org.hsqldb.Scanner.convertToNumber(Unknown Source)
	at org.hsqldb.types.NumberType.convertToType(Unknown Source)
	at org.hsqldb.ExpressionOp.getValue(Unknown Source)
	at org.hsqldb.StatementDML.getInsertData(Unknown Source)

A plain native INSERT with the same column list fails with JDBC exception executing SQL [data exception: invalid character value for cast].

2. How to Fix It

The fix is always to send each column a value of its own type. Where the value comes from decides how we do that.

2.1. Bind the Java Type of the Column

We query the column that holds the value, or convert the text to a number in Java first. With the conversion in Java, Integer.parseInt(“A12”) fails with NumberFormatException: For input string: “A12” in our own code, where we can return a clear validation message.

// Before: text for the integer column
.setParameter(1, "A12")                            // invalid character value for cast

// After: the right column, or an int
Query byNumber = em.createNativeQuery("select * from bus_pass where passNumber = ?", BusPass.class)
    .setParameter(1, "A12");                       // [A12 (Lokesh, zone 2, ...)]
Query byZone = em.createNativeQuery("select * from bus_pass where zone = ?", BusPass.class)
    .setParameter(1, Integer.parseInt(zoneInput)); // [A12 (Lokesh, zone 2, ...)] for "2"

HQL knows the type of every attribute, so it rejects the same mistake before any SQL reaches HSQLDB.

TypedQuery<BusPass> query = em.createQuery("from BusPass p where p.zone = :zone", BusPass.class)
    .setParameter("zone", "A12");
// QueryArgumentException: Argument to query parameter has an incompatible type: Error coercing value (argument [A12] is not assignable to java.lang.Integer)

TypedQuery<BusPass> literal = em.createQuery("from BusPass p where p.zone = 'A12'", BusPass.class);
// IllegalArgumentException: SemanticException: Cannot compare left expression of type 'java.lang.Integer' with right expression of type 'java.lang.String'

2.2. Make the Enum Mapping and the Column Agree

We have two options, and the choice depends on whether the existing rows stay as numbers.

• Keep the old data and map the field as it was, either by removing @Enumerated(EnumType.STRING) or by writing @Enumerated(EnumType.ORDINAL).
• Keep STRING and migrate the column, so that it holds names.

The migration changes the column type and turns each number into its name.

alter table bus_pass alter column type set data type varchar(20);
update bus_pass set type = case type when '0' then 'MONTHLY' when '1' then 'WEEKLY' when '2' then 'STUDENT' end;

After the migration, the old row reads as MONTHLY and the new pass saves as STUDENT. To catch the mismatch at startup instead of at the first insert, we let Hibernate validate the schema with Action.VALIDATE (hibernate.hbm2ddl.auto=validate).

jakarta.persistence.PersistenceException: Unable to build Hibernate SessionFactory  [persistence unit: bus-passes]
Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema validation: wrong column type encountered in column [type] in table [bus_pass]; found [tinyint (Types#TINYINT)], but expecting [varchar(255) (Types#VARCHAR)]

2.3. Parse Dates in Java

We parse the user’s format once with a DateTimeFormatter and bind a LocalDate. Hibernate then sends a real DATE and HSQLDB has nothing to parse.

// Before
.setParameter(1, "30/11/2026")                    // invalid datetime format

// After
LocalDate until = LocalDate.parse("30/11/2026", DateTimeFormatter.ofPattern("dd/MM/yyyy"));
.setParameter(1, until)                           // [B7 (Alex, zone 3, WEEKLY, 2026-10-31)]

2.4. Write the Column List in the Order of the Values

We always name the columns in an INSERT, in the same order as the values. An INSERT without a column list depends on the table’s column order, which changes when someone adds a column.

-- Before
INSERT INTO bus_pass (passNumber, zone, holderName) VALUES (p_number, p_holder, p_zone);

-- After
INSERT INTO bus_pass (passNumber, holderName, zone) VALUES (p_number, p_holder, p_zone);

The fixed procedure saves the pass C3 for Maria in zone 1.

The complete project on GitHub reproduces every error on HSQLDB and H2 with the full exception chain and checks each error and fix with 17 JUnit tests (mvn -q compile exec:java, mvn test).

3. HsqlException FAQs

3.1. Why Does Hibernate Throw DataException Instead of HsqlException?

Hibernate never passes the driver’s SQLException to our code. It converts it to a subclass of JDBCException, which is an unchecked PersistenceException. A java.sql.SQLDataException becomes DataException. The first words of the message show which step sent the bad value.

To log the SQLState, we walk the cause chain to the first SQLException.

static SQLException sqlException(Throwable error) {
  for (Throwable cause = error; cause != null; cause = cause.getCause()) {
    if (cause instanceof SQLException sql) {
      return sql;
    }
  }
  return null;
}

String sqlState = sqlException(e).getSQLState();   // 22018
String message = sqlException(e).getMessage();     // data exception: invalid character value for cast

A driver error that Hibernate cannot classify by type or SQLState becomes a GenericJDBCException instead.

3.2. Why Do I Get “incompatible data type in conversion” for an Enum?

The message comes from the reverse mismatch, where the column holds names such as ‘MONTHLY’ and the entity maps the field as ORDINAL. Writing still works, because HSQLDB stores the ordinal 1 as the text ‘1’ without an error. Reading an old row fails with SQLGrammarException.

org.hibernate.exception.SQLGrammarException: Could not extract column [4] from JDBC ResultSet [incompatible data type in conversion: from SQL type VARCHAR to java.lang.Integer, value: MONTHLY] [n/a]

The fix is the one from section 2.2, because @Enumerated(EnumType.STRING) reads ‘MONTHLY’ again.

3.3. What Does H2 Say for the Same Mistakes?

H2 reports the same SQLStates with its own wording, and Hibernate wraps each one in DataException.

Mistake	HSQLDB 2.7.4	H2 2.5.252
“A12” bound to zone	invalid character value for cast	Data conversion error converting “CHARACTER VARYING to DECFLOAT”
zone = ‘A12’ in the SQL	invalid character value for cast	Data conversion error converting “A12”
STRING enum into a TINYINT column	invalid character value for cast	Data conversion error converting “‘STUDENT’ (BUS_PASS: “”TYPE”” TINYINT)”
ORDINAL enum reads ‘MONTHLY’	incompatible data type in conversion (SQLGrammarException)	Data conversion error converting “MONTHLY”
“30/11/2026” for a DATE	invalid datetime format	Cannot parse “DATE” constant “30/11/2026”
‘Maria’ into zone (column order)	invalid character value for cast	Data conversion error converting “‘Maria’ (BUS_PASS: “”ZONE”” INTEGER NOT NULL)”

H2 names the value and the column, so the same test is often faster to debug on H2.

4. Conclusion

The error invalid character value for cast means HSQLDB received text for a number column and could not convert it. We find the value in the Hibernate message prefix, then bind the column’s own Java type, keep the enum mapping and the column in step (and validate the schema at startup), parse dates in Java, and write every INSERT column list in the order of its values.

5. References

• HSQLDB Guide: SQL Data Types and Operations
• Hibernate ORM 7.4 User Guide: Enums
• Hibernate ORM 7.4 User Guide: Schema generation
• EnumType JavaDoc (Jakarta Persistence 3.2)
• DataException JavaDoc (Hibernate 7.4)
• SQLDataException JavaDoc (Java 25)

Happy Learning !!

Source Code on Github