JasperReports not reading parameters properly?

Question

I'm working on a web application written in Java using the Tapestry 5.1.0.5 framework. This framework does not have out-of-the-box support for JasperReports, so I wrote a service that modifies ChenilleKit's JasperReport service. I do not depend on the ChenilleKit version, instead I use the JasperReport 3.5.0 dependency. This may not be necessary information, but it never hurts to be specific.

Anyway, my service works pretty well. I have it built into the webapp and I am able to compile and output basic reports in PDF, XLS, HTML, and CSV formats. However, I'm having a big issue with getting the SQL in the jasperReport XML file(s) to load the parameter map properly.

I get the following error when trying to run reports with startdate and enddate parameters.

SQLException: Missing IN or OUT parameter at index:: 1

SQL knowledge would say this means I have some form of parameter that is not being passed into the SQL. Debug statements indicate to me that I am passing in the parameters alright and that at least some are making their way into the XML report.

For example, I am passing three parameters into a report, Title, StartDate, and EndDate. Title shows up in the rendering of reports, but StartDate and EndDate seem to get lost in translation?

I'm not sure what I'm missing because nearly identical code works in my company's JSP-Tomcat-Servlet based application with JasperReports.

Anyway I'll start showing code and explaining what's going on:

public StreamResponse getReport(String reportTitle, ExportFormat formMode, Date startDate, Date endDate) {
    Map<String,String> parameterMap = loadParameters(reportTitle);
    Connection conn = null;
    OutputStream os = new ByteArrayOutputStream();

    try{
        conn = Report.getConnection();

        Resource resc = new ContextResource(cimpl, "src/main/webapp/reports/"+reportTitle+".xml");

        log.debug("Calling fillAndExport to fetch the report " + reportTitle);
        log.debug("resource="+resc+"\n"+"formMode="+formMode+"\n"+"parameterMap="+parameterMap+"\n"+"conn="+conn+"\n"+
                "outputStream="+os);

        SimpleDateFormat repDate = new SimpleDateFormat("MM/dd/yyyy HH:mm");
        parameterMap.put("StartDate", repDate.format(startDate));
        parameterMap.put("EndDate", repDate.format(endDate));

        log.debug("StartDate into report: " + parameterMap.get("StartDate"));
        log.debug("EndDate into report: " + parameterMap.get("EndDate"));

        js.fillAndExport(resc, formMode, parameterMap, conn, os);
        SimpleDateFormat sdf = new SimpleDateFormat("MMMddyyyy");

        return new JasperStreamResponse((ByteArrayOutputStream) os, formMode, reportTitle+"-"+sdf.format(startDate)+"-"
                +sdf.format(endDate));
    }catch (Exception e){
        log.error("Caught exception while trying to execute the report fillAndExport service method.");
        throw new TapestryException("Issue executing report", e);
    } finally {
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("Could not close the JDBC connection!!");
            }
        }
    }
}

In a nutshell, I load the report resource, and add the StartDate and EndDate parameters (Title is already in the parameterMap). I then make the call to a JasperService that uses fillAndExport to generate the report. If there are no exceptions it is returned to the browser in a stream.

Here is accompanying debug statements for a given time I get the exception:

[DEBUG] AppModule.ReportService Loaded report class: com.moremagic.reports.TriggerReport
[DEBUG] AppModule.ReportService Calling fillAndExport to fetch the report Trigger
[DEBUG] AppModule.ReportService resource=context:src/main/webapp/reports/Trigger.xml
formMode=HTML
parameterMap={Title=Triggering Merchant Commission}
conn=oracle.jdbc.driver.T4CConnection@7c974e4b
outputStream=
[DEBUG] AppModule.ReportService StartDate into report: 08/22/2010 14:19
[DEBUG] AppModule.ReportService EndDate into report: 08/23/2010 14:19
[DEBUG] AppModule.JasperService Constructed configuration: {}
[DEBUG] AppModule.JasperService Invoking method com.moremagic.services.AppModule.buildJasperService(Logger, Map) (at AppModule.java:188).
[DEBUG] AppModule.JasperService Using template -> src/main/webapp/reports/Trigger.xml
[DEBUG] AppModule.JasperService In fillReport, parameterMap is : {EndDate=08/23/2010 14:31, StartDate=08/22/2010 14:31, Title=Triggering Merchant Commission}

[ERROR] AppModule.JasperService Caught exception in fillReport of ReportsServiceImpl {}
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : WebappReport1
Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 1

So as you can see the values are in the parameter map all the way through right until the JasperService calls the JapserReports API. Now I will show the JasperService code and some of the report so you can see the SQL breaking things.

JasperService:

/**
 * Fills the report design loaded from the supplied input resource and returns the generated report object.
 * <p/>
 * if parameter <em>jasperPrint<em> not null, the data filled into <em>jasperPrint</em>
 * instead of returns a new jasper print object.
 *
 * @param jasperPrint
 * @param inputResource the input resource (report template file ".jrxml")
 * @param parameterMap  the parameter map
 * @param dataSource    the datasource, either a JRDataSource or an SQL JDBC Connection.
 *
 * @return
 */
public JasperPrint fillReport(JasperPrint jasperPrint, Resource inputResource, Map parameterMap, Object dataSource) throws JRException
{
    JasperReport jasperReport = doCompileReportSource(inputResource);
    JasperPrint actualJasperPrint;

    logger.debug("In fillReport, parameterMap is : " + parameterMap + "\n startDate: " + parameterMap.get("StartDate") + "\n endDate: " + parameterMap.get("EndDate"));

    try
    {
        if (dataSource != null && dataSource instanceof JRDataSource)
            actualJasperPrint = JasperFillManager.fillReport(jasperReport, parameterMap, (JRDataSource) dataSource);
        else if(dataSource != null && dataSource instanceof Connection)
            actualJasperPrint = JasperFillManager.fillReport(jasperReport, parameterMap, (Connection) dataSource);
        else
            actualJasperPrint = JasperFillManager.fillReport(jasperReport, parameterMap);

        if (jasperPrint != null)
        {
            for (Object page : actualJasperPrint.getPages())
                jasperPrint.addPage((JRPrintPage) page);
        }
        else
            jasperPrint = actualJasperPrint;

        return jasperPrint;
    }
    catch (JRException e)
    {
        logger.error("Caught exception in fillReport of ReportsServiceImpl {}", e);
        throw new JRException(e);
    }
}

Report:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jasperReport PUBLIC "-//JasperReports//DTD Report Design//EN" 
 "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport 
    name="WebappReport1" 
    pageWidth="595" 
    pageHeight="842" 
    columnWidth="555" 
    columnSpacing="0" 
    leftMargin="20" 
    rightMargin="20" 
    topMargin="30" 
    whenNoDataType="AllSectionsNoDetail"
    bottomMargin="30"> 
<reportFont name="Arial_Normal" isDefault="true" fontName="Arial" size="9" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<reportFont name="Arial_Bold" isDefault="false" fontName="Arial" size="9" isBold="true" pdfFontName="Helvetica-Bold" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<reportFont name="Arial_Italic" isDefault="false" fontName="Arial" size="9" isItalic="true" pdfFontName="Helvetica-Oblique" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<parameter name="Title" class="java.lang.String"/>
<parameter name="StartDate" class="java.lang.String" />
<parameter name="EndDate" class="java.lang.String" />


<!--This is report query string used to fill data-->
<queryString><![CDATA[
SELECT 
something
from table b
    where
b.ba_timestamp between to_date( $P!{StartDate}, 'MM/DD/YYYY HH24:MI' ) and to_date( $P!{EndDate}, 'MM/DD/YYYY HH24:MI' )+1 
]]></queryString>

Solution

I think you should either remove the ! in the SQL query or surround your parameters with quotes.

In a jasper query $P{xyz} creates and binds a parameter whereas $P!{xyz} modifies the query string. I think that in your case you're creating an invalid query by appending raw (unquoted) parameters.

More details in this blog post