Hibernate version: 3

Interesting issue, I have an id field with the value of 'zzzÐ' in my DB2 table. I can view this field value correctly with a variety of SQL tools.

Yet when I execute a Criteria.list() after setting the Criteria to query on this id field like zz, it returns the same row twice but with an id value of zzz with no Ð.

As you can see below, there are a total of 4 rows being returned when there are actually only 3 in the table that match zz. You will also see the first two in the Results have the same memory address, i.e. com.ibx.ccs.hibernate.ComplexMedicalRevenue@30953a80.

Now even more interesting is that any property field that contains this same character Ð is returned fine, say a property with the value Ðescription.

Is this a bug? Not sure what is going on or how two identical memory addresses can be returned by a single Criteria.list(). Anyway, having a ton of fun dealing with special characters (multi lingual and multi encoding) in my app...

[2005-04-19 20:05:55,071] DEBUG com.ibx.ccs.hibernate.ComplexMedicalRevenue Criteria = CriteriaImpl(com.ibx.ccs.hibernate.ComplexMedicalRevenue:this[][revenueCode ilike zz%])
[2005-04-19 20:05:55,231] DEBUG com.ibx.ccs.hibernate.ComplexMedicalRevenue Results = [com.ibx.ccs.hibernate.ComplexMedicalRevenue@30953a80, com.ibx.ccs.hibernate.ComplexMedicalRevenue@30953a80, com.ibx.ccs.hibernate.ComplexMedicalRevenue@26077a81, com.ibx.ccs.hibernate.ComplexMedicalRevenue@2459fa81]
[2005-04-19 20:05:55,231] DEBUG com.ibx.ccs.hibernate.ComplexMedicalRevenue Results Size = 4

Is more information required here? Does anyone not think this is an issue?

We'd need to see your Criteria definition.

Also enable sql output and try the resulting sql through the same JDBC Connection that Hibernate is using. What results do you get?

Steve:

Below is the SQL generated by the query:

Hibernate: select this_.RVN_CD as RVN1_0_, this_.RVN_EFF_DT as RVN2_0_0_, this_.RVN_TERM_DT as RVN3_0_0_, this_.RVN_DFL_DT_IND as RVN4_0_0_, this_.RVN_CTG_CD as RVN5_0_0_, this_.RVN_FU_DSC as RVN6_0_0_, this_.RVN_SUBC_STD_ABB as RVN7_0_0_, this_.CRE_USE_ID as CRE8_0_0_, this_.CRE_TS as CRE9_0_0_, this_.LAST_UPD_USE_ID as LAST10_0_0_, this_.LAST_UPD_TS as LAST11_0_0_, this_.DEL_IND as DEL12_0_0_ from IBC.IBC_REVENUE this_ where lcase(this_.RVN_CD) like ? order by this_.RVN_CD asc

Regarding the Criteria definition, I have a utility helper class that creates the Criteria based on form values and query options selected (i.e. Contains, Begins With, Ends With, etc).

For this particular query, the Criteria for the problematic field is set as follows...



where
key = the field to search on
value = field value (in this case 'zz')

The field in question is an id field in the mapping. When and id field has a "special character" (in this case 'Ð'), it will return a duplicate in the Criteria.list() but will not display the special character in the returned results. That same "special character" field value is fine to search on and display in non-id fields (i.e. property for example).

Below is my mapping for the class this is causing problems in, which seems like a very straightforward, simple mapping:





Running a similar select in WinSQL (or even a command prompt DB2 shell) returns the correct number of rows (3 instead of 4 as Hibernate does) with the correct values for the id field (including the special character Ð, unlike Hibernate which returns zzz instead of zzzÐ).


Let me know if you need any additional information, or can spot anything in the SQL or code above.

I said run it through the same JDBC driver, not through some other tool. JDBC drivers have been known to have bugs ;)

I do not think that is the Criteria builder you are using for that query. Notice the resulting sql query does not have the or expression in the where clause...

You were correct, the Criteria code I posted above was for a helper method dealing with searchable "text" fields, below is the Criteria for a single searchable "code" field "Begins With" clause:



where
key = key value (id key value 'revenueCode')
value = field value (id field value 'zz')

************************************************************
Now, before I move on, there was an error in what I said before that I just discovered. It turns out, in an Edit step in earlier usage, the truncated value ZZZÐ was added as another row as ZZZ.

This made a total of 4 rows matching ZZ {ZZZ1, ZZZ2, ZZZ, ZZZÐ}.
So Hibernate was returning the correct number of rows but displaying ZZZ and ZZZÐ both as ZZZ.

Similarly as you'll see below, via JDBC ZZZÐ is truncated to just ZZZ.

************************************************************



Tested with a Prepared statement via JDBC by doing a Context.lookup for the JNDI datasource and below are the logged results...

[2005-04-20 13:57:13,645] ERROR com.ibx.ccs.hibernate.ComplexMedicalRevenue ResultSet = com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet@6cffa290
[2005-04-20 13:57:13,645] ERROR com.ibx.ccs.hibernate.ComplexMedicalRevenue Getting Rev Code
[2005-04-20 13:57:13,645] ERROR com.ibx.ccs.hibernate.ComplexMedicalRevenue REV CODE FROM JDBC = ZZZ
[2005-04-20 13:57:13,645] ERROR com.ibx.ccs.hibernate.ComplexMedicalRevenue Getting Rev Code
[2005-04-20 13:57:13,645] ERROR com.ibx.ccs.hibernate.ComplexMedicalRevenue REV CODE FROM JDBC = ZZZ
[2005-04-20 13:57:13,645] ERROR com.ibx.ccs.hibernate.ComplexMedicalRevenue Getting Rev Code
[2005-04-20 13:57:13,645] ERROR com.ibx.ccs.hibernate.ComplexMedicalRevenue REV CODE FROM JDBC = ZZZ1
[2005-04-20 13:57:13,645] ERROR com.ibx.ccs.hibernate.ComplexMedicalRevenue Getting Rev Code
[2005-04-20 13:57:13,645] ERROR com.ibx.ccs.hibernate.ComplexMedicalRevenue REV CODE FROM JDBC = ZZZ2


So is this a driver issue? Or some translation issue due to a "special character"?

It seems the value is truncated in the key field but not in the other fields (i.e. non-keyed fields).

Thoughts?

Well the jdbc driver is presenting the value of that as ZZZ which is how Hibernate sees it. You'll have to check with your JDBC driver provider to see about character set support.

Excellent, thanks Steve.

I'll check into it and if I find anything interesting, I'll post back.
Thanks for your feedback and assistance!