DB2: SQL Error: -803, SQLState: 23505 due to NON-UTF8 character in parameter

Sometimes Java and databases do not use the same standards to represent data. This can lead to strange exceptions for which sometimes can be hard to pin point the cause.

We have the following Exception reported in the enterprise application log. Let’s analyse the errors.

[7/13/16 12:46:32:752 IST] 00001456 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions SQL Error: -302, SQLState: 22001
[7/13/16 12:46:32:753 IST] 00001456 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.. SQLCODE=-302, SQLSTATE=22001, DRIVER=4.19.26
[7/13/16 12:46:32:753 IST] 00001456 BusinessExcep E   CNTR0020E: EJB threw an unexpected (non-declared) exception during invocation of method "I" on bean "BeanId(XXXXX, null)". Exception data: javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not extract ResultSet
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:277)

STEP 1 SQL Error: -302, SQLState: 22001
The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.. SQLCODE=-302, SQLSTATE=22001, DRIVER=4.19.26

Translated this means that while executing a query the size of one of the parameters passed to the query exceeds the size of that column in the database.

The offending parameter is a code of type String having 11 characters. Looking in the Java code we see that in theory we are protected here.

...
if (code.length() == 11) {
  Query query = em.createNamedQuery("findByCode");
	query.setParameter("code", code);
        List> l = query.getResultList();
}
...

But then when we execute the query we get the above exception. It was found that the problem is due to the way DB2 represents Strings.
Our DB2 assumes we are working with UTF-8 encoding. In case the “code” contains some NON-UTF8 characters we do not have the guarantee that the DB2 representation will be on 11 chars.

STEP 2 Fix the Java code
To prevent this we must force a check that the string has the correct size after is converted by DB2.

We have to add a new method to do the check and change then the code to:

public static Boolean checkUTF8Length(String input, int length) {
		try {
			return (input.getBytes("UTF8").length) == length;
		} catch (Exception e) {
			log.info("Input string not UTF-8 encoded:" + input);
			return false;
		}
	}
... 

if (checkUTF8Length(code,11)) {
  Query query = em.createNamedQuery("findByCode");
	query.setParameter("code", code);
        List> l = query.getResultList();
}
...