• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to execute stored procedure?

 
SathishCse kumar
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • 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
 
Tim Holloway
Saloon Keeper
Pie
Posts: 18108
52
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • 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.
 
Mike Keith
author
Ranch Hand
Posts: 304
  • Mark post as helpful
  • send pies
  • 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 )
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 304
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 304
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 304
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 304
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Since you don't seem to be ignoring me I'm assuming that it wasn't me ;-)
 
kri shan
Ranch Hand
Posts: 1462
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
 
larben gab
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic