jbreault Posted May 18, 2017 Share Posted May 18, 2017 NOTE: I have solved this problem since posting this question. Thanks anyway. I am having trouble running this one particular report in JasperReports Server. Others I have tried run perfectly, but with this report I get the error below. Here are some things I have already tried to fix this: 1 - Removing comments from the SQL code. 2 - Changing Validator.ValidSQL=(?is)^\s*(select|call)\s+[^;]+;?\s*$ to Validator.ValidSQL=(?is)^\s*(select|call|with)\s+[^;]+;?\s*$ (my SQL code begins with "WITH") in WEB-INF/classes/esapi/validation.properties I am not sure what else to try or even what is going wrong exactly. Any help you can offer is appreciated. 2017-05-18 12:19:06,017 ERROR AsyncJasperPrintAccessor,pool-4-thread-21:321 - Error during report execution com.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632) Arguments: at com.jaspersoft.jasperserver.api.security.validators.Validator.validateSQL(Validator.java:500) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:548) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:924) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:880) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1829) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1169) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1051) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:946) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Link to comment Share on other sites More sharing options...
ravenheart Posted August 15, 2017 Share Posted August 15, 2017 If you've solved it, you should probably post your solution ;) Link to comment Share on other sites More sharing options...
jbreault Posted August 16, 2017 Author Share Posted August 16, 2017 Right, sorry about that. After changing validation.properties, I just had to restart the server. Link to comment Share on other sites More sharing options...
a.raffaeli Posted October 18, 2017 Share Posted October 18, 2017 Could you explain what did you modify into validation.properties ? Link to comment Share on other sites More sharing options...
jasperjones999 Posted February 28, 2018 Share Posted February 28, 2018 can also be caused by calling a stored proc with exec or call in mysql and not having the language set to plsqlalthough the plsql is confusing as its usually associated with oracle pl/sql ,so poor choice jaspersoft.but this option is applicable to ms sql server also, but jaspersoft docs and interface as usual are not so hot on precision of language or even concepts.hope this help someone else before there are no more jasper soft users left. Link to comment Share on other sites More sharing options...
tumelomapila Posted May 2, 2018 Share Posted May 2, 2018 Hi guys, can anyone help with the steps to resolve this issue? Link to comment Share on other sites More sharing options...
jsutherland_1 Posted September 10, 2018 Share Posted September 10, 2018 I was having this same error connecting back to an IBM i mid-range server using IBM DB2. Using Studio, language type SQL worked. But when it was running from the jasper server, it would not execute and gave the above error until I changed the language type to PLSQL, which I never would have thought of had it not been for finding this post. Many thanks for posting a solution!!! Link to comment Share on other sites More sharing options...
sockit Posted October 17, 2018 Share Posted October 17, 2018 you have to edit this file:..apache-tomcatwebappsjasperserverWEB-INFclassesesapivalidation.properties[/code]There you'll find a line like this:Validator.ValidSQL=(?is)^\s*(select|call)\b((?!\binto\b).)*;?\s*$[/code]You can put a hash '#' as very first character to mark this old line as comment. Than you have to insert the new line:Validator.ValidSQL=(?is)^\s*(select|call|with)\s+[^;]+;?\s*$[/code]Save & exit, then restart JasperServer Link to comment Share on other sites More sharing options...
david.onyon_1 Posted May 21, 2020 Share Posted May 21, 2020 The above solution also helped resolve a similar problem I was experiencing as I include a 'with' statement in the SQL. Thanks so much!! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now