[SQL Validation Issue]

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

in the security.properties file

[SQL Validation Issue]

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

[SQL Validation Issue]

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.

[LDAPS Error: No subject alternative DNS name matching]

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.

[SQL Validator Issue]

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.

[SQL Validator Issue]

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.

[js-ant import-minimal-ce fails]

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.

[js-ant import-minimal-ce fails]

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.

 

[Errors were encountered when compiling report expressions class file]

 

 

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.