[hibernate-dev] Stored procedure improvement

[Vlad Mihalcea]

True. In this case, probably we should consider adding a FunctionCall class
and isolate all these peculiarities from the regular stored procedures.


On Mon, Feb 15, 2016 at 10: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.
>>>
>>>
>>