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!
