We're updating the issue view to help you get more done. 

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.

Environment

None

Status

Assignee

Steve Ebersole

Reporter

Vlad Mihalcea

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

5.1.0

Priority

Major