[hibernate-dev] NativeQuery and parameters

[Steve Ebersole]

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