[hibernate-dev] NativeQuery and parameters

[Gail Badner]

+1 to default of 1.

On Fri, Sep 23, 2016 at 8:50 AM, Steve Ebersole <steve at hibernate.org> wrote:

> I did end up adding support for controlling this via a setting[1].  Now,
> when recognizing ordinal parameters (as distinct from positional
> parameters, i.e. ?1) we define the "base" as:
>
>    if ( factory.getSessionFactoryOptions().isJpaBootstrap() ) {
>       ordinalParameterBase = 1;
>    }
>    else {
>       final Integer configuredBase =
> factory.getSessionFactoryOptions().getNonJpaNativeQueryOrdinalPar
> ameterBase();
>       ordinalParameterBase = configuredBase == null
>             ? 0
>             : configuredBase;
>    }
>    ordinalParameterImplicitPosition = ordinalParameterBase;
>
>
> That
> `factory.getSessionFactoryOptions().getNonJpaNativeQueryOrdinalPar
> ameterBase()`
> call is driven by this setting.
>
> However back to the question of whether we want to go ahead and unify this
> base across the board (regardless of bootstrap method) to be 1 *by
> default*.  The difference is who needs to provide this setting... is it
> legacy apps upgrading to 6.0 that happen to use ordinal parameters?  or, is
> it apps that want the unified base?
>
> Essentially should it be:
>
> ordinalParameterBase = configuredBase == null ? 0 : configuredBase;
>
> or:
>
> ordinalParameterBase = configuredBase == null ? 1 : configuredBase;
>
> Personally I think it should be the latter.  It is pretty normal for legacy
> app upgrading to have to set some "use legacy behavior" flags.
>
> What do y'all think?
>
>
> [1] https://gist.github.com/sebersole/b13c11a9f7d55b84be6b4371e0259bc5
>
>
>
> On Wed, Sep 21, 2016 at 2:03 PM Steve Ebersole <steve at hibernate.org>
> wrote:
>
> > One additional thing we might consider is possibly unifying this 0- and
> 1-
> > based mismatch wrt JDBC-style parameters.
> >
> > In Hibernate's APIs these JDBC-style parameters were 0-based.  I have
> > already dropped support (it has been deprecated for a long time anyway)
> for
> > using JDBC-style params in HQL.  So this is now only a concern for native
> > queries.
> >
> > Interestingly JPA has the following to say (3.10.13 Positional
> Parameters):
> > <quote>
> > Only positional parameter binding ... may be portably used for native
> > queries... When binding the values of positional parameters, the
> numbering
> > starts as “1”. It is assumed that for native queries the parameters
> > themselves use the SQL syntax (i.e., “?”, rather than “?1”).
> > </quote>
> >
> > So for portable JPA, the native query would use JDBC/SQL syntax for the
> > parameter marker, but use the JPA convention for parameter index
> (1-based)
> > rather than the JDBC/SQL convention (0-based).
> >
> > As bassackwards as we may think JPA is here, it is a spec and we have to
> > support it: we have to support 1-based binding of these JDBC-style
> params.
> > The question is whether we should deviate from Hibernate's legacy
> decision
> > to align with JDBC/SQL (0-based) for binding of these JDBC-style params.
> > Perhaps a setting that controls how this should work - specifically
> limited
> > to JDBC-style param markers in native queries.  By default we'd honor the
> > 1-based approach whenever bootstrapping was done via JPA and 0-based when
> > native bootstrapping is used.  I guess what I am wondering is whether we
> > want to have a setting for the native bootstrapping piece that controls
> > whether to use 0-based or 1-based?  I do not think we should allow the
> > setting (if we add one) to force 0-based usage with JPA bootstrapping;
> this
> > would only control how it works in native bootstrapping.
> >
> > Thoughts?
> >
> > On Wed, Sep 21, 2016 at 1:40 PM Steve Ebersole <steve at hibernate.org>
> > wrote:
> >
> >> I never said anything about dropping support for named and positional
> >> parameters in native queries.
> >>
> >> I simply mentioned leveraging the new "JPA strict compliance" stuff I am
> >> adding to 6.0.  The idea is to allow you to enable that and get feedback
> >> when you use non-portable things.
> >>
> >>
> >> On Tue, Sep 20, 2016 at 11:45 PM Jordan Gigov <coladict at gmail.com>
> wrote:
> >>
> >>> Actually JPA defines it as "Only positional parameter binding and
> >>> positional access to result items may be portably used for native
> queries".
> >>> I believe "portably" means the providers are only required to support
> >>> positional, but not forbidden from supporting other.
> >>>
> >>> 2016-09-21 3:59 GMT+03:00 Steve Ebersole <steve at hibernate.org>:
> >>>
> >>>> In the interest of questioning everything, just to make sure we are
> all
> >>>> on
> >>>> the same page, Hibernate's support for native SQL queries currently
> >>>> recognizes named parameters, positional parameters as well as
> JDBC-style
> >>>> parameters.
> >>>>
> >>>> JPA only defines support for "JDBC-style parameters" as valid for
> native
> >>>> SQL queries:
> >>>> {quote}
> >>>> It is assumed that for native queries the parameters themselves use
> the
> >>>> SQL
> >>>> syntax (i.e., “?”, rather than “?1”).
> >>>> {quote}
> >>>>
> >>>> Furthermore Hibernate does not support a native query using both
> >>>> positional
> >>>> parameters and JDBC-style parameters in the same query because it
> >>>> causes a
> >>>> non-determinism wrt the positions.
> >>>>
> >>>> I assume we want to continue to support that full complement of
> >>>> parameter
> >>>> types, with the positional/JDBC-style caveat.
> >>>>
> >>>> Further I assume we will hook up the use of any non-JDBC-style
> >>>> parameters
> >>>> in with the "strict JPA compliance" checking and throw an error when
> >>>> indicated.
> >>>>
> >>>> Anyone have objections to any of that?
> >>>>
> >>> _______________________________________________
> >>>> 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
>