I always think the best solution is to ask the right question so there are
<= 7 pieces of info in the answer....;-)
A couple months ago there was an enormous discussion of this in the
firebird development list -- we ended up impelementing limit and skip. The
solution you propose has a problem if you are ordering on more than one
column (your example in fact is wrong, order by and where disagree). With
more than one column in order by, you have to write a horrendous > clause
nested to the depth = number of columns in order by.
Of course, limit and skip allow you to lose records and see records twice
if the underlying table changes between calls.
david jencks
On 2001.09.24 15:00:43 -0400 Ignacio Coloma wrote:
> > Dave Smith wrote:
> > > Keep the result set open, retieve from the set as necessary, probably
> > > using a cursor. That way if someone is doing a search that has
> > 1 million
> > > records and after I display the first 10 and the client chooses the
> 3rd
> > > one, we have only created 10 objects.
> > No, no, no! We can't keep the result set open!
> > 1) having open result set and open Connection for each client,
> > we'll end up with too many open connections and too many open result
> > sets (256 open cursors by default in Oracle).
> > 2) How about transaction bounds? Can you keep cursor open after
> > transaction ends? I don't think so.
>
> What about making an automatic paging? Think about something LIKE an open
> cursor. I'll try to be brief. Suppose this query to return 1,000,000
> records:
>
> SELECT ID, NAME FROM TABLE
> ORDER BY NAME;
>
> Suppose that you make a PagedCollection (configurable from
> jbosscmp-jdbc.xml) that only get the first 50 records (and you close the
> resultset). You show them. When someone tries to get the #51 record and
> so
> you make another call (possibly in another new Transaction context)
>
> SELECT ID, NAME FROM TABLE
> WHERE ID > #the ID of record 50#
> ORDER BY NAME;
>
> And so on. It would be more effective than any solution the programmer
> could
> make by hand. Are there Transaction consecuences on this? I could be
> missing
> something.
>
> My 2c.
>
>
> _______________________________________________
> Jboss-development mailing list
> [EMAIL PROTECTED]
> https://lists.sourceforge.net/lists/listinfo/jboss-development
>
>
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
