Hi! I've got a query working in Oracle SQL Developer that uses a "WITH" clause and it runs perfectly. However, the query editor in jaspersoft will not let the query start with anything exept select. I read somewhere that I need to add "with" to the sql validation file. Where would I do this on my local machine's jasper studio install? I can't find the appropriate config file.
Alternatively, if there is a way to perform the equivilent of a oracle pivot in jasper I would not need to do this (the query editor doesn't seem to like valid oracle pivot syntax).
Thanks!
1 Answer:
Hello,
To set SQL validation to off, here is the full file path:
C:\Jaspersoft\jasperreports-server-6.4.2\apache-tomcat\webapps\jasperserver-pro\WEB-INF\classes\esapi\security-config.properties
security.validation.sql.on=false
To add WITH to the security validator, go to path:
C:\Jaspersoft\jasperreports-server-6.4.2\apache-tomcat\webapps\jasperserver-pro\WEB-INF\classes\esapi\/validation.properties
Add the following to the ValidSQL rule:
Validator.ValidSQL=(?is)^\\s*(select|with)\\s+^;+$
Close the file and restart the server. The ValidSQL rule will now allow for the words "select" or "with" to be at the beginning of a query.
-Paul
