dynamicList retrieval using getSelectionList in 3.7.1

mscherfling

We have BIRT 3.7.1 embedded in our application and are attempting to retrieve the Report Parameters for a design file which includes a dynamic list. The query used works find within Eclipse, but within the application, it appears to be timing out. I've turned on BIRT tracing to All and this is the part of the log showing the failure.

Is there anything I need to do to open the connection first? The Data Source tests fine.

Thanks you,
Mark

---

Jul 3, 2012 5:26:55 PM org.eclipse.birt.data.engine.odaconsumer.Connection isOpen
FINER: RETURN true
Jul 3, 2012 5:26:55 PM org.eclipse.birt.data.engine.odaconsumer.Connection prepareStatement(String,String,QuerySpecification)
FINER: ENTRY SELECT pe_event_uid , pe_event_name
FROM SBCMDBA.SB_PREF_EVENT
where PE_ACCT_ID = ?
AND PE_IS_DELETED = 0
union all
SELECT 0 as pe_event_uid, 'All' as pe_event_name FROM Dual org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet
Jul 3, 2012 5:26:55 PM org.eclipse.birt.data.engine.odaconsumer.Connection prepareOdaQuery
FINER: ENTRY SELECT pe_event_uid , pe_event_name
FROM SBCMDBA.SB_PREF_EVENT
where PE_ACCT_ID = ?
AND PE_IS_DELETED = 0
union all
SELECT 0 as pe_event_uid, 'All' as pe_event_name FROM Dual org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet org.eclipse.datatools.connectivity.oda.spec.QuerySpecification@69560af9
Jul 3, 2012 5:26:55 PM org.eclipse.birt.data.engine.odaconsumer.Connection setOdaQuerySpec
FINER: ENTRY org.eclipse.datatools.connectivity.oda.spec.QuerySpecification@69560af9
Jul 3, 2012 5:26:55 PM org.eclipse.birt.data.engine.odaconsumer.Connection setOdaQuerySpec
FINER: RETURN
Jul 3, 2012 5:26:55 PM org.eclipse.birt.data.engine.odaconsumer.Connection prepareOdaQuery
SEVERE: Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet).
[ SELECT pe_event_uid , pe_event_name
FROM SBCMDBA.SB_PREF_EVENT
where PE_ACCT_ID = ?
AND PE_IS_DELETED = 0
union all
SELECT 0 as pe_event_uid, 'All' as pe_event_name FROM Dual]
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement.
SQL error #1:Closed Connection
;
java.sql.SQLRecoverableException: Closed Connection
at org.eclipse.birt.report.data.oda.jdbc.Statement.prepare(Statement.java:135)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doPrepare(OdaQuery.java:229)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.prepare(OdaQuery.java:186)
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareOdaQuery(Connection.java:290)
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareStatement(Connection.java:197)
at org.eclipse.birt.data.engine.executor.DataSource.prepareStatement(DataSource.java:302)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:281)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:455)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:341)
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:455)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:190)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:178)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:145)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:620)
at org.eclipse.birt.report.engine.api.impl.GetParameterDefinitionTask.executeQuery(GetParameterDefinitionTask.java:640)
at org.eclipse.birt.report.engine.api.impl.GetParameterDefinitionTask.getResultSetForParameter(GetParameterDefinitionTask.java:1368)
at org.eclipse.birt.report.engine.api.impl.GetParameterDefinitionTask.getChoicesFromParameterQuery(GetParameterDefinitionTask.java:1340)
at org.eclipse.birt.report.engine.api.impl.GetParameterDefinitionTask.doGetSelectionList(GetParameterDefinitionTask.java:515)
at org.eclipse.birt.report.engine.api.impl.GetParameterDefinitionTask.getSelectionList(GetParameterDefinitionTask.java:445)
at COM.soundbite.birtoffboard.BirtEngineImpl.buildBirtParam(BirtEngineImpl.java:1172)
at COM.soundbite.birtoffboard.BirtEngineImpl.getReportParameters(BirtEngineImpl.java:1104)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at COM.soundbite.base.microcontainer.ServiceInterceptor.proxyInvoke(ServiceInterceptor.java:133)
at COM.soundbite.base.microcontainer.InterceptorChain.doIntercept(InterceptorChain.java:77)
at COM.soundbite.base.jdbc.interceptor.Tracer.invoke(Tracer.java:38)
at COM.soundbite.base.microcontainer.InterceptorChain.doIntercept(InterceptorChain.java:85)
at COM.soundbite.base.microcontainer.ServiceInterceptor.invoke(ServiceInterceptor.java:97)
at $Proxy70.getReportParameters(Unknown Source)
at COM.soundbite.birtoffboard.BIRTEngineServer.getReportParameters(BIRTEngineServer.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:168)
at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:70)
at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:279)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:136)
at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:86)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:136)
at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:74)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:136)
at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:74)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1357)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1289)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1239)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1229)
at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:420)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:497)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:684)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:182)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLRecoverableException: Closed Connection
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3331)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:449)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:437)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:243)
at org.eclipse.birt.report.data.oda.jdbc.Statement.prepare(Statement.java:131)
... 68 more
Jul 3, 2012 5:26:55 PM org.eclipse.birt.report.engine.api.impl.GetParameterDefinitionTask getResultSetForParameter
WARNING: An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: EventList
Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet).
[ SELECT pe_event_uid , pe_event_name
FROM SBCMDBA.SB_PREF_EVENT
where PE_ACCT_ID = ?
AND PE_IS_DELETED = 0
union all
SELECT 0 as pe_event_uid, 'All' as pe_event_name FROM Dual]
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement.
SQL error #1:Closed Connection
;
java.sql.SQLRecoverableException: Closed Connection

CBR

Do you create your database connections outside of BIRT (by using a connection pool or injecting a connection into the report using ReportEngine API)?