Hello,

I get the following error for a simple query getHibernateTemplate().find("from productdetails") . Error and source below. Any help is appreciated.

I am using Hibernate 2.1.2, Spring 1.0, and Tapestry 3.0RC with JDK 1.4 on Apache Tomcat/5.0.19.


--- ERROR -----
Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'from' at line 1"
SQLState: 42000
errorCode: 1064
Stack Trace:

* com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1876)
* com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1098)
* com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1192)
* com.mysql.jdbc.Connection.execSQL(Connection.java:2051)
* com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1496)
* net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
* net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795)
* net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
* net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
* net.sf.hibernate.loader.Loader.doList(Loader.java:950)
* net.sf.hibernate.loader.Loader.list(Loader.java:941)
* net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
* net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1512)
* net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
* org.springframework.orm.hibernate.HibernateTemplate$17.doInHibernate(HibernateTemplate.java:331)
* org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:150)
* org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:170)
* org.springframework.orm.hibernate.HibernateTemplate.find(HibernateTemplate.java:328)
* org.apache.tapestry.pets.domain.PetshopManagerImpl.findByCategory(PetshopManagerImpl.java:173)
* sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
* sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
* sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
* java.lang.reflect.Method.invoke(Method.java:324)
* org.springframework.aop.framework.AopProxyUtils.invokeJoinpointUsingReflection(AopProxyUtils.java:59)
* org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:145)
* org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:114)
* org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:169)
* org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:134)
* org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:148)
* $Proxy4.findByCategory(Unknown Source)
* org.apache.tapestry.pets.presentation.pages.CategoryPage.pageBeginRender(CategoryPage.java:83)
* org.apache.tapestry.AbstractPage.firePageBeginRender(AbstractPage.java:504)
* org.apache.tapestry.AbstractPage.renderPage(AbstractPage.java:334)
* org.apache.tapestry.engine.RequestCycle.renderPage(RequestCycle.java:411)
* org.apache.tapestry.engine.AbstractEngine.renderResponse(AbstractEngine.java:775)
* org.apache.tapestry.engine.DirectService.service(DirectService.java:208)
* org.apache.tapestry.engine.AbstractEngine.service(AbstractEngine.java:913)
* org.apache.tapestry.ApplicationServlet.doService(ApplicationServlet.java:238)
* org.apache.tapestry.ApplicationServlet.doGet(ApplicationServlet.java:199)
* javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
* javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
* org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
* org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
* org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:257)
* org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
* org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
* org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
* org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
* org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
* org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
* org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:184)
* org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
* org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
* org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
* org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
* org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
* org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
* org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
* org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
* org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:206)
* org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:833)
* org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:732)
* org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:619)
* org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:688)
* java.lang.Thread.run(Thread.java:536)


--- SOURCE ---
public IProduct[] findByCategory(String catid, String locale)
throws DataAccessException {

List prodlist = this.getHibernateTemplate().find("from productdetails");
IProduct[] products = (IProduct[])prodlist.toArray();

return products;
}

Uhm, isn't an object name missing, here? If you write "from productdetails" you are assuming there is an entity "productdetails"... shouldn't it rather be something like the following (nb: since you don't include mappings I have to guess the name of your entities!)

"from Category cat join cat.productdetails where cat.id=?"

with catId bound to the id you are receiving as a param?

Uhm, maybe the following is better...

"select detail from Category cat join cat.productdetails detail where cat.id=?"

Please ignore the parameters passed to the method,
I wanted to list all product details

Ok, I got it working,
I put the entity and table name, after that it works.

This code works.

List prodlist = this.getHibernateTemplate().find("from ProductdetailBean productdetails");

Thanks for your input.

hi,
Iam getting the same exception according to your example I changed my code to the follows:

return (List)getHibernateTemplate().find("from Product products");

where "Product" is the bean and "products" is the name of the table

the exceptions iam geting are:

java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'double from PRODUCTS product0_' at line 1
at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.PreparedStatement.executeQuery(Unknown Source)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1512)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at org.springframework.orm.hibernate.HibernateTemplate$17.doInHibernate(HibernateTemplate.java:331)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:150)
at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:170)
at org.springframework.orm.hibernate.HibernateTemplate.find(HibernateTemplate.java:328)
at estalea.springapp.persistence.access.ProductManagerDaoHibernate.getProductList(ProductManagerDaoHibernate.java:67)
at estalea.springapp.persistence.ProductManager.getProducts(ProductManager.java:33)
at estalea.springapp.SpringappController.handleRequest(SpringappController.java:42)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:389)
at org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:342)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:318)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.caucho.server.http.FilterChainServlet.doFilter(FilterChainServlet.java:96)
at com.caucho.server.http.Invocation.service(Invocation.java:315)
at com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:246)
at com.caucho.server.http.HttpRequest.handleConnection(HttpRequest.java:164)
at com.caucho.server.TcpConnection.run(TcpConnection.java:139)
at java.lang.Thread.run(Unknown Source)
11868 [tcpConnection-8080-0] INFO support.SQLStateSQLExceptionTranslator - Translating SQLException with SQLState '42000' and errorCode '1064' and message [Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'double from PRODUCTS product0_' at line 1]; SQL was [null] for task [HibernateAccessor]
11868 [tcpConnection-8080-0] ERROR servlet.DispatcherServlet - Could not complete request
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [null] in task 'HibernateAccessor'; nested exception is java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'double from PRODUCTS product0_' at line 1
java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'double from PRODUCTS product0_' at line 1
at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.PreparedStatement.executeQuery(Unknown Source)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1512)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at org.springframework.orm.hibernate.HibernateTemplate$17.doInHibernate(HibernateTemplate.java:331)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:150)
at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:170)
at org.springframework.orm.hibernate.HibernateTemplate.find(HibernateTemplate.java:328)
at estalea.springapp.persistence.access.ProductManagerDaoHibernate.getProductList(ProductManagerDaoHibernate.java:67)
at estalea.springapp.persistence.ProductManager.getProducts(ProductManager.java:33)
at estalea.springapp.SpringappController.handleRequest(SpringappController.java:42)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:389)
at org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:342)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:318)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.caucho.server.http.FilterChainServlet.doFilter(FilterChainServlet.java:96)
at com.caucho.server.http.Invocation.service(Invocation.java:315)
at com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:246)
at com.caucho.server.http.HttpRequest.handleConnection(HttpRequest.java:164)
at com.caucho.server.TcpConnection.run(TcpConnection.java:139)
at java.lang.Thread.run(Unknown Source)