I've a fairly complex report using a servlet and a system incrementer; but it runs quite happily using iReport. Having finally got my classpath set correctly, I'm trying to run it from a servlet; but am getting the following error. Code:2007-03-21 17:18:23,237 FATAL [com.redeye.reports.beans.RunJasperReport] - Error running jasper report.
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1681)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3280)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:115)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:665)
at net.sf.jasperreports.engine.fill.JRFillDataset.setDatasource(JRFillDataset.java:609)
at net.sf.jasperreports.engine.fill.JRFillDataset.setParameterValues(JRFillDataset.java:598)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:879)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:708)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:666)
at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:402)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:234)
at com.redeye.reports.util.JasperReportUtil.compileToPrint(JasperReportUtil.java:142)
at com.redeye.reports.servlet.RunJasperReportServlet.doGet(RunJasperReportServlet.java:78)
at com.redeye.reports.servlet.RunJasperReportServlet.doPost(RunJasperReportServlet.java:57)[code] The query is also fairly complex: Code:SELECT a.campaign, a.schedule_id, a.schedule_name, a.sent, a.subject, NVL(a.assumed_delivered,0) assumed_Delivered, NVL(a.unsubscribes,0) unsubscribes, a.email_served_by_redeye_yn email_served_by_redeye_yn, NVL(ROUND(DECODE(NVL(a.assumed_delivered,0), 0, 0, a.unsubscribes*100/a.assumed_delivered),2),0) UnsubscribeRate, NVL(b.Opens,0) Opens, NVL(ROUND(DECODE(NVL(a.assumed_delivered,0), 0, 0, b.SumOpens*100/a.assumed_delivered),2),0) OpenRate, NVL(b.Clicks,0) Clicks, NVL(ROUND(DECODE(NVL(a.assumed_delivered,0), 0, 0,b.SumClicks*100/a.assumed_delivered),2),0) ClickRate, NVL(b.Arrivals,0) Arrivals, NVL(b.responded,0) Responded, NVL(b.responses,0) Responses, NVL(b.Items,0) Items, NVL(b.Revenue,0) Revenue, NVL(ROUND(DECODE(NVL(a.assumed_delivered,0), 0, 0,b.SumRespondents*100/a.assumed_delivered),2),0) Conversion, NVL(b.AvgOrderSize ,0) AvgOrderSize, NVL(b.AvgBasketSize ,0) AvgBasketSize, b.Minday Minday, NVL(c.reach,0) reach, c.MinImpressDay MinImpressDay FROM emails_delivered_summary a, (SELECT schedule_id, SUM(NVL(opens,0)) Opens, SUM(NVL(opens,0)) SumOpens, SUM(NVL(clicks,0)) Clicks, SUM(NVL(clicks,0)) SumClicks, SUM(NVL(arrivals,0)) Arrivals, SUM(NVL(respondents,0)) Responded, SUM(NVL(responses,0)) Responses, SUM(NVL(items,0)) Items, SUM(NVL(revenue,0)) Revenue, SUM(NVL(respondents,0)) SumRespondents, ROUND(DECODE(SUM(NVL(respondents,0)), 0, 0,SUM(NVL(revenue,0))/SUM(NVL(respondents,0))),2)AvgOrderSize, ROUND(DECODE(SUM(NVL(items,0)), 0, 0,SUM(NVL(revenue,0))/SUM(NVL(items,0))),2)AvgBasketSize, MIN(DAY) Minday FROM email_metrics_summary_view WHERE 1=1 $P!{START_FILTER} $P!{END_FILTER} $P!{RESPONSE_FILTER} GROUP BY schedule_id) b, (SELECT schedule_id, count(DISTINCT(personid)) reach, MIN(DAY) MinImpressDay FROM email_impress_view WHERE 1=1 $P!{START_FILTER} $P!{END_FILTER} group by schedule_id) c WHERE a.schedule_id = b.schedule_id(+) AND a.schedule_id = c.schedule_id(+) $P!{CAMPAIGN_FILTER} $P!{EMAIL_FILTER} ORDER BY a.campaign ASC,a.sent ASC, a.schedule_name ASCbut it does run happily from iReport. Parameters are all passed as java.lang.String, and are empty strings as default (no nulls). Database is Oracle 10.2.0.1.0, and I'm using the most recent ojdbc14.jar for that version. Any suggestions or help appreciated.
