JasperServer and SQL Query Using With As clause

[beekerc69]

I'm converting several reports from Crystal to Jaspersoft that have SQL queries that us the With/As clause before the acutal Select statement is encountered.

While this seems to work in Jaspersoft Studio - meaning i can see the report in Preview mode, it appears that JasperServer doesn't like it.  i get a 6632 error.

in one case, i was able to move the With/As clause queries down into the From/Join section and make it work, but i know there will be queries where i won't be able to do that.

is there a way to get JasperServer to work with the With/As clause?

[zh3ntil]

You are getting this error because of security validator. Security validator let you start a query with 'Select' only. You could set the SQL validation off or add 'WITH' to validator.

1. Set SQL validation off

     WEB-INFclassesesapisecurity-config.properties

     security.validation.sql.on=false

 

2. Adding WITH to the security validator

    Open 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.

 

I recommend you to use second one. Because disabling the validator causes lack of security.

[beekerc69]

based on the replies and some other research, i've got one more option.

 

Select * from 

(

with xyz as (select.....)

select 

  from

where 

)

 

in this form, you can still use with/as constructs but it complies with the rule that word SELECT must be first .

this would be useful when changing the server SQL validation is not an option. 

[ruthran]

Hi,

   One of my report uses query that has "WITH" clause in it. Though the query runs just fine in SQL tools, it is throwing error in SQL editor of Jasper studio. Is there any settings in Jasper studio that we can change to avoid this error.?