Hibernate, Stored Procedures and Invalid Parameter Index Error

I have a SQL Server Stored Procedure that looks like this:

CREATE PROCEDURE [dbo].[my_stored_procedure] 
    (
        @num INT,
        @name VARCHAR(50),
        @start_date DATETIME,
        @end_date DATETIME
    )
AS
BEGIN
...
END

And an Entity Object with a NamedNativeQuery that looks like this:

  • SQL Query selecting users personal rating, or if that doesn't exist the average rating
  • SqlParameter with Nullable value give error while ExecuteNonQuery?
  • Find overlapping price validity dates in SQL
  • SQL Inline or Scalar Function?
  • Combining fields from two rows into a single row
  • Divide a main table into multiple tables using SQL server
  • @Entity
    @NamedNativeQuery(
        name = "myObject.myStoredProcedure",
        query = "call my_stored_procedure(:num, :name, :start_date, :end_date)",
        callable = true,
        readOnly=true,
        resultSetMapping="implicit"
    )
    @SqlResultSetMapping(
        name="implicit",
        entities=@EntityResult(entityClass=org.mycompany.object.MyObject.class)
    )
    public class MyObject implements Serializable {
    ...
    

    But when I try to call it in my DAO like so:

    List<MyObject> objects = (List<MyObject>) getHibernateTemplate().execute(new HibernateCallback() {
                @Override
                public Object doInHibernate(Session session) throws HibernateException {
                    return session.getNamedQuery("myObject.myStoredProcedure")
                    .setInteger("num", num)
                    .setString("name", name)
                    .setDate("start_date", startDate)
                    .setDate("end_date", endDate)
                    .list();
                }
            });
    

    But I get this error:

    12 May 2010 10:55:43,040 100833 [http-8080-Processor23] ERROR org.hibernate.util.JDBCExceptionReporter  - Invalid parameter index 4.
    12 May 2010 10:55:43,042 100835 [http-8080-Processor23] FATAL org.mycompany.web.controller.BasePagingController  - org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
    org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
    

    It seems like it’s expecting another parameter, like a return parameter, but I tried adding a ‘?’ to the call and all the Hibernate documentation suggests against this.

    Any help would be appreciated. Thanks

  • Incorrect syntax near the keyword 'table' and could not extract ResultSet
  • Same Query Gives Different Output In MSSQL And Hibernate 3
  • Run sql script from jpa-hibernate ddl auto
  • How do I map a BigDecimal in Hibernate so I get back the same scale I put in?
  • How can I get the database name I am connected to through Hibernate?
  • N Hibernate Session.Delete Foreign key
  • One Solution collect form web for “Hibernate, Stored Procedures and Invalid Parameter Index Error”

    The Hibernate Documentation on calling stored procedures states:

    The recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>) }. Native call syntax is not supported.

    So the 4th line in your second snippet should probably be

    query = "{ ? = call my_stored_procedure(:num, :name, :start_date, :end_date) }",

    I don’t know, what your procedure returns, but you might want to check the following as well.
    Even more Hibernate Documentation:

    For Sybase or MS SQL server the
    following rules apply:

    • The procedure must return a result
      set. Note that since these servers can
      return multiple result sets and update
      counts, Hibernate will iterate the
      results and take the first result that
      is a result set as its return value.
      Everything else will be discarded.
    • If you can enable SET NOCOUNT ON in your
      procedure it will probably be more
      efficient, but this is not a
      requirement.
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.