Error With Sql And Jasper Report

**Heather** · 06-23-2007, 01:01 AM

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:

Java 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:

Java 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?

**Jack** · 06-30-2007, 04:01 PM

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

Ed · 06-30-2007, 04:05 PM

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

Java 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);

**radhakrishna_m** · 07-31-2007, 06:07 AM

Hi,

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

**Deepa** · 12-03-2008, 11:31 AM

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

Oterwise,try the below code.

Java Code

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

<?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">


</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">

</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">

</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">

</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">


</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">

</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">

</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">

</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">

</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">


</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">


</textElement>

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

</jasperReport>

Thread: Error With Sql And Jasper Report

LinkBack

Thread Tools

Search Thread

Display

Error With Sql And Jasper Report

error in jasper reports

Re:

Similar Threads

Error loading object from file : C:\Date.jrxml Jass Report

Jasper report + JPQL

org.apache.jasper.JasperException: null

Jasper iReports and Java

Jasper Report + iReport + NetBeans 4

Bookmarks

Bookmarks

Posting Permissions

Recent Blog Posts

sorry for all the questions

GSLB.me Dynamic DNS Client 0.1

Digtwit 1.2

Mo Da Browser 1.beta.01

sql2o 1.1.7

XWiki 5.1-milestone-2

jOpenDocument 1.3

Jppf 3.3.3

Ametys 0.1

Corn Httpclient 0.1

Corn Exception 0.1

Guangzhou, a house explosion...

chaussures nike pas cher 9mR4t

how to link pdf files with in Jsp,...

HTTP Error 407 Proxy...

Problem with a Simple Histogram...

What is wrong? Stanford SC106a...

if-else prob help....

NewtonRaphson java code help

JavaFX GUI contents not showing up

Using php code in Java?