Using SELECT statement inside an IF Else condition in Jasper reports SQL query in Jasper server

0

I have the following sample query:

IF(1=1)
      SELECT  top 10 * FROM Person
 
     ELSE
      SELECT top 10 * FROM Person WHERE Id < 20

When i create a report with above query using Ireport , the report works fine.

But when i upload the same report to jasper server and try to run the report, it is throwing the below error.

ERROR Validator,report1 subreports #1:493 - Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: IF(1=1)SELECT  top 10 * FROM PersonELSESelect top 10 * FROM Person WHERE Id < 20.

Does Jasper server supports IF..Else conditions outside the SELECT statements.?

Do any one know about this error?

Thanks in advance

vinayt's picture
49
Joined: Jun 26 2014 - 4:17am
Last seen: 4 years 6 months ago

1 Answer:

1

After searching for around 4 days, i found a solution.

Jasper Server allows the SQL queries that starts with only "SELECT" keyword. So in order to make other queries work, we have to modify a regular expression of "Validator.ValidSQL" that is present in "webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties/validation.properties" location.

I have modified this regular expression to support 'IF' conditions and made it work.

Got this solution from jasper community forum:

http://community.jaspersoft.com/questions/800208/stored-procedures-works-ireport-does-not-work-jasperserver.

vinayt's picture
49
Joined: Jun 26 2014 - 4:17am
Last seen: 4 years 6 months ago
Feedback