title: JasperReports not reading parameters properly?
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>I get the following error when trying to run reports with startdate and enddate parameters.</p>
  
  <pre><code>SQLException: Missing IN or OUT parameter at index:: 1
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>I'm not sure what I'm missing because nearly identical code works in my company's JSP-Tomcat-Servlet based application with JasperReports.</p>
  
  <p>Anyway I'll start showing code and explaining what's going on:</p>
  
  <pre><code>public StreamResponse getReport(String reportTitle, ExportFormat formMode, Date startDate, Date endDate) {
      Map&lt;String,String&gt; 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!!");
              }
          }
      }
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>Here is accompanying debug statements for a given time I get the exception:</p>
  
  <pre><code>[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 -&gt; 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
  </code></pre>
  
  <p>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.</p>
  
  <p>JasperService:</p>
  
  <pre><code>/**
   * Fills the report design loaded from the supplied input resource and returns the generated report object.
   * &lt;p/&gt;
   * if parameter &lt;em&gt;jasperPrint&lt;em&gt; not null, the data filled into &lt;em&gt;jasperPrint&lt;/em&gt;
   * 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 &amp;&amp; dataSource instanceof JRDataSource)
              actualJasperPrint = JasperFillManager.fillReport(jasperReport, parameterMap, (JRDataSource) dataSource);
          else if(dataSource != null &amp;&amp; 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);
      }
  }
  </code></pre>
  
  <p>Report:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;!DOCTYPE jasperReport PUBLIC "-//JasperReports//DTD Report Design//EN" 
   "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd"&gt;
  &lt;jasperReport 
      name="WebappReport1" 
      pageWidth="595" 
      pageHeight="842" 
      columnWidth="555" 
      columnSpacing="0" 
      leftMargin="20" 
      rightMargin="20" 
      topMargin="30" 
      whenNoDataType="AllSectionsNoDetail"
      bottomMargin="30"&gt; 
  &lt;reportFont name="Arial_Normal" isDefault="true" fontName="Arial" size="9" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/&gt;
  &lt;reportFont name="Arial_Bold" isDefault="false" fontName="Arial" size="9" isBold="true" pdfFontName="Helvetica-Bold" pdfEncoding="Cp1252" isPdfEmbedded="false"/&gt;
  &lt;reportFont name="Arial_Italic" isDefault="false" fontName="Arial" size="9" isItalic="true" pdfFontName="Helvetica-Oblique" pdfEncoding="Cp1252" isPdfEmbedded="false"/&gt;
  &lt;parameter name="Title" class="java.lang.String"/&gt;
  &lt;parameter name="StartDate" class="java.lang.String" /&gt;
  &lt;parameter name="EndDate" class="java.lang.String" /&gt;
  
  
  &lt;!--This is report query string used to fill data--&gt;
  &lt;queryString&gt;&lt;![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 
  ]]&gt;&lt;/queryString&gt;
  </code></pre>