Stored procedures: works in IReport, does not work in JasperServer

[ciprian_ivan]

Hello, 

    I wrote a simple stored procedure (no parameters in the report query, only hard-coded values as IN variables for the procedure). When i test it in Ireport, it works fine, the result is as expected.

   When i create a "JasperServer" report and try to "Run Jasper server report", i get the ERROR message: 

1 - An error has occurred. Please contact your system administrator. (6632)

---

java.lang.Exception: 1 - An error has occurred. Please contact your system administrator. (6632) 
    at com.jaspersoft.ireport.jasperserver.ws.WSClient.runReport(WSClient.java:403) 
    at com.jaspersoft.ireport.jasperserver.ws.WSClient.runReport(WSClient.java:320) 
    at com.jaspersoft.ireport.jasperserver.ReportRunner.run(ReportRunner.java:70) 
    at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) 
    at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) 

 

Can someone give me a pointer in the right direction ?? i "google" without results until now.

Thank you, 

  Ciprian

 

[lisacbeaton]

Can you post your JRXML file? Can you tell us more about this stored procedure? What does it do, etc.

 

[ciprian_ivan]

-- --------------------------------------------------------------------------------

-- Routine DDL

-- Note: comments before and after the routine body will not be stored by the server

-- --------------------------------------------------------------------------------

DELIMITER $$

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_show_users`(IN `user_id_low_param` INT, IN `user_id_high_param` INT, IN `birth_date_low_param` DATE, IN `birth_date_high_param` DATE, IN `school_ID_param` INT)

READS SQL DATA

BEGIN

/* Variables: */

 

 

/* SQL Statemens: */

select user_id, concat(last_name, ", ",first_name) as userName, date_of_birth as BirthDate

from user_profiles

where

school_instance_id = school_ID_param

AND ( (user_id >=user_id_low_param) and (user_id <=user_id_high_param) )

AND ( (date_of_birth >=birth_date_low_param) and (date_of_birth <=birth_date_high_param) )

order by

date_of_birth, user_id;

 

END

[ciprian_ivan]

A colleague find a solution:

In

 

webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties/validation.properties

 

you have to set:

 

Validator.ValidSQL = ^{s*(?i)(withs+.*)?(select|call|exec(ute)?)s+[^;]+}$

 

to allow for

 

{call "sp_name"(...)}

 

And IT WORKS!

 

Thanks a lot for responsiveness !

 

[mgeise]

Adding as an answer, instead of a comment...Thanks for posting the solution you found!

"In  webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties/validation.properties you have to set: Validator.ValidSQL = ^{s*(?i)(withs+.*)?(select|call|exec(ute)?)s+[^;]+}$ to allow for  {call "sp_name"(...)} And IT WORKS! Thanks a lot for responsiveness"

 

[kcsekhar25]

Hi all ,

i tried above one as well but not useful in my case.

i have generated a report usin stored function with 3 parameters in postgresql and the report is working very well in the ireport and it is throwing 6632 error in jasper server.

i tried all possible solutions from the forum but not solved my problem.

please guide me to solve this issue,it's irritating as i have solved this issue when used with sql queries by making sql validation false but this time not working the reg exp changed in validation properties or sql security properties file.

 

Thanks in advance

[vinayt]

For me, this approch worked like a Charm.. Thank you