-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
andrew_tyson
 Post subject: 3.1.3/IBM DB2 8.1 - Dialect bug using upper() ?
PostPosted: Sun Sep 17, 2006 7:37 pm 
Newbie

Joined: Fri Jun 23, 2006 1:10 am
Posts: 2
Hi,

I am having some problems with the DB2 dialect when trying to execute the upper() function on a named parameter in an HQL fragment against a DB2 database. I suspect that it is a problem with the DB2 dialect implementation because;

1. when I run the same piece of HQL against an in memory HSQLDB implementation everything works fine.
2. when I run the generated SQL through a DB client the query is fine.

There appears to be a problem populating filter parameters when a database function is involved.

Thanks and regards,
Andrew

Hibernate version: 3.1.3

Mapping documents:
Code:
<hibernate-mapping package="au.com.allianz.evolution.isp.business.print.impl.hibernate">
  <class name="PdsTrgtLnk" table="PDS_TRGT_LNK" schema="ALNZ">
    <cache usage="read-only"/>
    <composite-id name="id" class="PdsTrgtLnkKey">
      <key-property name="pdsTrgtCode" column="PDS_TRGT_CODE" type="string"/>
      <key-property name="productCode" column="PRDCT_CODE" type="string"/>
      <key-property name="riskClass" column="RISK_CLASS" type="string"/>
      <key-property name="effDate" column="EFF_DATE" type="date"/>
    </composite-id>
    <!-- NOTE THAT WE SET outer-join ATTRIBUTE TO ENSURE THAT THE PDS DATA IS LOADED IMMEDIATELY -->
    <many-to-one name="pdsData" column="PDS_DATA_CODE" class="PdsData" not-null="true" outer-join="true" lazy="false"/>
  </class>
  <class name="PdsData" table="PDS_DATA" schema="ALNZ">
    <cache usage="read-only"/>
    <id name="pdsDataCode" column="PDS_DATA_CODE" type="integer">
      <generator class="sequence">
        <param name="sequence">ALNZ.SQ_PDS_DATA</param>
      </generator>
    </id>
    <property name="pdsDscr" column="PDS_DSCR" type="string" not-null="true"/>
    <property name="pdsVal" column="PDS_VAL" type="binary" not-null="true" lazy="false"/>
  </class>
  <query name="au.com.allianz.evolution.isp.business.print.impl.hibernate.PdsTrgtLnkKey">
    from PdsTrgtLnk as pds where upper(rtrim(pds.id.pdsTrgtCode)) = upper(:pdsTrgtCode)
                                            and      rtrim(pds.id.productCode)= :productCode
                                            and      rtrim(pds.id.riskClass)= :riskClass
                                            and      pds.id.effDate &lt;= :effDate
                                           order by pds.id.effDate desc
  </query>
</hibernate-mapping>


Full stack trace of any exception that occurs:


[18/09/06 09:29:06:265 EST] 0000003e JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: -418, SQLState: 42610
[18/09/06 09:29:06:265 EST] 0000003e JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions A statement contains a use of a parameter marker that is not valid.
[18/09/06 09:29:06:265 EST] 0000003e JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: -727, SQLState: 56098
[18/09/06 09:29:06:265 EST] 0000003e JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-418", SQLSTATE "42610" and message tokens "".
[18/09/06 09:29:06:265 EST] 0000003e JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: -727, SQLState: 56098
[18/09/06 09:29:06:281 EST] 0000003e JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-418", SQLSTATE "42610" and message tokens "".
[18/09/06 09:29:06:312 EST] 0000003e SystemOut O 2006-09-18 09:29:06,312 [WebContainer : 1] ERROR au.com.allianz.evolution.isp.web.servlet.LocalPrintDocumentServlet - got exception processing request for document type PDS for policy null
au.com.allianz.evolution.isp.business.print.LocalPrintServiceException: There has been an error retrieving document from the DB [APPLICATION_ERROR::ISP_BUSINESS::PRINT_IMPL::DATABASE_QUERY_ERROR::1158535746296]
-Nested Exception: au.com.allianz.evolution.isp.business.referencedata.QueryHelperException: There has been a hibernate error [APPLICATION_ERROR::ISP_BUSINESS::REFERENCEDATA_HIBERNATE::HIBERNATE_ERROR::1158535746281]
-Nested Exception: org.hibernate.exception.SQLGrammarException: could not execute query
at au.com.allianz.evolution.isp.business.print.impl.LocalPrintDocumentServiceImpl.generateStaticPrintDocument(LocalPrintDocumentServiceImpl.java:142)
at au.com.allianz.evolution.isp.business.print.impl.LocalPrintDocumentServiceImpl.generatePrintDocument(LocalPrintDocumentServiceImpl.java:111)
at au.com.allianz.evolution.isp.web.servlet.LocalPrintDocumentServlet.doPost(LocalPrintDocumentServlet.java:98)
at au.com.allianz.evolution.isp.web.servlet.LocalPrintDocumentServlet.doGet(LocalPrintDocumentServlet.java:75)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1282)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1239)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:136)
at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118)
at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:142)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:121)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:82)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:670)
at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:2905)
at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:220)
at com.ibm.ws.webcontainer.VirtualHost.handleRequest(VirtualHost.java:204)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1829)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:84)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:469)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:408)
at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:101)
at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:566)
at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:619)
at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:952)
at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1039)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1455)
Caused by: au.com.allianz.evolution.isp.business.referencedata.QueryHelperException: There has been a hibernate error [APPLICATION_ERROR::ISP_BUSINESS::REFERENCEDATA_HIBERNATE::HIBERNATE_ERROR::1158535746281]
-Nested Exception: org.hibernate.exception.SQLGrammarException: could not execute query
at au.com.allianz.evolution.isp.business.referencedata.hibernate.QueryHelperHQLAdaptor.list(QueryHelperHQLAdaptor.java:114)
at au.com.allianz.evolution.isp.business.print.impl.hibernate.StaticPrintDocumentDAOImpl.getStaticPrintDocument(StaticPrintDocumentDAOImpl.java:48)
at au.com.allianz.evolution.isp.business.print.impl.LocalPrintDocumentServiceImpl.generateStaticPrintDocument(LocalPrintDocumentServiceImpl.java:137)
... 27 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1106)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at au.com.allianz.evolution.isp.business.referencedata.hibernate.QueryHelperHQLAdaptor.list(QueryHelperHQLAdaptor.java:101)
... 29 more
Caused by: com.ibm.db2.jcc.a.SqlException: A statement contains a use of a parameter marker that is not valid.
at com.ibm.db2.jcc.a.rf.e(rf.java:1680)
at com.ibm.db2.jcc.a.rf.a(rf.java:1239)
at com.ibm.db2.jcc.b.jb.h(jb.java:139)
at com.ibm.db2.jcc.b.jb.a(jb.java:43)
at com.ibm.db2.jcc.b.w.a(w.java:30)
at com.ibm.db2.jcc.b.cc.g(cc.java:160)
at com.ibm.db2.jcc.a.rf.n(rf.java:1219)
at com.ibm.db2.jcc.a.sf.gb(sf.java:1790)
at com.ibm.db2.jcc.a.sf.d(sf.java:2266)
at com.ibm.db2.jcc.a.sf.X(sf.java:508)
at com.ibm.db2.jcc.a.sf.executeQuery(sf.java:491)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:667)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:477)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)


Name and version of the database you are using:
Code:
IBM DB2 8.1


The generated SQL (show_sql=true):
Code:
select * from (
select rownumber() over(order by pdstrgtlnk0_.EFF_DATE desc) as rownumber_,
pdstrgtlnk0_.PDS_TRGT_CODE as PDS1_68_,
pdstrgtlnk0_.PRDCT_CODE as PRDCT2_68_,
pdstrgtlnk0_.RISK_CLASS as RISK3_68_,
pdstrgtlnk0_.EFF_DATE as EFF4_68_,
pdstrgtlnk0_.PDS_DATA_CODE as PDS5_68_ from ALNZ.PDS_TRGT_LNK
pdstrgtlnk0_ where upper(rtrim(pdstrgtlnk0_.PDS_TRGT_CODE))=upper(?)
and rtrim(pdstrgtlnk0_.PRDCT_CODE)=? and
rtrim(pdstrgtlnk0_.RISK_CLASS)=? and pdstrgtlnk0_.EFF_DATE<=?
order by pdstrgtlnk0_.EFF_DATE desc ) as temp_ where rownumber_ <= ?
Top
 Profile  
 
jpkrohling
 Post subject: Re: 3.1.3/IBM DB2 8.1 - Dialect bug using upper() ?
PostPosted: Thu Nov 05, 2009 7:54 am 
Beginner
Beginner

Joined: Wed Jul 09, 2008 5:34 am
Posts: 41
Location: Brno, Czech Republic
https://jira.jboss.org/jira/browse/JBPAPP-3056
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.