Eclipse Community Forums: BIRT » Stored procedure is not available in the New Data Set dialog

Home » Archived » BIRT » Stored procedure is not available in the New Data Set dialog(How do I make my stored procedure available in the New Data Set dialog?)

Stored procedure is not available in the New Data Set dialog [message #656472]

Fri, 25 February 2011 15:05

Eclipse User

Hello,

I am new to using BIRT... I apologize if my question is too simple.

I am following the ClassicModels tutorial that came with the BIRT download. I am using the birt-report-framework-2_5_2 version and MySql 5.5.9 for Windows.

I finished the tutorial but I now want to call a stored procedure within a newly created Data Set.

Below is the code to my stored procedure in my .sql file:

create procedure ClassicModels.getAllCustInfo()
BEGIN
select state, city, customerName, phone
from customers
order by state, city, customerName;
END

Within the MySql command line prompt, I created this stored procedure and called it without any problems as such:
call getAllCustInfo();

When I go back to the the BIRT New Data Set dialog, this stored procedure is not listed under the 'stored procedure' item in the tree for my database schema that it belongs to. Thus, I cannot call this stored procedure.

Am I missing a step?

Thanks so much in advance,
--Anna

Re: Stored procedure is not available in the New Data Set dialog [message #656475 is a reply to message #656472]

Fri, 25 February 2011 15:12

Eclipse User

Anna,

In the new dataset editor are you choosing SQL Stored Procedure Query as
the data set type?
After setting the type you should be able to enter:

{call getAllCustInfo()}

as the query.

Jason

On 2/25/2011 3:05 PM, anna.w.teittinen@gmail.com wrote:
> Hello,
>
> I am new to using BIRT... I apologize if my question is too simple.
>
> I am following the ClassicModels tutorial that came with the BIRT
> download. I am using the birt-report-framework-2_5_2 version and MySql
> 5.5.9 for Windows.
>
> I finished the tutorial but I now want to call a stored procedure within
> a newly created Data Set.
>
> Below is the code to my stored procedure in my .sql file:
>
> create procedure ClassicModels.getAllCustInfo()
> BEGIN
> select state, city, customerName, phone from customers
> order by state, city, customerName;
> END
>
> Within the MySql command line prompt, I created this stored procedure
> and called it without any problems as such:
> call getAllCustInfo();
>
> When I go back to the the BIRT New Data Set dialog, this stored
> procedure is not listed under the 'stored procedure' item in the tree
> for my database schema that it belongs to. Thus, I cannot call this
> stored procedure.
>
> Am I missing a step?
>
> Thanks so much in advance,
> --Anna

Re: Stored procedure is not available in the New Data Set dialog [message #656478 is a reply to message #656475]

Fri, 25 February 2011 15:27

Eclipse User

Hello Jason,

Yes, I did that but I got the following error after I click the "Finish Button" on the New Data Set dialog:

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.2.v20100209
Error Code:data.engine.fail.prepareExecution
Error Message:Failed to prepare the query execution for the data set: mySP
Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.SPSelectDataSet).
[{call ClassicModels.getAllCustInfo()} ]
Error preparing SQL statement.
SQL error #1: 'CLASSICMODELS.GETALLCUSTINFO' is not recognized as a function or procedure.

The last line in the error message indicates that GETALLCUSTINFO is not a stored procedure. It should be since I tested it fine on the MySql command line.

Am I missing a step?

Thanks,
--Anna

Re: Stored procedure is not available in the New Data Set dialog [message #656479 is a reply to message #656472]

Fri, 25 February 2011 15:30

Eclipse User

I don't know about MySQL, but in Oracle if you want to use a stored procedure to fetch a data set, you have to define the procedure to return a CURSOR type to the JDBC interface. It's neither intuitive nor simple. I suspect you'll need to find someone who has done this with BIRT and MySQL specifically to get an answer to your question.

Which leaves aside the main question, that being, why do you need to do that from the context of a stored procedure? BIRT can certainly execute what you're showing as a straight SQL query passed to the database back end.

Richard

Re: Stored procedure is not available in the New Data Set dialog [message #656483 is a reply to message #656479]

Fri, 25 February 2011 15:42

Eclipse User

Hello Richard,

I need to use stored procedures so that the intended end user does not need to have SQL smarts to create a report.

I am using MySql now just to follow the tutorial using the ClassicModels database that came with BIRT.

I will actually be using Oracle with BIRT for my work project after I get an understanding of how to call the stored procedure I created.

Just curious, how would I change the simple stored procedure below to return an CURSOR type as you suggested?

create procedure ClassicModels.getAllCustInfo()
BEGIN
select state, city, customerName, phone
from customers
order by state, city, customerName;
END

Thanks alot again,
--Anna

Re: Stored procedure is not available in the New Data Set dialog [message #656491 is a reply to message #656472]

Fri, 25 February 2011 16:51

Eclipse User

In Oracle (and it depends on which version of Oracle), I have to define the stored procedure similar to the following:

CREATE OR REPLACE procedure getcounterparties( p_cursor in out SYS_REFCURSOR)
as
begin
open p_cursor for select name, id from counterparty;
end;

I don't know how MySQL and the JDBC interface deal with stored procedure calls.

Re: Stored procedure is not available in the New Data Set dialog [message #656500 is a reply to message #656491]

Fri, 25 February 2011 17:48

Eclipse User

I just did this in MySQL:

--
------------------------------------------------------------ --------------------
-- Routine DDL
--
------------------------------------------------------------ --------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
Select * from orderdetails;
END

In the dataset editor I then entered:
{call test()}

And it worked fine.

I know I have seen issues in earlier version of the jdbc driver for MySQL.
I am using:
mysql-connector-java-5.0.4-bin.jar

Jason

On 2/25/2011 4:51 PM, Richard A. Polunsky wrote:
> In Oracle (and it depends on which version of Oracle), I have to define
> the stored procedure similar to the following:
>
> CREATE OR REPLACE procedure getcounterparties( p_cursor in out
> SYS_REFCURSOR)
> as
> begin
> open p_cursor for select name, id from counterparty;
> end;
>
> I don't know how MySQL and the JDBC interface deal with stored procedure
> calls.
>

Re: Stored procedure is not available in the New Data Set dialog [message #656501 is a reply to message #656500]

Fri, 25 February 2011 18:08

Eclipse User

Hello Jason,

Thank you for your solution.

I tried what you did and it works from the MySql command line but still not from within the BIRT gui.

I was wondering if you see your 'test' stored procedure listed in the 'Available Items' listbox in the New Data Set dialog.
If you do see it, then I think that is why it works from the BIRT gui.

I don't see my stored procedure listed in that listbox.

Thanks again,
--Anna

Re: Stored procedure is not available in the New Data Set dialog [message #656832 is a reply to message #656501]

Mon, 28 February 2011 12:56

Eclipse User

Anna

I do see it in the list. What version of the driver are you using?

Jason

On 2/25/2011 6:08 PM, Anna T. wrote:
> Hello Jason,
>
> Thank you for your solution.
>
> I tried what you did and it works from the MySql command line but still
> not from within the BIRT gui.
>
> I was wondering if you see your 'test' stored procedure listed in the
> 'Available Items' listbox in the New Data Set dialog.
> If you do see it, then I think that is why it works from the BIRT gui.
>
> I don't see my stored procedure listed in that listbox.
>
> Thanks again,
> --Anna

Re: Stored procedure is not available in the New Data Set dialog [message #656844 is a reply to message #656832]

Mon, 28 February 2011 13:46

Eclipse User

Hello Jason,

The JDBC driver for MySQL I am using is:

mysql-connector-java-5.1.15-bin

I am using MySQL Server 5.5.

Do you think I need to run my 'create procedure' command on a tool that comes with MySQL Server 5.5 rather than on the MySql command line?

Thanks so much again for your help and insights.
--Anna

Re: Stored procedure is not available in the New Data Set dialog [message #656865 is a reply to message #656844]

Mon, 28 February 2011 14:23

Eclipse User

Anna,

I am not sure what is going on here. I use MySql 5.1 and the driver:
mysql-connector-java-5.0.4-bin.jar and it works fine.

Jason

On 2/28/2011 1:46 PM, Anna T. wrote:
> Hello Jason,
>
> The JDBC driver for MySQL I am using is:
>
> mysql-connector-java-5.1.15-bin
>
> I am using MySQL Server 5.5.
>
> Do you think I need to run my 'create procedure' command on a tool that
> comes with MySQL Server 5.5 rather than on the MySql command line?
>
> Thanks so much again for your help and insights.
> --Anna

Previous Topic:	No events on Data Source
Next Topic:	Breakpoints never hit in Javascript methods

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 23 07:36:37 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter