| Problem creating dataset using db2 stored procedure in Eclipse BIRT [message #521878] |
Fri, 19 March 2010 05:43  |
 prodigy201 
Messages: 10
Registered: March 2010 |
Junior Member |
|
|
Hi,
I am using DB2 9.7 Express Edition in Eclipse BIRT(version 2.5.1) for generating reports. I have used Type4 driver for jdbc connection.
For that, I have established jdbc connection using db2jcc.jar and db2jcc_license_cu.jar files.
I have successfully created data source, say DB2BIRT having following requisites-
Driver Class - com.ibm.db2.jcc.DB2Driver ( v3.50)
Driver URL - jdbc:db2://localhost:50000/database_name
User name - user_name
Password - Password
I have written some stored procedures and trying to use result sets from those stored procedures into my report..
The stored procedures having involvement of only single result set are working absolutely fine for new dataset using above DB2BIRT.
But, I am unable to create new dataset using stored procedures those having involvement of multiple resultsets.
Well, in my stored procedure I have used 2 to3 cursors(as per my business logic) and all cursors except one are holding result sets. That exceptional cursor is intended for holding as well as returning result set after call to the procedure. And its perfectly running on db2 and returning the desired output. The problem is with Eclipse. The same procedure is not working in Eclipse BIRT.
I am getting following error on my terminal window as -
org.eclipse.birt.data.engine.odaconsumer.PreparedStatement$S equentialResultSetHandler getMoreResults
SEVERE: Cannot get more result sets from the statement.
Cannot get the result set.
SQL error #1: [jcc][10120][10943][3.50.152] Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get the result set.
SQL error #1: [jcc][10120][10943][3.50.152] Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null
;
com.ibm.db2.jcc.b.SqlException: [jcc][10120][10943][3.50.152] Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null
at org.eclipse.birt.report.data.oda.jdbc.CallStatement.getMoreR esults(CallStatement.java:1760)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaAd vancedQuery.getMoreResults(OdaAdvancedQuery.java:214)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement$S equentialResultSetHandler.getMoreResults(PreparedStatement.j ava:5183)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.g etMoreResults(PreparedStatement.java:792)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.f lushResultSets(PreparedStatement.java:1009)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.c lose(PreparedStatement.java:980)
at org.eclipse.birt.data.engine.executor.DataSource$DataSourceR eleaser.run(DataSource.java:374)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.ibm.db2.jcc.b.SqlException: [jcc][10120][10943][3.50.152] Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null
at com.ibm.db2.jcc.b.wc.a(wc.java:55)
at com.ibm.db2.jcc.b.wc.a(wc.java:102)
at com.ibm.db2.jcc.b.tk.db(tk.java:3118)
at com.ibm.db2.jcc.b.tk.a(tk.java:1063)
at com.ibm.db2.jcc.b.tk.getMoreResults(tk.java:908)
at org.eclipse.birt.report.data.oda.jdbc.CallStatement.getMoreR esults(CallStatement.java:1756)
... 7 more
Moreover, I tried to resolve above issue by changing Driver Class from com.ibm.db2.jcc.DB2Driver ( v3.50) to com.ibm.db2.jcc.uw.DB2StoredProcDriver ( v3.50)
So again while "Test Connection" for new Data source using this new driver class for stored procedure, there is an error reflection as -
org.eclipse.birt.report.data.oda.jdbc.JDBCException: The selected driver cannot parse the given url.
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.test Connection(JDBCDriverManager.java:627)
at org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.t estConnection(DriverLoader.java:120)
at org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.t estConnection(DriverLoader.java:133)
at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelecti onPageHelper.testConnection(JDBCSelectionPageHelper.java:653 )
at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelecti onPageHelper.access$7(JDBCSelectionPageHelper.java:627)
at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelecti onPageHelper$7.widgetSelected(JDBCSelectionPageHelper.java:5 49)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListe ner.java:228)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java :84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1176)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.ja va:3493)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java :3112)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:825 )
at org.eclipse.jface.window.Window.open(Window.java:801)
at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open( BaseDialog.java:110)
at org.eclipse.birt.report.designer.data.ui.actions.EditDataSou rceAction.doAction(EditDataSourceAction.java:68)
at org.eclipse.birt.report.designer.internal.ui.views.actions.A bstractElementAction.run(AbstractElementAction.java:70)
at org.eclipse.jface.action.Action.runWithEvent(Action.java:498 )
at org.eclipse.jface.action.ActionContributionItem.handleWidget Selection(ActionContributionItem.java:584)
at org.eclipse.jface.action.ActionContributionItem.access$2(Act ionContributionItem.java:501)
at org.eclipse.jface.action.ActionContributionItem$5.handleEven t(ActionContributionItem.java:411)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java :84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1176)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.ja va:3493)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java :3112)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.jav a:2405)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2369)
at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:22 21)
at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:500)
at org.eclipse.core.databinding.observable.Realm.runWithDefault (Realm.java:332)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Work bench.java:493)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.j ava:149)
at org.eclipse.ui.internal.ide.application.IDEApplication.start (IDEApplication.java:113)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(Eclips eAppHandle.java:194)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher .runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher .start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseS tarter.java:368)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseS tarter.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce ssorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe thodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java: 559)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:514)
at org.eclipse.equinox.launcher.Main.run(Main.java:1311)
Can anybody address my this issue for successful implementation of stored procedure(with involvement of multiple resultsets) for creating Data set in Eclipse BIRT ?
I will be really thankful.
Thanks in advance,
prodigy201
|
|
|
| Re: Problem creating dataset using db2 stored procedure in Eclipse BIRT [message #522009 is a reply to message #521878] |
Fri, 19 March 2010 11:06  |
|
Are you setting the result set to use as per:
http://www.eclipse.org/birt/phoenix/project/notable2.3.php#j ump_12
Jason
prodigy201 wrote:
> Hi,
>
> I am using DB2 9.7 Express Edition in Eclipse BIRT(version 2.5.1) for
> generating reports. I have used Type4 driver for jdbc connection.
> For that, I have established jdbc connection using db2jcc.jar and
> db2jcc_license_cu.jar files.
> I have successfully created data source, say DB2BIRT having following
> requisites-
>
> Driver Class - com.ibm.db2.jcc.DB2Driver ( v3.50)
> Driver URL - jdbc:db2://localhost:50000/database_name
> User name - user_name
> Password - Password
>
> I have written some stored procedures and trying to use result sets from
> those stored procedures into my report..
> The stored procedures having involvement of only single result set are
> working absolutely fine for new dataset using above DB2BIRT.
> But, I am unable to create new dataset using stored procedures those
> having involvement of multiple resultsets. Well, in my stored procedure
> I have used 2 to3 cursors(as per my business logic) and all cursors
> except one are holding result sets. That exceptional cursor is intended
> for holding as well as returning result set after call to the procedure.
> And its perfectly running on db2 and returning the desired output. The
> problem is with Eclipse. The same procedure is not working in Eclipse BIRT.
>
> I am getting following error on my terminal window as -
>
> org.eclipse.birt.data.engine.odaconsumer.PreparedStatement$S
> equentialResultSetHandler getMoreResults
> SEVERE: Cannot get more result sets from the statement.
> Cannot get the result set.
> SQL error #1: [jcc][10120][10943][3.50.152] Invalid operation: statement
> is closed. ERRORCODE=-4470, SQLSTATE=null
>
> org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get the
> result set.
> SQL error #1: [jcc][10120][10943][3.50.152] Invalid operation: statement
> is closed. ERRORCODE=-4470, SQLSTATE=null
> ;
> com.ibm.db2.jcc.b.SqlException: [jcc][10120][10943][3.50.152] Invalid
> operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null
> at org.eclipse.birt.report.data.oda.jdbc.CallStatement.getMoreR
> esults(CallStatement.java:1760)
> at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaAd
> vancedQuery.getMoreResults(OdaAdvancedQuery.java:214)
> at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement$S
> equentialResultSetHandler.getMoreResults(PreparedStatement.j ava:5183)
> at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.g
> etMoreResults(PreparedStatement.java:792)
> at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.f
> lushResultSets(PreparedStatement.java:1009)
> at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.c
> lose(PreparedStatement.java:980)
> at org.eclipse.birt.data.engine.executor.DataSource$DataSourceR
> eleaser.run(DataSource.java:374)
> at java.lang.Thread.run(Thread.java:619)
> Caused by: com.ibm.db2.jcc.b.SqlException: [jcc][10120][10943][3.50.152]
> Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null
> at com.ibm.db2.jcc.b.wc.a(wc.java:55)
> at com.ibm.db2.jcc.b.wc.a(wc.java:102)
> at com.ibm.db2.jcc.b.tk.db(tk.java:3118)
> at com.ibm.db2.jcc.b.tk.a(tk.java:1063)
> at com.ibm.db2.jcc.b.tk.getMoreResults(tk.java:908)
> at org.eclipse.birt.report.data.oda.jdbc.CallStatement.getMoreR
> esults(CallStatement.java:1756)
> .. 7 more
>
> Moreover, I tried to resolve above issue by changing Driver Class from
> com.ibm.db2.jcc.DB2Driver ( v3.50) to
> com.ibm.db2.jcc.uw.DB2StoredProcDriver ( v3.50)
> So again while "Test Connection" for new Data source using this new
> driver class for stored procedure, there is an error reflection as -
>
> org.eclipse.birt.report.data.oda.jdbc.JDBCException: The selected driver
> cannot parse the given url.
> at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.test
> Connection(JDBCDriverManager.java:627)
> at org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.t
> estConnection(DriverLoader.java:120)
> at org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.t
> estConnection(DriverLoader.java:133)
> at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelecti
> onPageHelper.testConnection(JDBCSelectionPageHelper.java:653 )
> at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelecti
> onPageHelper.access$7(JDBCSelectionPageHelper.java:627)
> at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelecti
> onPageHelper$7.widgetSelected(JDBCSelectionPageHelper.java:5 49)
> at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListe
> ner.java:228)
> at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java :84)
> at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1176)
> at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.ja va:3493)
> at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java :3112)
> at org.eclipse.jface.window.Window.runEventLoop(Window.java:825 )
> at org.eclipse.jface.window.Window.open(Window.java:801)
> at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(
> BaseDialog.java:110)
> at org.eclipse.birt.report.designer.data.ui.actions.EditDataSou
> rceAction.doAction(EditDataSourceAction.java:68)
> at org.eclipse.birt.report.designer.internal.ui.views.actions.A
> bstractElementAction.run(AbstractElementAction.java:70)
> at org.eclipse.jface.action.Action.runWithEvent(Action.java:498 )
> at org.eclipse.jface.action.ActionContributionItem.handleWidget
> Selection(ActionContributionItem.java:584)
> at org.eclipse.jface.action.ActionContributionItem.access$2(Act
> ionContributionItem.java:501)
> at org.eclipse.jface.action.ActionContributionItem$5.handleEven
> t(ActionContributionItem.java:411)
> at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java :84)
> at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1176)
> at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.ja va:3493)
> at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java :3112)
> at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.jav a:2405)
> at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2369)
> at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:22 21)
> at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:500)
> at org.eclipse.core.databinding.observable.Realm.runWithDefault
> (Realm.java:332)
> at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Work
> bench.java:493)
> at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.j ava:149)
> at org.eclipse.ui.internal.ide.application.IDEApplication.start
> (IDEApplication.java:113)
> at org.eclipse.equinox.internal.app.EclipseAppHandle.run(Eclips
> eAppHandle.java:194)
> at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher
> .runApplication(EclipseAppLauncher.java:110)
> at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher
> .start(EclipseAppLauncher.java:79)
> at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseS
> tarter.java:368)
> at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseS
> tarter.java:179)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce
> ssorImpl.java:39)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe
> thodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java: 559)
> at org.eclipse.equinox.launcher.Main.basicRun(Main.java:514)
> at org.eclipse.equinox.launcher.Main.run(Main.java:1311)
>
> Can anybody address my this issue for successful implementation of
> stored procedure(with involvement of multiple resultsets) for creating
> Data set in Eclipse BIRT ?
> I will be really thankful.
>
> Thanks in advance,
> prodigy201
|
|
|
|
|
| Re: Problem creating dataset using db2 stored procedure in Eclipse BIRT [message #522379 is a reply to message #522125] |
Mon, 22 March 2010 09:18 |
|
I just tried this with 2.5.2 and it appears there is a bug. Can you
open a bugzilla entry for this?
Jason
prodigy201 wrote:
> Well, I didn't set result set according to that initially.. But, now I
> have been through it as Edit Data Set->Settings. While "selecting result
> set by name/number" under "Result Set Selection" error is getting
> reflected as -
>
> A BIRT exception occurred.
> Plug-in Provider:Eclipse.org
> Plug-in Name:BIRT Data Engine
> Plug-in ID:org.eclipse.birt.data
> Version:2.5.1.v20090826
> Error Code:data.engine.errorHappenWhenRetrieveResultSet
> Error Message:Error happened when visiting result set:"3".The specified
> result set might not exist.
>
> Is the result set number according to number of cursors used in stored
> procedure or result set name by name of cursors? If not, then on what
> basis we have to specify result set numer/name? Please guide.
>
> Thank you.
> prodigy201
>
|
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.07297 seconds
] 
Search
Help
Register
Login
Home
