Date problem

[abu_gurnah]

I have a problem passing date parameter from java into jasperreport, please help!

 

Im getting empty output

 

This is a call from java

Code:

Date d1=null,d2=null;
      String result1="",result2="";
      SimpleDateFormat df=new SimpleDateFormat("dd MMM yyyy"«»);
      
      Map<String, Object> params = new HashMap<String, Object>();
      
      try {
	 d1=df.parse("1 april 2007"«»);
	 result1=df.format(d1);
	 
	 d2=df.parse("10 april 2007"«»);
	 result2=df.format(d2);
	 
      } catch (ParseException ex) {
	 ex.printStackTrace();
      }
      
      params.put("start_date",result1);
      params.put("last_date", result2);
      params.put("currency_type","834"«»);

 

And this is my parameter and sql in jasper report

 

Code:

[code] <parameter name="first_date" isForPrompting="false" class="java.lang.String"> <parameterDescription><![CDATA[it takes the first date]]></parameterDescription> </parameter> <parameter name="last_date" isForPrompting="false" class="java.lang.String"> <parameterDescription><![CDATA[it takes last date]]></parameterDescription> </parameter> <parameter name="currency_type" isForPrompting="false" class="java.lang.String"> <parameterDescription><![CDATA[the currency type]]></parameterDescription> </parameter> <queryString><![CDATA[select a.card_number,a.transaction_local_date,m.company_name,a.trace_audit_number,a.transaction_fee,a.transaction_amount,a.transaction_currency from autho_activity a,ptserv p,merchant m where a.card_acceptor_id = p.outlet_number and p.merchant_number = m.merchant_number and to_date(a.transaction_local_date) between TO_DATE($P{first_date}, 'dd/mm/yy') and TO_DATE($P{last_date}, 'dd/mm/yy') and a.transaction_currency = $P{currency_type} and a.acquirer_bank='983411']]></queryString>

 

im using oracle 9i

[Juriy]

Why do you use:

 

d2 = df.parse("10 april 2007");

result2 = df.format(d2);

 

instead of simply

 

d2 = "10 april 2007"; ???

 

 

As I see - the problem is that you pass your date in "dd MMM yyyy" format, and oracle expects 'dd/mm/yy' format:

TO_DATE($P{first_date}, 'dd/mm/yy')

 

 

Try to do it like this:

result2 = new SimpleDateFormat("dd/MM/yyyy").format(d2);

[abu_gurnah]

I changed the date in every format even if i do what you have said still the same error.

 

Can you give another alternative

[abu_gurnah]

Try what you have said but still errors

 

even i tried doing this

 

Code:

params.put("start_date","01/04/2007"«»);
params.put("last_date", "10/04/2007"«»);
params.put("currency_type","834"«»);

 

Code:

[code] <parameter name="first_date" isForPrompting="false" class="java.lang.String"> <parameterDescription><![CDATA[it takes the first date]]></parameterDescription> </parameter> <parameter name="last_date" isForPrompting="false" class="java.lang.String"> <parameterDescription><![CDATA[it takes last date]]></parameterDescription> </parameter> <parameter name="currency_type" isForPrompting="false" class="java.lang.String"> <parameterDescription><![CDATA[the currency type]]></parameterDescription> </parameter> <queryString><![CDATA[select a.card_number,a.transaction_local_date,m.company_name,a.trace_audit_number,a.transaction_fee,a.transaction_amount,a.transaction_currency from autho_activity a,ptserv p,merchant m where a.card_acceptor_id = p.outlet_number and p.merchant_number = m.merchant_number and to_date(a.transaction_local_date) between TO_DATE($P{first_date}, 'dd/mm/yy') and TO_DATE($P{last_date}, 'dd/mm/yy') and a.transaction_currency = $P{currency_type} and a.acquirer_bank='983411']]></queryString>

 

but still in vain...

 

I going to cry :blush: :blush: :S :S

[Juriy]

Hmm, that's a bit strange.

 

I've got this code perfectly working for me:

 

Code:

...
Map parameters = new HashMap();
parameters.put("dateFrom", "01.01.2007"«»);
parameters.put("dateTo", "01.04.2007"«»);
...

 

And then

<parameter name="dateFrom" class="java.lang.String" />

<parameter name="dateTo" class="java.lang.String" />

 

Code:

[code]<queryString> <![CDATA[select * from myschema.mytable t where t.dateofaddingrecord between TO_DATE($P{dateFrom}, 'dd.mm.yy') and TO_DATE($P{dateTo}, 'dd.mm.yy')]]> </queryString>

 

Maybe the problem is not in DateFormat? Have you tried to execute your query at the actual DB with appropriate parameters?

 

If you are absolutely sure that your query is correct and it is actually returns data - use sqlMonitor (from TOAD distribution) to monitor the query that is actually sent by JasperReprots.

 

Try to hardcode the parameters right in the query and create the report. Is there any data in it?

[abu_gurnah]

funny thing is, if i hard code the date.. it return the data and i see it in preview, but the problem is with the parameter, is there anything to do with drivers.

[Juriy]

So, when you hardcode param values in the query - you see the data in the report. And you're passing your parameters in the same format, as you hardcoded them. And it doesn't work with params?

 

My boss says "There's no miracles in IT". ;) Try to output passed params values in the report. Compare the values with those you've hardcoded. I guess you will find some differences.

 

BTW, what version of jasperreports engine do you use?

[abu_gurnah]

heheh i will try that...

 

thanks! if doesnt work i will i will.. or i dont know what to do.. i will come with the feedback.. thanks for your support..

[Teodor Danciu]

Hi,

 

If the parameters are dates, you should let them be dates, not strings.

Declare the report parameters as java.util.Date and pass the values as Date objects not String.

In the query, don't attempt to make dates out of strings and just put the parameter placeholder without the TO_DATE function.

 

I hope this helps.

Teodor

[avu]

Not sure how you have your reports setup, but if you are using subreport, make sure that you pass in the parameters to the subreport and if you are using subdataset make sure you also pass in you parameters to your subdatasets.

 

This has happened to me many time and it's because I forgot to pass in my input parameters to my subreports or subdatasets.

 

However, if you are trying to pass dates into a store procedure --- I think there is a problem with it at this time.

 

I hope this helps.

 

Ann

[abu_gurnah]

Well, i just wanted to pass the parameter through the simple report, but I tried another alternatives and it work. let me paste the code here!

 

I do everything from java then pass the resultset to jasper, this is easier. Sorry for not sharing this with you.

 

Code:

String reportSource = "./report/templates/rptTourOperatorCardWise.jrxml";
      String reportDest = "./report/results/rptTourOperatorCardWise.jrxml";
      
      
      ResultSet resultSet;
      try {
	 SimpleDateFormat sd=new SimpleDateFormat("dd MMM yyyy"«»);
	 String sdate=sd.format(cmbTOCWStartDate.getDate());
	 String edate=sd.format(cmbTOCWEndDate.getDate());
	 
	 Map<String, Object> params = new HashMap<String, Object>();
	 params.put("fromDate",sdate);
	 params.put("toDate",edate);
	 
	 String strSQL="select * from table ";
	 
	 resultSet = statement.executeQuery(strSQL);
	 JRResultSetDataSource resultSetDataSource = new JRResultSetDataSource(resultSet);
	 
	 JasperReport jasperReport = JasperCompileManager.compileReport(reportSource);
	 
	 JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, params, resultSetDataSource);
	 
	 JasperExportManager.exportReportToPdfFile(
	   jasperPrint, reportDest);
	 JasperViewer jasperViewer= new JasperViewer(jasperPrint,false);
	 
	 jasperViewer.setVisible(true);
	 
	 resultSet.close();
      }
      
      catch (JRException e) {
	 e.printStackTrace();
      }
      
      // Exception handling for the DriverManager.getConnection method.
      catch (SQLException ex) {
	 ex.printStackTrace();
      }

 

bear in mind I already open the Connection, and Statement object.

 

And a querystring tag for jasper report should be remove, but i see no difference if you leave the tag.

 

Thanks

Post edited by: abu_gurnah, at: 2007/04/25 06:11

[javaiiispl]

 I am new ireport..

can anyone help me how to pass date value from java to ireport(jasper).....