Data Truncation while reading DB2 Character dataype

srbhatt · **Joined:** Mon May 22, 2006 9:11 am **Posts:** 2

Hi,
I am facing strange problem of data truncation when I load data from DB2 Character(X) field type. I am trying to get data using session.createSQLQuery() and get the object[][] array.

I am facing data truncation problems when I try to get data from object array.

Pleaes review the following example to understand the problem better.

SQLQuery query = session.createSQLQuery(strSQL);

ArrayList tmpactivityDetails=(ArrayList)query.list();

ArrayList activityDetails=new ArrayList();

for(Iterator it=tmpactivityDetails.iterator();it.hasNext();)
{
Object[] row = (Object[]) it.next();
QueryByCaseNumberDetailTO detail=new QueryByCaseNumberDetailTO();
detail.setCaseYear(new Integer(row[1].toString()).intValue());
detail.setCaseType(row[2].toString());

}

Here the problem is that caseType is character datatype in DB2 and we just get the first character and not the entire string as we face data truncation.

Please suggest us how to get the complete string value from the caseType column in DB2 using Hibernate SQL Query?

Thanks in advance,
Sam

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I don't think that hibernate will be truncating anything here: it doesn't get involved in any type changes when SQL queries are involved. Are you sure that your database column contains more than one character? It could be that your hibernate mapping is off, and only one character was saved to the database.

Aside: you shouldn't cast the think that query.list() returns to ArrayList: it's a List, and there's no guarantee that the object returned is an ArrayList.

sudhir0104 · **Posted:** Tue Jun 20, 2006 4:10 am

Not sure what java type you have defined for this column......I would suggest keep the column type as String (if its going to contain more than one character) and retrive it normal fashion......

if you want only first character of the string, use string's charAt(0) method.....

Sudhir

sudhir0104 · **Posted:** Tue Jun 20, 2006 4:11 am

Not sure what java type you have defined for this column......I would suggest keep the column type as String (if its going to contain more than one character) and retrive it normal fashion......

if you want only first character of the string, use string's charAt(0) method.....

Sudhir

madeinstein · **Joined:** Thu Jul 27, 2006 10:02 am **Posts:** 17

tenwit wrote:

I don't think that hibernate will be truncating anything here: it doesn't get involved in any type changes when SQL queries are involved. Are you sure that your database column contains more than one character? It could be that your hibernate mapping is off, and only one character was saved to the database.

Maybe it shouldn't but it does.

I have CHARACTER(32) column in database when I run my query

Code:

SELECT p.pointid FROM point p WHERE p.pointid = '8ac084d20d5f4cb6010d5f4cd6460003'

and try to get a result

Code:

List result = query.list();
Object row = result.iterator().next();
System.out.println(row.getClass());
System.out.println(row);

this is printed to the screen

Code:

'class java.lang.Character'
8

Please advice if the code above is the right way to do this.. otherwise I'll post it as a BUG in hibernate.

madeinstein · **Joined:** Thu Jul 27, 2006 10:02 am **Posts:** 17

I found a way around.

Code:

SQLQuery q = s.createSQLQuery("SELECT {point.*} FROM point point WHERE point.pointid = '8ac084d20d5f4cb6010d5f4cd6460003'");
q.addEntity("p",Point.class);

List result = query.list();
Object row = result.iterator().next();
System.out.println(row.getClass());          
String pointId = ((Point)row).getId();
System.out.println(pointId);

gives me this output, as expected

Code:

class model.Point
8ac084d20d5f4cb6010d5f4cd6460003

So there is a bug with SQLQuery mapping for CHARACTER column, but it works fine with default hibernate mapping.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

No there isn't. The hibernate character mapping is from/to the java Character class. That is, a single unicode character. The bug here is that you thought that type="character" length="32" would work, when it cannot. This isn't a bug in hibernate that you worked around, it's a flaw in your understanding that you corrected.

DB2 type character(32) maps to type="string" length="32". If you had posted your mapping earlier, that would have been obvious.

madeinstein · **Joined:** Thu Jul 27, 2006 10:02 am **Posts:** 17

Sorry tenwit but I don't know where the flaw in my understanding is.

I have column in DB2 which is CHARACTER(32) and this doesn't have anything to do with java.lang.Character, it's like VARCHAR(32) but fixed length.

Code:

TABLE:POINT
ID:CHARACTER(32)
NAME:VARCHAR(32)

This are the sample values

Code:

ID:  '8ac084d20d5f4cb6010d5f4cd6460003'
NAME: '8ac084d20d5f4cb6010d5f4cd6460003'

I run this query

Code:

SELECT p.id, p.name FROM point p WHERE p.id = '8ac084d20d5f4cb6010d5f4cd6460003'

and the result is:

Code:

p.id = '8'
p.name = '8ac084d20d5f4cb6010d5f4cd6460003'

Do you see the problem now? Somehow hibernate is using java.lang.Characer rather than java.lang.String for the CHARACTER(32) and it's incorrect because CHARACTER(32) is either String or Character[32] array not a single Character.

piet.t · **Joined:** Fri May 12, 2006 4:05 am **Posts:** 106

Hi,

while you haven't posted your hibernate-mappings I will assume tenwit has it right.
Just note that type=.... will define the java-type used for mapping this attribute while sqltype=.... defines the database-type.

madeinstein · **Joined:** Thu Jul 27, 2006 10:02 am **Posts:** 17

If I do this in Hibernate

Code:

Session s = getSession();
SQLQuery q = s.createSQLQuery("SELECT p.id, p.name FROM point p WHERE point.p = '8ac084d20d5f4cb6010d5f4cd6460003'"); 
List list = q.list();

Does hibernate use mapping file? If yes, how?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

No, that code snippet does. But this is what you posted earlier:

Code:

SQLQuery q = s.createSQLQuery("SELECT {point.*} FROM point point WHERE point.pointid = '8ac084d20d5f4cb6010d5f4cd6460003'");
q.addEntity("p",Point.class); 

This code does use you mapping: it uses the mapping for the Point class, because of the addEntity combined with the {point.*} notation. It uses the mapping for Point to read columns from the result set.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Oops, first line was suppose to read "No, that code snippet doesn't.". Wouldn't want to confuse you further :)

madeinstein · **Joined:** Thu Jul 27, 2006 10:02 am **Posts:** 17

You're right..

This code is using mapping files and is working fine.. I get correct values back

Code:

SQLQuery q = s.createSQLQuery("SELECT {point.*} FROM point point WHERE point.pointid = '8ac084d20d5f4cb6010d5f4cd6460003'"); 
q.addEntity("p",Point.class); 

But this one is not using mapping files, and returns single character.

Code:

Session s = getSession(); 
SQLQuery q = s.createSQLQuery("SELECT p.id, p.name FROM point p WHERE point.p = '8ac084d20d5f4cb6010d5f4cd6460003'"); 
List list = q.list();

And I think it's incorrect.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

No, it's not incorrect. Hibernate does not profess any ability at guessing what columns contain what kind of data. When using SQLQuery, you must describe what each returned column contains, by using addEntity or addScalar (or for named SQL queries, <return> or <return-scalar>). The behaviour of SQLQuery without these column-describing features is very definitely not defined. Have a careful read of all the examples in section 16 of the ref docs: there isn't a single one that doesn't fully describe all columns.

max · **Posted:** Mon Sep 11, 2006 5:47 pm

hibernate 3.2 does not require addScalar/addEntity. if nothing is specified we will "autodiscover" it and thus your query should return an Object[] with 2 elements per "row"