Tags: Exception , Hibernate

I'm creating sample code for one of my article of hibernate and database. I got exception while calling Stored Procedure from java in hibernate.

So I googled and after trying different option found the solution and thought let me post it in proper way so you guys don't need to waste your time to check other options.

EXCEPTION

Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.
 at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
 at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)
 at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2334)
 at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:643)
 at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:506)
 at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:979)
 at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
 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:2211)
 ... 7 more

CODE WITH ERROR

Query callStoredProcedure = session.createSQLQuery("EXEC SP_MSSQL_HIBERNATE (:param1, :param2, :param3)").addEntity(User.class);
callStoredProcedure.setInteger("param1", 10);
callStoredProcedure.setInteger("param2", 10);
callStoredProcedure.setString("param3", "javaQuery");
      
List userList = callStoredProcedure.list();

SOLUTION

• If you are calling MSSQL stored procedure then SQL query must be without Parentheses ( and ).
  e.g EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3
• If you are calling MYSQL stored procedure then SQL query must be with Parentheses ( and ).
  e.g CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)

Tags: Exception , Hibernate