[hibernate-dev] Stored procedure improvement

[Vlad Mihalcea]

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