Passing Date parameter do report (Oracle driver)

[pawel_r]

Hi

I was trying to pass Date as parameter to report. Unfortunatelly, the default casting seems not to work good enough (or there is some error with my Oracle driver).

I am using iReports(3.5) (and jasperreports 3.5.3) with following query as DataSource:

SELECT (...) AND chre.ON_PROD_INTRODUCTION_DATE between '$P!{START_DATE}' and '$P!{END_DATE}'

Both parameters are of java Date type.

Parameters:

<parameter name="START_DATE" class="java.util.Date"><defaultValueExpression><![CDATA[new java.util.Date()]]></defaultValueExpression>

</parameter>

<parameter name="END_DATE" class="java.util.Date"><defaultValueExpression><![CDATA[new java.util.Date()]]></defaultValueExpression>

 Driver: ojdbc14.jar 

Database system: Oracle 9i

 

 

I got following error:

Error filling print... Error executing SQL statement for : myreport
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : terminowosc 
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:145) 
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:685)

(...)
Caused by: java.sql.SQLException: ORA-01841: (peÅ‚ny) rok musi być pomiÄ™dzy -4713 i +9999 i nie może być 0 (eng. (full) year must be between -4713 i +9999 and cannot be 0)

Error seems to be obvious, but, as you see above, the default Date is today, so definitely not year 0 and in given interval.

Am I missing some cast? As a I language for report I choose Groovy.

Is there any better way of passing Date parameter to report? I tried to use strings and then to parse them using Oracle build in function, however it gave me even more problems. Firstly, I couldn't set the format of string date properly (DD-MM-YYYY)   and then jasperreport was throwing an error that it doesn't have proper executer for PL/SQL query (it seems the standard jasperreports library doesn;t have it, although it is somewhere in iReports libraries - I haven't found proper jar yet).

 

 

Post Edited by pawel_r at 08/24/2009 21:36

[pawel_r]

Problem solved. I spent a few days thinking about mistake. As it truns out the parameters should be passed as P{START_DATE}, P{END_DATE}, no quotation or exclamation mark (as I read in some tutorial)