Add support for calling SQL functions and retrieving the function result

Description

There's been a discussion on the Hibernate mailing list regarding this topic, and I'll simply copy Steve's concusions:

I think too that we need to keep the native/JPA split in mind. We are much more limited in how we might support this in JPA due to not being able to change those contracts. So in JPA that means either hints or an extension contract.

Let's model the native API first since there we have the most flexibility. Again, it really comes down to whether it makes sense to model the distinction between "call return" and "call arguments" (arguments might also retrieve values back). Technically the existing registerParameter/ParameterRegistration infrastructure could handle modeling the idea of a "call return" assuming that:

The parameters are always registered by position, not name

The first parameter is the "call return"

We are given some indication (hint, etc) that we need to be dealing with the `{?=call(...)}` syntax

Or we could instead model the "call return" as separate from "call arguments", whether directly on ProcedureCall or on a separate contract FunctionCall. And in fact if we go the route of modeling this "call return" separately, we can have a single-point trigger for the type of executable call to make:

ProcedureCall call = session.createStoredProcedureCall( ... );

call.registerCallReturn( Integer.class );

call.registerParameter( ... );

In fact if we end up going this route, I'd suggest deprecating `#registerParameter` in favor of `#registerCallArgument`. Anyway, above the call to `#registerCallReturn` tells us completely everything we need to make a `{?=call(...)}` call instead of the `{call(...)}` form.

For JPA the only options really are a hint or an extension. With the hint approach, we pretty much have to follow the 3-point assumptions I set above in terms of the JPA object.

Activity

Show:

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...

  1. Which JDBC "call form" do we use? `{call it(...)}` or `{?=call it(...)}`

  2. How are the return v. parameters registered?

  3. How are the out values accessed between return v. parameters?

Out of Date

Assignee

Reporter

Priority

Created February 16, 2016 at 6:31 AM
Updated October 8, 2021 at 7:09 PM
Resolved October 8, 2021 at 7:09 PM
Loading...