db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fantry, John" <JFan...@analogic.com>
Subject RE: Results portion
Date Wed, 09 Aug 2006 17:14:38 GMT

Using a scrollable cursor is a possibility.  However, you incur a
performance penalty, which in my experience, is quite severe if your
result set is very large.  Let's say you need to move the cursor to the
last row in the result set.   Derby will iterate through all the rows in
the result set from the current position of the cursor until the last
row is reached.  In my application I felt that the amount of time the
user was forced to wait while this took place was unacceptable.

What Sergey, and several others would like to be able to do, is use a
LIMIT command with an OFFSET modifier which will allow you to generate a
result set that only has rows 10 - 20 in it.  The result set does not
contain rows 1 - 9, nor does it contain any rows after 20.  This is what
Sergey is asking for.  This behavior is possible in databases like MySQL
and Postgres, but of course those are not Java based embedded
implementations.

So, Sergey, if you execute your query using a scrollable cursor you can
move the cursor to your desired offset and then read the desired number
or rows (in this case you'll probably want to call setMaxRows() on your
Statement object so you don't retrieve any additional rows that you
don't need).  You may find this to be perfectly acceptable for your
needs.  This approach didn't work well for me due to the rather large
size of my result set.

-----Original Message-----
From: Michael Segel [mailto:msegel@segel.com]
Sent: Wednesday, August 09, 2006 1:00 PM
To: 'Derby Discussion'
Subject: RE: Results portion

Hmmm...

I realize its been a while since I've read Derby's manual, but doesn't
Derby
support Scrollable cursors?

A scrollable cursor will allow you to run a query and to fetch specific
rows
from the result set. At a minimum, that is what Sergey is originally
asking
and which is supported by multiple RDBMSs.


> -----Original Message-----
> From: Fantry, John [mailto:JFantry@analogic.com]
> Sent: Wednesday, August 09, 2006 10:00 AM
> To: Derby Discussion
> Subject: RE: Results portion
>
>
> This question has bean asked several times already . . . Once by me
;-)
> The answer is NO.  There is no way to retrieve rows in this fashion.
I
> had to create an extra "index" column in my table in order to simulate
> this kind of behavior.  Big pain in the . . .
>
>
> -----Original Message-----
> From: Sergey Zolotaryov [mailto:anydoby@gmail.com]
>
> Sent: Wednesday, August 09, 2006 7:43 AM
> To: derby-user@db.apache.org
> Subject: Results portion
>
> Is there a way to retrieve a portion of results using Derby? For
> example, I want to get rows from 10 to 20 from a resultset. All dbs I
> know of, have this feature, smth like "LIMIT n, m" , or "ROWS n TO m".
> Also is there a way to retrieve the total number of rows in last
> select?
> Thank you.
>
> ****************************************************************
> 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.



****************************************************************
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.

Mime
View raw message