Use Variable or With clause in Jasper Server.

With or Variable not working in Jasper Server.

I am getting error in subsequent report. if I use “common table expression” in MY/MS sql query in Jasperserver.

for example:
declare @var integer
or
with a as ( select…)

Solution for this problem:

Change the below parameter in /jasperserver-pro/WEB-INF/classes/esapi/security-config.properties  to generate the report.

security.validation.sql.on=true
and 
Go to // WEB-INF/classes/esapi/validation.properties

Find property:
Validator.ValidSQL=(?is)^\\s*(select)\\s+[^;]+;?\\s*$
And change in to:
Validator.ValidSQL=(?is)^\\s*(select|with|declare)\\s+[^;]+;?\\s*$

If you want to use other sql constructions (in start of query string), just add to regular expressions your sql command.

Tags: 

Mysql

Mssql

Jasperserver.