Variables in Query

[2006 IR Open Discussion]

By: Chris Green - c_g12

Variables in Query

2003-04-04 17:19

Is it possible to include variables in the SQL Query?

 

The reason I ask is that I want my date parameters to be of class java.util.Date so that I can dynamically query a JasperReport object for its parameters and generate an appropriate UI that corresponds to the class(a calendar for Dates, checkboxes for booleans, etc).

 

I thought I would be able to use a Variable to format the Date parameter into a string that could be included in the Query:

<variable name="SQLStartDate" class="java.lang.String" resetType="Report" calculation="Nothing">

<variableExpression><![CDATA[(new SimpleDateFormat("yyyy-MM-dd")).format($P{StartDate})]]></variableExpression>

</variable>

<variable name="SQLEndDate" class="java.lang.String" resetType="Report" calculation="Nothing">

<variableExpression><![CDATA[(new SimpleDateFormat("yyyy-MM-dd")).format($P{EndDate})]]></variableExpression>

</variable>

 

Then I could include $V{SQLStartDate} and $V{SQLEndDate} in my SQL query in the JasperReport. However this doesn't seem to work so I thought I'd check to see if it's even possible. If not, is there any other way to include java.util.Date objects in the SQL query?