[hibernate-dev] NativeQuery and parameters

[Steve Ebersole]

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