Results 1 to 5 of 5
  1. #1
    Heather is offline Senior Member
    Join Date
    Jun 2007
    Posts
    164
    Rep Power
    8

    Default 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:
    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?
    Last edited by Heather; 06-30-2007 at 04:07 PM.

  2. #2
    Jack is offline Senior Member
    Join Date
    Jun 2007
    Posts
    130
    Rep Power
    0

    Default

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

  3. #3
    Ed
    Ed is offline Senior Member
    Join Date
    Jun 2007
    Posts
    110
    Rep Power
    0

    Default

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

  4. #4
    Join Date
    Jul 2007
    Posts
    1
    Rep Power
    0

    Default error in jasper reports

    Hi,

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

  5. #5
    Deepa is offline Member
    Join Date
    Nov 2008
    Posts
    85
    Rep Power
    0

    Default Re:

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

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

Similar Threads

  1. Replies: 2
    Last Post: 12-03-2008, 11:14 AM
  2. Jasper report + JPQL
    By tarat in forum Advanced Java
    Replies: 2
    Last Post: 08-01-2008, 06:47 PM
  3. org.apache.jasper.JasperException: null
    By ranjan_ashish26 in forum Java Servlet
    Replies: 1
    Last Post: 03-21-2008, 05:06 AM
  4. Jasper iReports and Java
    By shinky in forum New To Java
    Replies: 2
    Last Post: 08-09-2007, 09:24 AM
  5. Jasper Report + iReport + NetBeans 4
    By Ed in forum NetBeans
    Replies: 2
    Last Post: 07-02-2007, 07:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •