-->
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
yush_a
 Post subject: Using column alias throws exception in hibernate.SQLQuery
PostPosted: Fri May 07, 2010 6:06 am 
Newbie

Joined: Wed Apr 21, 2010 4:26 am
Posts: 5
Hi,
I have recently migrated my application from JCONNECT 5.5 to JCONNECT 6.0. I am using SYBASE ASE 5.5 database. The problem is that I found that the underlying sql's using 'org.hibernate.SQLQuery createSQLQuery(..)' method do not work if the query contains aliasname / label for the column name.
For example:
Query qryObj = getSession().createSQLQuery("select noe.NoeId, noe.NoeDestinationType, noe.NoeCondition, adapter.AdapterName as Name, noe.RegionBitMask, noe.BusinessBitMask " +
"from md_config_repo_crm..t_noe_routing_rule noe, " +
"md_config_repo_crm..t_adapter adapter " +
"where noe.AdapterId = adapter.AdapterId " );

This query when run throws following exception:

Quote:
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:2216)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at com.jpmorgan.marketdirect.dao.DomainObjectDaoImpl.findBySQLQuery(DomainObjectDaoImpl.java:383)
at com.jpmorgan.marketdirect.dao.NoeRoutingRuleDaoImpl.findAllNoeRoutingRules(NoeRoutingRuleDaoImpl.java:28)
at com.jpmorgan.marketdirect.app.logic.admin.NoeRoutingRuleEntryImpl.getAllNoeRoutingRules(NoeRoutingRuleEntryImpl.java:41)
at com.jpmorgan.marketdirect.web.admin.NoeRoutingRuleListController.getAllResults(NoeRoutingRuleListController.java:56)
at com.jpmorgan.marketdirect.web.common.CommonListController.showForm(CommonListController.java:63)
at com.jpmorgan.marketdirect.web.common.RegionBusinessCommonListController.showForm(RegionBusinessCommonListController.java:23)
at org.springframework.web.servlet.mvc.AbstractFormController.showNewForm(AbstractFormController.java:338)
at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:278)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
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:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: S0022: Invalid column name 'AdapterName'.
at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
at com.sybase.jdbc3.tds.TdsResultSet.findColumnByLabel(Unknown Source)
at com.sybase.jdbc3.jdbc.SybResultSet.findColumn(Unknown Source)
at com.sybase.jdbc3.jdbc.SybResultSet.getString(Unknown Source)
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3472)
at org.hibernate.type.StringType.get(StringType.java:18)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:189)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:474)
at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:420)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:317)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:606)
at org.hibernate.loader.Loader.doQuery(Loader.java:701)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2213)
... 46 more

=07-May-2010 15:05:50.755 - http-8080-4 - ERROR - org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:615)
at com.jpmorgan.marketdirect.dao.DomainObjectDaoImpl.findBySQLQuery(DomainObjectDaoImpl.java:388)
at com.jpmorgan.marketdirect.dao.NoeRoutingRuleDaoImpl.findAllNoeRoutingRules(NoeRoutingRuleDaoImpl.java:28)
at com.jpmorgan.marketdirect.app.logic.admin.NoeRoutingRuleEntryImpl.getAllNoeRoutingRules(NoeRoutingRuleEntryImpl.java:41)
at com.jpmorgan.marketdirect.web.admin.NoeRoutingRuleListController.getAllResults(NoeRoutingRuleListController.java:56)
at com.jpmorgan.marketdirect.web.common.CommonListController.showForm(CommonListController.java:63)
at com.jpmorgan.marketdirect.web.common.RegionBusinessCommonListController.showForm(RegionBusinessCommonListController.java:23)
at org.springframework.web.servlet.mvc.AbstractFormController.showNewForm(AbstractFormController.java:338)
at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:278)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
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:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)



Note that the AdapterName has an alias Name which does not work.

Now, the JCONNECT 6 connection parameter gives a property:
GET_COLUMN_ LABEL_FOR_NAME
This maintains backward compatibility with jConnect 5.5 or earlier, where a call to ResultMetaData.getColumnName returns the column label rather than the column name. Values:
True – ResultMetaData.getColumnName returns column label.
False – ResultMetaData.getColumnName returns column name.

I tried using this property and set it to true in my URL. However still the exception persists. Kindly help let know if there is anything else to be done?
Top
 Profile  
 
yush_a
 Post subject: Re: Using column alias throws exception in hibernate.SQLQuery
PostPosted: Mon May 10, 2010 5:06 am 
Newbie

Joined: Wed Apr 21, 2010 4:26 am
Posts: 5
Hi I need this to be resolved urgently. Please help me. In short:

Hibernate is ignoring native sql aliases and generating "column not found" error when i Use JCONNECT 6.
hibernateSession.createSQLQuery("select noe.NoeId, noe.NoeDestinationType, noe.NoeCondition, adapter.AdapterName as Name, noe.RegionBitMask, noe.BusinessBitMask " +
"from md_config_repo_crm..t_noe_routing_rule noe, " +
"md_config_repo_crm..t_adapter adapter " +
"where noe.AdapterId = adapter.AdapterId " +
"UNION " +
"select noe.NoeId, noe.NoeDestinationType, noe.NoeCondition, alias.AliasName as Name, noe.RegionBitMask, noe.BusinessBitMask " +
"from md_config_repo_crm..t_noe_routing_rule noe, " +
"md_config_repo_crm..t_alias alias " +
"where noe.AliasId = alias.AliasId " +
"ORDER BY Name ")


For this query hibernate throws exception saying Column AdapterName not found. This is because it has an alias as "Name" .

I have set the following connection property:
GET_COLUMN_LABEL_FOR_NAME=true

Inspite of this, the same hibernate error persists !!
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.