[hibernate-dev] Stored procedure improvement

[Steve Ebersole]

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