Simple question:

I am using MySQL 5.0.18 and want to call a stored procedure. The stored procedure does all the work and does not return anything back. I have added the following to my hbm.xml:

<sql-query name="calComm">
{? = proc_calculate_commission() }
</sql-query>

Now how do I call it?

I thought the following would work, but it did not:
Session session = getSession();
session.getNamedQuery("calComm").excuteUpdate()

So how do I execute the stored procedure?

Stored procedures must return a result set to work in hibernate. You can add a "select 0" at the end of it, and ignore the result. You also need to specify "callable", add "call", and define return types:

This is a gist of my mapping file:
<hibernate-mapping . . .>
<class . . .>

</class>
<sql-query name="calComm" callable="true">
<return-scalar type="integer"/>
{ ? = call func_calculate_commission() }
</sql-query>
</hibernate-mapping>


I changed from a stored procedure to a stored function in MySQL. The problem that I get is that the hbm.xml file does not get loaded properly any more. What could be the problem?

just run your stored procedure via a normal stored procedure.

If it does not return anything why would you need to call it throuhg hibernate ?

We want to seperate this functionality from our application as other applications in different programming languages might also use this.

Now this is my problem now:

I have to do the following:
<sql-query name="calComm">
<return-scalar type="integer" column=""/>
select func_calculate_commission()
</sql-query>

When I put <sql-query name="calComm" callable="true"> then my mapping does not get loaded properly. The problem that I now have is that Hibernate says there is no column "", which is true however what would I put as the return from the function is 1 (ie. RETURN 1; END |)

You might be wondering why I changed the statement { ? = call func_calculate_commission() } to select func_calculate_commission() and the reason is that the other statement was not being executed, this one does get executed however I get a error after execution for the column "" problem.

What do I need to do now?

You're now doing a select, not calling an stp. So you don't want callable="true" any more.

The original query would have worked, but you unconditionally need a ? = call stp..., and the stp must return a result set. The select func() is probably the simpler solution.

Well I got it! I had to do the following and it worked like a charm:
<sql-query name="calComm">
<return-scalar type="integer" column="myret"/>
select 1 as myret, func_calculate_commission()
</sql-query>

Thanks for all your help you guys!