$P! syntax help

[alexmarshall]

Hey everyone,

 

I need some help with the $P! syntax but you should probably have some context first:

 

I'm creating reports to which I'm passing Calendars as parameters which are reflectively bound to the report parameter map from my Spring form backing objects. I want to keep everything as Calendars because they're better for internationalizing my application and normalizing dates / times to timzones. That said, is there a good way of using the $P!{calendarTimestamp} syntax from within queries ? As I understand it, $P!{} essentially just calls .toString() on the passed object to get the text that's inserted into the query at run time and I want to be able to apply formatting for dates. Currently I'm creating extra parameters in my report with expressions like 'new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format($P{calendarTimestamp})' and inserting those strings into my queries using the $P! syntax. I find it a kludgy way of doing things and I was wondering if there's a better way to get date / calendar parameters into my queries ? I've looked and looked on Google and other forums for help with the $P! syntax but there just doesn't seem to be much out there. Can anybody here help ? Thank you kindly in advance.

 

Alex Marshall

[lucianc]

$P!{..} placeholders are normally used to alter the query by dynamically substituting some query fragments/clauses (e.g. the order by clause). $P!{..} fragments are replaced, as you've seen, replaced with the String representation of the parameter value (the parameters used in $P!{..} are usually Strings).

 

The standard way of using a value in a query is by using $P{..}. Such placeholders are transformed into JDBC statement parameters. So one would usually do something like

Code:

<parameter name="dateValue" class="java.util.Date"/>
<queryString>select .. from .. where dateColumn > $P{dateValue}</queryString>

 

Afaik, JDBC does not support java.util.Calendars, so you might need to use java.util.Date parameters if you follow this approach.

 

HTH,

Lucian

Post edited by: lucianc, at: 2007/03/16 12:34