[hibernate-dev] Stored procedure improvement

[Steve Ebersole]

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.