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

[Sanne Grinovero]

+1 to "clean this up" as it's a major release.

Ideally, would it be possible to already introduce some warning logs
in some later 5.x ?

Thanks,
Sanne


On 27 July 2017 at 15:11, Steve Ebersole <steve at hibernate.org> wrote:
> 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
>>>>
>>>
>>>
>>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev