[hibernate-dev] 6.0 - procedure/function calls via NativeQuery

[Steve Ebersole]

Vlad, we have had this discussion like 5 times now ;)
http://www.mail-archive.com/hibernate-dev@lists.jboss.org/msg13885.html ->
https://hibernate.atlassian.net/browse/HHH-10530

The ProcedureCall support for this is already mostly in place.  Using your
post example e.g., you'd do something like:

ProcedureCall call = session.createStoredProcedureCall( "fn_count_comments"
);
// an inherent "parameter #0" registration
call.markAsFunction( Types.INTEGER );
call.registerParameter( 1, Integer.class, IN );

The only part still not completely determined is exactly how to access the
return value upon execution.  Do we access that through the existing
`#getOutputParameterValue`
approach?  That is the approach that will work "seamlessly" with JPA
usage + hint.  So that would look like:

call.execute();
final Integer commentCount = call.getOutputParameterValue( 0 );


In 6.0, ProcedureCall (Hibernate) extends StoredProcedureQuery (JPA) so we
will for sure have consistent support both via an explicit call as well as
a hint.  In terms of a series of pure JPA calls, however, this comes off a
little bit awkward:

StoredProcedureQuery spq = em...;
// again, an inherent "parameter #0" registration
spq.setHint( "hibernate.procedure.function_return_jdbc_type_code",
Types.INTEGER );
call.registerStoredProcedureParameter( 1, Integer.class, IN );


Your PostgreSQL comment is actually a 3rd concern, encapsulated
in org.hibernate.procedure.spi.CallableStatementSupport#shouldUseFunctionSyntax.
Essentially PostgreSQL supports REF_CURSOR but in a very very very specific
way..


As I tried to point out in that linked discussion as well as the other
times time has come up - we really need to consider this in a few pieces...

   1. Which JDBC "call form" do we use?  `{call it(...)}` or `{?=call
   it(...)}`
   2. How are the return v. parameters registered?
   3. How are the out values accessed between return v. parameters?


Any continued discussion on this topic related to ProcedureCall
or StoredProcedureQuery really ought to happen on the Jira issue.


On Thu, Jul 27, 2017 at 3:54 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
wrote:

> If we make this change, we need to make sure the StoredProcedureQuery
> works properly for functions too:
>
>
> https://vladmihalcea.com/2016/04/27/how-to-call-sql-server-stored-procedures-and-functions-from-hibernate/
>
> Currently, it only supports stored procedures and not database functions.
> However, users can also use session.doWork instead so there is a
> workaround anyway.
>
> Vlad
>
> On Thu, Jul 27, 2017 at 11:37 AM, Arnold Gálovics <
> galovicsarnold at gmail.com> wrote:
>
>> Hey,
>>
>> @Vlad: if they eventually have to adjust their code, why is it so hard to
>> use the proper API for calling stored procedures?
>>
>> I'm supporting Steve's idea to remove this complexity and force the users
>> to use the proper API. I think for 6.0 this change can be acceptable and
>> should be mentioned in the migration guide.
>>
>> Best Regards,
>> Arnold
>>
>>
>>
>> On Thu, Jul 27, 2017 at 7:25 AM, Vlad Mihalcea <mihalcea.vlad at gmail.com>
>> wrote:
>>
>>> I run a quick Google search for "Hibernate NnativeQuery stored procedure"
>>> and found these links:
>>>
>>> http://www.baeldung.com/stored-procedures-with-hibernate-tutorial
>>>
>>>
>>> https://www.mkyong.com/hibernate/how-to-call-store-procedure-in-hibernate/
>>>
>>> I guess people used to do this. We could use some QueryHint which needs
>>> to
>>> be supplied when users want to execute a SP via NativeQuery.
>>> I think it's less painful to have this option instead of disallowing it
>>> completely.
>>>
>>> Vlad
>>>
>>> On Thu, Jul 27, 2017 at 1:08 AM, Steve Ebersole <steve at hibernate.org>
>>> wrote:
>>>
>>> > Another unnecessary complexity I'd like discuss removing is the
>>> ability to
>>> > execute procedure/function calls via NativeQuery.  The complexity is a
>>> > bunch of String parsing and token interpretation we need to do in
>>> order to
>>> > discovery this intention.  Given that both JPA and Hibernate define
>>> > specific APIs for executing procedure/function calls this seems like an
>>> > unnecessary complexity and overhead.
>>> >
>>> > Objections?  Thoughts?
>>> > _______________________________________________
>>> > hibernate-dev mailing list
>>> > hibernate-dev at lists.jboss.org
>>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>> >
>>> _______________________________________________
>>> hibernate-dev mailing list
>>> hibernate-dev at lists.jboss.org
>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>
>>
>>
>