Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
From: Michael Segel <derby@segel.com>
Reply-To: derby@segel.com
Organization: MSCC 
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Paginating result sets
Date: Wed, 19 Jul 2006 13:53:32 -0500
User-Agent: KMail/1.8.2
References: <49B82763C87DB944922AF1F052796D4970330D@SCORPIUS.analogic.com>
In-Reply-To: <49B82763C87DB944922AF1F052796D4970330D@SCORPIUS.analogic.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200607191353.32879.derby@segel.com>

Did you try to use a scrollable cursor?
Or how about storing your result set in to a temp table with some sort of row 
id?

Both of these solutions are pretty much database independent, assuming of 
course that they support the concept of cursors...

If you're going to use either solution, you're also going to need to consider 
what happens when you time out the user and you need to free up resources.

HTH

-G

On Wednesday 19 July 2006 1:24 pm, Fantry, John wrote:
> I need some help figuring how to paginate result sets with Derby.  I
> have scanned the list archives and have found one similar thread that
> never received a complete answer from May 2005.
>
> From what I understand this pagination behavior is possible in MySQL by
> using the 'LIMIT' query.  This will allow me to select the first 100
> rows, the second 100 rows, or any arbitrary chunk of rows that may be
> part of the result set.
>
> I understand that Derby does not support the LIMIT query.  I also
> understand that the JDBC Statement class has two methods that are
> related (setFetchSize() and setMaxRows()), but they do not support the
> pagination behavior I desire.
>
> So let's say that a given SELECT statement will return 1000 rows.  How
> do I create a SELECT statement that will only return me rows 600 - 699?
> Having spent a lot of time searching through Derby docs I have concluded
> that this is not possible in Derby, but I am hoping I am wrong.
>
> Does anyone know how to do this, or do I have to continue my search for
> an embedded database that supports LIMIT?
>
> Thanks,
> -John
>
>
> ****************************************************************
> The information transmitted in this message is confidential and may be
> privileged.  Any review, retransmission, dissemination, or other use of
> this information by persons or entities other than the intended recipient
> is prohibited.  If you are not the intended recipient, please notify
> Analogic Corporation immediately - by replying to this message or by
> sending an email to DeliveryErrors@analogic.com - and destroy all copies of
> this information, including any attachments, without reading or disclosing
> them.
>
> Thank you.

-- 
--
Michael Segel
Principal 
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]