Calling stored procedure from hibernate

[lrjainjain brotherjain]

I am trying to call a stored procedure from hibernate but I keep getting errors. Here's what I am doing..

My table
table emp
(
ssn varchar(9),
firstname varchar(10)
)

my Employee class is

public class Employee
{
public String ssn ;
public String firstname ;
...getter and setter methods.
}

My test procedure in SQL SERVER is

create procedure test_proc_hibernate
as
BEGIN
select ssn,firstname from emp;
END

My hibernate mapping file the following entry

<sql-query name="testingProc" callable="true">
<return alias="empexample" class="Employee">
<return-property name="ssn" column="ssn"/>
<return-property name="firstname" column="firstname"/>
</return>
{ ? = call test_proc_hibernate() }
</sql-query>


My DAO is making the following call

session = openSession() ;
List mylist = session.getNamedQuery("testingProc").list();


CASE A)
I get the following error
org.hibernate.HibernateException: Errors in named queries: testingProc

-----------------------
CASE B)
If I change it to

<sql-query name="testingProc" callable="true">
{ ? = call test_proc_hibernate() }
</sql-query>

I get
org.hibernate.exception.SQLGrammarException: could not execute query

---------------------
CASE C)
Only this one works. But it returns a list of object type java.lang.Object.

<sql-query name="testingProc" callable="true">
{ call test_proc_hibernate() }
</sql-query>


What am I doing wrong, How can I seperate the ssn, firstname from the returned list in case (C)

Please HELP !!

[Mark Spritzler]

There are rules to calling Stored Procedures. They are

1. Only one return value allowed
2. It must be the first out value.
3. The out value must be a reference cursor.

This last one is the difference between what you are seeing in version C. Since you do not have an out parameter Hibernate will have no idea what type of object to create.

Mark

[a ramazany]

you should define also a class tag in your hbm.xml file like this :
<hibernate-mapping>
<class name="aip.law.orm.hntest.SpHntest">
<id name="id" type="java.lang.Integer">
<column name="ID" />
</id>
<property name="caption" type="java.lang.String">
<column name="Caption" length="60" not-null="true" />
</property>
<loader query-ref="testingProc" />
</class>
<sql-query name="testingProc" callable="true" >
<return alias="SpHntest" class="aip.law.orm.hntest.SpHntest">
<return-property name="id" column="ID"/>
<return-property name="caption" column="Caption"/>
</return>
{call sphntest()}

</sql-query>
</hibernate-mapping>

[Ingoba Ningthoujam]

Hi brotherjain ,

Write a POJO as well as the corresponding mapping file having fields which are returning from the store procedure.

[John Grath]

On previous projects I have created a Spring managed JDBC service to execute stored procedures and run more complicated bespoke SQL. I find that this approach works best on larger projects.