Return-Path: 
 <cocoon-users-return-29871-apmail-xml-cocoon-users-archive=xml.apache.org@xml.apache.org>
Delivered-To: apmail-xml-cocoon-users-archive@xml.apache.org
Received: (qmail 87088 invoked by uid 500); 7 Mar 2002 08:08:16 -0000
Mailing-List: contact cocoon-users-help@xml.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:cocoon-users-help@xml.apache.org>
list-unsubscribe: <mailto:cocoon-users-unsubscribe@xml.apache.org>
list-post: <mailto:cocoon-users@xml.apache.org>
Reply-To: cocoon-users@xml.apache.org
Delivered-To: mailing list cocoon-users@xml.apache.org
Received: (qmail 87077 invoked from network); 7 Mar 2002 08:08:15 -0000
Date: Thu, 7 Mar 2002 09:08:22 +0100
From: Christian Joelly <christian.joelly@knapp.com>
To: Cocoon ML <cocoon-users@xml.apache.org>
Subject: [C2.0.1] esql:group/eqsl:member
Message-ID: <20020307080821.GA3440@knapp.com>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="5I6of5zJg18YgZEa"
Content-Disposition: inline
User-Agent: Mutt/1.3.27i
Sender: Christian Joelly <christian.joelly@knapp.com>
X-Virus-Scanned: by AMaViS-perl11-milter (http://amavis.org/)
X-Spam-Rating: daedalus.apache.org 1.6.2 0/1000/N

--5I6of5zJg18YgZEa
Content-Type: multipart/mixed; boundary="DocE+STaALJfprDB"
Content-Disposition: inline


--DocE+STaALJfprDB
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Hi!

i have a *strange* problem with esql:group/esql:member:

when i add esql:group/esql:member to my xsp page then the loop fetching
the results from the resultset changes to an endless loop, till i get an
java.lang.OutOfMemoryError.

settings vm: 			-ms128m -mx256m
settings: storejanitor:	freememory=3D10000000 heapsize=3D268435456

the logic of this page is: (simplified)

page

	// main query
	esql-connection #1
		esql-pool
		esql-query #1
			esql-results #1
		=09
				// nested query
				esql-connection #2
					esql-pool
					esql-query #2
						esql-results #2

						// one more query for lookup data
						esql-connection #3
							esql-pool
							esql-query #3
							esql-results #3
						/esql-connection #3

						// add results to xml tree
						output esql-result #2

						// xsp-logic
						if display-details then

							// esql-query with group/member
							esql-connection #4
								esql-pool
								esql-query #4
								esql-results #4

									esql-group
								=09
										esql-member
											// add row to xml tree
											esql-result #4
										/esql-member

										// add summary row to xml tree
										summary-line

									/esql-group

							/esql-connection #5

						/if

				/esql-connection #2

	/esql-connection #1

/page

if i use esql:group/esql:member with an simpler query, all works as
expected. and if i remove the esql:group, esql:member and summary line
from the xsp page shown above thers also no problem...

thx for any hint, chris

btw: i have attached the xsp page for reference :o)

--=20
KNAPP Logistics Automation  http://www.knapp.com
Ing. Christian J=F6lly        Tel/FAX: (++43) 316 / 495 1926 / 495 394
G=FCnter-Knapp-Stra=DFe 5-7     A-8075 Hart bei Graz


--DocE+STaALJfprDB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="zeitenprodukt.xsp"
Content-Transfer-Encoding: quoted-printable

<?xml version=3D"1.0"?>

<xsp:page language=3D"java"
	xmlns:esql=3D"http://apache.org/cocoon/SQL/v2"
	xmlns:xsp=3D"http://apache.org/xsp"
	xmlns:xsp-request=3D"http://apache.org/xsp/request/2.0"
	>

	<page title=3D"Report">
   =20
		<xsp:logic>
			String produkt =3D ""; if (<xsp-request:get-parameter name=3D"produkt"/>=
 !=3D null) { produkt =3D <xsp-request:get-parameter name=3D"produkt"/>; }
			String auftrag =3D ""; if (<xsp-request:get-parameter name=3D"auftrag"/>=
 !=3D null) { auftrag =3D <xsp-request:get-parameter name=3D"auftrag"/>; }
			String kanr =3D ""; if (<xsp-request:get-parameter name=3D"kanr"/> !=3D =
null) { kanr =3D <xsp-request:get-parameter name=3D"kanr"/>; }
			String report =3D ""; if (<xsp-request:get-parameter name=3D"report"/> !=
=3D null) { report =3D <xsp-request:get-parameter name=3D"report"/>; }
			String sLi =3D ""; if (<xsp-request:get-parameter name=3D"li"/> !=3D nul=
l) { sLi =3D <xsp-request:get-parameter name=3D"li"/>; }
			String sLit =3D ""; if (<xsp-request:get-parameter name=3D"lit"/> !=3D n=
ull) { sLit =3D <xsp-request:get-parameter name=3D"lit"/>; }
			String sLitPos =3D ""; if (<xsp-request:get-parameter name=3D"litpos"/> =
!=3D null) { sLitPos =3D <xsp-request:get-parameter name=3D"litpos"/>; }

			int nKaNr;
			String sProdukt;

			float nMenge =3D 0;
			float nSollzeit =3D 0;
			float nSollzeitStk =3D 0;
			float nIstzeit =3D 0;
			float nIstzeitSchnitt =3D 0;
			float nSumMenge =3D 0;
			float nSumIstzeit =3D 0;
			float nSumSollzeit =3D 0;
			float nSumIstzeitSchnitt =3D 0;
		=09
			float nSumIstzeitKstGr =3D 0;
			float nSumSollzeitKstGr =3D 0;
			float nSumIstzeitSchnittKstGr =3D 0;
			float nSumSollzeitStkKstGr =3D 0;

			String sQuery =3D "";
			String sQueryInner =3D "";
		</xsp:logic>

		<head/>
=20
		<body>
			<report>
				<reporttitle>
					<reportparam>
						<xsp:content>Auftrag: <xsp:expr>auftrag</xsp:expr></xsp:content>
						<xsp:content><xsp:expr>sLi</xsp:expr><xsp:expr>sLit</xsp:expr><xsp:ex=
pr>sLitPos</xsp:expr></xsp:content>
						<xsp:content>Produkt: <xsp:expr>produkt</xsp:expr></xsp:content>
						<xsp:logic>
							if ((kanr !=3D null) &amp;&amp; (kanr.length() &gt; 0)) {
								<xsp:content>KANR: <xsp:expr>kanr</xsp:expr></xsp:content>
							}
						</xsp:logic>
					</reportparam>
					<reportname>
						<xsp:content>Soll/Ist Zeiten</xsp:content>
						<xsp:logic>
							if (report.equals("istzeit-kst")) {
								<xsp:content> nach Kostenstellen</xsp:content>
							}
						</xsp:logic>
					</reportname>
				</reporttitle>

				<reportheader>
					<headercol order=3D"1" name=3D"li">
						<xsp:logic>
								if (report.equals("istzeit-kst")) {
									<xsp:attribute name=3D"subscript">(Kst Gr.)</xsp:attribute>
								}
						</xsp:logic>
						Li
					</headercol>
					<headercol order=3D"2" name=3D"menge">
						<xsp:logic>
							if (report.equals("istzeit-kst")) {
								<xsp:attribute name=3D"subscript">(Kst)</xsp:attribute>
							}
						</xsp:logic>
						Menge
					</headercol>
					<headercol order=3D"3" name=3D"sollzeitstk">Sollzeit (Stk. [h])</heade=
rcol>
					<headercol order=3D"4" name=3D"sollzeit">Sollzeit (Gesamt [h])</header=
col>
					<headercol order=3D"5" name=3D"istzeit">Istzeit BDE (Gesamt [h])</head=
ercol>
					<headercol order=3D"6" name=3D"istzeitschnitt">Istzeit BDE (Schnitt [h=
])</headercol>
				</reportheader>

				<reportdata>
		=09
					<esql:connection>
				     =20
						<esql:pool>pps</esql:pool>

						<xsp:logic>
							sQuery =3D "
								SELECT
									SUM(ap.menge) as menge,
									ap.li,
									ap.lit,
									ap.litpos
								FROM
									aufpos=3Dap";
								=09
							if ((kanr !=3D null) &amp;&amp; (kanr.length() &gt; 0)) {
								sQuery +=3D ",
									stbk_kopf=3Dsk
								";
							}
							=09
							sQuery +=3D "
								WHERE
									ap.auf =3D \"" + auftrag + "\"
								AND
									CONCAT(ap.produkt, ap.idx) =3D \"" + produkt + "\"
								";
							=09
							if ((kanr !=3D null) &amp;&amp; (kanr.length() &gt; 0)) {
								sQuery +=3D "
									AND
										sk.auf =3D ap.auf
									AND
										sk.bau =3D ap.bau
									AND
										sk.pos =3D ap.pos
									AND
										sk.tei =3D ap.tei
									AND
										sk.kanr =3D \"" + kanr + "\"
								";
							}

							if ((sLi !=3D null) &amp;&amp; (sLi.length() &gt; 0)) {
								sQuery +=3D "
									AND
										ap.li =3D \"" + sLi + "\"
									";
							}
							if ((sLit !=3D null) &amp;&amp; (sLit.length() &gt; 0)) {
								sQuery +=3D "
									AND
										ap.lit =3D \"" + sLit + "\"
									";
							}
							if ((sLitPos !=3D null) &amp;&amp; (sLitPos.length() &gt; 0)) {
								sQuery +=3D "
									AND
										ap.litpos =3D \"" + sLitPos + "\"
									";
							}
						=09
							sQuery +=3D "
								GROUP BY
									ap.li, ap.lit, ap.litpos
								";
						</xsp:logic>

						<esql:execute-query>

							<esql:query><xsp:expr>sQuery</xsp:expr></esql:query>

							<esql:results>
								<esql:row-results>

									<xsp:logic>
										nMenge =3D <esql:get-float column=3D"menge"/>;
										nSumMenge +=3D nMenge;
										sLi =3D <esql:get-string column=3D"li"/>;
										sLit =3D <esql:get-string column=3D"lit"/>;
										sLitPos =3D <esql:get-string column=3D"litpos"/>;
									</xsp:logic>

									<esql:connection>
										<esql:pool>pps</esql:pool>
										<xsp:logic>
											sQueryInner =3D "
												SELECT
													sum(s.irzt+s.iezt) as istzeit
												FROM
													aufpos=3Da
														LEFT OUTER JOIN stbk_kopf=3Dsk
														ON
														(
															sk.auf =3D a.auf
														AND
															sk.bau =3D a.bau
														AND
															sk.pos =3D a.pos
														AND
															sk.tei =3D a.tei
														)
														LEFT OUTER JOIN stbk=3Ds
														ON
														(
															s.stbk =3D sk.stbk
														AND
															s.stbkindex =3D sk.stbkindex
														)
												WHERE
													a.auf =3D \"" + auftrag + "\"
												AND
													CONCAT(a.produkt, a.idx) =3D \"" + produkt + "\"
												AND
													a.li =3D \"" + sLi + "\"
												AND
													a.lit =3D \"" + sLit + "\"
												AND
													a.litpos =3D \"" + sLitPos + "\"
											";
											if ((kanr !=3D null) &amp;&amp; (kanr.length() &gt; 0)) {
												sQueryInner +=3D "
													AND
														sk.kanr =3D \"" + kanr + "\"
												";
											}
										</xsp:logic>
										<esql:execute-query>
											<esql:query><xsp:expr>sQueryInner</xsp:expr></esql:query>

											<esql:results>
												<esql:row-results>

													<!-- Durchschnitt eines Stuecks berechnen -->
													<xsp:logic>
														nIstzeit =3D <esql:get-float column=3D"istzeit"/>;
														nSumIstzeit +=3D nIstzeit;

														if (nMenge > 0) {
															nIstzeitSchnitt =3D nIstzeit / nMenge;
														}
														else {
															nIstzeitSchnitt =3D 0;
														}
													</xsp:logic>

													<!-- Sollzeit ueber alle Kostenstellen ermitteln -->
													<esql:connection>
														<esql:pool>pps</esql:pool>
														<esql:execute-query>
															<esql:query>
																SELECT
																	sum(szeit) AS sollzeitkst
																FROM
																	sollzeiten_kst
																WHERE
																	produkt =3D <esql:parameter type=3D"string"><xsp:expr>prod=
ukt.substring(0,8)</xsp:expr></esql:parameter>
															</esql:query>
															<esql:results>
																<esql:row-results>
																	<xsp:logic>
																		nSollzeitStk =3D <esql:get-float column=3D"sollzeitkst"/>;
																		nSumSollzeit +=3D nSollzeitStk * nMenge;
																	</xsp:logic>
																</esql:row-results>
																<esql:noresults/>
															</esql:results>
														</esql:execute-query>
													</esql:connection>

													<!-- Die Daten ausgeben -->
													<reportrow type=3D"master">
														<datacol order=3D"1" name=3D"li"><esql:get-string column=3D"l=
i" ancestor=3D"1"/><esql:get-string column=3D"lit" ancestor=3D"1"/><esql:ge=
t-string column=3D"litpos" ancestor=3D"1"/></datacol>
														<datacol order=3D"2" type=3D"number" name=3D"menge"><xsp:expr=
>nMenge</xsp:expr></datacol>
														<datacol order=3D"3" type=3D"number" name=3D"sollzeitstk"><xs=
p:expr>nSollzeitStk</xsp:expr></datacol>
														<datacol order=3D"4" type=3D"number" name=3D"sollzeit"><xsp:e=
xpr>nSollzeitStk * nMenge</xsp:expr></datacol>
														<datacol order=3D"5" type=3D"number" name=3D"istzeit"><xsp:ex=
pr>nIstzeit</xsp:expr></datacol>
														<datacol order=3D"6" type=3D"number" name=3D"istzeitschnitt">=
<xsp:expr>nIstzeitSchnitt</xsp:expr></datacol>
													</reportrow>

													<!-- sollen auch die Kostenstellen detailiert ausgewertet werd=
en? -->
													<xsp:logic>
														if (report.equals("istzeit-kst")) {
															<esql:connection>
																<esql:pool>pps</esql:pool>
																<esql:execute-query>
																	<esql:query>
																		SELECT
																			s.ikst,
																			s.kstgr,
																			sum(s.irzt+s.iezt) as istzeit,
																			skst.szeit as sollzeitstk
																		FROM
																			aufpos=3Da
																				LEFT OUTER JOIN stbk_kopf=3Dsk
																				ON
																				(
																					sk.auf =3D a.auf
																				AND
																					sk.bau =3D a.bau
																				AND
																					sk.pos =3D a.pos
																				AND
																					sk.tei =3D a.tei
																				)
																				LEFT OUTER JOIN stbk=3Ds
																				ON
																				(
																					s.stbk =3D sk.stbk
																				AND
																					s.stbkindex =3D sk.stbkindex
																				)
																				LEFT OUTER JOIN sollzeiten_kst=3Dskst
																				ON
																				(
																					skst.kst =3D s.skst
																				AND
																					skst.produkt =3D a.produkt
																				)
																		WHERE
																			a.auf =3D <esql:parameter type=3D"string"><xsp:expr>auft=
rag</xsp:expr></esql:parameter>
																		AND
																			CONCAT(a.produkt, a.idx) =3D <esql:parameter type=3D"str=
ing"><xsp:expr>produkt</xsp:expr></esql:parameter>
																		AND
																			a.li =3D <esql:parameter type=3D"int"><esql:get-int ance=
stor=3D"2" column=3D"li"/></esql:parameter>
																		AND
																			a.lit =3D <esql:parameter type=3D"string"><esql:get-stri=
ng ancestor=3D"2" column=3D"lit"/></esql:parameter>
																		AND
																			a.litpos =3D <esql:parameter type=3D"int"><esql:get-int =
ancestor=3D"2" column=3D"litpos"/></esql:parameter>
																		AND
																			length(s.ikst) > 0
																		GROUP BY
																			s.ikst
																	</esql:query>
																	<esql:results>
																		<esql:row-results>
																			<esql:group group-on=3D"kstgr">
																		=09
																				<xsp:logic>
																					nSumSollzeitStkKstGr =3D 0;
																					nSumSollzeitKstGr =3D 0;
																					nSumIstzeitKstGr =3D 0;
																					nSumIstzeitSchnittKstGr =3D 0;
																				</xsp:logic>

																				<esql:member>
																					<xsp:logic>
																						nSollzeitStk =3D <esql:get-float column=3D"sollzeitst=
k"/>;

																						nSumSollzeitStkKstGr +=3D nSollzeitStk;
																						nSumSollzeitKstGr +=3D nSollzeitStk * nMenge;
																					=09
																						nIstzeit =3D <esql:get-float column=3D"istzeit"/>;

																						if (nMenge > 0) {
																							nIstzeitSchnitt =3D nIstzeit / nMenge;
																						}
																						else {
																							nIstzeitSchnitt =3D 0;
																						}

																						nSumIstzeitKstGr +=3D nIstzeit;
																						nSumIstzeitSchnittKstGr +=3D nIstzeitSchnitt;
																					</xsp:logic>
																					<reportrow type=3D"detail">
																						<datacol order=3D"1" type=3D"string" name=3D"kstgr"><=
esql:get-string column=3D"kstgr"/></datacol>
																						<datacol order=3D"2" type=3D"string" name=3D"kst"><es=
ql:get-string column=3D"ikst"/></datacol>
																						<datacol order=3D"3" type=3D"number" name=3D"sollzeit=
stk"><xsp:expr>nSollzeitStk</xsp:expr></datacol>
																						<datacol order=3D"4" type=3D"number" name=3D"sollzeit=
"><xsp:expr>nSollzeitStk * nMenge</xsp:expr></datacol>
																						<datacol order=3D"5" type=3D"number" name=3D"istzeit"=
><xsp:expr>nIstzeit</xsp:expr></datacol>
																						<datacol order=3D"6" type=3D"number" name=3D"istzeits=
chnitt"><xsp:expr>nIstzeitSchnitt</xsp:expr></datacol>
																					</reportrow>
																				</esql:member>
																			=09
																				<reportrow type=3D"master">
																					<datacol order=3D"1" type=3D"string" name=3D"kstgr">Su=
mme <esql:get-string column=3D"kstgr"/></datacol>
																					<datacol order=3D"2" type=3D"string" name=3D"kst"/>
																					<datacol order=3D"3" type=3D"number" name=3D"sollzeits=
tk"><xsp:expr>nSumSollzeitStkKstGr</xsp:expr></datacol>
																					<datacol order=3D"4" type=3D"number" name=3D"sollzeit"=
><xsp:expr>nSumSollzeitKstGr</xsp:expr></datacol>
																					<datacol order=3D"5" type=3D"number" name=3D"istzeit">=
<xsp:expr>nSumIstzeitKstGr</xsp:expr></datacol>
																					<datacol order=3D"6" type=3D"number" name=3D"istzeitsc=
hnitt"><xsp:expr>nSumIstzeitSchnittKstGr</xsp:expr></datacol>
																				</reportrow>

																			</esql:group>
																		=09
																		</esql:row-results>
																	</esql:results>
																	<esql:no-results/>
																</esql:execute-query>
															</esql:connection>
														}
													</xsp:logic>
												</esql:row-results>
											</esql:results>
											<esql:no-results/>
										</esql:execute-query>
									</esql:connection>
							=09
								</esql:row-results>
							</esql:results>
							 =20
							<esql:no-results/>
							 =20
						</esql:execute-query>

					</esql:connection>
				=09
				</reportdata>

				<xsp:logic>
					if (nSumMenge > 0) {
						nSumIstzeitSchnitt =3D nSumIstzeit / nSumMenge;
					}
					else {
						nSumIstzeitSchnitt =3D 0;
					}
				</xsp:logic>

				<reportfooter>
					<footercol order=3D"1" name=3D"li">Summe</footercol>
					<footercol order=3D"2" type=3D"number" name=3D"menge"><xsp:expr>nSumMe=
nge</xsp:expr></footercol>
					<footercol order=3D"3" type=3D"number" name=3D"sollzeitstk"><xsp:expr>=
0</xsp:expr></footercol>
					<footercol order=3D"4" type=3D"number" name=3D"sollzeit"><xsp:expr>nSu=
mSollzeit</xsp:expr></footercol>
					<footercol order=3D"5" type=3D"number" name=3D"istzeit"><xsp:expr>nSum=
Istzeit</xsp:expr></footercol>
					<footercol order=3D"6" type=3D"number" name=3D"istzeitschnitt"><xsp:ex=
pr>nSumIstzeitSchnitt</xsp:expr></footercol>
				</reportfooter>
			</report>
		</body>

	</page>
</xsp:page>


--DocE+STaALJfprDB--

--5I6of5zJg18YgZEa
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: Weitere Infos: siehe http://www.gnupg.org

iD8DBQE8hx/1H9oxN0KOR1IRAi8aAJ9eRxlU1VwXbC3Lu6D7jC5C/ODaWwCfYeEY
pA3y6+6rxGkoS5UuS7YF/fY=
=V5rC
-----END PGP SIGNATURE-----

--5I6of5zJg18YgZEa--