Hibernate, Stored Procedures and Invalid Parameter Index Error

Question

Hi,

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:

@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

Answer 1

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.