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)
If you've solved it, you should probably post your solution ;)
Right, sorry about that. After changing validation.properties, I just had to restart the server.
Could you explain what did you modify into validation.properties ?
Hi guys, can anyone help with the steps to resolve this issue?
you have to edit this file:
..\apache-tomcat\webapps\jasperserver\WEB-INF\classes\esapi\validation.propertiesThere you'll find a line like this:
Validator.ValidSQL=(?is)^\\s*(select|call)\\b((?!\\binto\\b).)*;?\\s*$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*$Save & exit, then restart JasperServer