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:
@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 ge开发者_开发知识库t 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
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.
精彩评论