embedded sql query containing Date range

[2004 IR Help]

By: cryan - cryan-valista

embedded sql query containing Date range

2005-10-03 10:31

 

I cannot get sql queries to work within my reports if they contain date ranges. I'd appreciate any help. For example, I define a date_from param of type java.util.Date a value of '2004/10/03 15:01:28' and date_to a value of '2006/10/03 15:01:28' for the .jrxml query:

 

<queryString><![CDATA[sELECT e.BOOKING_DATE, e.ADJUSTMENT, e.TRANSFER_ID, e.CURRENCY, e.ENTRY_ID, t.DESCRIPTION from ENTRY e, TRANSFER t WHERE (e.BOOKING_DATE BETWEEN "$P!{date_from}" AND "$P!{date_to}") AND e.TRANSFER_ID = t.TRANSFER_ID ORDER by e.BOOKING_DATE]]></queryString>

 

this generates an empty report, even though running the sql directly in DbVis works fine with actual substitutions so the query is a valid one. Any clues on how to get this to work? Also, any idea how to turn on debugging to see exactly what sql is being executed? I tried log4j config:

 

<logger name="net.sf.jasperreports">

<level value="DEBUG" />

<appender-ref ref="roll" />

</logger>

 

but I only see the query prior to param substitution ie:

 

03 Oct 2005 17:53:14,515 DEBUG Digester - [CallMethodRule]{jasperReport/queryString} Call net.sf.jasperreports.engine.design.JRDesignQuery.setText(SELECT e.BOOKING_DATE, e.ADJUSTMENT, e.TRANSFER_ID, e.CURRENCY, e.ENTRY_ID, t.DESCRIPTION from ENTRY e, TRANSFER t WHERE (e.BOOKING_DATE BETWEEN "$P!{date_from}" AND "$P!{date_to}") AND e.TRANSFER_ID = t.TRANSFER_ID ORDER by e.BOOKING_DATE/java.lang.String)

 

I'd very much appreciate any pointers! thanks, craig.

 

 

 

 

 

By: Lucian Chirita - lucianc

RE: embedded sql query containing Date range

2005-10-06 03:42

Hi

 

Try to change your query to .. BETWEEN $P{date_from} and $P{date_to} .. This would result in the Date objects being used as IN parameters for a prepared statement, which is safer than modifying the query.

 

HTH,

Lucian