Edisandro, It isn't support in any database specific way. Some databases let you call a stored proc like a prepared statement if it doesn't have any out parameters. If this works with your database, you could use JPA's native query.
So you are telling that JPA has a feature called "native query" where I can submit stored procedure calls ? (Depending of database support of course)
What benefit would JPA provide here?
Well, actually we have many legacy systems which heavily use stored procedures thru prepared statements as you said, so we started to wonder whether we could still continue using stored procedures for further applications but getting the benefits of JPA's connection pool manager and transactions, which are transparently managed by API, instead of hard controlling transactions by ourselves as we have done so far.
Thank you very much. Edisandro
author & internet detective
Hi James, I followed the link you suggested and found this: "JPA does not support stored procedures that use OUTPUT or INOUT parameters.". I'm using MySQL, does it mean I cannot retrieve the out parameter from a procedure? I'm using EclipseLink annotations.
If there is a way, could you please post the code snippet?
theBalance is the OUT parameter, which I think I should not say "q.setParameter("param4", theBalance); " Still, how to retrieve the OUT parameter after doing the q.executeUpdate(); ?
Thanks a lot!
Joined: Feb 01, 2010
Got it now, I had a little mistake. Here is my code:
CODE CALLING IT:
My discovery was that I had to keep the returnsResultSet=false to false in the annotation. I still don't know what a true would mean, but that was my initial try... Anyways, it works now with the OUT parameter and through JPA annotations.
Joined: Oct 01, 2007
Yes, you need to set returnsResultSet=false if you want the query to return the output parameter values. If you set it to true, then it is assumed that the procedure will return a ResultSet, and the result set value will be returned (an outputParametersDetected event would be raised for any output parameters).
Joined: Feb 01, 2010
Hello James, I came up with another doubt
I have a stored procedure call getBalance. This balance, given an accountNumber IN parameter, should return with and OUT parameter the balance of that account holder. However, I would like to know how to handle exceptions correctly from my stored procedure. I thought registering an additional OUT parameter with something like a errorMessage name would be fine. Here is my code:
How do you recommend me handling SQL errors (like querying for an inextistent row)? and use JPA appropriately? I need to know from my JPAController class if something went wrong inside the procedure, and have an error message of what went wrong. Can you share some code snippet of MySQL and JPAController class?
James Sutherland wrote:Yes, you need to set returnsResultSet=false if you want the query to return the output parameter values. If you set it to true, then it is assumed that the procedure will return a ResultSet, and the result set value will be returned (an outputParametersDetected event would be raised for any output parameters).
Hi, Iam using Open JPA 1.2 where there is not @NamedStoredProc, Can some one help me how to call Stored proc in JPA 1.2 with IN & OUT Parameters.
Sorry buddy. I've been looking for over 3 days and it would appear open jpa doesn't let you call stored procedures with in and out parameters. Best bet is to crack open the stored proc and try and get the sql into a native named query.