On Mon, Feb 15, 2016 at 10:16 AM Vlad Mihalcea <mihalcea.vlad(a)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.