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

+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(a)hibernate.org&gt; 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(a)gmail.com&gt; > 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-procedu... >> >> 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(a)gmail.com&gt; 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(a)gmail.com&gt; >>> 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(a)hibernate.org&gt; >>>> 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(a)lists.jboss.org >>>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> > >>>> _______________________________________________ >>>> hibernate-dev mailing list >>>> hibernate-dev(a)lists.jboss.org >>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> >>> >>> >> > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev