Error With Sql And Jasper Report

[Heather]

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?

[Jack]

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]

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

[radhakrishna_m]

Hi,

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