Add support for calling SQL functions and retrieving the function result
Description
relates to
Activity
Steve Ebersole October 8, 2021 at 7:09 PM
I believe this is already done
Steve Ebersole July 27, 2017 at 3:45 PM
The ProcedureCall
support for this is already mostly in place. To call a function named fn_count_comments
you'd do something like:
The only part still not completely determined is exactly how to access the return value upon execution. Do we access that through the existing `#getOutputParameterValue` approach? That is the approach that will work "seamlessly" with JPA usage + hint. So that would look like:
In 6.0, ProcedureCall (Hibernate) extends StoredProcedureQuery (JPA) so we will for sure have consistent support both via an explicit call as well as a hint. In terms of a series of pure JPA calls, however, this comes off a little bit awkward:
Your PostgreSQL comment is actually a 3rd concern, encapsulated in org.hibernate.procedure.spi.CallableStatementSupport#shouldUseFunctionSyntax. Essentially PostgreSQL supports REF_CURSOR but in a very very very specific way..
Really this is something we need to consider in a few distinct, albeit related, parts...
Which JDBC "call form" do we use? `{call it(...)}` or `{?=call it(...)}`
How are the return v. parameters registered?
How are the out values accessed between return v. parameters?
There's been a discussion on the Hibernate mailing list regarding this topic, and I'll simply copy Steve's concusions: