Add a Jaspersoft Function to SQL Validator
search cancel

Add a Jaspersoft Function to SQL Validator

book

Article ID: 231295

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

SQL Validator in Jasper7.8 is blocking WITH or REGEXP_REPLACE function in sub-query. It works fine on commenting the function lines, what are your options if you don't want to disable the SQL Validator? 

Environment

Release : Any

Component : CLARITY JASPERSOFT

Resolution

If you don't wish to disable the SQL Validator but just allow for this function:

Allow the specific function in SQL Validator:

  1. Open the file: 
    C:\Jaspersoft\apache-tomcat\webapps\reportservice\WEB-INF\classes\esapi\validation.properties
  2. Add the function to the ValidSQL rule:
    Example : Add "WITH" to be allowed in the beginning of the query:
    Validator.ValidSQL=(?is)^\\s*(select|with)\\s+^;+$
  3. Close the file and restart the Jaspersoft server.
  4. The ValidSQL rule will now allow for the words "select" or "with" to be at the beginning of a query.