Parameters, SQL queries and REPORT_SCRIPTLET

[2004 IR Help]

By: Jeffrey Gilbert - visualjeff

Parameters, SQL queries and REPORT_SCRIPTLET

2004-02-04 14:59

I have parameter that I'm passing into a SQL query. The data type is java.util.Date and I want to call a scriptlet that converts the Date object in to a String format of "MM/dd/yyyy". I have written a scriptlet to facilitate this. So how can I call this scriptlet from my SQL query?

 

This is what I'm wanting to call within my SQL statement.

 

((com.mamu.rbusiness.htrmes.struts.utility.scriptlets.WebappScriptlet)$P{REPORT_SCRIPTLET}).convertDateToString($P{beginDate})

 

Is this possible? Does one have access to the REPORT_SCRIPTLET within the SQL statement???

 

Jeff

 

 

 

 

By: Giulio Toffoli - gt78

RE: Parameters, SQL queries and REPORT_SCRIPTLET

2004-02-05 05:35

 

This can't works because jasperreports don't process the query string as an expression.

 

if a !$P{xxx} is specified, the value of parameter xxx is replaced to the string "!$P{xxx}", else if a

simple $P{xxx} is specified, jasperreports replace it

with the string "?" and will call a prepare statement to pass the value of $P{xxx} as sql parameter to teh statement.

 

The only way is convert the parameter before pass

it to jasperreports.

 

I hope this helps you

 

Giulio

 

 

Giulio

 

 

 

 

By: Jeffrey Gilbert - visualjeff

RE: Parameters, SQL queries and REPORT_SCRIPTLET

2004-02-05 08:55

Is it possible to do this in a SQL query?

 

$P{new SimpleDateFormat("MM/dd/yyyy", Locale.US).format(dateParameter)}

 

If it isn't possible then how could someone convert a an object of type Date to an Oracle format of "MM/dd/yyyy" without having having to use PLSQL functions?

 

Jeff

 

 

 

 

 

 

By: Jeffrey Gilbert - visualjeff

RE: Parameters, SQL queries and REPORT_SCRIPTLET

2004-02-05 09:39

Actually I mean...

 

to_date($P{new SimpleDateFormat("MM/dd/yyyy", Locale.US).format(beginDate)}),'mmddyyyy')

 

Jeff

 

 

 

 

By: Jeffrey Gilbert - visualjeff

RE: Parameters, SQL queries and REPORT_SCRIPTLET

2004-02-06 09:00

Or for you Oracle DBA experts:

 

Is there a way to convert a java.util.Date field to a a String format via the to_date function?

 

Jeff

 

PS. I want the report input to remain a data type of Date. So I need to be able to feed this in my plsql statement.

 

 

 

 

By: Jeffrey Gilbert - visualjeff

RE: Parameters, SQL queries and REPORT_SCRIPTLET

2004-02-06 14:01

After working through this a bit I'm able to resolve the issue via Oracle functions:

 

But my final question for the forum is:

 

If I have an parameter of type java.util.Date and my SQL statement has the following $P{myDate} in it what will this output into my SQL statement? Is the output the same as calling toString() on the Date object (i.e., Fri Feb 06 13:05:21 PST 2004).

 

Jeff

 

 

 

 

By: Giulio Toffoli - gt78

RE: Parameters, SQL queries and REPORT_SCRIPTLET

2004-02-08 13:53

Good question.

 

Try to take a look to the jasperreports code to see exacly what it does. But I think that jasperreports expect a string....

 

Why don't pass two parameters, the first is of type java.util.Date, the second the same date formatted in a string. You can use the first when you nedd a date, the second when you need a string.

 

Giulio

[alex_mendelev]

Hi!

I need to use a variable instead of a parameter in the report query. My user enters $P{month} in form of mm/yyyy and I convert it to $V{from_date} and $V{to_date}, which I have to put into the SQL.

Is this possible?

Post edited by: alex_mendelev, at: 2007/04/17 16:01