Parameters in Ireport / Jasperreports

I´m using a tool called runjasper.jar to fill a JRXML File. In that file, I have a parameter called querywhere. This string will be appended to the Connection SQL (MySQL DB) via $P!{querywhere}, but I got some problems by using some chars. for example: ", ' and ( and so on.

I´d opened the java file in NetBeans to debug it. At that line:

JasperPrint print = JasperFillManager.fillReport(report, parameters, conn);

Parameters is a string with the name querywhere that is equal to:

WHERE my_company_id ='12345'

(copied from NetBeans debugger) That is OK.

If I print the Reportvariable

$P{querywhere}

in the report I will have a line like this:

WHERE m5_pdc.m5_company_id =\'12010901\'

If I set the parameter escaped by \ to

WHERE my_company_id =\'12345\'

the $P{querywhere} param is:

WHERE my_company_id =\\'12345\\'

So: Short question, how can I transfer a string like:

WHERE my_company_id ='12345'

to the report.

Any idea how to transfer chars like " and '? It seems that escape char \ doesn't work in this case.

Thank you and best regards

Christian

Solution

You can use the expression like this: SELECT .. FROM .. WHERE strAttr=$P{strFilter} in jrxml file.

The snippet from the report's template (jrxml file):

<parameter name="strFilter" class="java.lang.String"/>
<queryString>
    <![CDATA[SELECT city FROM address WHERE city=$P{strFilter}]]>
</queryString>
<field name="city" class="java.lang.String"/>

This code works fine for strings with single quotation, double quotation and parenthesis symbols:

Map<String, Object> params = new HashMap<String, Object>();
JasperReport jasperReport = JasperCompileManager.compileReport(reportSource);
//params.put("strFilter", "Berl)in");
//params.put("strFilter", "Muni\"ch");
params.put("strFilter", "Stuttga'rt");
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, params, connection);

JasperExportManager.exportReportToPdfFile(jasperPrint, pdfFileName);

Or you can use the expression like this: SELECT .. FROM .. WHERE $P!{whereClause}.

The snippet from the report's template (jrxml file):

<parameter name="strFilter" class="java.lang.String"/>
<parameter name="whereClause" class="java.lang.String"/>
<queryString>
    <![CDATA[SELECT city FROM address WHERE city=$P{strFilter} $P!{whereClause}]]>
</queryString>

This code works fine for strings with single quotation, double quotation and parenthesis symbols:

Map<String, Object> params = new HashMap<String, Object>();
JasperReport jasperReport = JasperCompileManager.compileReport(reportSource);
//params.put("strFilter", "Berl)in");
//params.put("whereClause", "AND city = 'Berl)in'");
//params.put("strFilter", "Muni\"ch");
//params.put("whereClause", "AND city = 'Muni\"ch'");
params.put("strFilter", "Stuttga'rt");
params.put("whereClause", "AND city = 'Stuttga''rt'");
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, params, connection);

JasperExportManager.exportReportToPdfFile(jasperPrint, pdfFileName);