Ok

Does anyone have any ideas with this one? Im getting a QueryException: Not All Parameters Have Been Set Error When I try to call a stored procedure. This despite the fact that the paramters are actually set in the application. Does anyone have any idea why.

3.00:

Mapping documents:
<hibernate-mapping default-access="property" default-lazy="false" package="com.pctfiler.fee.roles">

<class name="Role" lazy="true">
<id name="roleId" column="roleId" type="string" />
<property name="accountId" type="integer" />

<bag name="roleDetails">
<key column="feeTypeId" update="false" />
<one-to-many class="ParentFeeTypeId" />
<loader query-ref="Role" />
</bag>

</class>

<class name="ParentFeeTypeId">
<id name="feeTypeId" type="integer" />
</class>

<sql-query name="Role">
<return alias="role" class="Role">
<return-property name="roleId" column="RoleID" />
<return-property name="accountId" column="SFAccountID" />
<return-property name="feeTypeId" column="FeeTypeID" />
</return>
<load-collection alias="fs" role="Role.roleDetails" />
{ CALL usp_get_fees( :ProductID, :CountryId ) }
</sql-query>
</hibernate-mapping>


Code
Session session = HibernateSessionFactory.currentSession();
Query query = session.getNamedQuery("Role");
try {
session.beginTransaction();
query.setReadOnly(true);

query.setParameter("ProductID", product.getId());
query.setParameter("CountryId", area.getId());

List l = query.list();
return l;
[b]

[b]Full stack trace of any exception that occurs:
Caused by: org.hibernate.QueryException: Not all named parameters have been set: [ProductID] [{ CALL usp_get_fees( :ProductID, :CountryId ) }]
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:284)
at org.hibernate.impl.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:214)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
at org.hibernate.persister.collection.NamedQueryCollectionInitializer.initialize(NamedQueryCollectionInitializer.java:54)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:520)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1695)
at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:454)
at org.hibernate.engine.StatefulPersistenceContext.initializeNonLazyCollections(StatefulPersistenceContext.java:828)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:229)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1674)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:147)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at com.pctfiler.fee.roles.RoleFactory.getList(RoleFactory.java:85)
... 16 more


MySQL 5.0

{ CALL usp_get_fees( :ProductID, :CountryId ) }

Any help at all is appreciated

Thanks

Chris

Have a look at the ref docs, section 16.3.2, "Using stored procedures for querying":

Not true anymore. I think we need to update that doc page.

This isn't an "anymore" case though, is it? He's using version 3.0(.x?). And if it is, what's the problem? Does the stored proc need to return something, so a "? =" prefix is required?

"? = " is only required on stupid oracle.

The point I were trying to make was that <return-join> and <load-collection> should work fine with stored procedures. Tricky to do since you are now responsible for providing a correct and complete sql query, but it works

Thanks for the reply guys.

The squery syntax works for me in hundreds of other queries throughout my application without any trouble. The SQL syntax is correct as it works correctly when run directly against the database. The problem seems to be occuring somewhere whitin hibernate.

Any more ideas?

Are you able to debug step into Hibernate code? If you can then step into AbsQueryImpl.verifyParameters. Inside the first if clause (the one that throws the QueryException that you're seeing), see what namedParameters contains.

You coud also check what's happening inside setParameter("ProductID", value).