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 (nike.apache.org: local policy)
Message-Id: <BF16473D-FAF9-4E8E-9605-D037925BDF26@sixfriedrice.com>
From: Six Fried Rice <technet@sixfriedrice.com>
To: Derby Discussion <derby-user@db.apache.org>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v919.2)
Subject: Paging Result Sets
Date: Mon, 28 Apr 2008 07:52:52 -0700

I think this is a FAQ (indeed, it is in the FAQ with a rather terse  
answer) but I'm looking for a little more guidance.

I need to be able to "page" the results of queries in Derby. Since  
Derby does not have an equivalent of MySQL's LIMIT, MSSql's TOP, or  
Oracle's ROW_NUM, there doesn't seem to be a way to do this on the SQL  
side. The recommendation is to use JDBC. I found a few mailing list  
responses that suggest using JDBC conventions but don't go in to much  
detail.

I'm assuming the solution is something along these lines:

1: Calculate the starting and ending offset desired.
2: call setMaxRows() on the statement to fetch up to the *ending*  
offset.
3: spin through the result set until the *starting* offset is reached.
4: process records until the end of the result set.

Is this the right approach? Are there any best practices to ensure  
that step 3 is as fast as possible?

Thanks!

Geoff