[hibernate-dev] NativeQuery and parameters

[Steve Ebersole]

Well therein lies (yet another) minor rub....

On Thu, Sep 22, 2016 at 7:06 AM Steve Ebersole <steve at hibernate.org> wrote:

> Well therein lies a minor rub in having split SQM out from ORM.
> Specifically if the "compliance violation" occurs in the HQL/JPQL/Criteria
> query the exception is a type from SQM (it has no deps on ORM).  Here
> though it is not reasonable to throw an SQM exception type.
>
>
>
>
> On Wed, Sep 21, 2016 at 2:28 PM Jordan Gigov <coladict at gmail.com> wrote:
>
>> That sounds reasonable. So long as there is a property to control it from
>> persistence.xml, more people will be happy with it.
>>
>> It would be very important to quote whatever error people will get in the
>> docs, so they can find the solution easier.
>>
>> 2016-09-21 22:03 GMT+03:00 Steve Ebersole <steve at hibernate.org>:
>>
>>> 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
>>>>>
>>>>>
>>