Jasper Reports do not work for select users

Document ID : KB000124378
Last Modified Date : 12/02/2019
Show Technical Document Details
Issue:
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:

2019-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
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:
Recommendation is to 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.
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