Calling stored procedure using SQL Select Query dataset issue

In Birt 2.3 design, I am calling stored procedure using dataset of type 'SQL Select Query'. In am using query text like "exec myProc ?,?,?" (assuming myProc has three params). This works fine. 
 
Issue is: I am trying to migrate this design to Birt4.2.2. When I try to preview dataset result using Birt 4.2.2 Eclipse Report Design it is giving error pasted below... 
 
Doesn't 4.2.2 support procedure call using the way I am using in 2.3? Just FYI, I am trying to avoid changing type of data set to stored procedure due to two reasons (1) There are many design files to migrate and I am looking for minimal changes (2) My db procedures return multiple result sets but I always want last or one before last so for Stored Procedure Data set type I have to mention required sequence number of result set which may turn difficult to maintain in future. 
 
Appreciate help and thanks in advance! 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report.
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:299)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.runDataset(DatasetPreviewTask.java:261)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.execute(DatasetPreviewTask.java:72)
at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:68)
at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:365)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Execute cursor 'jconnect_implicit_124' is declared on a procedure which contains a non-SELECT or a SELECT with a COMPUTE clause. For the declaration of this cursor to be legal it should have a single SELECT statement without a COMPUTE clause.

;
com.sybase.jdbc3.jdbc.SybSQLException: Execute cursor 'jconnect_implicit_124' is declared on a procedure which contains a non-SELECT or a SELECT with a COMPUTE clause. For the declaration of this cursor to be legal it should have a single SELECT statement without a COMPUTE clause.

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.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:414)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377)
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:957)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:445)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1208)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.extractQuery(DatasetPreviewTask.java:333)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:290)
... 5 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Execute cursor 'jconnect_implicit_124' is declared on a procedure which contains a non-SELECT or a SELECT with a COMPUTE clause. For the declaration of this cursor to be legal it should have a single SELECT statement without a COMPUTE clause.

;
com.sybase.jdbc3.jdbc.SybSQLException: Execute cursor 'jconnect_implicit_124' is declared on a procedure which contains a non-SELECT or a SELECT with a COMPUTE clause. For the declaration of this cursor to be legal it should have a single SELECT statement without a COMPUTE clause.

at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingPolicy1(Statement.java:420)
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:316)
at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:412)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:379)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407)
... 16 more
Caused by: com.sybase.jdbc3.jdbc.SybSQLException: Execute cursor 'jconnect_implicit_124' is declared on a procedure which contains a non-SELECT or a SELECT with a COMPUTE clause. For the declaration of this cursor to be legal it should have a single SELECT statement without a COMPUTE clause.

at com.sybase.jdbc3.tds.Tds.processEed(Tds.java:2942)
at com.sybase.jdbc3.tds.Tds.nextResult(Tds.java:2246)
at com.sybase.jdbc3.tds.Tds.getResultSetResult(Tds.java:2853)
at com.sybase.jdbc3.tds.TdsCursor.open(TdsCursor.java:295)
at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(SybStatement.java:1659)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:97)
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)
... 22 more
</pre>

Find more posts tagged with

Comments

kclark

Birt 4.2.2 does support stored procedures. Instead of mirgrating a report can you create a test report with your stored procedure and tell me if you get any errors doing this? When you create the dataset make sure you select "SQL Stored Procedure Query" from the drop down on the first page.

BirtRunn

Thanks for your reply!

Please note my procedure has multiple selects. Report is interested in last select for fetching and printing values. Initial selects in proc are selecting or assigning of values to declared variables.
I had tried new design using 4.2.2 and created data set using both ways and below are my observations:
1) Using 'SQL Stored Procedure query' - e.g. {call myProc (?,?,?) }:- No error occurred but nothing is displayed in Preview window. I think atleast it should have displayed column headings.
2) Using 'SQL Select query' - e.g. exec myProc ?,?,? :- Error occurred and I had posted full stack trace in my initial post.

I understand stored procedure is supported by 4.2.2, but really speaking I am expecting above 2nd approach to work in 4.2.2 which already works in 2.3

Please reply and thanks again for your help.

kclark

Did you define default values for the parameters in the dataset when you created it in a new report?

BirtRunn

Yes I did. Thank you.

BirtRunn

Any update on this ?

Matthew_sb

I am also experiencing this error when executing stored procedures with multiple result sets, and am interested in any response.

Note that, like the OP, the reports experiencing trouble in 4.2.2 but not in earlier versions, the procs are called in a dataset of type SQL Select Query, not SQL Stored Procedure Query.

Matthew_sb

BitRunn, we were able to address this 4.2.2 error for our environment by switching to jTDS driver from the Sybase jcon driver. If that is an option for you, it may be worth trying.