Ectomorph Posted February 19, 2014 Share Posted February 19, 2014 Hi,I'm running JasperReports Server v5.5 in Glassfish and with a MySQL db. I have an issue when trying to produce a particular report, the server logs the following error message;Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL:select o.orgname, a.accountno, a.level, a.startdate, a.expiry from organization o, account a where o.id=a.memberid and a.acctypeid=1 and a.status=1 and a.level='Trial' and startdate >= ? and startdate <= ? order by a.startdate|#][#|2014-02-19T10:46:36.363+1000|INFO|glassfish3.1.2|javax.enterprise.system.std.com.sun.enterprise.server.logging|_ThreadID=151;_ThreadName=Thread-2;|2014-02-1910:46:36,362 ERROR AsyncJasperPrintAccessor,pool-16-thread-2:299 -Error during report executioncom.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632)[/code]As you can see, the query is simple enough. The startdate and endate values are matched against passed parameters. If I set sql validation to false from within security-config.properties then the report works fine, but ideally I dont want to do that. Can anyone spot anything in the query that is causing JasperReports Server to fail validation? Thanks. Link to comment Share on other sites More sharing options...
hozawa Posted February 19, 2014 Share Posted February 19, 2014 Try removing the "and startdate >= ? and startdate <= ?" to see if that'll remove the error. If it does, it's probably the SQL validatation rule. You'll have to modify the following regular expression in apache-tomcatwebappsjasperserver-proWEB-INFclassesesapivalidation.propertiesValidator.ValidSQL=(?is)^\s*(select)\s+[^;]+;?\s*$Further info below:http://community.jaspersoft.com/documentation/jasperreports-server-administration-guide-beta/configuring-input-validation Link to comment Share on other sites More sharing options...
Ectomorph Posted February 19, 2014 Author Share Posted February 19, 2014 Many thanks for your suggestion, hozawa, but the "and startdate >= ? and startdate <= ?" works just fine in similar report queries I have running successfully. For example, the query below has no problems generating a report; select o.orgname, a.accountno, a.level, c.fee, a.discount, a.startdate, a.expiry FROM organization o, account a, charges c where o.id=a.memberid and c.acclevel=a.level and status=1 and acctypeid=2 and acctype=2 and startdate >= $P{startDate} and startdate <= $P{endDate} order by level asc Note: The above query was taken directly from the report thats why the startdate and enddate have the parameterized EL as opposed to '?' as shown in the error snippet. Link to comment Share on other sites More sharing options...
ajinkya_c Posted February 20, 2014 Share Posted February 20, 2014 Hi,Try Startdate with alias a.startdate that may be the issue.Thanks,Ajinkya Link to comment Share on other sites More sharing options...
Ectomorph Posted February 20, 2014 Author Share Posted February 20, 2014 startdate works just fine in other reports, as mentioned, and the query still fails even if i remove this line completely. Link to comment Share on other sites More sharing options...
hozawa Posted February 21, 2014 Share Posted February 21, 2014 I think there's something in ajinkya_c's reply. I think I've seen this before.Try adding "a.status", "a.acctypeid", and "a.acctype" in your "SELECT" Link to comment Share on other sites More sharing options...
jasperjones999 Posted February 28, 2018 Share Posted February 28, 2018 problem exists, tibco says "we dont care" thanks for that Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now