simple stored proc

entr0py · **Joined:** Fri May 19, 2006 10:49 am **Posts:** 5

Hi
I have a (mysql) stored proc called simpleproc

Code:

select count(*) into param1 from entity;

my NamedQueries.hbm.xml is set up as follows:

Code:

<hibernate-mapping>
<sql-query name="sp_simpleproc" callable="true">
<return alias="param1" class="org.matt.test.db.Entity"/>
{call simpleproc(@param1)}
</sql-query>
</hibernate-mapping>

and at the moment I try

Code:

int result = session.getNamedQuery("sp_simpleproc").list().size();

I tried hacking the above named query together because there doesn't appear to be any examples on how a scalar-returning stored proc is used, so I guess this is were the problem is.

Anyway when I run I get this:

ArrayIndexOutOfBoundsException: -1
at com.mysql.jdbc.CallableStatement.setOutParams(CallableStatement.java:1914)
...
at org.hibernate.dialect.MySQLDialect.getResultSet(MySQLDialect.java:316)
...
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)

What's the correct hibernate mapping for a (stored proc) named query like the one above? (I know how the normal recordset ones work).

max · **Posted:** Fri Jun 30, 2006 11:55 am

why are you using <return-entity> when you want to return scalars ? (see <return-scalar>)

entr0py · **Joined:** Fri May 19, 2006 10:49 am **Posts:** 5

I tried but do not know what to put inside return-scalar's column attribute?

max · **Posted:** Fri Jun 30, 2006 12:19 pm

what about the column name from the resultset your stored procedure returns ? (because you did read the docs that states that we only support stored procedures querying that returns resultsets, right ?)

entr0py · **Joined:** Fri May 19, 2006 10:49 am **Posts:** 5

From the docs:
For Oracle the follwing rules apply:
A function must return a result set.

For Sybase or MS SQL server the following rules apply:
The procedure must return a result set.

No mention of mysql. As I stated, my stored proc is really simple, (From OP: select count(*) into param1 from entity;)

max · **Posted:** Fri Jun 30, 2006 1:15 pm

this part is in the general docs: "The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate."

that sentence should actually be "The stored procedure/function must return a resultset directly or as the first out-parameter to be able to work with Hibernate; which one is decided by the Dialect." to be complete

entr0py · **Joined:** Fri May 19, 2006 10:49 am **Posts:** 5

thanks for the clarification