Eclipse Community Forums: BIRT » Problem with executing Stored Proc in BIRT

Home » Archived » BIRT » Problem with executing Stored Proc in BIRT(Having issues accessing DB2 Z/OS v9.1 via birt)

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 #879796 is a reply to message #879758]

Thu, 31 May 2012 17:25

Eclipse User

Carlos,

I assume the stored proc does not return a result set only the five
output parameters. Is that correct? If so could you copy the Stored
Proc and edit it to return a bogus result set and give it a try?

Jason

On 5/31/2012 3:39 PM, Carlos Ferrer wrote:
> 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 #880135 is a reply to message #879796]

Fri, 01 June 2012 09:53

Eclipse User

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.

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.

Re: Problem with executing Stored Proc in BIRT [message #880295 is a reply to message #880278]

Fri, 01 June 2012 15:47

Eclipse User

Jason,

Sorry I gave you wrong information earlier, I rechecked with the DBAs and looked at the parameters for all our stored procs. Our stored procs are all defined with ResultSet = 1 which means ResultSet is turned on. Because in a lot of cases we return more than 1 record when we call the stored proc. Is there a way around this in BIRT to handle a result set for a stored proc.

Re: Problem with executing Stored Proc in BIRT [message #880316 is a reply to message #880295]

Fri, 01 June 2012 17:04

Eclipse User

Do all the parameters have default values set? Look at the parameters
tab in the dataset editor.

Jason

On 6/1/2012 3:47 PM, Carlos Ferrer wrote:
> Jason,
>
> Sorry I gave you wrong information earlier, I rechecked with the DBAs
> and looked at the parameters for all our stored procs. Our stored procs
> are all defined with ResultSet = 1 which means ResultSet is turned on.
> Because in a lot of cases we return more than 1 record when we call the
> stored proc. Is there a way around this in BIRT to handle a result set
> for a stored proc.

Re: Problem with executing Stored Proc in BIRT [message #881474 is a reply to message #880316]

Mon, 04 June 2012 12:17

Eclipse User

I have uploaded a file with screen shots of what you are asking. When I go to the parameter tab and edit each output, the default is write-protected and I cannot add a default value to them.

Also, I did as you asked and created a bogus stored proc that returns a result set per your request. I first tested it in IBM Data Studio and it is returning a result set. Then I created a dataset for it and it got the same error about cannot get column count.

The stored procs we have are external stored procs using COBOL as the programming language. I don't know if that matters or not.

Thanks.

Attachment: birtproblem.doc
(Size: 67.50KB, Downloaded 234 times)

Re: Problem with executing Stored Proc in BIRT [message #881501 is a reply to message #881474]

Mon, 04 June 2012 13:36

Eclipse User

Jason,

I got it working. I had to modify the call by adding the schema name:
{call SAPSCH.PDSB917('06465574',?,?,?,?,?)}

I had to use a type III jdbc driver: COM.ibm.db2.jdbc.net.DB2Driver (v8.1)

Problem I was reporting went away.

Thanks.

Previous Topic:	How to avoid ReferenceError error messages?
Next Topic:	Forecast using BIRT

Goto Forum:

-=] Back to Top [=-

Current Time: Mon Jul 07 16:24:57 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter