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 <st...@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.v...@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@lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
