alanm

Fixed. I had forgotten to uncomment the line: sqlQueryExecutor=Alpha,ValidSQL,500000,true,SQL_Query_Executor_context in the security.properties file

This is for Jasper Server 7.5. The report runs fine in Jaspersoft Studio 6.11

Hi validation.properties I have set: Validator.ValidSQL=(?is)^\s*(select|call|with|With|exec)\b((?!\binto\b)[^;])*;?\s*$ But the following still returns an error and won't run: Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: With LogRecords as (select PROTOCOL.PROTID,PROTMESSAGEID,PROTUSERNAME,PROTMESSAGEDATE,PROTMESSAGETEXT,PROTFCTDATE from protocol inner join prot_message on protocol.protid = prot_message.protid where protmessagedate between trunc(current_date - ?) and trunc(current_date) and protactionid = 21500 ) Select PROTMESSAGEID, PROTUSERNAME, PROTMESSAGEDATE, PROTMESSAGETEXT, EVENTGROUP, ERROR, TOTALERRORS, LOCATION,DEVICE ,CASE WHEN UPPER(PROTMESSAGETEXT) LIKE '/RFA/%' AND (LEAD(UPPER(PROTMESSAGETEXT),1) OVER (PARTITION BY DEVICE ORDER BY PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID) LIKE '%SIZE ADJUSTED%' OR LEAD(UPPER(PROTMESSAGETEXT),1) OVER (PARTITION BY DEVICE ORDER BY PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID) LIKE '%FILE TRUNCATED%' OR LEAD(UPPER(PROTMESSAGETEXT),1) OVER (PARTITION BY DEVICE ORDER BY PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID) LIKE '%ENTRY WILL BE ERASED%') THEN 1 ELSE 0 END AS FILESIZEADJUSTED ,CASE WHEN UPPER(PROTMESSAGETEXT) LIKE '/RFA/%' AND UPPER(PROTMESSAGETEXT) NOT LIKE '%.TMP' AND LEAD(UPPER(PROTMESSAGETEXT),1) OVER (PARTITION BY DEVICE ORDER BY PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID) LIKE '%CROSSLINKED%' THEN 1 ELSE 0 END AS FILESCROSSLINKED ,CASE WHEN TOTALERRORS >= ? THEN 1 ELSE 0 END ERRORTRIGGERED From ( select PROTMESSAGEID, PROTUSERNAME,PROTMESSAGEDATE,PROTMESSAGETEXT ,ProtFctDate as EventGroup ,CASE WHEN UPPER(PROTMESSAGETEXT) LIKE '/RFA/%' THEN 1 ELSE 0 END AS ERROR ,SUM(CASE WHEN UPPER(PROTMESSAGETEXT) LIKE '/RFA%' THEN 1 ELSE 0 END) OVER (Partition by ProtUserName) as TOTALERRORS ,SUBSTR(PROTUSERNAME,INSTR(PROTUSERNAME,'-V') + 2,INSTR(PROTUSERNAME,'-D') - INSTR(PROTUSERNAME,'-V')-2) AS LOCATION ,SUBSTR(PROTUSERNAME,INSTR(PROTUSERNAME,'-D') + 2,INSTR(PROTUSERNAME,'-M') - INSTR(PROTUSERNAME,'-D')-2) AS DEVICE from LogRecords where SUBSTR(PROTUSERNAME,INSTR(PROTUSERNAME,'-D') + 2,INSTR(PROTUSERNAME,'-M') - INSTR(PROTUSERNAME,'-D')-2) like '%' || ? and PROTID in (Select Max(PROTID) over (Partition By ProtUserName,ProtFCTDate) From LogRecords)) order by PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID Any thoughts Thanks Al.

Installing Jasper Server 7.5 Connecting to one our our Domain Controllers via LDAPS for login authentication I've exported the certificates (including the RootCA and EnterpriseCA) for the DC and put them in the java cacerts file on the Jasper server (Windows 2016) Also checked the certificates using keytool and openssl - it all looks OK Still getting: org.springframework.ldap.PartialResultException: nested exception is javax.naming.PartialResultException [Root exception is javax.naming.CommunicationException: simple bind failed: metrotas.com.au:636 [Root exception is javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: No subject alternative DNS name matching metrotas.com.au found.]] The js-externalAuth.properties file has the host name of the DC in it as per the documentation: external.ldap.url=ldaps://vm-dc01.metrotas.com.au:636/dc=metrotas,dc=com,dc=au I've checked the Subject had the CN of the DC correct and the SAN has the DNS name of the DC correct It looks like Java is trying to validate just the domain name.... why? Why is it looking for 'alternative DNS name matching metrotas.com.au'? I also did this for 7.1 a few months back and regenerated the certificate adding metrotas.com.au as a SAN - made no difference - didn't fix the issue - ended up having to turn end point identiication off I'm trying not to turn off end point indentification this time - but maybe thats the only solution Any ideas/suggestions Thanks in advance Al.

Found it - was due to my lack of familiarity with both java and regex. It was the {call text in the validator string. Removed the { and all is well Added it in to call stored procedures using {call <procedure>} but forgot to escape the { Now also changed back to SQL and using: exec <procedure> and that seems to work as well Al.

Hi I've just upgraded to JasperServer 7.1.0. I'm implementing security as per the security guide I'm having an issue with the SQL validator. It's current config is: Validator.ValidSQL=(?is)^\s*(select|{call|with|exec)\b((?!\binto\b)[^;])*;?\s*$ With SQL query executor logging on Debug - I receive the following error running an Oracle query. If I select the language as plsql, the report runs fine. When the language is SQL, the below error occurs I can't see what is wrong with the query Also, because the report works with pgsql - does this mean pgsql bypasses the SQL validator even though the query text is exactly the same? 019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:152 - DB is Oracle version Oracle Database 11g Release 11.2.0.1.0 - 64bit Production (11/2) 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:156 - driver is Oracle JDBC driver version 11.2.0.2.0 (11/2) 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:160 - jdbc 11/2 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:162 - connection URL is jdbc:oracle:thin:@mtsdb1:1521:mobile 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:204 - system timezone is sun.util.calendar.ZoneInfo[id="Australia/Hobart",offset=36000000,dstSavings=3600000,useDaylight=true,transitions=150,lastRule=java.util.SimpleTimeZone[id=Australia/Hobart,offset=36000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=9,startDay=1,startDayOfWeek=1,startTime=7200000,startTimeMode=1,endMode=3,endMonth=3,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=1]] 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:205 - report timezone is sun.util.calendar.ZoneInfo[id="Australia/Hobart",offset=36000000,dstSavings=3600000,useDaylight=true,transitions=150,lastRule=java.util.SimpleTimeZone[id=Australia/Hobart,offset=36000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=9,startDay=1,startDayOfWeek=1,startTime=7200000,startTimeMode=1,endMode=3,endMonth=3,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=1]] 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:206 - JDBC timezone parameter is null 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:207 - JDBC timezone property is null 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:213 - JDBC parameters timezone parameter is null 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:225 - JDBC parameters timezone property is null 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:232 - parameters timezone null 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:238 - JDBC fields timezone parameter is null 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:250 - JDBC fields timezone property is null 2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:257 - fields timezone null 2019-02-22 10:39:40,549 ERROR Validator,pool-4-thread-5:496 - Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: select ef_id, cardbalance/100 as cardbalance, minimumamount/100 as minimumamount,valuetocharge/100 as valuetocharge from view_topupcards order by to_number(ef_id) As a test, I changed the query to a very plain: select ef_id, cardbalance, minimumamount,valuetocharge from view_topupcards order by ef_id But still have the error. I know I could just use language="plsql" - but does that just bypass the validator - or that that what makes it work correctly? And if that is the fix - then why doesn't setting the language as sql work? Any ideas? Al.

I think I've found the answer I copied the version 4.2 export zip file from the older server to the new server At a command prompt in the buildomatic directory of the fresh install I typed: js-ant import-upgrade-ce -DimportFIle=<filepath> The data was imported and so far it's working well Hope this helps someone Al.

Hi I'm upgrading jasper server 4.2 to 6.2 I'm upgrading using the war file Going smoonthly until js-ant import-minimal-ce This fails. Log is: import-minimal-ce: [echo] [echo] Property values (in install.xml): [echo] archiveArg = --input-zip=C:/Install/jasperreports-server-cp-6.2.1-b in/buildomatic/install_resources/export/js-catalog-postgresql-minimal-ce.zip [echo] dbType = postgresql [echo] jdbcJar = postgresql-9.2-1002.jdbc4.jar [echo] currentConf = C:/Install/jasperreports-server-cp-6.2.1-bin/buildoma tic/build_conf/default [echo] importExportConfig = C:/Install/jasperreports-server-cp-6.2.1-bin/b uildomatic/conf_source/ieCe [echo] [echo] Validating JasperServer database connection at jdbc:postgresql://loc alhost:5432/jasperserver. Phase: [import-export-test] [echo] For JDBC driver the artifactId and version properties are set: [echo] maven.jdbc.artifactId=postgresql [echo] maven.jdbc.version=9.2-1002.jdbc4 [echo] Specified JDBC driver jar exists [advanced-sql] Executing commands [advanced-sql] 1 of 1 SQL statements executed successfully [echo] Connection OK [java] Error occurred during initialization of VM [java] Could not reserve enough space for object heap [java] Error: Could not create the Java Virtual Machine. [java] Error: A fatal exception has occurred. Program will exit. BUILD FAILED C:Installjasperreports-server-cp-6.2.1-binbuildomaticbinimport-export.xml:327: The following error occurred while executing this line: C:Installjasperreports-server-cp-6.2.1-binbuildomaticbinimport-export.xml:203: Java returned: 1 Total time: 1 second I've played withe the import-export.xml. Line 327 points to 203. I've played with the memory sizes to no avail 203 <java classname="${class}" fork="true" failonerror="true" inputstring=""> <arg value="--@{type}"/> <arg value="${archiveArg}"/> <args/> <!-- add arg that tells oracle JDBC driver to use utf-8 types; won't hurt anyone else --> <jvmarg value="-Doracle.jdbc.defaultNChar=true"/> <!-- add this to handle properly import catalogs with utf-8 names --> <jvmarg value="-Dfile.encoding=UTF-8"/> <!-- disable ehcache --> <jvmarg value="-Dnet.sf.ehcache.disabled=true"/> <!-- set this var to avoid certain network exceptions in OS X --> <jvmarg value="-Djava.net.preferIPv4Stack=true"/> <jvmarg value="-Xms1024m"/> <jvmarg value="-Xmx2048m"/> <jvmarg value="-XX:PermSize=512m"/> <jvmarg value="-XX:MaxPermSize=1024m"/> <!-- Uncomment to debug remotely --> <!--<jvmarg value="-Xdebug"/> <jvmarg value="-Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=9009"/>--> <classpath> <!-- TODO we should put currentConf first so it can pick up the files generated based on db settings, etc., but if you do that, beans don't get recognized because the Spring context loader uses a pattern of "applicationContext*.xml", which is sensitive to the order of the classpath. We should use one of the args such as -configResources to pass a pattern that is more explicit for Spring, doing something like this: -configResources=file:blah/import-export/dir/applicationContext*.xml --> <pathelement location="${importExportConfig}"/> <fileset dir="${importExportLibDir}" includes="*.jar"/> <fileset dir="${currentDbJDBCDir}" includes="${jdbcJar}"/> <pathelement location="${currentConf}"/> </classpath> </java> Any hints or ideas? The server is current Win 2003, 4Gb RAM and just jasper server running I wanted to upgrade this version then move the database and repository to a new Win 2012 R2 server I have ready Or is there a better way for me to do the upgrade/move? Cheers Al.

Hi I am using iReports 4.1.1 and JasperServer CE 4.1.0 I have a report which works fine in iReports, but when I upload it to the server and run the report I get: java.lang.Exception: 1 - Errors were encountered when compiling report expressions class file: 1. Cannot cast from Timestamp to String value = (java.lang.String)(((java.sql.Timestamp)field_TRIPDATETIME.getValue())); //$JR_EXPR_ID=50$ <---------------------------------------------------------------------> 2. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_TRIPDATETIME_1.getValue())); //$JR_EXPR_ID=51$ <-------------------------------------------------------------------------> 3. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_SumBefore8am.getValue())); //$JR_EXPR_ID=52$ <-----------------------------------------------------------------------> 4. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_Sum8am459am.getValue())); //$JR_EXPR_ID=53$ <----------------------------------------------------------------------> 5. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_Sum9am4510am.getValue())); //$JR_EXPR_ID=54$ <-----------------------------------------------------------------------> 6. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_Sum10am4511am.getValue())); //$JR_EXPR_ID=55$ <------------------------------------------------------------------------> 7. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_Sum11am4512pm.getValue())); //$JR_EXPR_ID=56$ <------------------------------------------------------------------------> 8. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_Sum12pm451pm.getValue())); //$JR_EXPR_ID=57$ <-----------------------------------------------------------------------> 9. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_Sum1pm452pm.getValue())); //$JR_EXPR_ID=58$ <----------------------------------------------------------------------> 10. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_Sum2pm453pm.getValue())); //$JR_EXPR_ID=59$ <----------------------------------------------------------------------> 11. Cannot cast from Integer to String value = (java.lang.String)(((java.lang.Integer)variable_Sum3pm454pm.getValue())); //$JR_EXPR_ID=60$ <----------------------------------------------------------------------> etc etc I can't understand why iReports would run fine, but the server not. I understand its a coversions problem, but not why when iReports runs without error. Thanks Al.