Problem with executing Stored Proc in BIRT [message #879758] |
Thu, 31 May 2012 15:39  |
Eclipse User |
|
|
|
I am a novice at BIRT. We have been using BRIO for our reports but have been looking at BIRT as a replacement.
Version of BIRT I am using is 3.7.0
Database: DB2 for Z/OS verion 9.1
I have tried using a type 2 and type 4 jdbc driver:
COM.ibm.db2.jdbc.app.DB2Driver
com.ibm.db2.jcc.DB2Driver
For Data Set Type I selected SQL Stored Procedure Query. I have selected both drivers above.
The Stored proc call looks like the following
NOTE: Am using a Stored Proc we have been using for almost 8 years in dev and prod. It has one string input and 5 outputs. I verified with IBM Data Studio that the stored proc is working.
The call looks like the following:
{call XXXXXXXX('06465574',?,?,?,?,?)}
I then click ok and the following errors appeared below. I am probably doing something wrong. If y'all could point out what I am doing wrong. Again this stored proc is working in dev and prod.
!ENTRY org.eclipse.birt.data 4 1001 2012-05-31 14:05:35.479
!MESSAGE A BIRT exception occurred.
!STACK 0
org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the column count.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get a column count.
SQL error #1:[IBM][CLI Driver] CLI0101E The statement did not return a result set. SQLSTATE=07005
;
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0101E The statement did not return a result set. SQLSTATE=07005
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.ResultSetMetaData.getColumnCount(ResultSetMetaData.java:70)
at org.eclipse.birt.data.engine.odaconsumer.ProjectedColumns.<init>(ProjectedColumns.java:51)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetProjectedColumns(PreparedStatement.java:433)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:378)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:563)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:927)
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.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:133)
at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRuntimeMetaData(DataSetMetaDataHelper.java:194)
at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRealMetaData(DataSetMetaDataHelper.java:153)
at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getDataSetMetaData(DataSetMetaDataHelper.java:115)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.getDataSetMetaData(DataRequestSessionImpl.java:236)
at org.eclipse.birt.report.designer.data.ui.util.DataSetProvider.populateAllOutputColumns(DataSetProvider.java:216)
at org.eclipse.birt.report.designer.data.ui.dataset.OutputColumnsPage.populateOutputColums(OutputColumnsPage.java:401)
at org.eclipse.birt.report.designer.data.ui.dataset.OutputColumnsPage.updateOutputColumns(OutputColumnsPage.java:345)
at org.eclipse.birt.report.designer.data.ui.dataset.OutputColumnsPage.pageActivated(OutputColumnsPage.java:244)
at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.showPage(AbstractPropertyDialog.java:588)
at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.showSelectionPage(AbstractPropertyDialog.java:482)
at org.eclipse.birt.report.designer.data.ui.dataset.DataSetEditor.showSelectionPage(DataSetEditor.java:911)
at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog$2$1.run(AbstractPropertyDialog.java:438)
at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:70)
at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog$2.selectionChanged(AbstractPropertyDialog.java:433)
at org.eclipse.jface.viewers.Viewer$2.run(Viewer.java:164)
at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)
at org.eclipse.ui.internal.JFaceUtil$1.run(JFaceUtil.java:49)
at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:175)
at org.eclipse.jface.viewers.Viewer.fireSelectionChanged(Viewer.java:162)
at org.eclipse.jface.viewers.StructuredViewer.updateSelection(StructuredViewer.java:2188)
|
|
|
|
|
Re: Problem with executing Stored Proc in BIRT [message #880278 is a reply to message #880135] |
Fri, 01 June 2012 14:51   |
Eclipse User |
|
|
|
Carlos
When you hit finish the BIRT designer will run the stored proc to
collect meta data (parameter, cols etc). I believe the issue my be that
the JDBC driver is expecting a result set, even if it not used. Can you
create a temp stored proc to test it?
Jason
On 6/1/2012 9:53 AM, Carlos Ferrer wrote:
> Hi Jason,
>
> The stored proc returns 5 outputs and not a result set. The following
> {call XXXXXXXX('06465574',?,?,?,?,?)} '06465574' is a valid value and it
> will return the values below from our dev database:
> Name Type Data type Value Value (OUT)
> -------- ------ --------- --------
> ------------------------------------------------- SIDNO INPUT CHAR
> 06465574 SB917FLG OUTPUT CHAR Y
> SQLCODE OUTPUT INTEGER 0
> ERRTBL OUTPUT CHAR ERRPARA OUTPUT CHAR ERRMSG OUTPUT CHAR
> The error I am reporting occurs when I click the Finish button on the
> Query screen when creating the data set. I haven't even clicked on the
> Preview Output yet.
> Thanks.
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.04362 seconds