Parameter not being passed to SQL query in report when executed by the Scheduler

0

I am running a JasperReports 5.6.0 Server with a set of reports that have been working for the past 3 years without any issue.  Some time between the last scheduled running of the reports on December 15h, 2018 and the next running of the reports on January 1, 2019 something happened to the cause the reports to no longer be able to substitute a parameter into the SQL query.

I can run the reports just fine from JasperStudio 5.6.0 by specifying the "ministry" parameter, but when the report is uploaded to the JasperReports server it fails to substitute the $P{ministry} parameter. 

Here is an example from the .jrxml file, the parameter "ministry" is setup in the scheduler to have a defined five character code (such as ININD):

<parameter name="ministry" class="java.lang.String"/>
    <queryString language="SQL">
        <![CDATA[SELECT Nodes.Ministry,
                        UDT_AllEndPoints.ConnectedTo,
                        Nodes.IP_Address,
                        UDT_AllEndPoints.PortNumber,
                        UDT_AllEndPoints.PortName,
                        UDT_AllEndPoints.VLAN,
                        UDT_AllEndPoints.FirstSeen,
                        UDT_EndpointIP.LastUpdate,
                        UDT_AllEndPoints.HostName,
                        UDT_AllEndPoints.IPAddress,
                        UDT_AllEndPoints.MACAddress,
                        Nodes.NodeID,
                        UDT_AllEndPoints.NodeID
                FROM Nodes,
                     UDT_AllEndPoints,
                     UDT_EndpointIP
                WHERE 
                     UDT_AllEndPoints.NodeID = Nodes.NodeID 
                     AND UDT_AllEndPoints.IPAddress = UDT_EndpointIP.IPaddress
                     AND UDT_AllEndPoints.ConnectionTypeName = 'Direct'
                     AND Nodes.Ministry LIKE $P{ministry}
                 ORDER BY
                     Nodes.IP_Address,UDT_AllEndPoints.PortNumber;]]>
    </queryString>

However, when the report is run via the scheduler on the JasperReports server the following error is displayed:

com.jaspersoft.jasperserver.api.JSException: Error filling report
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1262)
    at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:211)
    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:135)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:886)
    at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1804)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousReportFiller.fillReport(EngineServiceImpl.java:790)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1731)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1086)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1015)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:908)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:886)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:607)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReportUnitRequest(EngineServiceImpl.java:1937)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:67)
    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:497)
    at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.runReport(ReportExecutionJob.java:693)
    at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeReport(ReportExecutionJob.java:658)
    at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeAndSendReport(ReportExecutionJob.java:468)
    at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.execute(ReportExecutionJob.java:222)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : 

SELECT Nodes.Ministry,
                        UDT_AllEndPoints.ConnectedTo,
                        Nodes.IP_Address,
                        UDT_AllEndPoints.PortNumber,
                        UDT_AllEndPoints.PortName,
                        UDT_AllEndPoints.VLAN,
                        UDT_AllEndPoints.FirstSeen,
                        UDT_EndpointIP.LastUpdate,
                        UDT_AllEndPoints.HostName,
                        UDT_AllEndPoints.IPAddress,
                        UDT_AllEndPoints.MACAddress,
                        Nodes.NodeID,
                        UDT_AllEndPoints.NodeID
                FROM Nodes,
                     UDT_AllEndPoints,
                     UDT_EndpointIP
                WHERE 
                     UDT_AllEndPoints.NodeID = Nodes.NodeID 
                     AND UDT_AllEndPoints.IPAddress = UDT_EndpointIP.IPaddress
                     AND UDT_AllEndPoints.ConnectionTypeName = 'Direct' 
                     AND Nodes.Ministry LIKE ?


    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:377)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:196)
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1114)
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:691)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1314)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:931)
    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)
    ... 19 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The fetch size cannot be negative.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.setFetchSize(SQLServerStatement.java:1617)
    at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:300)
    ... 25 more

 

As stated at the beginning, this report ran just fine on December 15, 2018, but started failing with the above error messages on January 1, 2019.

Any insight as to where I should start troubleshooting these errors?

Thank you!

Andy.G_AT's picture
Joined: Jan 16 2019 - 6:43am
Last seen: 9 months 3 weeks ago

0 Answers:

No answers yet
Feedback
randomness