"Unexpected error occurs" appears for select users in Jasper Reports

Products

CA Service Desk Manager CA Service Management - Service Desk Manager

Issue/Introduction

Attempting to run a report on JasperStudio; the report is a simple query to return active entries with no major filters or criteria. While most users do not experience issues running the report, certain users will see the following on their UI's and in the jasperserver.log file:

20XX-01-01 11:01:00,000 ERROR GenericExceptionMapper,http-nio-8443-exec-11:47 - Unexpected error occurs
net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:

select
crs.sym, crs.delete_flag, crs.code
from
casd.crs
where crs.delete_flag = 0

   at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:528)
   at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:297)
   at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169)
   at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:114)
   at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRQueryExecuterAdapter.executeQuery(JRQueryExecuterAdapter.java:135)
   at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRQueryExecuterAdapter.executeQuery(JRQueryExecuterAdapter.java:110)
   at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeQuery(EngineServiceImpl.java:2269)
   at sun.reflect.GeneratedMethodAccessor914.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
   at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at com.sun.proxy.$Proxy62.executeQuery(Unknown Source)

The log will reveal:
Caused by: java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]AHD12033:Unable to process request - your session timed out at 01/09/2019 10:52 am after 60 minutes of inactivity

Similar messages seen in which AHD12033 matches up to a corresponding "Error preparing statement for executing the report query" message

Environment

Release: 17.1 and higher
Component: USRD

Cause

When running JasperReports, the backend ODBC connection is established in the name of the logged in SDM user. However, the connection may be timing out too soon.

Resolution

Recommendations:

1. Increase the timeout from default 60 min as needed in the SDM web.cfg file (setting odbcTimeout).

As of SDM 17.1, there is a known limitation with how the ODBC/JDBC connection works, that there is no way for a reconnect to occur automatically once the connection has timed out.

2. Increase timeout on JasperServer

- WEB-INF/jasperserver-servlet.xml in the JasperSoft Apache Tomcat folder.

Modify below property/line to a larger value.

from:

to:

Additional Information

Additional details on the above ODBC/JDBC limitation are discussed in the following community post:

https://communities.ca.com/thread/241788503-cabi-jaspersoft-and-ahd12033