Report stopped working on JasperReportsServer after modification of stored procedure

We have a report that was running perfectly using the iReport designer, and from the JasperReports Serer.

I made some minor modifications to the underlying MySQL stored procedure, and adjusted the report structure accordingly, and now I can run the report from the Designer interface, but not at all from the server.

I'm receiving an error as follows:

Error Message

java.lang.ClassCastException: java.lang.String cannot be cast to java.util.Date

Which I understand to be a problem with one of the dates we use in the report, but none of the date information changed that I am aware of.

I'll start by attaching the report, and the top section of the stored procedure:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_fasb`(start_date varchar(10), end_date     varchar(10), loc varchar(45))
BEGIN
declare friday_end_date varchar(10);
declare in_str varchar(255);

if dayofweek(end_date) = 7 then
  SET friday_end_date = date_add(end_date, interval 6 day);
else
  SET friday_end_date = date_add(end_date, interval (6-dayofweek(end_date)) day);
end if; 

if dayofweek(end_date) = 7 then
SET end_date = date_add(end_date, interval -1 day);
end if;

if dayofweek(end_date) = 1 then
  SET end_date = date_add(end_date, interval -2 day);
end if;

Appreciate any suggestions.

Solution

From what I can see, all the date datatype is varchar(10).

Since you are using date_add functions, you cannot have your 'start_date' as a string.

When you try this on MYSQL

SELECT date_add(curdate(), interval 6 day);
Result:2013-05-06

But when you try

SELECT date_add('2013-04-30', interval 6 day);

You do not get any result or you get 'BLOB' which means it is expecting a variable of Date datatype inside the funcation.

Also in your report, may be you have your input parameter defined as java.util.Date. You may want to change that and see if this is a problem with input parameter. This could also be a problem.

Hope this helps.!