David
You were absolutely correct...it was the use of $P!{}
I've been stareing at that for days....!
Thanks for spotting my error so quickly(and so early in the day!)
John Dunn
Sefas Innovation Limited.
Tel: + 44 (0) 117 373 6122
P Please consider the environment before printing this email
-----Original Message-----
From: David Bolen [mailto:db3l....@gmail.com]
Sent: 13 November 2009 09:09
To: jasperreports-questions@lists.sourceforge.net
Subject: Re: [jasperreports-questions] Default oracle date format in
ireport
"John Dunn" <jd...@sefas.com> writes:
> Thanks, but my problem relates to a date used in the SQL in my report
> query
Ah, other direction - sorry.
> I have tried using a value that is a java.util.date in my SQL query.
> Here is the relevant bit of my SL query :
>
> AND submitted_date >= $P!{PARAM_TO_DATE}
>
> parameter PARAM_TO_DATE is defined as a java.util.Date with the
> default Value Expression set to new java.util.Date("01/01/2050")
>
> But when the the Report query reads the SQL is gives the error
>
> Error : SQL problems: Missing IN or OUT parameter at index ::1
Ah - it may be your use of $P!{}.
It's my understanding that by using $P!{} you're overriding normal
parameter replacement behavior and forcing iReport/jasperreport to
insert the parameter as a raw string value into the SQL without being
able to parameterize the query to leave data conversions to the driver.
In such cases I believe it's best to ensure that the parameter being
used is a string formatted exactly as you wish for the SQL, and also to
include any necessary quoting, either as part of the parameter or in the
query.
Is there a specific reason that you need to use $P!{} at this point in
your query? It seems like a normal $P{} would be fine, and in that case
the date should transit across the JDBC connection just fine. That
should also have the advantage of being database server agnostic as the
date isn't being converted into a string by your code, but inside the
driver.
If you have to stick with $P!{}, I'm guessing your current parameter
definition is causing the query to likely be built using the result of
.toString() on your parameter, which given what I think is the default
representation for a java.util.Date probably means that internally you
end up with a query like:
and submitted_date >= Sat Jan 01 00:00:00 XXX 2050
(where XXX is the local timezone of the machine the report is run on)
In order to use $P!{}, I'd suggest:
* Make your parameter a type of java.Util.String and then construct the
value so it is exactly the string representation you want of the date.
So perhaps something like:
String.format("%tY-%<tm-%<td", new Date("01/01/2050"))
* Include any necessary quoting in your SQL, which I think would be:
and submitted_date >= '$P!{PARAM_TO_DATE}'
The combination of these two should, I believe, result in a SQL command
sent to the server of:
and submitted_date >= '2005-01-01'
If Oracle doesn't like that format by default, just adjust the format
string as desired.
Beyond that, if you still have problems, I would try to obtain a trace
of the actual SQL that is making it to your server. I'm not familiar
with Oracle but presume there is statement logging of some sort that can
be enabled. Alternatively, I think you can use the JDBC driver manager
to add local logging to your JDBC connection.
-- David
------------------------------------------------------------------------
------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008
30-Day trial. Simplify your report design, integration and deployment -
and focus on what you do best, core application coding. Discover what's
new with Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
jasperreports-questions mailing list
jasperreports-questions@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
jasperreports-questions mailing list
jasperreports-questions@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
