Re: [jasperreports-questions] Default oracle date format in ireport
Thanks, but my problem relates to a date used in the SQL in my report query I have a report that I need to run against a variety of SQL databases, i.e Oracle, MYSQL, DB2 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 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 02:30 To: jasperreports-questions@lists.sourceforge.net Subject: Re: [jasperreports-questions] Default oracle date format in ireport John Dunn jd...@sefas.com writes: Where?...I can't see anywhere to change it You can change the display format of a date field as one of the properties of that field in the report, or by manually formatting the field when constructing a string field. For simple date/time fields, use the Pattern property to select from one of the predefined patterns. You can also manually type in a pattern using the appropriate letter codes. For more elaborate formatting, you can make the field a String field, and then use String.Format with date/time formatting codes. For example, I have a title field defined as a string that uses: String.format('%tA, %tB %te', $F{class_date}) + , + String.format('%tl:%tM %tp', $F{start_time}) + - + String.format('%tl:%tM %tp', $F{end_time}) where class_date is a database date field, and start_time/end_time are database time fields. (Note that I think the above syntax may require a groovy Report interpreter, so if using Java you probably need some new() calls or something) which produces an output like: Monday, August 31, 9:30 am - 10:20 am I don't think the above should be database-specific. The JDBC interface to the database should be returning date/time fields in an appropriate java object, so once it's on the iReport side any of the formatting should be possible. -- 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
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
Re: [jasperreports-questions] Default oracle date format in ireport
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
Re: [jasperreports-questions] Default oracle date format in ireport
Hi John, Possible we can change it in the report design Thanks Suresh From: John Dunn jd...@sefas.com To: jasperreports-questions@lists.sourceforge.net Sent: Thu, 12 November, 2009 12:35:07 AM Subject: [jasperreports-questions] Default oracle date format in ireport Where does ireport get the default Oracle date format from...is it from the JDBC driver? Can it be changed? John Dunn Product Consultant Sefas Innovation Limited. Tel: + 44 (0) 117 373 6122 www.sefas.com P Please consider the environment before printing this email Sefas Innovation Limited, CityPoint, Temple Gate, Bristol BS1 6PL, UK. Tel: +44 (0) 117 373 6114 Fax: +44 (0) 117 373 6115. Registered No: 3769761 England. Registered Office: One New Street, Wells, Somerset, BA5 2LA, United Kingdom. VAT Registration No: GB 741 5377 32 Unless stated to be non-confidential, this email and any attachments are private and confidential and are for the addressee only. Sefas monitors e-mails to ensure its systems operate effectively and to minimize the risk of viruses. Whilst Sefas has taken reasonable steps to scan this email, it does not accept liability for any virus that may be contained in it. Internet communications are not 100% secure and as such Sefas is not responsible for their abuse by 3rd parties, nor for any alteration or corruption in transmission. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/-- 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
Re: [jasperreports-questions] Default oracle date format in ireport
Where?...I can't see anywhere to change it John Dunn Sefas Innovation Limited. Tel: + 44 (0) 117 373 6122 P Please consider the environment before printing this email From: Suresh Kumar [mailto:sures...@yahoo.co.in] Sent: 12 November 2009 14:35 To: John Dunn; jasperreports-questions@lists.sourceforge.net Subject: Re: [jasperreports-questions] Default oracle date format in ireport Hi John, Possible we can change it in the report design Thanks Suresh From: John Dunn jd...@sefas.com To: jasperreports-questions@lists.sourceforge.net Sent: Thu, 12 November, 2009 12:35:07 AM Subject: [jasperreports-questions] Default oracle date format in ireport Where does ireport get the default Oracle date format from...is it from the JDBC driver? Can it be changed? John Dunn Product Consultant Sefas Innovation Limited. Tel: + 44 (0) 117 373 6122 www.sefas.com http://www.sefas.com/ P Please consider the environment before printing this email Sefas Innovation Limited, CityPoint, Temple Gate, Bristol BS1 6PL, UK. Tel: +44 (0) 117 373 6114 Fax: +44 (0) 117 373 6115. Registered No: 3769761 England. Registered Office: One New Street, Wells, Somerset, BA5 2LA, United Kingdom. VAT Registration No: GB 741 5377 32 Unless stated to be non-confidential, this email and any attachments are private and confidential and are for the addressee only. Sefas monitors e-mails to ensure its systems operate effectively and to minimize the risk of viruses. Whilst Sefas has taken reasonable steps to scan this email, it does not accept liability for any virus that may be contained in it. Internet communications are not 100% secure and as such Sefas is not responsible for their abuse by 3rd parties, nor for any alteration or corruption in transmission. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage http://in.rd.yahoo.com/tagline_yyi_1/*http://in.yahoo.com/ . -- 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