• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Devaka Cooray
  • Ron McLeod
  • paul wheaton
Saloon Keepers:
  • Tim Moores
  • Piet Souris
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Frits Walraven
  • Scott Selikoff

Calling Stored Procedure from Hibernate mapping file

 
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 265
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
There will be plenty of time to discuss your objections when and if you return. The cargo is this tiny ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic