aspose file tools*
The moose likes Object Relational Mapping and the fly likes Calling Stored Procedure from Hibernate mapping file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Calling Stored Procedure from Hibernate mapping file" Watch "Calling Stored Procedure from Hibernate mapping file" New topic
Author

Calling Stored Procedure from Hibernate mapping file

Mat Anthony
Ranch Hand

Joined: May 21, 2008
Posts: 236
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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Mat Anthony
Ranch Hand

Joined: May 21, 2008
Posts: 236
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:-




Mat
Mat Anthony
Ranch Hand

Joined: May 21, 2008
Posts: 236
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




Mat
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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
Greenhorn

Joined: Sep 09, 2009
Posts: 25
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Calling Stored Procedure from Hibernate mapping file