You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Rob Gregory <Mi...@RosesGroup.com> on 2004/02/15 01:17:59 UTC
[ESQL] esql:more-results seems broke with Oracle
Using Cocoon 2.1++ the esql logic sheet seems to have stopped working
with an Oracle database?!?
Code that has been working fine under C2.0.3 no longer generates any
content for the <esql:more-results> although the previous-results tag
still works ok. I have googled for a week now with no luck and checked
all the FAQ's I could find also with out any luck so am asking here for
some advice as to where I can look to resolve this? I did see a post
that mentions some changes for the Postgres database but am guessing
this shouldn't affect Oracle? Anybody shed some light on this?
Thanks in advance for any help
Rob
Environment Details:-
Tomcat 5.0.18 (also fails on 4.1.24)
Cocoon 2.1.3 (also fails on 2.1.4)
Oracle 9
Windows 2000 (also fails on XP)
JDK 1.4.1
JDBC driver ojdbc14.jar
RE: [ESQL] esql:more-results seems broke with Oracle
Posted by Rob Gregory <Mi...@RosesGroup.com>.
Thanks very much Christian ;o)
Was a little concerned nobody seemed to be able to help with this and
grateful one of the Guru's has been able to offer some advice. I have
seen your name mentioned all over the net while trying to resolve this -
especially with regards to ESQL and am glad for some pointers into where
to look next.
I will no doubt be posting again once I have narrowed this down a little
as would be a real shame for me to have to degrade back to Cocoon
version 2.0.3 and while I hope this is only a problem for me, I suspect
that it's more a problem with Oracle.
Again thanks very much for the pointer & I will help as much as I can to
help resolve this for all if it's not just my problem.
Much appreciated
>
> Rob Gregory wrote:
> >
> > Please could anyone confirm that they are using Oracle with ESQL on
> > Cocoon 2.1+ with the <esql:more-results> tag working??
> > Thanks in advance for any input.
>
> There have been some refactorings of the code that determines the
> number of rows in a resultset. These are most likely the cause of your
> troubles :-|
> Please have a look at
> org.apache.cocoon.components.language.markup.xsp.OracleQuery
> and super
> classes. Maybe you can give some advice how to adjust it to your needs
> or point out different cases we need to distinguish.
>
> Chris.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: MORE: [ESQL] esql:more-results seems broke with Oracle
Posted by Christian Haul <ha...@informatik.tu-darmstadt.de>.
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
MORE: [ESQL] esql:more-results seems broke with Oracle
Posted by Rob Gregory <Mi...@RosesGroup.com>.
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.
Is this an Oracle problem or a Cocoon one (as the same code works fine
under 2.0.3) without the change to jdbc.
> > Please could anyone confirm that they are using Oracle with ESQL on
> > Cocoon 2.1+ with the <esql:more-results> tag working??
> > Thanks in advance for any input.
>
> There have been some refactorings of the code that determines the
> number of rows in a resultset. These are most likely the cause of your
> troubles :-|
> Please have a look at
> org.apache.cocoon.components.language.markup.xsp.OracleQuery
> and super
> classes. Maybe you can give some advice how to adjust it to your needs
> or point out different cases we need to distinguish.
>
> Chris.
Futher Details to my initial problem are as follows:-
========================================================================
======================
<esql:more-results> no longer output!
Hi people
I have been using Cocoon since version 2.0.3 and have used the ESQL
logicsheet from within XSP and all was fine until I recently upgraded to
Cocoon version 2.1.3. Since the upgrade the esql queries etc still work
and produce results in the page size I specify but the esql node
<esql:more-results> is no longer output. The paging I specify still
seems to work and 10 rows are skipped if I use the <esql:skip-rows> node
and I know there are lots more results available in the result set. If I
ignore the more-results tag and hard code a link to the next results
this also works but I really need to know when there are more results
available and not.
We are using Cocoon in a production environment so any help to resolve
this matter is gratefully received. I can degrade back to version 2.0.3
but following a bug where resources were being lost when the esql error
block was entered I would really like to keep to the current versions.
Thanks in advance for any help
Environment Details:-
Tomcat 5.0.18 (also fails on 4.1.24)
Cocoon 2.1.3 (also fails on 2.1.4)
Oracle 9
Windows 2000 (also fails on XP)
JDK 1.4.1
JDBC driver ojdbc14.jar
========================================================================
======================
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: [ESQL] esql:more-results seems broke with Oracle
Posted by Christian Haul <ha...@informatik.tu-darmstadt.de>.
Rob Gregory wrote:
>
> Please could anyone confirm that they are using Oracle with ESQL on
> Cocoon 2.1+ with the <esql:more-results> tag working??
> Thanks in advance for any input.
There have been some refactorings of the code that determines the
number of rows in a resultset. These are most likely the cause of your
troubles :-|
Please have a look at
org.apache.cocoon.components.language.markup.xsp.OracleQuery and super
classes. Maybe you can give some advice how to adjust it to your needs
or point out different cases we need to distinguish.
Chris.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
RE: [ESQL] esql:more-results seems broke with Oracle
Posted by Rob Gregory <Mi...@RosesGroup.com>.
Please could anyone confirm that they are using Oracle with ESQL on
Cocoon 2.1+ with the <esql:more-results> tag working??
Thanks in advance for any input.
----------------------------- Previous Post --------------------
Using Cocoon 2.1++ the esql logic sheet seems to have stopped working
with an Oracle database?!?
Code that has been working fine under C2.0.3 no longer generates any
content for the <esql:more-results> although the previous-results tag
still works ok. I have googled for a week now with no luck and checked
all the FAQ's I could find also with out any luck so am asking here for
some advice as to where I can look to resolve this? I did see a post
that mentions some changes for the Postgres database but am guessing
this shouldn't affect Oracle? Anybody shed some light on this?
Thanks in advance for any help
Rob
Environment Details:-
Tomcat 5.0.18 (also fails on 4.1.24)
Cocoon 2.1.3 (also fails on 2.1.4)
Oracle 9
Windows 2000 (also fails on XP)
JDK 1.4.1
JDBC driver ojdbc14.jar