+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 <st...@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 <st...@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.v...@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 <st...@hibernate.org>
>>> wrote:
>>>
>>>> On Mon, Feb 15, 2016 at 10:16 AM Vlad Mihalcea <mihalcea.v...@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.
>>>>
>>>>
>>>
_______________________________________________
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
