Formatting parameter input before query execute

[jasonfsbo]

Hello,

 

Is there a way to format parameter inputs before query execution?

 

The reason I ask is that, evaluating jasperserver pro, i have made a parameter using the datatypes/date input type.

 

I get this error: "java.sql.SQLException: Bad format for DATE 'Sun Sep 09 20:00:00 EDT 2007' in column 1."

 

And then I get "net.sf.jasperreports.engine.JRException: Unable to get value for field 'test' of class 'java.util.Date'"

 

Why is this and how can I change "Sun Sep 09 20:00:00 EDT 2007" to "2007-09-09"?

 

Is that possible?

 

Thanks in advance.

[rathina04]

i am binding the date fields like this

SELECT f1,f2 from table1 where

AND TRDDDATE=TO_DATE($P{optDate},'YYYY-MM-DD')

i am having the data type of parameter optDate as String.

[csbac]

Hi!

Maybe you have this problem:

http://jasperforge.org/sf/go/artf2625?nav=1

 

Was fixed in 2.0.1 ...

 

Sebastian

[jasonfsbo]

I've managed to find the solution to my problem. I'm going to post below in hopes that it will help someone with a similar issue:

 

Code:

<parameter name="startDate" isForPrompting="true" class="java.util.Date">
	<defaultValueExpression ><![CDATA[(new Date("July 1, 2007"«»))]]></defaultValueExpression>
</parameter>
<parameter name="sqlStartDate" isForPrompting="false" class="java.lang.String">
	<defaultValueExpression ><![CDATA[(new SimpleDateFormat("yyyy-MM-dd"«»).format($P{startDate}))]]></defaultValueExpression>
</parameter>

 

Note the isForPrompting="false" for the hidden parameter sqlStartDate, and that I'm using java.lang.String because I'm converting the startDate parameter into a string in sqlStartDate.

 

With this, you can pre-process any user input. With more experimentation, I hope to be able to say something like: [if input is valid, insert a where clause, else leave out of the query all together]

[itchytoes]

Hi -- I'm interested in this solution. Does it rely on the <parameter> elements being processed in the order that they are specified? Will it always work? Does Jasper always process the report parameters in the order they are specified?

 

Betty

[jasonfsbo]

Hi Betty,

 

The parameter elements do have to be in order to the extent that any parameter must be set before you use it.

 

So, if you're going to use $P{myparam}, you're going to need <parameter name="myparam" ...> somewhere above that.

 

It will work as long as you can find the java methods to do what you need. I haven't been able to do any complex logic such as if/else, only single commands; I intend to explore this further.

 

As far as I know, Jasper should process the report parameters in the order they are specified.

 

Please let me know if you discover any tips with this method.

 

Thanks!