Error with SQL and jrxml

[Sentinel77]

hi

 

I use ireport 1.3.3 and jasperreport 1.3.3 and I have to list a pdf with several data. If I use the ireport I can see the pdf but if I execute my aplication I receive this error: Error executing SQL statement for : presupuesto

 

My jrxml is:

...

<parameter name="SQLquery" isForPrompting="false" class="java.lang.String">

<defaultValueExpression ><![CDATA["select kc.de_compras, kc.nm_client, kc.tx_ship_notes, kc.tx_comments, kol.nm_product , kol.nu_quantity from k_compras kc inner join k_compras_lines kcl on kc.gu_compras = kcl.gu_compras inner join k_order_lines kol on kol.gu_order = kcl.gu_order"]]></defaultValueExpression>

</parameter>

<queryString>$P!{SQLquery}</queryString>

 

<field name="de_compras" 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:

...

String connectString = "jdbc:postgresql://172.27.4.15:5432/cultura/comun";

String usuarioBD = "XXXXXXXX";

String passwordBD = ""XXXXXXXX";

String driver = "org.postgresql.Driver";

Class.forName(driver);

 

Class.forName(driver).newInstance();

Connection conn = DriverManager.getConnection(connectString, usuarioBD, passwordBD);

 

 

String sql = "select kc.de_compras, kc.nm_client, kc.tx_ship_notes, kc.tx_comments, kol.nm_product , kol.nu_quantity from k_compras kc inner join k_compras_lines kcl on kc.gu_compras = kcl.gu_compras inner join k_order_lines kol on kol.gu_order = kcl.gu_order where kc.gu_compras = '" + codigo + "'";

 

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(application.getRealPath("shop/report/presupuesto.jrxml"));

 

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

 

Map parameters = new HashMap();

 

parameters.put("SQLquery", sql);

 

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

...

_____________________________________________________

 

 

 

I have read several thread of this error but I can't fix it :S

 

 

I wait you help

thanks for all:)

[mar_ass]

To be frank, I don't understand this part:

where kc.gu_compras = '" + codigo + "'";

 

I guess that you want to specify the gu_compras but by what ? Was it user input ?

 

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

 

select

kc.de_compras,

kc.nm_client,

kc.tx_ship_notes,

kc.tx_comments,

kol.nm_product,

kol.nu_quantity

from

k_compras kc

inner join k_compras_lines kcl on kc.gu_compras = kcl.gu_compras

inner join k_order_lines kol on kol.gu_order = kcl.gu_order

where

kc.gu_compras = $P{codigo}

 

and in your JSP:

parameters.put("some_value", codigo);