Return-Path: 
 <users-return-73803-apmail-cocoon-users-archive=cocoon.apache.org@cocoon.apache.org>
Delivered-To: apmail-cocoon-users-archive@www.apache.org
Received: (qmail 83836 invoked from network); 25 Oct 2004 21:17:03 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur-2.apache.org with SMTP; 25 Oct 2004 21:17:03 -0000
Received: (qmail 55282 invoked by uid 500); 25 Oct 2004 21:16:22 -0000
Delivered-To: apmail-cocoon-users-archive@cocoon.apache.org
Received: (qmail 55193 invoked by uid 500); 25 Oct 2004 21:16:21 -0000
Mailing-List: contact users-help@cocoon.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:users-help@cocoon.apache.org>
list-unsubscribe: <mailto:users-unsubscribe@cocoon.apache.org>
list-post: <mailto:users@cocoon.apache.org>
Reply-To: users@cocoon.apache.org
Delivered-To: mailing list users@cocoon.apache.org
Received: (qmail 55163 invoked by uid 99); 25 Oct 2004 21:16:20 -0000
X-ASF-Spam-Status: No, hits=0.1 required=10.0
	tests=DNS_FROM_RFC_ABUSE
X-Spam-Check-By: apache.org
Received-SPF: pass (hermes.apache.org: local policy)
Received: from [165.189.168.135] (HELO comexch2.commerce.state.wi.us)
 (165.189.168.135)
  by apache.org (qpsmtpd/0.28) with ESMTP; Mon, 25 Oct 2004 14:16:18 -0700
Received: from COMEXCH4.commerce.state.wi.us ([165.189.168.107] RDNS failed)
 by comexch2.commerce.state.wi.us with Microsoft SMTPSVC(5.0.2195.6713);
	 Mon, 25 Oct 2004 16:18:49 -0500
Received: by comexch4.commerce.state.wi.us with Internet Mail Service
 (5.5.2657.72)
	id <TC23ABVB>; Mon, 25 Oct 2004 16:14:12 -0500
Message-ID: 
 <A4CC48E7ADCC1D48AF6070678668198A01CCF5A5@comexch4.commerce.state.wi.us>
From: "Schultz, Gary - COMM" <GSchultz@commerce.state.wi.us>
To: "'users@cocoon.apache.org'" <users@cocoon.apache.org>
Subject: RE: Cocoon-Oracle questions
Date: Mon, 25 Oct 2004 16:14:12 -0500
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2657.72)
Content-Type: text/plain;
	charset="iso-8859-1"
X-OriginalArrivalTime: 25 Oct 2004 21:18:49.0750 (UTC)
 FILETIME=[388A3360:01C4BAD8]
X-Virus-Checked: Checked
X-Spam-Rating: minotaur-2.apache.org 1.6.2 0/1000/N

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:GSchultz@commerce.state.wi.us]
Sent: Friday, October 22, 2004 10:44 AM
To: 'users@cocoon.apache.org'
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:mvaterlaus@gmail.com]
Sent: Friday, October 22, 2004 3:25 AM
To: users@cocoon.apache.org
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
<gschultz@commerce.state.wi.us> 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: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
> 
>

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

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

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