[hibernate-dev] Stored procedure improvement

[Vlad Mihalcea]

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