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=10000000 heapsize=268435456
the logic of this page is: (simplified)
page
// main query
esql-connection #1
esql-pool
esql-query #1
esql-results #1
// 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
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)
--
KNAPP Logistics Automation http://www.knapp.com
Ing. Christian Jölly Tel/FAX: (++43) 316 / 495 1926 / 495 394
Günter-Knapp-Straße 5-7 A-8075 Hart bei Graz
<?xml version="1.0"?>
<xsp:page language="java"
xmlns:esql="http://apache.org/cocoon/SQL/v2";
xmlns:xsp="http://apache.org/xsp";
xmlns:xsp-request="http://apache.org/xsp/request/2.0";
>
<page title="Report">
<xsp:logic>
String produkt = ""; if (<xsp-request:get-parameter
name="produkt"/> != null) { produkt = <xsp-request:get-parameter name="produkt"/>; }
String auftrag = ""; if (<xsp-request:get-parameter
name="auftrag"/> != null) { auftrag = <xsp-request:get-parameter name="auftrag"/>; }
String kanr = ""; if (<xsp-request:get-parameter name="kanr"/>
!= null) { kanr = <xsp-request:get-parameter name="kanr"/>; }
String report = ""; if (<xsp-request:get-parameter
name="report"/> != null) { report = <xsp-request:get-parameter name="report"/>; }
String sLi = ""; if (<xsp-request:get-parameter name="li"/> !=
null) { sLi = <xsp-request:get-parameter name="li"/>; }
String sLit = ""; if (<xsp-request:get-parameter name="lit"/>
!= null) { sLit = <xsp-request:get-parameter name="lit"/>; }
String sLitPos = ""; if (<xsp-request:get-parameter
name="litpos"/> != null) { sLitPos = <xsp-request:get-parameter name="litpos"/>; }
int nKaNr;
String sProdukt;
float nMenge = 0;
float nSollzeit = 0;
float nSollzeitStk = 0;
float nIstzeit = 0;
float nIstzeitSchnitt = 0;
float nSumMenge = 0;
float nSumIstzeit = 0;
float nSumSollzeit = 0;
float nSumIstzeitSchnitt = 0;
float nSumIstzeitKstGr = 0;
float nSumSollzeitKstGr = 0;
float nSumIstzeitSchnittKstGr = 0;
float nSumSollzeitStkKstGr = 0;
String sQuery = "";
String sQueryInner = "";
</xsp:logic>
<head/>
<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:expr>sLitPos</xsp:expr></xsp:content>
<xsp:content>Produkt:
<xsp:expr>produkt</xsp:expr></xsp:content>
<xsp:logic>
if ((kanr != null) &&
(kanr.length() > 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="1" name="li">
<xsp:logic>
if
(report.equals("istzeit-kst")) {
<xsp:attribute
name="subscript">(Kst Gr.)</xsp:attribute>
}
</xsp:logic>
Li
</headercol>
<headercol order="2" name="menge">
<xsp:logic>
if
(report.equals("istzeit-kst")) {
<xsp:attribute
name="subscript">(Kst)</xsp:attribute>
}
</xsp:logic>
Menge
</headercol>
<headercol order="3"
name="sollzeitstk">Sollzeit (Stk. [h])</headercol>
<headercol order="4" name="sollzeit">Sollzeit
(Gesamt [h])</headercol>
<headercol order="5" name="istzeit">Istzeit
BDE (Gesamt [h])</headercol>
<headercol order="6"
name="istzeitschnitt">Istzeit BDE (Schnitt [h])</headercol>
</reportheader>
<reportdata>
<esql:connection>
<esql:pool>pps</esql:pool>
<xsp:logic>
sQuery = "
SELECT
SUM(ap.menge)
as menge,
ap.li,
ap.lit,
ap.litpos
FROM
aufpos=ap";
if ((kanr != null) &&
(kanr.length() > 0)) {
sQuery += ",
stbk_kopf=sk
";
}
sQuery += "
WHERE
ap.auf = \"" +
auftrag + "\"
AND
CONCAT(ap.produkt, ap.idx) = \"" + produkt + "\"
";
if ((kanr != null) &&
(kanr.length() > 0)) {
sQuery += "
AND
sk.auf
= ap.auf
AND
sk.bau
= ap.bau
AND
sk.pos
= ap.pos
AND
sk.tei
= ap.tei
AND
sk.kanr = \"" + kanr + "\"
";
}
if ((sLi != null) &&
(sLi.length() > 0)) {
sQuery += "
AND
ap.li
= \"" + sLi + "\"
";
}
if ((sLit != null) &&
(sLit.length() > 0)) {
sQuery += "
AND
ap.lit
= \"" + sLit + "\"
";
}
if ((sLitPos != null)
&& (sLitPos.length() > 0)) {
sQuery += "
AND
ap.litpos = \"" + sLitPos + "\"
";
}
sQuery += "
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
= <esql:get-float column="menge"/>;
nSumMenge += nMenge;
sLi =
<esql:get-string column="li"/>;
sLit =
<esql:get-string column="lit"/>;
sLitPos = <esql:get-string column="litpos"/>;
</xsp:logic>
<esql:connection>
<esql:pool>pps</esql:pool>
<xsp:logic>
sQueryInner = "
SELECT
sum(s.irzt+s.iezt) as istzeit
FROM
aufpos=a
LEFT OUTER JOIN stbk_kopf=sk
ON
(
sk.auf = a.auf
AND
sk.bau = a.bau
AND
sk.pos = a.pos
AND
sk.tei = a.tei
)
LEFT OUTER JOIN stbk=s
ON
(
s.stbk = sk.stbk
AND
s.stbkindex = sk.stbkindex
)
WHERE
a.auf = \"" + auftrag + "\"
AND
CONCAT(a.produkt, a.idx) = \"" + produkt + "\"
AND
a.li = \"" + sLi + "\"
AND
a.lit = \"" + sLit + "\"
AND
a.litpos = \"" + sLitPos + "\"
";
if ((kanr != null) && (kanr.length() > 0)) {
sQueryInner += "
AND
sk.kanr = \"" + 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 = <esql:get-float column="istzeit"/>;
nSumIstzeit += nIstzeit;
if (nMenge > 0) {
nIstzeitSchnitt = nIstzeit / nMenge;
}
else {
nIstzeitSchnitt = 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 = <esql:parameter
type="string"><xsp:expr>produkt.substring(0,8)</xsp:expr></esql:parameter>
</esql:query>
<esql:results>
<esql:row-results>
<xsp:logic>
nSollzeitStk =
<esql:get-float column="sollzeitkst"/>;
nSumSollzeit += nSollzeitStk
* nMenge;
</xsp:logic>
</esql:row-results>
<esql:noresults/>
</esql:results>
</esql:execute-query>
</esql:connection>
<!-- Die Daten ausgeben -->
<reportrow type="master">
<datacol order="1" name="li"><esql:get-string column="li"
ancestor="1"/><esql:get-string column="lit" ancestor="1"/><esql:get-string
column="litpos" ancestor="1"/></datacol>
<datacol order="2" type="number"
name="menge"><xsp:expr>nMenge</xsp:expr></datacol>
<datacol order="3" type="number"
name="sollzeitstk"><xsp:expr>nSollzeitStk</xsp:expr></datacol>
<datacol order="4" type="number"
name="sollzeit"><xsp:expr>nSollzeitStk * nMenge</xsp:expr></datacol>
<datacol order="5" type="number"
name="istzeit"><xsp:expr>nIstzeit</xsp:expr></datacol>
<datacol order="6" type="number"
name="istzeitschnitt"><xsp:expr>nIstzeitSchnitt</xsp:expr></datacol>
</reportrow>
<!-- sollen auch die Kostenstellen detailiert ausgewertet werden? -->
<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=a
LEFT OUTER
JOIN stbk_kopf=sk
ON
(
sk.auf = a.auf
AND
sk.bau = a.bau
AND
sk.pos = a.pos
AND
sk.tei = a.tei
)
LEFT OUTER
JOIN stbk=s
ON
(
s.stbk = sk.stbk
AND
s.stbkindex = sk.stbkindex
)
LEFT OUTER
JOIN sollzeiten_kst=skst
ON
(
skst.kst = s.skst
AND
skst.produkt = a.produkt
)
WHERE
a.auf =
<esql:parameter type="string"><xsp:expr>auftrag</xsp:expr></esql:parameter>
AND
CONCAT(a.produkt,
a.idx) = <esql:parameter type="string"><xsp:expr>produkt</xsp:expr></esql:parameter>
AND
a.li =
<esql:parameter type="int"><esql:get-int ancestor="2" column="li"/></esql:parameter>
AND
a.lit =
<esql:parameter type="string"><esql:get-string ancestor="2"
column="lit"/></esql:parameter>
AND
a.litpos =
<esql:parameter type="int"><esql:get-int ancestor="2"
column="litpos"/></esql:parameter>
AND
length(s.ikst) > 0
GROUP BY
s.ikst
</esql:query>
<esql:results>
<esql:row-results>
<esql:group
group-on="kstgr">
<xsp:logic>
nSumSollzeitStkKstGr = 0;
nSumSollzeitKstGr = 0;
nSumIstzeitKstGr = 0;
nSumIstzeitSchnittKstGr = 0;
</xsp:logic>
<esql:member>
<xsp:logic>
nSollzeitStk = <esql:get-float column="sollzeitstk"/>;
nSumSollzeitStkKstGr += nSollzeitStk;
nSumSollzeitKstGr += nSollzeitStk * nMenge;
nIstzeit = <esql:get-float column="istzeit"/>;
if (nMenge > 0) {
nIstzeitSchnitt = nIstzeit / nMenge;
}
else {
nIstzeitSchnitt = 0;
}
nSumIstzeitKstGr += nIstzeit;
nSumIstzeitSchnittKstGr += nIstzeitSchnitt;
</xsp:logic>
<reportrow type="detail">
<datacol order="1" type="string" name="kstgr"><esql:get-string
column="kstgr"/></datacol>
<datacol order="2" type="string" name="kst"><esql:get-string
column="ikst"/></datacol>
<datacol order="3" type="number"
name="sollzeitstk"><xsp:expr>nSollzeitStk</xsp:expr></datacol>
<datacol order="4" type="number" name="sollzeit"><xsp:expr>nSollzeitStk *
nMenge</xsp:expr></datacol>
<datacol order="5" type="number"
name="istzeit"><xsp:expr>nIstzeit</xsp:expr></datacol>
<datacol order="6" type="number"
name="istzeitschnitt"><xsp:expr>nIstzeitSchnitt</xsp:expr></datacol>
</reportrow>
</esql:member>
<reportrow
type="master">
<datacol order="1" type="string" name="kstgr">Summe <esql:get-string
column="kstgr"/></datacol>
<datacol order="2" type="string" name="kst"/>
<datacol order="3" type="number"
name="sollzeitstk"><xsp:expr>nSumSollzeitStkKstGr</xsp:expr></datacol>
<datacol order="4" type="number"
name="sollzeit"><xsp:expr>nSumSollzeitKstGr</xsp:expr></datacol>
<datacol order="5" type="number"
name="istzeit"><xsp:expr>nSumIstzeitKstGr</xsp:expr></datacol>
<datacol order="6" type="number"
name="istzeitschnitt"><xsp:expr>nSumIstzeitSchnittKstGr</xsp:expr></datacol>
</reportrow>
</esql:group>
</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>
</esql:row-results>
</esql:results>
<esql:no-results/>
</esql:execute-query>
</esql:connection>
</reportdata>
<xsp:logic>
if (nSumMenge > 0) {
nSumIstzeitSchnitt = nSumIstzeit /
nSumMenge;
}
else {
nSumIstzeitSchnitt = 0;
}
</xsp:logic>
<reportfooter>
<footercol order="1"
name="li">Summe</footercol>
<footercol order="2" type="number"
name="menge"><xsp:expr>nSumMenge</xsp:expr></footercol>
<footercol order="3" type="number"
name="sollzeitstk"><xsp:expr>0</xsp:expr></footercol>
<footercol order="4" type="number"
name="sollzeit"><xsp:expr>nSumSollzeit</xsp:expr></footercol>
<footercol order="5" type="number"
name="istzeit"><xsp:expr>nSumIstzeit</xsp:expr></footercol>
<footercol order="6" type="number"
name="istzeitschnitt"><xsp:expr>nSumIstzeitSchnitt</xsp:expr></footercol>
</reportfooter>
</report>
</body>
</page>
</xsp:page>
msg09014/pgp00000.pgp
Description: PGP signature
