• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to execute stored procedure?

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear friends,
I'm having stored procedure call fetchStudDetails(?, ?, ?, ?,?); First 2 are input parameters and other 3 are output parameters. Anyone please tell me how to execute this stored procedure through hibernate (in Java)?

I have seen some of the threads here to call the stored procedure in hibernate but I couldn't get an idea to call the stored procedure with in and out parameter.

Could anyone please help me to configure this stored procedure in hibernate with in and out parameter and to execute this in Java?



Thanks in advance,
Sathish kumar
 
Saloon Keeper
Posts: 27763
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I could give you my standard diatribe about the perils of over-reliance on stored procedures, but that's beside the point.

There's a decent amount of coverage in Hibernate's documentation on this, and I'd recommend checking it. But if I haven't got my ORMs crossed, what it all boils down to is that you have to obtain the Connection object from the Hibernate session object and do it the hard way using the Connection.
 
author
Posts: 304
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Or... you could JPA and make use of vendor extensions that are easy to use and consistent with the programming model. (How's that for a sales pitch )
 
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

Mike Keith wrote:Or... you could JPA and make use of vendor extensions that are easy to use and consistent with the programming model. (How's that for a sales pitch )



Are the caveats of;

One and only one out parameter and it must be the first parameter and it must be a RefCursor?

Thanks

Mark
 
Mike Keith
author
Posts: 304
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Mark Spritzler wrote:

Mike Keith wrote:Or... you could JPA and make use of vendor extensions that are easy to use and consistent with the programming model. (How's that for a sales pitch )



Are the caveats of;

One and only one out parameter and it must be the first parameter and it must be a RefCursor?

Thanks

Mark



Sorry, I don't understand your comment. You should be able to have as many OUT parameters as you want. If you want an output cursor then you can only have one of those, but any number of output params, and at any place in the stored proc.
 
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

Mike Keith wrote:

Mark Spritzler wrote:

Mike Keith wrote:Or... you could JPA and make use of vendor extensions that are easy to use and consistent with the programming model. (How's that for a sales pitch )



Are the caveats of;

One and only one out parameter and it must be the first parameter and it must be a RefCursor?

Thanks

Mark



Sorry, I don't understand your comment. You should be able to have as many OUT parameters as you want. If you want an output cursor then you can only have one of those, but any number of output params, and at any place in the stored proc.



Well to be vendor specific, this is a caveat in Hibernate in calling Stored Procedures. And as far as I know today before Hibernate 3.5 (haven't used it yet) the caveats still exists.

Thanks

Mark
 
Mike Keith
author
Posts: 304
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Well to be vendor specific, this is a caveat in Hibernate in calling Stored Procedures. And as far as I know today before Hibernate 3.5 (haven't used it yet) the caveats still exists.



Ah. You didn't click on the vendor extensions link. I was linking to the EclipseLink stored procedure extensions. They are are much more powerful and flexible, and much nicer to use.
 
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
No, I didn't even see the link. Kind of read things a little too quickly most of the time.

I see those annotations. Any chance they could be in the next JPA spec?

Mark
 
Mike Keith
author
Posts: 304
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Stored procs are certainly on my wishlist for the next version. I obviously can't make any promises as to what ends up in the spec, though. The final product is the result of a maze of expert group twists and turns, inputs and requirements, preferences and abhorrences...
 
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

Mike Keith wrote:Stored procs are certainly on my wishlist for the next version. I obviously can't make any promises as to what ends up in the spec, though. The final product is the result of a maze of expert group twists and turns, inputs and requirements, preferences and abhorrences...



Yeah, I know, read a lot of those emails.

Even got slammed, in email, by one of those "experts", not based on what I emailed them, but because they thought I had no real-world experience, because at that time I worked for JBoss. Even though I had worked at real businesses for over 15 years before JBoss.

Anyway, lets just say that that expert is not high on my list and after that I just ignored that person.

Mark
 
Mike Keith
author
Posts: 304
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Since you don't seem to be ignoring me I'm assuming that it wasn't me ;-)
 
Ranch Hand
Posts: 1491
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Anyone please tell me how to execute this stored procedure through hibernate (in Java)?


Thread question is about Hibernate, not about JPA related issues.
 
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

kri shan wrote:

Anyone please tell me how to execute this stored procedure through hibernate (in Java)?


Thread question is about Hibernate, not about JPA related issues.



Yep, can't be done in Hibernate.

Read my post about the caveats of calling Stored Procedures in Hibernate

1) Only one out parameter allowed
2) It must be the first parameter
3) It must be a reference cursor.

You can always get the JDBC Connection and call via JDBC Api ways. Although check your vendors JDBC api to make sure it implements the JDBC spec fully. Some don't

Mark
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,
I need to call a stored proc in Oracle that accepts an array as input parameter.

Please let me know how should i call it from my JPA. Is this even possible without using JDBC directly?

i keep getting the ff error:

wrong number or types of arguments in call to ....

my code is something like this:

...
String[] myArr...

Query query = em.createNativeQuery("BEGIN myStoredProc(:arr); END;");
query.setParameter("arr", myArr);
...

Thanks in advance.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic