Jump to content
Changes to the Jaspersoft community edition download ×

java.sql.SQLException: Missing IN or OUT parameter


MarkBaker

Recommended Posts

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 ASC

but 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.

Link to comment
Share on other sites

  • 1 year later...
  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

  • 12 years later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...