Error With Sql And Jasper Report

I use ireport 1.3.3 and jasper report 1.3.3 to create a pdf file.
If I use ireport it works, but if I execute my web application it returns to me this error:

Error executing SQL statement for : presup

My jrxml is:

Code:

---

...
<parameter name="SQLquery" isForPrompting="false" class="java.lang.String">
<defaultValueExpression ><![CDATA["select kc.de_buys, kc.nm_client, kc.tx_ship_notes, kc.tx_comments, kol.nm_product , kol.nu_quantity from k_buys
kc inner join k_buys_lines kcl on kc.gu_buys= kcl.gu_buys inner join k_order_lines kol on kol.gu_order = kcl.gu_order"]]></defaultValueExpression>
</parameter>
<queryString>$P!{SQLquery}</queryString>

<field name="de_buys" class="java.lang.String"/>
<field name="nm_client" class="java.lang.String"/>
<field name="tx_ship_notes" class="java.lang.String"/>
<field name="tx_comments" class="java.lang.String"/>
<field name="nm_product" class="java.lang.String"/>
<field name="nu_quantity" class="java.lang.Double"/>
...

---

My jsp is:

Code:

---


String connectString = "jdbcostgresql://172.27.4.15:5432/cult/common";
String userBD = "XXXXXXXX";
String passwordBD = ""XXXXXXXX";
String driver = "org.postgresql.Driver";
Class.forName(driver);

Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(connectString, userBD, passwordBD);


String sql = "select kc.de_buys, kc.nm_client, kc.tx_ship_notes, kc.tx_comments, kol.nm_product , kol.nu_quantity from k_buys
 inner join k_buys_lines kcl on kc.gu_buys= kcl.gu_buys inner join k_order_lines kol on kol.gu_order = kcl.gu_order
where kc.gu_buys= '" + code + "'";

System.setProperty("jasper.reports.compile.class.path",
application.getRealPath("../WEB-INF/lib/jasperreports-1.3.3.jar") + System.getProperty("path.separator")
+ application.getRealPath("../WEB-INF/classes/"));

System.setProperty("jasper.reports.compile.temp", application.getRealPath("tmp"));
JasperCompileManager.compileReportToFile(applicati on.getRealPath("shop/report/presup.jrxml"));

File reportFile = new File(application.getRealPath("shop/report/presup.jasper"));

Map parameters = new HashMap();

parameters.put("SQLquery", sql);

byte[] bytes = JasperRunManager.runReportToPdf(reportFile.getPath (), parameters, conn);
...

---

so? can you help me?

I guess that you want to specify the gu_buys but by what ? Was it user input ?
check this, maybe it is not necessary

If I were you, I'd put your query in the query section and declare parameter code (?) which you use like this:

Code:

---

select
kc.de_buys,
kc.nm_client,
kc.tx_ship_notes,
kc.tx_comments,
kol.nm_product,
kol.nu_quantity
from
k_buys kc
inner join k_buys_lines kcl on kc.gu_buys= kcl.gu_buys
inner join k_order_lines kol on kol.gu_order = kcl.gu_order
where
kc.gu_buys= $P{code}

and in your JSP:
parameters.put("some_value", code);

---

Hi,

i am also facing the same problem
can somebody help me out to solve this problem

Hi,
U will put the query in <queryString> tag

Oterwise,try the below code.

Java Code

Quote:

---

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;

public class GenerateReport
{
public static void main(String args[]) throws Exception
{
java.sql.Connection connection = null;
ResultSet resultSet = null;
Statement statement = null;
String reportFile = "C:\\Recent Jasper\\GenerateReport.jrxml";
Class.forName("oracle.jdbc.driver.OracleDriver");
String query="Select USERID,CURRENCYCODE,NAME,USERTYPE from GWUSER ;
connection = DriverManager.getConnection("jdbc:oracle:thin:@172 .16.0.81:1521:CTSINWDT","ctsinwuser","ctsinwpwd123 ");
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
JasperReport jasperReport = JasperCompileManager.compileReport(reportFile);
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, new HashMap(),new JRResultSetDataSource(resultSet));
if(jasperPrint.getPages().isEmpty()== false)
{
JasperExportManager.exportReportToPdfFile(jasperPr int,"C://1.pdf");
System.out.println("your file updated to pdf file plz check pdf file on desktop");
}

}
}

---

JRXML Code

Quote:

---

<?xml version="1.0"?>

<!DOCTYPE jasperReport PUBLIC "-//JasperReports//DTD Report Design//EN" "http://jasperReports.sourceforge.net...sperreport.dtd">

<jasperReport
name="SimpleReport"
columnCount="1"
printOrder=" Vertical"
orientation="Portrait"
pageWidth="595"
pageHeight="400"
columnWidth="535"
columnSpacing="0"
leftMargin="30"
rightMargin="30"
topMargin="20"
bottomMargin="20"
whenNoDataType="NoPages"
isTitleNewPage="false"
isSummaryNewPage="false">


<field name="USERID" class="java.lang.String"/>
<field name="CURRENCYCODE" class="java.lang.String"/>
<field name="NAME" class="java.lang.String"/>
<field name="USERTYPE" class="java.lang.String"/>



<title>
<band height="50" isSplitAllowed="true">
<staticText>

<reportElement
mode="Transparent"
x="108"
y="6"
width="345"
height="39"
forecolor="#FF3300"
backcolor="#FFFFFF"
key="staticText-1"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Center"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">

<font
fontName="Arial"
pdfFontName="Helvetica-Bold"
size="10"
isBold="true"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<text><![CDATA[User List]]></text>
</staticText>
</band>
</title>

<pageHeader>
<band height="18">

<staticText>
<reportElement
mode="Transparent"
x="0"
y="0"
width="50"
height="18"
forecolor="#0000CC"
backcolor="#FFFFFF"
key="staticText-2"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Left"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">
<font
fontName="Arial"
pdfFontName="Helvetica-Bold"
size="8"
isBold="true"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<text><![CDATA[USERID]]></text>
</staticText>

<staticText>
<reportElement
mode="Transparent"
x="140"
y="0"
width="90"
height="18"
forecolor="#0000CC"
backcolor="#FFFFFF"
key="staticText-2"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Left"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">
<font
fontName="Arial"
pdfFontName="Helvetica-Bold"
size="8"
isBold="true"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<text><![CDATA[CURRENCY CODE]]></text>
</staticText>

<staticText>
<reportElement
mode="Transparent"
x="280"
y="0"
width="50"
height="18"
forecolor="#0000CC"
backcolor="#FFFFFF"
key="staticText-2"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Left"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">
<font
fontName="Arial"
pdfFontName="Helvetica-Bold"
size="8"
isBold="true"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<text><![CDATA[NAME]]></text>
</staticText>

<staticText>
<reportElement
mode="Transparent"
x="420"
y="0" width="50"
height="18"
forecolor="#0000CC"
backcolor="#FFFFFF"
key="staticText-2"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Left"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">

<font
fontName="Arial"
pdfFontName="Helvetica-Bold"
size="8"
isBold="true"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<text><![CDATA[USERTYPE]]></text>
</staticText>


</band>
</pageHeader>

<detail>
<band height="18">

<textField
isStretchWithOverflow="false"
pattern=""
isBlankWhenNull="false"
evaluationTime="Now"
hyperlinkType="None"
hyperlinkTarget="Self"
bookmarkLevel="0">

<reportElement
mode="Transparent"
x="0"
y="0"
width="100"
height="18"
forecolor="#000000"
backcolor="#FFFFFF"
key="textField"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Left"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">
<font
fontName="Arial"
pdfFontName="Helvetica"
size="8"
isBold="false"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<textFieldExpression class="java.lang.String">
<![CDATA[$F{USERID}]]>
</textFieldExpression>
</textField>

<textField
isStretchWithOverflow="false"
pattern=""
isBlankWhenNull="false"
evaluationTime="Now"
hyperlinkType="None"
hyperlinkTarget="Self"
bookmarkLevel="0">

<reportElement
mode="Transparent"
x="140"
y="0"
width="50"
height="18"
forecolor="#000000"
backcolor="#FFFFFF"
key="textField"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Left"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">
<font
fontName="Arial"
pdfFontName="Helvetica"
size="8"
isBold="false"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<textFieldExpression class="java.lang.String">
<![CDATA[$F{CURRENCYCODE}]]>
</textFieldExpression>
</textField>

<textField
isStretchWithOverflow="false"
pattern=""
isBlankWhenNull="false"
evaluationTime="Now"
hyperlinkType="None"
hyperlinkTarget="Self"
bookmarkLevel="0">

<reportElement
mode="Transparent"
x="280" y="0"
width="100"
height="18"
forecolor="#000000"
backcolor="#FFFFFF"
key="textField"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Left"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">
<font
fontName="Arial"
pdfFontName="Helvetica"
size="8"
isBold="false"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<textFieldExpression class="java.lang.String">
<![CDATA[$F{NAME}]]>
</textFieldExpression>
</textField>

<textField
isStretchWithOverflow="false"
pattern=""
isBlankWhenNull="false"
evaluationTime="Now"
hyperlinkType="None"
hyperlinkTarget="Self"
bookmarkLevel="0">

<reportElement
mode="Transparent"
x="420"
y="0"
width="50"
height="18"
forecolor="#000000"
backcolor="#FFFFFF"
key="textField"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Left"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">
<font
fontName="Arial"
pdfFontName="Helvetica"
size="8"
isBold="false"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<textFieldExpression class="java.lang.String">
<![CDATA[$F{USERTYPE}]]>
</textFieldExpression>
</textField>


</band>
</detail>

<columnFooter>
<band height="9" isSplitAllowed="true" />
</columnFooter>

<pageFooter>
<band height="40" isSplitAllowed="true">
<line direction="TopDown">
<reportElement x="0" y="10" width="515" height="1" positionType="FixRelativeToTop" stretchType="NoStretch" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false" />
<graphicElement />
</line>
<textField isStretchWithOverflow="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" bookmarkLevel="0">
<reportElement x="200" y="20" width="80" height="15" positionType="FixRelativeToTop" stretchType="NoStretch" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false" />
<textElement textAlignment="Right" />
<textFieldExpression class="java.lang.String">
<![CDATA[ "Page " + String.valueOf($V{PAGE_NUMBER}) + " of"]]>
</textFieldExpression>
</textField>
<textField evaluationTime="Report" isStretchWithOverflow="false" hyperlinkType="None" hyperlinkTarget="Self" bookmarkLevel="0">
<reportElement x="280" y="20" width="75" height="15" positionType="FixRelativeToTop" stretchType="NoStretch" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false" />
<textElement />
<textFieldExpression class="java.lang.String">
<![CDATA[ " " + String.valueOf($V{PAGE_NUMBER})
]]>
</textFieldExpression>
</textField>
</band>
</pageFooter>


<lastPageFooter>
<band height="20" isSplitAllowed="true" >
<staticText>

<reportElement
mode="Transparent"
x="0"
y="0"
width="345"
height="18"
forecolor="#FF3300"
backcolor="#FFFFFF"
key="staticText-1"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Center"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">

<font
fontName="Arial"
pdfFontName="Helvetica-Bold"
size="10"
isBold="true"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<text><![CDATA[End]]></text>
</staticText>
</band>
</lastPageFooter>
<summary>
<band height="35" isSplitAllowed="true" >
<staticText>

<reportElement
mode="Transparent"
x="108"
y="6"
width="345"
height="29"
forecolor="#FF3300"
backcolor="#FFFFFF"
key="staticText-1"
stretchType="NoStretch"
positionType="FixRelativeToTop"
isPrintRepeatedValues="true"
isRemoveLineWhenBlank="false"
isPrintInFirstWholeBand="false"
isPrintWhenDetailOverflows="false" />

<box
topBorder="None"
topBorderColor="#000000"
leftBorder="None"
leftBorderColor="#000000"
rightBorder="None"
rightBorderColor="#000000"
bottomBorder="None"
bottomBorderColor="#000000" />

<textElement
textAlignment="Center"
verticalAlignment="Top"
rotation="None"
lineSpacing="Single">

<font
fontName="Arial"
pdfFontName="Helvetica-Bold"
size="10"
isBold="true"
isItalic="false"
isUnderline="false"
isPdfEmbedded="false"
pdfEncoding="Cp1252"
isStrikeThrough="false" />
</textElement>

<text><![CDATA[User List]]></text>
</staticText>
</band>
</summary>


</jasperReport>

---