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(a)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(a)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(a)lists.jboss.org
>
https://lists.jboss.org/mailman/listinfo/hibernate-dev
>