Mailing-List: contact users-help@cocoon.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@cocoon.apache.org
Message-ID: <4041042B.9000306@informatik.tu-darmstadt.de>
Date: Sat, 28 Feb 2004 22:12:11 +0100
From: Christian Haul <haul@informatik.tu-darmstadt.de>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
 rv:1.6) Gecko/20040122 Debian/1.6-1
MIME-Version: 1.0
To: users@cocoon.apache.org
Subject: Re: MORE: [ESQL] esql:more-results seems broke with Oracle
References: <002901c3fd48$0c003a40$585c6351@rosesgro7puzo0>
In-Reply-To: <002901c3fd48$0c003a40$585c6351@rosesgro7puzo0>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Rob Gregory wrote:
> Hi All
> 
> I have been looking into the issue of EQSL paging no longer working with
> Oracle since upgrading Cocoon from 2.0.3 to 2.1.4 and although I can't
> see any direct reason in the code for this problem I have noticed that
> if I use jdbc as the limit clause instead of our default setting of auto
> the paging seems to work again and the <esql:more-results> is output.
> 
> Can anyone (hopefully Chris) expand on this issue and advise on any
> further suggestions on how to fix this as I would prefer not to have to
> visit all our XSP's and change the limit clause setting to be JDBC as we
> plan on supporting more than just Oracle sometime in the future.

I believe the problem is burried in the code that tries to determine the
number of rows in the result set. JDBC2 does not allow to ask the result
set the size. Most database management systems don't know the exact 
result set size before having delivered all rows. One way is to use a 
scrollable result set and try to position the cursor at a position that 
would require another result page ("more-results"). Another is to use 
another query, that just returns the number of resulting rows.

IMHO both solutions suck from a performance POV.

OTOH, paging through results makes most sense with ordered results, so
the DBMS would need to have retrieved all rows anyway, so additional
costs could be transfer over the network if the select is not limited or
locks preventing concurrent queries / additional overhead to maintain
the scrollable resultset.

Retrieving just one row too many is fine but you'll know only at the end
that there's still more data to be displayed -- thus you couldn't put 
the "more" button at the top of the page unless you're using eg XSLT
to move the button up. You probably wouldn't want this for large results :-)

Back to your question: You'll need to look at AbstractEsqlQuery, 
JdbcEsqlQuery, and OracleEsqlQuery at the getQueryString() method.

Pick the one model that suits you best and works with your Oracle.
We'll happily incorporate a patch that allows Oracle to use the
"more-results" feature again :-))

Good luck.

	Chris.

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org