Hi all,
I've searched the forums, read the FAQs, and even tried debugging Hibernate in my IDE, but no joy.
I'm trying to sort the elements of a one-to-many set by one of their string properties, case-insensitively. The generated SQL works fine in Hypersonic (as proven by my unit tests), but not in SQL Server 2000 (my production database). I'm surprised it even works in Hypersonic, given the weird syntax "order by keywords0_.upper(name)", but it does.
Any ideas why such odd-looking SQL is being generated?
Hibernate version: 3.0.5
Mapping documents:
Code:
<hibernate-mapping>
<class table="keyword_type" name="au.com.bisinfo.cmgr.logic.keyword.KeywordType">
<id access="field" name="id" type="java.lang.Integer">
<generator class="native"/>
</id>
<set cascade="all" order-by="lower(name)" lazy="false" name="keywords">
<key not-null="true" foreign-key="FK_KeywordKeywordType" column="keyword_type_fk"/>
<one-to-many class="au.com.bisinfo.cmgr.logic.keyword.Keyword"/>
</set>
<property name="parentClass" access="field" update="false" not-null="true"/>
<property name="name" length="30" access="field" not-null="true"/>
</class>
</hibernate-mapping>
N/A - using Spring's HibernateTemplate
Full stack trace of any exception that occurs:Code:
12:09:04,905 ERROR [JDBCExceptionReporter] [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid object name 'keywords0_.upper'.
12:09:04,920 WARN [DispatcherServlet] Handler execution resulted in exception - forwarding to resolved error view
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not initialize a collection: [au.com.bisinfo
.cmgr.logic.keyword.KeywordType.keywords#1]; bad SQL grammar [select keywords0_.keyword_type_fk as keyword3_1_, keywords
0_.id as id1_, keywords0_.id as id0_, keywords0_.name as name2_0_ from keyword keywords0_ where keywords0_.keyword_type_
fk=? order by keywords0_.upper(name)]; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Invalid object name 'keywords0_.upper'.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid object name 'keywords0_.upper'.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:296)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1434)
at org.hibernate.loader.collection.OneToManyLoader.initialize(OneToManyLoader.java:111)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:48
8)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeColl
ectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1430)
at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:2
80)
at org.hibernate.engine.PersistenceContext.initializeNonLazyCollections(PersistenceContext.java:796)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:751)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:312)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:742)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:738)
at au.com.bisinfo.cmgr.data.keyword.KeywordTypeDAOHibernate.getTypes(KeywordTypeDAOHibernate.java:95)
at au.com.bisinfo.cmgr.logic.keyword.KeywordServicePOJOImpl.getKeywordTypes(KeywordServicePOJOImpl.java:60)
at au.com.bisinfo.cmgr.client.web.view.KeywordTypeListModelAndView.<init>(KeywordTypeListModelAndView.java:56)
at au.com.bisinfo.cmgr.client.web.controller.KeywordTypeListController.list(KeywordTypeListController.java:67)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController
.java:351)
at org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionContro
ller.java:305)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:128)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java
:44)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:684)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:625)
at org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:386)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:346)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118)
at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at net.sf.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:292)
at net.sf.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:84)
at net.sf.acegisecurity.intercept.web.SecurityEnforcementFilter.doFilter(SecurityEnforcementFilter.java:182)
at net.sf.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at net.sf.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:114)
at net.sf.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at net.sf.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:305)
at net.sf.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at net.sf.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter
.java:225)
at net.sf.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at net.sf.acegisecurity.securechannel.ChannelProcessingFilter.doFilter(ChannelProcessingFilter.java:168)
at net.sf.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at net.sf.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:173)
at net.sf.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:66)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:153)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:54)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
12:09:05,608 INFO [ResourceBundleThemeSource] Theme created: name 'theme', basename [theme]
Name and version of the database you are using: MS SQL Server 2000
The generated SQL (show_sql=true):Code:
select keywords0_.keyword_type_fk as keyword3_1_,
keywords0_.id as id1_,
keywords0_.id as id0_,
keywords0_.name as name2_0_
from keyword keywords0_
where keywords0_.keyword_type_fk=?
order by keywords0_.upper(name)
Debug level Hibernate log excerpt: Will post if relevant...
Login
FAQ
Search