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

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 >

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

+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