Calling Stored Procedure from Hibernate mapping file

[Mat Anthony]

Hi,
I'm trying to call the following stored procedure from a hibernate mapping file.
Not sure if this is the correct way to call a stored procedure from Hiberanate.
All I want back is the String result back from the stored procedure.

<sql-query name="personDetails" callable="true">
{call PERSON_DETAILS(?,:thePersonId)}
</sql-query>


public String findPersonDetails(Long perId)
{
return this.getHibernateTemplate().findByNamedQueryAndNamedParam("personDetails", "thePersonId", perId);
}

Mat

[Mark Spritzler]

In Hibernate to call a stored procedure, you can only return Reference cursors. And it must be the first out parameter.

Since you are using Spring, I recommend you look at the StoredProcedure class Spring has and use JDBCTemplate to run that code. I hate to say it this way, but because of some other vendor (Not Hibernate) not 100% implementing the JDBC spec caused all this trouble with Stored Procedures in Hibernate.

Anyway, good luck

Mark

[Mat Anthony]

Hi Mark,
thanks for your response. I got the following error:-

Caused by: org.hibernate.MappingException: Named query not known: select PERSON_DETAILS(ID) from PERSON person where person.ID = 12345
at org.hibernate.impl.AbstractSessionImpl.getNamedQuery(AbstractSessionImpl.java:70)

I have implemented the following code:-

 public String findPersonDetails(Long perId)
 { 
	  String result;
	  final String queryString="select PERSON_DETAILS(ID) from PERSON person where person.ID = "+perId.longValue();
	  try
	  {
		  result = executeStoredProcedure(queryString);
	  }
	  catch(RuntimeException re)
	  {
		 log.error("failed in findPersonDetails using perId"+perId.intValue(),re); 
	     throw re;
	  }
      return result;
 }

 public String executeStoredProcedure(final String queryString) throws RuntimeException 
  {
  	String result = null;
  	try
  	{
        result = (String)getHibernateTemplate().executeWithNativeSession(
                          new HibernateCallback() 
                          {
                                public Object doInHibernate(Session session) throws HibernateException, SQLException 
                                {
                                  return (new Integer(session.getNamedQuery(queryString).executeUpdate()));
                                }
                          });
    }
  	catch(Exception ex)
  	{
      ex.printStackTrace();
      throw new RuntimeException("unable to execute stored procedure query:" + queryString,ex);
    }
    return result;
  } 

Mat

[Mat Anthony]

Hi,
can any body give me a help with this problem, I'm new to calling stored procedures via hibernate .

I have updated the original code to include the file: person.hbm.xml
The sql does not seem to get exceuted, hence result=SQLQueryImpl{ call PERSON_DETAILS(?, :perId) })

Not sure how to solve this one

public String findPersonDetails(String perId)   
 {    
      String result;   
      try  
      {   
          result = executeStoredProcedure(perId);   
      }   
      catch(RuntimeException re)   
      {   
         log.error("failed in findPersonDetails using perId"+perId,re);    
         throw re;   
      }   
      return result;   
 }   

 public String executeStoredProcedure(final String perId) throws RuntimeException    
  {   
    String result = null;   
    try  
    {   
        result = (String)getHibernateTemplate().executeWithNativeSession(   
                          new HibernateCallback()    
                          {   
                                public Object doInHibernate(Session session) throws HibernateException, SQLException    
                                {   
                                  return session.getNamedQuery("getPersonDetails").setParameter("perId",perId).toString();  
                                }   
                          });   
    }   
    catch(Exception ex)   
    {   
      ex.printStackTrace();   
      throw new RuntimeException("unable to execute stored procedure perId:" + perId,ex);   
    }   
    return result;   
  }   
  

file:Person.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

    <sql-query name="getPersonDetails" callable="true">   
        { call PERSON_DETAILS(?, :perId) }
    </sql-query>

</hibernate-mapping>

Mat

[Mark Spritzler]

Will, your stored procedure is not returning a reference cursor. Which is still a requirement with how you used the template.

I suggested using the StoredProcedure class that Spring has. Can you try that route?

See the third or fourth reply in this thread here

http://forum.springsource.org/showthread.php?t=19472

Good Luck

Mark

[Justin Chi]

There are rules and limitations for using stored procedures with Hibernate as Mark said.
Suppose you are using oracle , a function must return a result set, the first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle and you need to define a REF CURSOR type.
While these rules vary with different databases, if your functions or procedures do not satisfy these rules no matter what kind of changes have been done to your java codes or hbm.xml ,it won't work.
May be you could call stored procedure through JDBC connection(new Configuration().configure("hibernate.cfg.xml").buildSettings().getConnectionProvider().getConnection();) instead of Hibernate Session.