[hibernate-dev] Stored procedure improvement

[Vlad Mihalcea]

+1. I added a JIRA issue for this:

https://hibernate.atlassian.net/browse/HHH-10530

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

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