Re: Fetching rows with a specific range

Hi Ramsey,

Really, with Sybase you're out of luck? I don't have personal  
experience with Sybase but I know it's been a while, ran on black NeXT  
hardware, and is a good database. There must be a way.

Here's the article I used to morph what WOnder gave us in a SQLHelper  
to something that was deterministic for Oracle:

http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html

I didn't spend too many brain cycles but it appears Sybasers use a  
temp table to get a pagination query:

http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12

Perhaps there are other ways... temp table is a little evil...

Cheers,
-- Aaron


On Dec 22, 2012, at 12:39 PM, Ramsey Gurley wrote:

>
> On Dec 22, 2012, at 10:18 AM, Aaron Rosenzweig wrote:
>
>> Hi WOrriors,
>>
>> The "SQLHelpers" allow you to randomly pull a handful of records out
>> of a result set. It is up to you to look at them and be sure they are
>> deterministic. I remember the Oracle one that came with wonder was  
>> not
>> deterministic but I was not allowed to give back the fix to the
>> community when I worked at clinworX.
>>
>> It's not so hard. Spend a few days learning about the nuances of your
>> Database and either fix the Wonder SQL helper or make a new one….
>
>
> It depends. If you're stuck on Sybase you're boned. Millions of rows  
> and no way to use a batching display group… nice. Real nice.
>
>
>> or
>> at least confirm. I did confirm the Frontbase one is spot on correct.
>>
>> WOnder has sql helpers for most databases.
>>
>> See "ERXEOControlUtilities.objectsInRange()" methods. They internally
>> use the sql helpers to do their magic.
>>
>> Also see the "ERXBatchingDisplayGroup" as it, too, uses the sql  
>> helpers.
>>
>> Of course, for simple "first 10" results just use an ordered  
>> fetchspec
>> with a fetch limit. But for random grabs, the sql helper does it.
>>
>> Cheers,
>> -- Aaron
>>
>> On Dec 20, 2012, at 3:21 PM, Kieran Kelleher wrote:
>>
>>> Hi Ryan,
>>>
>>> To get FetchSpec fetchLimit to generate SQL that limits the number
>>> of rows returned from the database you need to have three conditions
>>> (IIRC):
>>>
>>> (1) You must use ERXFetchSpecification subclass
>>> (2) Your database PlugIn must support it (MySQL does, PostgreSQL
>>> does, not sure about the others)
>>> (3) promptsAfterFetchLimit should be false, which is its default
>>> anyway
>>>
>>> Also, ERXFetchSpecification supported a fetchRange which is a
>>> similar feature that generates the correct LIMIT statement in MySQL
>>> for example - I don't know if any other db Plugins support  
>>> fetchRange.
>>>
>>> HTH,
>>>
>>> Kieran
>>>
>>> On Dec 19, 2012, at 9:16 AM, Maik Musall <[email protected]>  
>>> wrote:
>>>
>>>> Hi Ryan,
>>>>
>>>> have you tried
>>>>
>>>> EOFetchSpecification fspec = ...
>>>> fspec.setFetchLimit( 10 );
>>>>
>>>> using an EOSortOrdering? That should let the database pre-sort the
>>>> result and stop after 10 rows returned.
>>>>
>>>> Maik
>>>>
>>>>
>>>>
>>>> Am 19.12.2012 um 14:38 schrieb Y. Dong
>>>> <[email protected]>:
>>>>
>>>>> Hi there,
>>>>>
>>>>> I would do something like SELECT * FROM  ( SELECT * FROM Articles
>>>>> ORDER BY createdTime) WHERE ROWNUM<=10;
>>>>>
>>>>> Semantically it means please give me the oldest 10 articles by
>>>>> createdTime. Any idea how to express this in terms of webobjects.
>>>>> ERXFetchSpecification alone doesn't seem adequate.
>>>>>
>>>>> Thanks
>>>>>
>>>>> Ryan
>>>>> ------------------------------------------------------------------------------
>>>>> LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free  
>>>>> Trial
>>>>> Remotely access PCs and mobile devices and provide instant support
>>>>> Improve your efficiency, and focus on delivering more value-add
>>>>> services
>>>>> Discover what IT Professionals Know. Rescue delivers
>>>>> http://p.sf.net/sfu/logmein_12329d2d
>>>>> _______________________________________________
>>>>> Wonder-disc mailing list
>>>>> [email protected]
>>>>> https://lists.sourceforge.net/lists/listinfo/wonder-disc
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
>>>> Remotely access PCs and mobile devices and provide instant support
>>>> Improve your efficiency, and focus on delivering more value-add
>>>> services
>>>> Discover what IT Professionals Know. Rescue delivers
>>>> http://p.sf.net/sfu/logmein_12329d2d
>>>> _______________________________________________
>>>> Wonder-disc mailing list
>>>> [email protected]
>>>> https://lists.sourceforge.net/lists/listinfo/wonder-disc
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
>>> Remotely access PCs and mobile devices and provide instant support
>>> Improve your efficiency, and focus on delivering more value-add
>>> services
>>> Discover what IT Professionals Know. Rescue delivers
>>> http://p.sf.net/sfu/logmein_12329d2d
>>> _______________________________________________
>>> Wonder-disc mailing list
>>> [email protected]
>>> https://lists.sourceforge.net/lists/listinfo/wonder-disc
>>
>>
>> ------------------------------------------------------------------------------
>> LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
>> Remotely access PCs and mobile devices and provide instant support
>> Improve your efficiency, and focus on delivering more value-add  
>> services
>> Discover what IT Professionals Know. Rescue delivers
>> http://p.sf.net/sfu/logmein_12329d2d
>> _______________________________________________
>> Wonder-disc mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/wonder-disc
>


------------------------------------------------------------------------------
LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
Remotely access PCs and mobile devices and provide instant support
Improve your efficiency, and focus on delivering more value-add services
Discover what IT Professionals Know. Rescue delivers
http://p.sf.net/sfu/logmein_12329d2d