Tags: Exception , Hibernate , sql

Scenario: 
We were calling MSSQL Stored Procedure using hibernate. My colleague called me to check out error and even I was clueless. It was working fine at database level. And while checking out calling code it seems alright but it thrown an error as follow.

org.hibernate.exception.SQLGrammarException: could not execute query
  at Incorrect syntax near '@P1'..()
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  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.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
  at could not execute query.(Native Method)
  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:2223)
  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.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
  ... 53 more
Caused by: java.sql.SQLException: Incorrect syntax near '@P1'.
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  ... 50 more

Solution:
My colleague forgot to put comma , after each parameter. Follow below queries one with error and other without.

EXEC MY_STORED_PROC :PARAM1 :PARAM2 /* Query that throwing error because of missing comma. */
EXEC MY_STORED_PROC :PARAM1, :PARAM2 /* Query that works fine. */

Tags: Exception , Hibernate , sql