I continue to get mixed results. At first it appeared it worked fine as soon
as I did the following:
1. Changed the incremental-processing value from false to true as shown
below.
<xslt-processor logger="core.xslt-processor">
<parameter name="use-store" value="true"/>
<parameter name="incremental-processing" value="true"/>
</xslt-processor>
<component class="org.apache.excalibur.xml.xslt.XSLTProcessorImpl"
logger="core.xslt-processor"
role="org.apache.excalibur.xml.xslt.XSLTProcessor/xalan">
<parameter name="use-store" value="true"/>
<parameter name="incremental-processing" value="true"/>
<parameter name="transformer-factory"
value="org.apache.xalan.processor.TransformerFactoryImpl"/>
</component>
2. Following an example I saw in JavaWorld I set the following in the
sitemap:
<map:pipeline match="*.xsp">
<map:generate type="serverpages" src="examples/{1}.xsp"/>
<map:serialize type="xml"/>
</map:pipeline>
Used the following to restrict the records. Can't remember where I found
this.
<esql:skip-rows>8000</esql:skip-rows>
<esql:max-rows>1000</esql:max-rows>
When I restrict the results to 4000 or less records the results are
consistent. When I increase the number of records I get inconsistent
results, it either works or I get an error message.
Here is the code from my XSP page:
<?xml version="1.0" encoding="utf-8"?>
<xsp:page language="java" xmlns:xsp="http://apache.org/xsp";
xmlns:xsp-request="http://apache.org/xsp/request/2.0";
xmlns:esql="http://apache.org/cocoon/SQL/v2";
>
<Grants>
<esql:connection>
<esql:pool>connection1</esql:pool>
<esql:execute-query>
<esql:query>select grantid,
agencyid,
program,
granteename,
projectname,
county as projectcounty,
muni as projectmunicipality,
projectaddress,
grantloanamount,
fundingsource,
cfda,
strategicarea,
projectdescription,
earliestannouncedate,
latestannouncedate,
startdate,
enddate,
status,
state
from webdb.grant_data order by grantid
</esql:query>
<esql:results>
<esql:row-results>
<Grant>
<GrantID><esql:get-int column="grantid" /></GrantID>
<AgencyID><esql:get-int column="agencyid" /></AgencyID>
<Program><esql:get-string column="program" /></Program>
<GranteeName><esql:get-string column="granteename" /></GranteeName>
<ProjectName><esql:get-string column="projectname" /></ProjectName>
<ProjectCounty><esql:get-string column="projectcounty"
/></ProjectCounty>
<ProjectMunicipality><esql:get-string column="projectmunicipality"
/></ProjectMunicipality>
<ProjectAddress><esql:get-string column="projectaddress"
/></ProjectAddress>
<GrantLoanAmount><esql:get-long column="grantloanamount"
/></GrantLoanAmount>
<FundingSource><esql:get-string column="fundingsource"
/></FundingSource>
<CFDA><esql:get-string column="cfda" /></CFDA>
<StrategicArea><esql:get-string column="strategicarea"
/></StrategicArea>
<ProjectDescription><esql:get-string column="projectdescription"
/></ProjectDescription>
<EarliestAnnounceDate><esql:get-date column="earliestannouncedate"
/></EarliestAnnounceDate>
<LatestAnnounceDate><esql:get-date column="latestannouncedate"
/></LatestAnnounceDate>
<!-- <AnnouncedDate><esql:get-date column="announceddate"
/></AnnouncedDate> -->
<StartDate><esql:get-date column="startdate" /></StartDate>
<EndDate><esql:get-date column="enddate" /></EndDate>
<Status><esql:get-string column="status" /></Status>
</Grant>
</esql:row-results>
</esql:results>
<esql:no-results>
<error>
<p>Nothing found.</p>
</error>
</esql:no-results>
</esql:execute-query>
</esql:connection>
</Grants>
</xsp:page>
I took out order by grantid, but that didn't change things.
Here are two examples one with no restriction on the number of records and
one with a restirction of 2000 records. The no restriction example contains
10967 records. There is a large amount of data.
No restriction on records: http://commerce.wi.gov/commdb/xsp/grant_tracker
2000 record restriction:
http://commerce.wi.gov/commdb/xsp/grant_tracker_1-2000
Any suggestions on how I can improve either my XSP code or Cocoon
configuration are greatly appreciated.
Gary T. Schultz
Web Technical Administrator / GIS Coordinator
Wisconsin Department of Commerce
6th Floor
P.O. Box 7970
Madison, WI
1-608-266-1283
-----Original Message-----
From: Schultz, Gary - COMM [mailto:[EMAIL PROTECTED]
Sent: Friday, October 22, 2004 10:44 AM
To: '[EMAIL PROTECTED]'
Subject: RE: Cocoon-Oracle questions
Thank you for your help.
Are you transforming with XSL or STX? I've tried restrictions with where,
limited the results with max-rows, but have had mixed results. Sometimes it
works sometimes it doesn't. The problem is that the DOM is becoming so large
that too much of the server resources are consumed. I think I could solve
problems with STX, but I'm still trying to figure out how to use STX with
ESQL.
Gary T. Schultz
Web Technical Administrator / GIS Coordinator
Wisconsin Department of Commerce
6th Floor
P.O. Box 7970
Madison, WI
1-608-266-1283
-----Original Message-----
From: Markus Vaterlaus [mailto:[EMAIL PROTECTED]
Sent: Friday, October 22, 2004 3:25 AM
To: [EMAIL PROTECTED]
Subject: Re: Cocoon-Oracle questions
Hi there,
I'm using ESQL to connect to an Oracle 8i DB. Some tables I'm using
have far more than 10000 records in it. Allthough, I don't need all
the records; restrictions with WHERE come in very handy. :-)
To check your issue I built a logicsheet with the following query
<esql:query>
select * from tablename order by key
</esql:query>
The table tablename has about 30000 records. It takes some time to get
the result back, transform it and display it in IE or Moz. But it
works!
My setup: cocoon 2.1.4 on tomcat 4.1.21 on a 2 cpu-linux box with 4 GB
RAM. Oracle is running on the same server.
BTW: If you use the restriction <esql:max-rows>100000</esql:max-rows>
the row-results contains an additional element called <ROWNUM/>.
Cheers
--mv
On Wed, 20 Oct 2004 09:35:48 -0500, Schultz, Gary - COMM
<[EMAIL PROTECTED]> wrote:
> I need to gain access to an Oracle database with over 11000 records.
>
> Is there a limit to the number of rows that can be retrieved using ESQL in
> an XSP page? My XSP tops out at 2000 records. Can this be increased? Where
> should I look for information on this.
>
> Does Cocoon support rownum >? I have not been able to get it to work. I
did
> not find anything in the Cocoon Wiki related to rownum. If rownum > is not
> supported, what is an easy way to retrieve sets of records from a query.
>
> Any information that points me in the right direction is greatly
> appreciated.
>
> Gary T. Schultz
> Web Technical Administrator / GIS Coordinator
> Wisconsin Department of Commerce
> 6th Floor
> P.O. Box 7970
> Madison, WI
> 1-608-266-1283
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
