[hibernate-dev] Stored procedure improvement

[Steve Ebersole]

A function can also define (IN/)OUT arguments depending on database, so I'd
rather not get into validating that.  I have no idea whether any databases
support REFCURSOR arguments for a function.

I think the registerFunctionReturnType suggestion works on this assumptions
that (1) we are only returning the function RETURN (aka, not allowing OUT
args) and (2) that the function only returns "simple" types.  I think that
the normal parameter registration could work for the function return.  The
only thing I worry about is whether it is better to handle the param
registration representing the return separately (see below).

So I'd suggest the following...

For the Hibernate native ProcedureCall I would add a method to indicate
whether the call is a procedure or a function.  Just a simple boolean,
something like:

ProcedureCall#setTreatAsFunction(boolean isFunction)

The the question becomes how to deal with the function-return.  To me it
makes the most sense to reuse the notion of a ParameterRegistration,
although I can see 2 specific options:

   1. hold a separate "ProcedureCallImpl#functionReturnParameterRegistration"
   field with appropriate ProcedureCall methods exposed
   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


Another, more drastic, option on the native side is a
specialized ProcedureCall type for functions: `FunctionCall
extends ProcedureCall`.  But that effectively requires a new method (set
of?) on Session to create the FunctionCall.

And, unless I am mistaken, drivers support calling functions differently in
terms of the "call string" we pass it so we'd also have to have a
Dialect/CallableStatementSupport hook here.  This is the place where the
difference in how we handle the ParameterRegistration for the function
return is important.

On a completely separate tangent... I wonder if we maybe want to wander
into trying to
leverage java.sql.DatabaseMetaData#getFunctions/java.sql.DatabaseMetaData#getProcedures
to just automatically handle the difference (as opposed to the need for the
user to explicitly hand us a boolean)?

On Mon, Feb 15, 2016 at 8:58 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
wrote:

> Hi Steve,
>
> I'm glad we have plans to improve this, so let's use this conversation to
> gather as much info as we need to open a JIRA issue for this task.
>
> For the Hibernate-specific ProcedureCall, should add a method like this:
>
> ProcedureCall#registerFunctionReturnType(Class type)
>
> This way we can define the result type and also know that we should expect
> a function and not a stored procedure.
> We can add a validation so that we disallow registering an OUT/REF_CURSOR
> and a function-return-type for the same ProcedureCall instance.
>
> For @javax.persistence.NamedStoredProcedureQuery and StoredProcedureQuery,
> we could add the following hint:
>
> org.hibernate.registerFunctionReturnType
>
> The logic should be just like for the
> ProcedureCall#registerFunctionReturnType(Class type).
>
> Does it sound reasonable?
>
> Vlad
>
> On Mon, Feb 15, 2016 at 4:34 PM, Steve Ebersole <steve at hibernate.org>
> wrote:
>
>> So to be clear...
>>
>> I absolutely think we should add support for this.  The question really
>> is how to expose this, both in the native API
>> (org.hibernate.procedure.ProcedureCall) and the JPA API
>> (javax.persistence.StoredProcedureQuery), as well as
>> @javax.persistence.NamedStoredProcedureQuery.  As far as the JPA contracts,
>> obviously this requires a hint since we cannot change them (of course we
>> could offer an extension to build a StoredProcedureQuery that models a
>> function rather than a procedure.
>>
>> Notice too that there is another concern though: namely defining the spec
>> for the output parameter.
>>
>> On Mon, Feb 15, 2016 at 8:27 AM Steve Ebersole <steve at hibernate.org>
>> wrote:
>>
>>> Well as my todo comment says:
>>>
>>> // todo : how to identify calls which should be in the form `{? = call
>>> procName...}` ??? (note leading param marker)
>>>
>>> //        more than likely this will need to be a method on the native API.  I can see this as a trigger to
>>> //    both: (1) add the `? = ` part and also (2) register a REFCURSOR parameter for DBs (Oracle, PGSQL) that
>>> //    need it.
>>>
>>> :)
>>>
>>>
>>> On Mon, Feb 15, 2016 at 7:54 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> While writing the stored procedure section, I found a way to improve the
>>>> current implementation to FUNCTIONS as well.
>>>>
>>>> Considering the following function:
>>>>
>>>> CREATE FUNCTION fn_post_comments(postId integer)
>>>> RETURNS integer
>>>> DETERMINISTIC
>>>> READS SQL DATA
>>>> BEGIN
>>>>     DECLARE commentCount integer;
>>>>     SELECT COUNT(*) INTO commentCount
>>>>     FROM post_comment
>>>>     WHERE post_comment.post_id = postId;
>>>>     RETURN commentCount;
>>>> END
>>>>
>>>> We could call this function and fetch the result ith plain-old JDBC:
>>>>
>>>> session.doWork(connection -> {
>>>>     try (CallableStatement function = connection.prepareCall("{ ? =
>>>> call fn_count_comments(?) }")) {
>>>>         function.registerOutParameter(1, Types.INTEGER);
>>>>         function.setInt(2, 1);
>>>>         function.execute();
>>>>         int commentCount = function.getInt(1);
>>>>         assertEquals(2, commentCount);
>>>>     }
>>>> });
>>>>
>>>> When using the JPA 2.1 API:
>>>>
>>>> StoredProcedureQuery query =
>>>> entityManager.createStoredProcedureQuery("fn_count_comments");
>>>> query.registerStoredProcedureParameter("postId", Long.class,
>>>> ParameterMode.IN);
>>>>
>>>> query.setParameter("postId", 1L);
>>>>
>>>> Long commentCount = (Long) query.getSingleResult();
>>>>
>>>> We get a "PROCEDURE fn_count_comments does not exist" exception because
>>>> the
>>>> SQL statement is built as "{call fn_count_comments(?)}" instead of "{ ?
>>>> =
>>>> call fn_count_comments(?) }".
>>>> I think we could define a hint like this:
>>>>
>>>> query.setHint(QueryHints.HINT_CALL_FUNCTION, true);
>>>>
>>>> So we could adjust the callable statement to work like a function.
>>>>
>>>> What do you think of this?
>>>>
>>>> Vlad
>>>> _______________________________________________
>>>> hibernate-dev mailing list
>>>> hibernate-dev at lists.jboss.org
>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>>
>>>
>