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

[Steve Ebersole]

I agree about the 5.x warnings.

On Fri, Jul 28, 2017, 3:15 AM Sanne Grinovero <sanne at hibernate.org> wrote:

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