whitespace bug?
"Harry LeBlanc" <[email protected]> Thu, 22 Jan 2004 20:58:20 -0600
| Newsgroups | gmane.comp.krysalis.user |
|---|---|
| Message-ID | <[email protected]> |
I'm running into some strange behavior. I have a report query that uses lots
of $P!{param} substitutions, so I can have one report that serves several
purposes. The problem is that whitespace (decimal 32, hex 20) is being
cooked to some weird character that looks like a whitespace but is actually
decimal 160, hex a0.
Here's my query string:
<queryString><![CDATA[SELECT $P!{GroupTable}.name AS GroupName, Outlet.Name
AS OutletName, Address.City, State.State, Invoice, DekInvoice.InvoiceDate,
BlocksShipped FROM DekInvoice, State, Outlet, Chain, $P!{MfgTable},
$P!{TruckloadTable}, Address WHERE DekInvoice.Dropoff = Outlet.Outlet
$P!{TruckloadWhereClause} $P!{InvoiceStatusWhereClause} $P!{MfgWhereClause}
AND DekInvoice.ShippingAddress=Address.Address AND
Outlet.ParentCompany=Chain.Chain AND Address.State=State.State AND
DekInvoice.InvoiceDate BETWEEN '$P!{StartDate}' AND '$P!{EndDate}' ORDER BY
$P!{GroupTable}.name, InvoiceDate, Outlet.Name
]]></queryString>
...and here's what it's being cooked to:
SELECT Chain.name AS GroupName, Outlet.Name AS OutletName, Address.City, Sta
te.State, Invoice, DekInvoice.InvoiceDate, BlocksShipped FROM DekInvoice, St
ate, Outlet, Chain, Pick, Truckload, Address WHERE DekInvoice.Dropoff = Outl
et.Outlet  AND DekInvoice.Truckload=Truckload.Truckload   AND Truckload.Pick
=Manufacturer.company AND DekInvoice.ShippingAddress=Address.Address AND Out
let.ParentCompany=Chain.Chain AND Address.State=State.State AND DekInvoice.I
nvoiceDate BETWEEN '12/01/2003' AND '12/31/2003' ORDER BY Chain.name, Invoic
eDate, Outlet.Name
The character between SELECT and Chain.name is a weird non-whitespace char
(not sure if it'll be preserved properly in this email, but trust me, it
is). When I run the report, Oracle throws an "invalid column name"
exception. But when I paste the cooked query into sqlplus, it processes just
fine, and returns a result set as I'd expect.
I'm running ireports 0.2.2, jasperreports 0.5.0, jvm 1.4, against an Oracle
9i database. Other reports in this same run fine -- I just ran one with the
following query:
SELECT * FROM $P!{TableName}
WHERE $P!{WhereClause}
ORDER BY 3, 1
...so my installation is sound, I believe, and some queries with the
two-pass interpolation are working.
Any clues? It looks like it should work, only it doesn't. Help!
Many thanks,
Harry
PS xml file for trouble report is attached.
______________________________________________
We are the ones we've been waiting for.
--Elders, Hopi Nation, Oraibi AZ
DekDetailSalesReport.xml
(text/xml, 17 KB)
<?xml version="1.0" encoding="UTF-8" ?>
<!-- Created with iReport - A designer for JasperReports -->
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport
name="DekDetailSalesReport"
columnCount="1"
printOrder="Vertical"
orientation="Portrait"
pageWidth="595"
pageHeight="842"
columnWidth="535"
columnSpacing="0"
leftMargin="30"
rightMargin="30"
topMargin="20"
bottomMargin="20"
whenNoDataType="NoPages"
isTitleNewPage="false"
isSummaryNewPage="false">
<parameter name="GroupTable" isForPrompting="false" class="java.lang.String">
<parameterDescription><![CDATA[The name of the grouping table]]></parameterDescription>
<defaultValueExpression><![CDATA["Chain"]]></defaultValueExpression>
</parameter>
<parameter name="MfgTable" isForPrompting="false" class="java.lang.String">
<parameterDescription><![CDATA[The table containing the manufacturer/pick info]]></parameterDescription>
<defaultValueExpression><![CDATA["Pick"]]></defaultValueExpression>
</parameter>
<parameter name="MfgWhereClause" isForPrompting="false" class="java.lang.String">
<parameterDescription><![CDATA[The filtering applied to the manufacturer table, if any]]></parameterDescription>
<defaultValueExpression><![CDATA[" AND Truckload.Pick=Manufacturer.company"]]></defaultValueExpression>
</parameter>
<parameter name="TruckloadTable" isForPrompting="false" class="java.lang.String">
<parameterDescription><![CDATA[The name of the truckload table or view]]></parameterDescription>
<defaultValueExpression><![CDATA["Truckload"]]></defaultValueExpression>
</parameter>
<parameter name="TruckloadWhereClause" isForPrompting="false" class="java.lang.String">
<parameterDescription><![CDATA[The truckload filtering, if any]]></parameterDescription>
<defaultValueExpression><![CDATA[" AND DekInvoice.Truckload=Truckload.Truckload"]]></defaultValueExpression>
</parameter>
<parameter name="InvoiceStatusWhereClause" isForPrompting="false" class="java.lang.String">
<parameterDescription><![CDATA[The status filtering, if any]]></parameterDescription>
<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
</parameter>
<parameter name="StartDate" isForPrompting="false" class="java.lang.String">
<parameterDescription><![CDATA[The start date]]></parameterDescription>
<defaultValueExpression><![CDATA["12/01/2003"]]></defaultValueExpression>
</parameter>
<parameter name="EndDate" isForPrompting="false" class="java.lang.String">
<parameterDescription><![CDATA[The end date]]></parameterDescription>
<defaultValueExpression><![CDATA["12/31/2003"]]></defaultValueExpression>
</parameter>
<queryString><![CDATA[SELECT $P!{GroupTable}.name AS GroupName, Outlet.Name AS OutletName, Address.City, State.State, Invoice, DekInvoice.InvoiceDate, BlocksShipped FROM DekInvoice, State, Outlet, Chain, $P!{MfgTable}, $P!{TruckloadTable}, Address WHERE DekInvoice.Dropoff = Outlet.Outlet $P!{TruckloadWhereClause} $P!{InvoiceStatusWhereClause} $P!{MfgWhereClause} AND DekInvoice.ShippingAddress=Address.Address AND Outlet.ParentCompany=Chain.Chain AND Address.State=State.State AND DekInvoice.InvoiceDate BETWEEN '$P!{StartDate}' AND '$P!{EndDate}' ORDER BY $P!{GroupTable}.name, InvoiceDate, Outlet.Name
]]></queryString>
<field name="GroupName" class="java.lang.String"/>
<field name="OutletName" class="java.lang.String"/>
<field name="City" class="java.lang.String"/>
<field name="State" class="java.lang.String"/>
<field name="Invoice" class="java.lang.Integer"/>
<field name="BlocksShipped" class="java.lang.Integer"/>
<field name="InvoiceDate" class="java.util.Date"/>
<variable name="PageTitle" class="java.lang.String" resetType="Report" calculation="Nothing">
<variableExpression><![CDATA["Sales for " + $F{GroupName}]]></variableExpression> </variable>
<variable name="SumOfBlocksShipped" class="java.lang.Integer" resetType="Group" resetGroup="GroupName" calculation="Sum">
<variableExpression><![CDATA[$F{BlocksShipped}]]></variableExpression> </variable>
<variable name="CityState" class="java.lang.String" resetType="Report" calculation="Nothing">
<variableExpression><![CDATA[$F{City} + ", " + $F{State}]]></variableExpression> </variable>
<variable name="PrettyDate" class="java.lang.String" resetType="Report" calculation="Nothing">
<variableExpression><![CDATA[(new SimpleDateFormat("MM/dd/yyyy")).format($F{InvoiceDate})]]></variableExpression> </variable>
<variable name="TotalTitle" class="java.lang.String" resetType="Report" calculation="Nothing">
<variableExpression><![CDATA["Total Blocks Shipped For " + $F{GroupName} + ":"]]></variableExpression> </variable>
<group name="GroupName" isStartNewColumn="false" isStartNewPage="true" isResetPageNumber="false" isReprintHeaderOnEachPage="false" minHeightToStartNewPage="0" >
<groupExpression><![CDATA[$F{GroupName}]]></groupExpression>
<groupHeader>
<band height="0" isSplitAllowed="true" >
</band>
</groupHeader>
<groupFooter>
<band height="31" isSplitAllowed="true" >
<line direction="TopDown">
<reportElement
mode="Opaque"
x="2"
y="3"
width="530"
height="0"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<graphicElement stretchType="NoStretch" pen="Thin" fill="Solid" />
</line>
<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement
mode="Opaque"
x="450"
y="4"
width="83"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<textFieldExpression class="java.lang.Integer"><![CDATA[$V{SumOfBlocksShipped}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement
mode="Opaque"
x="3"
y="4"
width="443"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$V{TotalTitle}]]></textFieldExpression>
</textField>
</band>
</groupFooter>
</group>
<background>
<band height="0" isSplitAllowed="true" >
</band>
</background>
<title>
<band height="0" isSplitAllowed="true" >
</band>
</title>
<pageHeader>
<band height="111" isSplitAllowed="true" >
<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement
mode="Opaque"
x="3"
y="13"
width="530"
height="27"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Center" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="18" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$V{PageTitle}]]></textFieldExpression>
</textField>
<line direction="BottomUp">
<reportElement
mode="Opaque"
x="3"
y="104"
width="530"
height="0"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<graphicElement stretchType="NoStretch" pen="Thin" fill="Solid" />
</line>
<staticText>
<reportElement
mode="Opaque"
x="3"
y="82"
width="145"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<text><![CDATA[Outlet]]></text>
</staticText>
<staticText>
<reportElement
mode="Opaque"
x="148"
y="82"
width="135"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<text><![CDATA[City, State]]></text>
</staticText>
<staticText>
<reportElement
mode="Opaque"
x="281"
y="82"
width="82"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<text><![CDATA[Invoice]]></text>
</staticText>
<staticText>
<reportElement
mode="Opaque"
x="363"
y="82"
width="87"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<text><![CDATA[Date]]></text>
</staticText>
<staticText>
<reportElement
mode="Opaque"
x="450"
y="82"
width="83"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<text><![CDATA[Blocks ]]></text>
</staticText>
</band>
</pageHeader>
<columnHeader>
<band height="0" isSplitAllowed="true" >
</band>
</columnHeader>
<detail>
<band height="28" isSplitAllowed="true" >
<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement
mode="Opaque"
x="3"
y="4"
width="145"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$F{OutletName}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement
mode="Opaque"
x="148"
y="4"
width="135"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$V{CityState}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement
mode="Opaque"
x="281"
y="4"
width="82"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<textFieldExpression class="java.lang.Integer"><![CDATA[$F{Invoice}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement
mode="Opaque"
x="363"
y="4"
width="87"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$V{PrettyDate}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement
mode="Transparent"
x="450"
y="4"
width="83"
height="21"
forecolor="#000000"
backcolor="#FFFFFF"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false"/>
<textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single">
<font fontName="SansSerif" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />
</textElement>
<textFieldExpression class="java.lang.Integer"><![CDATA[$F{BlocksShipped}]]></textFieldExpression>
</textField>
</band>
</detail>
<columnFooter>
<band height="0" isSplitAllowed="true" >
</band>
</columnFooter>
<pageFooter>
<band height="0" isSplitAllowed="true" >
</band>
</pageFooter>
<summary>
<band height="0" isSplitAllowed="true" >
</band>
</summary>
</jasperReport>