[hibernate-dev] Stored procedure improvement

[Steve Ebersole]

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:

   1. The parameters are always registered by position, not name
   2. The first parameter is the "call return"
   3. 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.


On Mon, Feb 15, 2016 at 2:21 PM Steve Ebersole <steve at hibernate.org> wrote:

> It is undefined in JDBC spec - not that JDBC covers function call in any
> kind of depth to begin with ;)
>
> However, that does not mean that it won't ever be supported.  So its
> really a question of do we complicate future support for that just because
> support for it is not defined at the moment?
>
> Like I think its clear that referring to function/procedure args by a mix
> of name and position is bad form : both at the JPA/Hibernate and JDBC
> levels.  But a function return is special.  And lumping that in with
> argument handling and inheriting the same limitations I am not so sure
> makes sense.
>
>
> On Mon, Feb 15, 2016 at 2:12 PM Vlad Mihalcea <mihalcea.vlad at gmail.com>
> wrote:
>
>> I tried out with MySQL and PostgreSQL and I cannot call a function by
>> name.
>> It works with positional parameters.
>>
>> So, I'm not even sure it's possible to use named parameters with
>> functions that return a value.
>> Have you ever seen such a function call working with named parameters?
>>
>> Vlad
>>
>> On Mon, Feb 15, 2016 at 7:43 PM, Steve Ebersole <steve at hibernate.org>
>> wrote:
>>
>>> On Mon, Feb 15, 2016 at 10:16 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
>>> wrote:
>>>
>>>> The problem with naming the method as
>>>> "ProcedureCall#setTreatAsFunction(boolean isFunction)" is that the term
>>>> "function" is very leaky.
>>>>
>>>
>>> The distinction being made is whether we need to use the JDBC function
>>> escape syntax.  How one database implements that or misnames a procedure a
>>> function is not really pertinent IMO.  The bottom line is that JDBC defines
>>> 2 distinct syntaxes here and we need to know which to use, either:
>>>
>>>    1. {call it(...)}
>>>    2. {?=call it(...)}
>>>
>>> The only important distinction here is that in one case we need to put
>>> one of the parameters at the start.  And I do not find it "leaky" to call
>>> that second form "function syntax".  IMO the pgsql name is just a
>>> misnomer.  Again the important piece of information is the template, the
>>> syntax.  Even though on pgsql it would be a function in their vocab, it is
>>> really the first (proc) syntax.
>>>
>>>
>>> To summarize, we can have the discriminator method for knowing we should
>>>> handle a return-like SQL function:
>>>>
>>>> 1. ProcedureCall#returnResultSet(boolean isReturn)
>>>>
>>>> Now, considering the options that you proposed, I'd go for the 2nd one:
>>>>
>>>> "2. use the existing ProcedureCall param methods and just assume that
>>>> in the case of a function that the first parameter represents the function
>>>> return"
>>>>
>>>
>>>
>>>>
>>>> This is actually very close to JDBC too, so it would be easier for a
>>>> developer to recall the syntax because in JDBC the syntax is:
>>>>
>>>> try (CallableStatement function = connection.prepareCall(
>>>>         "{ ? = call fn_count_comments(?) }" )) {
>>>>     function.registerOutParameter( 1, Types.INTEGER );
>>>>     function.setInt( 2, 1 );
>>>>     function.execute();
>>>>     int result = function.getInt( 1 );
>>>> }
>>>>
>>>> But then, it means that we register the return type and the fact that
>>>> we use the first index like with a stored procedure:
>>>>
>>>> query.registerStoredProcedureParameter(1, Integer.class,
>>>> ParameterMode.OUT);
>>>>
>>>> This way we have only a new methods being added (e.g. returnResultSet)
>>>> and we alter the callable statement syntax based on it.
>>>> When it comes to fetching the result set, we need to do it just like in
>>>> the JDBC example.
>>>>
>>>
>>> Which effectively means you'd never be able to use named parameters with
>>> functions since you cannot mix named and positional parameters.
>>>
>>>
>>