Not sure why the Criteria query is bombing out on VARCHAR, but not for a CHAR field. (i.e. works for PRO_ABB_DSC but not for PRO_FU_DSC)

See SQL generated below...

This field was recently changed from CHAR to VARCHAR, so I suspect it may be a VARCHAR related issue...but why is the LCASE not working on VARCHAR? According to DB2 docs, the field for LCASE has to be CHAR or VARCHAR.

Note I see in the docs now where I can utilize a MatchMode so ignore the % + value + % below! ;)

DB2 Doc:
The LCASE or LOWER function returns a string in which all the SBCS characters have been converted to lowercase characters (that is, the characters A-Z will be translated to the characters a-z, and characters with diacritical marks will be translated to their lower case equivalents if they exist. For example, in code page 850, É maps to é). Since not all characters are translated, LCASE(UCASE(string-expression)) does not necessarily return the same result as LCASE(string-expression).

The argument must be an expression whose value is a CHAR or VARCHAR data type.




TABLE DDL:
CREATE TABLE IBC.IBC_PROCEDURE(
PRO_CD CHARacter(5) NOT NULL PRIMARY KEY,
PT_PRO_TP_CD CHARacter(2),
PRO_EFF_DT DATE NOT NULL,
PRO_TERM_DT DATE,
PRO_GRA_PRD_IND CHARacter(1) NOT NULL DEFAULT 'N',
PRO_CTG_CD CHARacter(1) NOT NULL DEFAULT 'S',
PRO_LVL_CD CHARacter(1) NOT NULL,
PRO_ABB_DSC CHARacter(100) NOT NULL,
PRO_FU_DSC VARCHAR(3000) NOT NULL,
CRE_USE_ID CHARacter(8) NOT NULL DEFAULT USER,
CRE_TS TIMESTAMP NOT NULL,
LAST_UPD_USE_ID CHARacter(8) NOT NULL DEFAULT USER,
LAST_UPD_TS TIMESTAMP NOT NULL,
DEL_IND CHARacter(1) NOT NULL DEFAULT 'N'
)

Relative Code:

c.add(Expression.ilike(key,"%" + value.trim() + "%"));

where
c = Criteria
key = field
value = value of the field to query on


Hibernate version:3.0.2

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="com.ibx.ccs.hibernate.ComplexMedicalProcedure" schema="IBC" table="IBC_PROCEDURE">
<id name="procedureCode" column="PRO_CD" type="string" length="5"><generator class="assigned"/></id>
<property name="procedureTypeCode" column="PT_PRO_TP_CD" type="string" not-null="false" length="2"/>
<property name="effectiveDate" column="PRO_EFF_DT" type="date" not-null="true" length="10"/>
<property name="terminationDate" column="PRO_TERM_DT" type="date" not-null="false" length="10"/>
<property name="gracePeriodIndicator" column="PRO_GRA_PRD_IND" type="string" not-null="true" length="1"/>
<property name="categoryCode" column="PRO_CTG_CD" type="string" not-null="true" length="1"/>
<property name="levelCode" column="PRO_LVL_CD" type="string" not-null="true" length="1"/>
<property name="abbreviatedDescription" column="PRO_ABB_DSC" type="string" not-null="true" length="100"/>
<property name="fullDescription" column="PRO_FU_DSC" type="string" not-null="true" length="3000"/>
<property name="createUser" column="CRE_USE_ID" type="string" not-null="true" length="8"/>
<property name="createTimestamp" column="CRE_TS" type="timestamp" not-null="true" length="26" />
<property name="lastUpdateUser" column="LAST_UPD_USE_ID" type="string" not-null="true" length="8"/>
<property name="lastUpdateTimestamp" column="LAST_UPD_TS" type="timestamp" not-null="true" length="26" />
<property name="deleteIndicator" column="DEL_IND" type="string" not-null="true" length="1"/>
</class>
</hibernate-mapping>

Full stack trace of any exception that occurs:

[2005-05-02 15:58:57,129] DEBUG com.ibx.ccs.hibernate.ComplexMedicalProcedure Criteria = CriteriaImpl(com.ibx.ccs.hibernate.ComplexMedicalProcedure:this[][fullDescription ilike %ZZZ%])
Hibernate: select this_.PRO_CD as PRO1_0_, this_.PT_PRO_TP_CD as PT2_2_0_, this_.PRO_EFF_DT as PRO3_2_0_, this_.PRO_TERM_DT as PRO4_2_0_, this_.PRO_GRA_PRD_IND as PRO5_2_0_, this_.PRO_CTG_CD as PRO6_2_0_, this_.PRO_LVL_CD as PRO7_2_0_, this_.PRO_ABB_DSC as PRO8_2_0_, this_.PRO_FU_DSC as PRO9_2_0_, this_.CRE_USE_ID as CRE10_2_0_, this_.CRE_TS as CRE11_2_0_, this_.LAST_UPD_USE_ID as LAST12_2_0_, this_.LAST_UPD_TS as LAST13_2_0_, this_.DEL_IND as DEL14_2_0_ from IBC.IBC_PROCEDURE this_ where lcase(this_.PRO_FU_DSC) like ? order by this_.PRO_CD asc
[5/2/05 15:59:01:406 EDT] 47e53271 JDBCException W org.hibernate.util.JDBCExceptionReporter SQL Error: -171, SQLState: 42815
[5/2/05 15:59:01:406 EDT] 47e53271 JDBCException E org.hibernate.util.JDBCExceptionReporter [IBM][CLI Driver][DB2] SQL0171N The data type, length or value of argument "1" of routine "LCASE" is incorrect. SQLSTATE=42815

[5/2/05 15:59:01:796 EDT] 47e53271 SystemErr R [2005-05-02 15:59:01,796] ERROR com.ibx.ccs.exceptions.CCSWebException CCSWebException Caught: org.hibernate.exception.SQLGrammarException: could not execute query
[5/2/05 15:59:01:796 EDT] 47e53271 PropertyMessa I org.apache.struts.util.PropertyMessageResources Initializing, config='org.apache.struts.action.LocalStrings', returnNull=true
[2005-05-02 15:59:01,806] FATAL com.ibx.ccs.exceptions.CCSWebException CCSWeb Application Fatal Error!
Application Region: Desktop
Application Server: PA103248
Application Exception: org.hibernate.exception.SQLGrammarException: could not execute query
Application Exception Cause: N/A
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1560)
at org.hibernate.loader.Loader.list(Loader.java:1540)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:113)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1254)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)
at com.ibx.ccs.hibernate.ComplexMedicalProcedure.executeInquiry(ComplexMedicalProcedure.java:607)
at com.ibx.ccs.struts.actions.ComplexMedicalProcedureInquiryAction.execute(ComplexMedicalProcedureInquiryAction.java:166)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:76)
at com.ibx.ccs.struts.actions.RequestEncodingFilter.doFilter(RequestEncodingFilter.java:57)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:132)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:71)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:939)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:530)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:176)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:201)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:186)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:610)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:435)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0171N The data type, length or value of argument "1" of routine "LCASE" is incorrect. SQLSTATE=42815
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:269)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:206)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:457)
at COM.ibm.db2.jdbc.app.DB2ResultSet.next(DB2ResultSet.java:548)
at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.java:2418)
at org.hibernate.loader.Loader.doQuery(Loader.java:387)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
at org.hibernate.loader.Loader.doList(Loader.java:1557)
... 36 more

Name and version of the database you are using:
DB2 version 07.01.0002
DB2 JDBC Driver version 07.02.0004

The generated SQL (show_sql=true):

select this_.PRO_CD as PRO1_0_, this_.PT_PRO_TP_CD as PT2_2_0_, this_.PRO_EFF_DT as PRO3_2_0_, this_.PRO_TERM_DT as PRO4_2_0_, this_.PRO_GRA_PRD_IND as PRO5_2_0_, this_.PRO_CTG_CD as PRO6_2_0_, this_.PRO_LVL_CD as PRO7_2_0_, this_.PRO_ABB_DSC as PRO8_2_0_, this_.PRO_FU_DSC as PRO9_2_0_, this_.CRE_USE_ID as CRE10_2_0_, this_.CRE_TS as CRE11_2_0_, this_.LAST_UPD_USE_ID as LAST12_2_0_, this_.LAST_UPD_TS as LAST13_2_0_, this_.DEL_IND as DEL14_2_0_ from IBC.IBC_PROCEDURE this_ where lcase(this_.PRO_FU_DSC) like ? order by this_.PRO_CD asc

*bump*

Some follow-up info...

Apparently there is a data relative issue in a VARCHAR field that is impacting the LCASE and throwing this error. Still narrowing down on what it is...I am flagging every character outside of ASCII range and CR, LF, and TAB via a check outside the range of

(ch >= 0x20 && ch <= 0x7F) || ch == 0x09 || ch == 0x0A || ch == 0x0D

The only exception thus far is a degree symbol, which when I remove has no impact on the query failing and when I drop the table data and insert a single row with the degree symbol everything works fine.

I suspect maybe a control character like CR/LF/TAB may be causing the error? No clue here...anyone have any insight into LCASE constraints?

OK...so we come to an answer.

DB2 Universal Database for OS/390 and z/OS SQL Reference Version 7 states:

The LCASE or LOWER function returns a string in which all the characters have been converted to lowercase characters.

| string-expression
An expression that specifies the string to be converted. The string must be a character or graphic string. A character string argument must not be a CLOB and must have an actual length that is not greater than 255. A graphic string argument must not be a DBCLOB and must have an actual length that is not greater than 127.


Although the fields in other tables are VARCHAR exceeding 255, there are no values in those fields exceeding 255 characters right now, so they work fine. In the table I am having trouble with there are values that exceed 255 characters and therefore throw this error.


So that brings me to a new question, how can I perform case insensitive searching with VARCHAR fields greater than 255 as the ilike casts to LCASE to perform this type of functionality but cannot on field values larger than 255 characters.


Perhaps this is not the right forum or place for that question, but in the very least at least the Hibernate Team can be aware that there is a limitation in the LCASE operation on ilike matching with Criteria...which aligns with the DB2 documentation (and may be considered beyond the scope of their implementation...I will let them decide).

Thanks for the help, or lack thereof! ;)
Good Times! :)

So another follow-up, again worth noting perhaps for the sake of the Hibernate implementation regarding doing Expression.ilike queries on fields in DB2 version 7 larger than 255 characters:

This Does NOT Work:
SELECT * FROM TABLE WHERE LCASE(FIELD) like '%VALUE%;

This Works:
SELECT * FROM TABLE WHERE FIELD like LCASE('%VALUE%');

I will be using an explicit implementation now to work around this issue as I cannot use the ilike method in Hibernate.

Let me know if you have any Qs or want me to try anything else.

Nevermind, nix the above.