Birt Tables with empty resultset

Monica D
Monica D Member
edited February 11, 2022 in Analytics #1
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

Comments

  • mwilliams
    mwilliams Member
    edited December 31, 1969 #2
    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?
    Warning No formatter is installed for the format ipb
  • Monica D
    Monica D Member
    edited December 31, 1969 #3
    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
    Monica D Member
    edited December 31, 1969 #4
    Hii,
    This error comes even while previewing results in the DataSet
    Regards,
    Monica Dayaramani
  • mwilliams
    mwilliams Member
    edited December 31, 1969 #5
    Where do you do the iteration over the result set to find the number of rows? In the query? Or using an aggregation?
    Warning No formatter is installed for the format ipb
  • Monica D
    Monica D Member
    edited December 31, 1969 #6
    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
    johnw Member
    edited December 31, 1969 #7
    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?
    Warning No formatter is installed for the format ipb
  • Monica D
    Monica D Member
    edited December 31, 1969 #8
    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
    Monica D Member
    edited December 31, 1969 #9
    Hi,
    Any suggestions regarding how to solve this error?
    Thanks & Regards,
    Monica Dayaramani
  • Monica D
    Monica D Member
    edited December 31, 1969 #10
    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
    mwilliams Member
    edited December 31, 1969 #11
    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.
    Warning No formatter is installed for the format ipb
  • Monica D
    Monica D Member
    edited December 31, 1969 #12
    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
    mwilliams Member
    edited December 31, 1969 #13
    So, you're only having an issue when deployed now? If so, are you sure you're pointing to your database correctly?
    Warning No formatter is installed for the format ipb

Categories

image
OpenText CE Products
Magellan Text Mining
Docs & APIs