Re: [eclipselink-users] Named Native Query is not producing LIMIT based

[Tim Hollosy <hollosyt@xxxxxxxxx>]

Thanks,
I still filed a bug for this, because it doesn't make sense that there
are two different ways that the same platform does paging. It is
easier to code around if the same behavior always happens when using
setfirst/max.

https://bugs.eclipse.org/bugs/show_bug.cgi?id=284729

./tch



On Tue, Jul 21, 2009 at 3:00 PM, christopher
delahunt<christopher.delahunt@xxxxxxxxxx> wrote:
> That is not entirely true.  For maxRows, a native query or non-paginated
> query will use statement.setMaxRows().  So depending on how the driver
> implements this, it should not be sending the entire result set.
>  FirstResult is used to jump to the first row on the returned resultset
> though.
>
> Best Regards,
> Chris
>
> Tim Hollosy wrote:
>>
>> Thanks Tom,
>> That answers my question -- so the entire result is still downloaded
>> if you have a native named query. Interesting information :) Should I
>> file a bug to request this functionality be added? I think it should
>> be the desired behavior.
>>
>> ./tch
>>
>>
>>
>> On Tue, Jul 21, 2009 at 2:35 PM, Tom Ware<tom.ware@xxxxxxxxxx> wrote:
>>
>>>
>>> Hi Tim,
>>>
>>>  Is the romnum part of the query missing for a standard Named Query or
>>> just
>>> for  a Named Native Query?
>>>
>>>  I ran a quick test and I seem to get the proper rownum SQL for a dynamic
>>> query and a normal named query.
>>>
>>>  If the issue you are seeing is for a Named Native Query, at the moment
>>> we
>>> do not edit the SQL you provide to add the rownum syntax.  The way we
>>> deal
>>> with firstResult and maxRows in native queries is to get the full result
>>> set
>>> from the query and to use indices to return the requested results.
>>>
>>>  I cannot think of a way of getting the number of results from a select
>>> query that is better than just adding a count of some field to the result
>>> set.
>>>
>>> -Tom
>>>
>>> Tim Hollosy wrote:
>>>
>>>>
>>>> I'm hoping someone can help explain the native paging feature in
>>>> Eclipse Link. I am using the oracle platform, when I call
>>>> setFirstResult setMaxResults on a JPA Query the SQL that gets produced
>>>> and logged by EclipseLink contains the normal limit stuff with rownum.
>>>>
>>>> However if I do the same thing, but produce my query by calling
>>>> createNamedQuery paging still works but the SQL that gets logged (and
>>>> maybe not even executed) isn't the LIMIT stuff, it just looks like the
>>>> regular ol' NativeNamedQuery. This is causing some issues for us since
>>>> we're trying to do some string processing on the SQL in order to
>>>> dynamically create a count query (minus the subselect limit/rownum).
>>>>
>>>> So I have two questions:
>>>>
>>>> 1) How does this even work, since I don't see the LIMIT based query
>>>> being created, yet paging seems to still work. Yet with a JPA query
>>>> you can clearly see the LIMIT SQL being created/executed.
>>>>
>>>> 2) Is there an easier way to get the count from a query without having
>>>> to dynamically (or statically) creating the SQL?
>>>>
>>>> Thanks!
>>>>
>>>> ./tch
>>>> _______________________________________________
>>>> eclipselink-users mailing list
>>>> eclipselink-users@xxxxxxxxxxx
>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>>
>>>
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@xxxxxxxxxxx
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>>>
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>