| Stored procedure is not available in the New Data Set dialog [message #656472] |
Fri, 25 February 2011 20:05  |
 anna.w.teittinen
Messages: 23
Registered: February 2011 |
Junior Member |
|
|
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 20:12  |
|
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 #656479 is a reply to message #656472] |
Fri, 25 February 2011 20:30 |
|
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 #656491 is a reply to message #656472] |
Fri, 25 February 2011 21:51 |
|
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 22:48 |
|
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 #656832 is a reply to message #656501] |
Mon, 28 February 2011 17:56 |
|
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 #656865 is a reply to message #656844] |
Mon, 28 February 2011 19:23 |
|
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
|
|
|
Powered by
FUDForum. Page generated in 0.03912 seconds
] 
Search
Help
Register
Login
Home
