One thing to keep in ming with OFBiz, and most webapps, is that you won't hold a cursor open for long. Depending on how the db handles things using a short-life cursor versus offset/limit may not make too much of a difference, on the other hand it may... Anyway, I added it in Moqui yesterday, but adding it in OFBiz would be a bit more work. If anyone goes for it chances are it would make it into the project if contributed though.
UNION is interesting, but that's one feature of SQL that I haven't found the need to use much. I suppose it depends on your data model though... dunno, maybe sometimes it's quite useful.
> Thanks David.
>
> The problem is that we have some huge tables. 9 million+ records. And the
> limit and offset are perfect for dealing with these large tables. Postgres
> can grab a 1000 records starting at record 5,000,000 very efficiently. This
> allows us to operate on large datasets without holding a cursor open. Also
> for UI's that page through data it is perfect. For the UI side you can
> close/reopen cursor as normally people are not paging through 10's of
> thousands of records.
>
> I noticed that someone has been working on UNION statements inside the
> entity engine. That would be a nice addition.
>
> -Mark
>
> On Sun, Feb 27, 2011 at 5:12 PM, David E Jones <
[hidden email]> wrote:
>
>>
>> That's somewhat true, ie OFBiz mostly uses generic SQL, but there are
>> variations supported for different databases configured on the datasource
>> element in the entityengine.xml file. Most of the variations are for the
>> meta-data operations (maintain tables, columns, indexes, fks), but they are
>> not limited to that.
>>
>> If someone wanted to implement such a thing it could certainly be done.
>> This is right up there on the would be nice list with the "FOR UPDATE"
>> support. I've already added FOR UPDATE support to Moqui (or rather included
>> it in the original design), and this is a good point that I'll consider over
>> there instead of relying on a cursor to do the offset/limit.
>>
>> -David
>>
>>
>> On Feb 27, 2011, at 1:14 PM, Scott Gray wrote:
>>
>>> Hi Mark
>>>
>>> You can impose a limit by using EntityFindOptions.setMaxRows(...) but
>> there isn't any option to set an offset because the syntax (and in some
>> cases even support) varies across different databases. OFBiz doesn't
>> support differing SQL dialects per database so at the moment we're limited
>> to commands that have universal support.
>>>
>>> The only real work around is to use an EntityListIterator and then page
>> forward to the desired offset.
>>>
>>> Regards
>>> Scott
>>>
>>> HotWax Media
>>>
http://www.hotwaxmedia.com>>>
>>> On 28/02/2011, at 8:27 AM, Mark Gordon wrote:
>>>
>>>> Hello,
>>>>
>>>> I have a small application that is using a version of the entity engine
>> from
>>>> 2007. We want to be able to use limit and offset functionality of our
>>>> database (postgres). I have scanned the current trunk release and
>> release
>>>> 10.04 to see if there is any offset and limit. I have seen some
>> references
>>>> but I can figure out how to use it and even if it is what I am looking
>> for.
>>>>
>>>> Is this functionality in the latest trunk? Can anyone point to how to
>> use
>>>> it if it is indeed available.
>>>>
>>>> Thanks!
>>>>
>>>> Mark
>>>
>>
>>
Locked
smime.p7s (3K)