Birt Tables with empty resultset

Monica D

Hi,
I am working with Birt tables & IBM Db2 Database. I have a query which can return null resultset i.e no. of rows returned by query is 0. My table is getting displayed properly when resultset is not null but when the resultset is null I am getting following error msg:-
The following items have errors:


Table (id = 572):
+ Cannot fetch the next data row.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot move down to next row in the result set.
SQL error #1:[jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
;
com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null (Element ID:572)

I want to show only column headings when resultset is null.

Thanks & Regards,
Monica Dayaramani

mwilliams

Can you click on the '+' sign and expand the error stack and post it in here? I've not seen a table produce an error because it had an empty resultset. Do you do any scripting or anything with your table that affects the query?

Monica D

No, I am not doing any scripting and my table is not affecting my query.

I guess problem is coming because of iteration over the result set. When I set Auto Summarize on(For the No. of Detail Rows to be displayed) while creating table then this error is not getting Displayed.

Following is the stack Trace:-

The following items have errors:


Table (id = 1495):
- Cannot fetch the next data row.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot move down to next row in the result set.
SQL error #1:[jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
;
com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null (Element ID:1495)
odaconsumer.CannotFetchNextRow ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Cannot fetch the next data row.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot move down to next row in the result set.
SQL error #1:[jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
;
com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null (Element ID:1495)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1206)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1185)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:96)
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:180)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:77)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:929)
at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:973)
at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:131)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183)
at sun.reflect.GeneratedMethodAccessor82.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)
at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:126)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:317)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:326)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:939)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot fetch the next data row.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot move down to next row in the result set.
SQL error #1:[jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
;
com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
at org.eclipse.birt.data.engine.odaconsumer.ResultSet.fetch(ResultSet.java:143)
at org.eclipse.birt.data.engine.executor.cache.OdiAdapter.fetch(OdiAdapter.java:214)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(RowResultSet.java:105)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.getNext(RowResultSet.java:192)
at org.eclipse.birt.data.engine.executor.transform.SimpleResultSet.initialize(SimpleResultSet.java:133)
at org.eclipse.birt.data.engine.executor.transform.SimpleResultSet.<init>(SimpleResultSet.java:108)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:1009)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:441)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1495)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:173)
at org.eclipse.birt.report.engine.data.dte.QueryResultSet.<init>(QueryResultSet.java:98)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:168)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:265)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1897)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
... 57 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot move down to next row in the result set.
SQL error #1:[jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
;
com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
at org.eclipse.birt.report.data.oda.jdbc.ResultSet.next(ResultSet.java:196)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaResultSet.next(OdaResultSet.java:180)
at org.eclipse.birt.data.engine.odaconsumer.ResultSet.fetch(ResultSet.java:138)
... 72 more
Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.58.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
at com.ibm.db2.jcc.am.ed.a(ed.java:660)
at com.ibm.db2.jcc.am.ed.a(ed.java:60)
at com.ibm.db2.jcc.am.ed.a(ed.java:103)
at com.ibm.db2.jcc.am.hm.Db(hm.java:4352)
at com.ibm.db2.jcc.am.hm.c(hm.java:325)
at com.ibm.db2.jcc.am.hm.next(hm.java:304)
at org.eclipse.birt.report.data.oda.jdbc.ResultSet.next(ResultSet.java:187)
... 74 more

Monica D

Hii,
This error comes even while previewing results in the DataSet
Regards,
Monica Dayaramani

mwilliams

Where do you do the iteration over the result set to find the number of rows? In the query? Or using an aggregation?

Monica D

Hi,
I am just simply dragging and dropping columns of the Dataset onto my table.
The error mentioned above is also coming when I preview results of the query in DataSet.
I am using IBM Db2 Database and Eclipse Indigo.




By iteration I meant that when table rows are getting created then birt would be doing some iteration over the resultset iternally.

Regards,
Monica

johnw

You will see this error when the JDBC ResultSet object is in a close state, and the result of the next() operation throws an error. Normally this shouldn't be an issue with a 0 result query, you would just get an empty table. Are you using a query, or a stored procedure of some sort? Or is this just a straight SQL statement? What version of the DB2 JDBC driver are you using?

Monica D

I am using sql statements and I am getting this error with a 0 result query. How should I handle 0 result queries in Birt. I am using com.ibm.db2.jcc.DB2Driver (v3.58) Driver.


Regards,
Monica

Monica D

Hi,
Any suggestions regarding how to solve this error?
Thanks & Regards,
Monica Dayaramani

Monica D

Hi,
Noticed one more thing this error is coming when I am using eclipse Indigo with birt 3.7 and if use eclipse Heliose Service Pack 2 with Birt 2.6 reports works properly without throwing exceptions. Is there any problem with plugins or jar files?

Regards,
Monica Dayaramani

mwilliams

Hi Monica,

Is there any way you can reproduce this error with the sample database or a scripted dataSet or something? I've not seen this error come because of an empty result set before.

Monica D

Hi,


Thanks for the reply


This exception got resolved in the preview report when I viewed the reports from Eclipse Indigo 3.7.1 with birt 3.7.1 but if I view same report from Eclipse Indigo 3.7.0 with Birt 3.7.0 then this exception comes.
Now only one error remains when I embed this report in a jsp page using birt viewer tag. Then on screen this error comes "Cannot Load Query....".Table is not getting the dataset coz it is null so this error starts coming on screen.Same report works properly in the preview mode.

Thanks & Regards,
Monica Dayaramani

mwilliams

So, you're only having an issue when deployed now? If so, are you sure you're pointing to your database correctly?