Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Problem with Mysql Stored Procedure
Problem with Mysql Stored Procedure [message #884639] Mon, 11 June 2012 14:35 Go to next message
karvesh ghunsam is currently offline karvesh ghunsam
Messages: 95
Registered: July 2011
Member
Dear All,
I created a stored procedure in MySql 5 that works fine when i try it using the MySql WorkBench.

I an trying to invoke it from BIRT. So I used the SQL STORED PROCEDURE QUERY dataset.

I used the query:

{CALL KPIDebtor('2012-01-01', '2012-12-31');}


But for some reason, when i click finish, nothing happens. The window will not close and save.. Sad

Could any one give me an idea what I am doing wrong?

Regards,
Karvesh
Re: Problem with Mysql Stored Procedure [message #884727 is a reply to message #884639] Mon, 11 June 2012 17:25 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Look in your workspace and search for .log. Do any error messages show
up in the log?

Jason

On 6/11/2012 10:35 AM, karvesh Mising name wrote:
> Dear All, I created a stored procedure in MySql 5 that works fine when i
> try it using the MySql WorkBench.
>
> I an trying to invoke it from BIRT. So I used the SQL STORED PROCEDURE
> QUERY dataset.
>
> I used the query:
>
> {CALL KPIDebtor('2012-01-01', '2012-12-31');}
>
> But for some reason, when i click finish, nothing happens. The window
> will not close and save.. :(
> Could any one give me an idea what I am doing wrong?
>
> Regards,
> Karvesh
Re: Problem with Mysql Stored Procedure [message #884888 is a reply to message #884727] Tue, 12 June 2012 03:01 Go to previous messageGo to next message
karvesh ghunsam is currently offline karvesh ghunsam
Messages: 95
Registered: July 2011
Member
Dear Jason,

In the log it says:

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:No value specified for parameter 1


Has it got something to do with the parameter?
I am simply writing
CALL KPIDebtor('2012-01-01', '2012-12-31');

for the moment. I am hard coding the parameter just for test..

Any help please...
Thanks a lot
Karvesh
Re: Problem with Mysql Stored Procedure [message #884890 is a reply to message #884727] Tue, 12 June 2012 03:06 Go to previous messageGo to next message
karvesh ghunsam is currently offline karvesh ghunsam
Messages: 95
Registered: July 2011
Member
Just in case it might help, here is the stored procedure. It is working fine in MySql workbench. It has 2 cursors and a temporary table that is created, filled, queried and then deleted.


-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `KPIDebtor`(dtFrom DATE, dtTo DATE)
READS SQL DATA
BEGIN

-- ========================================================
-- ========================================================
-- DECLARATIONS:
-- ========================================================

-- Declare variables used just for cursor and loop control
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;


-- declare some variables
DECLARE sales DECIMAL(12,2);
DECLARE revenue DECIMAL(12,2);
DECLARE openingBalance DECIMAL(12,2);
DECLARE monthPeriod VARCHAR(20);


-- declare the opening debtor balance into the cursor openBalCur
DECLARE openBalCur CURSOR FOR
SELECT IFNULL(SUM(AccDue),0) AS Sales, IFNULL(SUM(AccPaid),0) AS Inflow
FROM tblPatientAcc
WHERE TxDt < dtFrom;

-- declare the monthly sales and revenue cursor montlyTransCur
DECLARE montlyTransCur CURSOR FOR
SELECT
DATE_FORMAT(TxDt, '%Y-%m-01') AS Yr_month,
SUM(AccDue) AS MonthSales,
SUM(AccPaid) AS MonthRevenue
FROM
tblPatientAcc WHERE TxDt BETWEEN dtFrom AND dtTo
GROUP BY DATE_FORMAT(TxDt, '%Y-%m-01')
ORDER BY EXTRACT(YEAR FROM TxDt),
EXTRACT(MONTH FROM TxDt);


-- Declare 'handlers' for exceptions
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;

-- ========================================================
-- ========================================================
-- PROGRAM LOGIC:
-- ========================================================


SET sales = 0.00;
SET revenue = 0.00;
SET openingBalance = 0.00;

-- create the temporary table
DROP TEMPORARY TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp
(
PERIOD DATE,
SALES DECIMAL(12,2) NOT NULL DEFAULT 0.00,
INFLOW DECIMAL(12,2) NOT NULL DEFAULT 0.00,
DEBTOR DECIMAL(12,2) NOT NULL DEFAULT 0.00
);


-- open the opening balance cursor
OPEN openBalCur;
SELECT FOUND_ROWS() into num_rows;

openBalLoop: LOOP

FETCH openBalCur
INTO sales, revenue;

-- break out of the loop if
-- 1) there were no records, or
-- 2) we've processed them all
IF no_more_rows THEN
CLOSE openBalCur;
LEAVE openBalLoop;
END IF;

SET openingBalance = (sales-revenue);

-- count the number of times looped
SET loop_cntr = loop_cntr + 1;

END LOOP openBalLoop;


-- open the monthly transactions cursor

SET sales = 0.00;
SET revenue = 0.00;
SET num_rows = 0;
SET loop_cntr = 0;
SET no_more_rows = FALSE;

OPEN montlyTransCur;
SELECT FOUND_ROWS() into num_rows;

monthlyTransLoop: LOOP

FETCH montlyTransCur
INTO monthPeriod, sales, revenue;

-- break out of the loop if
-- 1) there were no records, or
-- 2) we've processed them all
IF no_more_rows THEN
CLOSE montlyTransCur;
LEAVE monthlyTransLoop;
END IF;

SET openingBalance = openingBalance + (sales-revenue);
INSERT INTO tmp (PERIOD, SALES, INFLOW, DEBTOR) VALUES (monthPeriod, sales, revenue, openingBalance);

-- count the number of times looped
SET loop_cntr = loop_cntr + 1;

END LOOP monthlyTransLoop;



SELECT * FROM tmp;


DROP TEMPORARY TABLE IF EXISTS tmp;

END

Re: Problem with Mysql Stored Procedure [message #885160 is a reply to message #884890] Tue, 12 June 2012 14:44 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Any chance you could alter the sp to return 1 row of dummy data?

Jason

On 6/11/2012 11:06 PM, karvesh Mising name wrote:
> Just in case it might help, here is the stored procedure. It is working
> fine in MySql workbench. It has 2 cursors and a temporary table that is
> created, filled, queried and then deleted.
>
>
> --
> --------------------------------------------------------------------------------
>
> -- Routine DDL
> -- Note: comments before and after the routine body will not be stored
> by the server
> --
> --------------------------------------------------------------------------------
>
> DELIMITER $$
>
> CREATE DEFINER=`root`@`localhost` PROCEDURE `KPIDebtor`(dtFrom DATE,
> dtTo DATE)
> READS SQL DATA
> BEGIN
>
> -- ========================================================
> -- ========================================================
> -- DECLARATIONS:
> -- ========================================================
> -- Declare variables used just for cursor and loop control
> DECLARE no_more_rows BOOLEAN;
> DECLARE loop_cntr INT DEFAULT 0;
> DECLARE num_rows INT DEFAULT 0;
> -- declare some variables
> DECLARE sales DECIMAL(12,2);
> DECLARE revenue DECIMAL(12,2);
> DECLARE openingBalance DECIMAL(12,2);
> DECLARE monthPeriod VARCHAR(20);
>
> -- declare the opening debtor balance into the cursor openBalCur
> DECLARE openBalCur CURSOR FOR
> SELECT IFNULL(SUM(AccDue),0) AS Sales, IFNULL(SUM(AccPaid),0) AS Inflow
> FROM tblPatientAcc WHERE TxDt < dtFrom;
> -- declare the monthly sales and revenue cursor montlyTransCur
> DECLARE montlyTransCur CURSOR FOR
> SELECT DATE_FORMAT(TxDt, '%Y-%m-01') AS Yr_month,
> SUM(AccDue) AS MonthSales, SUM(AccPaid) AS MonthRevenue FROM
> tblPatientAcc WHERE TxDt BETWEEN dtFrom AND dtTo
> GROUP BY DATE_FORMAT(TxDt, '%Y-%m-01')
> ORDER BY EXTRACT(YEAR FROM TxDt), EXTRACT(MONTH FROM TxDt);
>
>
> -- Declare 'handlers' for exceptions
> DECLARE CONTINUE HANDLER FOR NOT FOUND
> SET no_more_rows = TRUE;
> -- ========================================================
> -- ========================================================
> -- PROGRAM LOGIC:
> -- ========================================================
> SET sales = 0.00;
> SET revenue = 0.00;
> SET openingBalance = 0.00;
> -- create the temporary table
> DROP TEMPORARY TABLE IF EXISTS tmp;
> CREATE TEMPORARY TABLE tmp
> (
> PERIOD DATE,
> SALES DECIMAL(12,2) NOT NULL DEFAULT 0.00,
> INFLOW DECIMAL(12,2) NOT NULL DEFAULT 0.00,
> DEBTOR DECIMAL(12,2) NOT NULL DEFAULT 0.00
> );
>
>
> -- open the opening balance cursor
> OPEN openBalCur;
> SELECT FOUND_ROWS() into num_rows;
>
> openBalLoop: LOOP
>
> FETCH openBalCur
> INTO sales, revenue;
>
> -- break out of the loop if
> -- 1) there were no records, or
> -- 2) we've processed them all
> IF no_more_rows THEN
> CLOSE openBalCur;
> LEAVE openBalLoop;
> END IF;
>
> SET openingBalance = (sales-revenue);
>
> -- count the number of times looped
> SET loop_cntr = loop_cntr + 1;
>
> END LOOP openBalLoop;
> -- open the monthly transactions cursor SET sales = 0.00;
> SET revenue = 0.00;
> SET num_rows = 0;
> SET loop_cntr = 0;
> SET no_more_rows = FALSE;
> OPEN montlyTransCur;
> SELECT FOUND_ROWS() into num_rows;
>
> monthlyTransLoop: LOOP
>
> FETCH montlyTransCur
> INTO monthPeriod, sales, revenue;
>
> -- break out of the loop if
> -- 1) there were no records, or
> -- 2) we've processed them all
> IF no_more_rows THEN
> CLOSE montlyTransCur;
> LEAVE monthlyTransLoop;
> END IF;
>
> SET openingBalance = openingBalance + (sales-revenue);
> INSERT INTO tmp (PERIOD, SALES, INFLOW, DEBTOR) VALUES (monthPeriod,
> sales, revenue, openingBalance);
>
> -- count the number of times looped
> SET loop_cntr = loop_cntr + 1;
>
> END LOOP monthlyTransLoop;
> SELECT * FROM tmp;
>
> DROP TEMPORARY TABLE IF EXISTS tmp;
>
> END
>
Re: Problem with Mysql Stored Procedure [message #885454 is a reply to message #885160] Wed, 13 June 2012 03:10 Go to previous message
karvesh ghunsam is currently offline karvesh ghunsam
Messages: 95
Registered: July 2011
Member
Dear Jason,

I found the problem.
In the store procedure declaration, i missed the IN setting for the parameter

CREATE DEFINER=`root`@`localhost` PROCEDURE `KPIDebtor`(IN dtFrom DATE, IN dtTo DATE)

It is working fine now..

Thanks a lot!

Karvesh
Previous Topic:Replace Function
Next Topic:Custom StyleSheet not included when viewed?
Goto Forum:
  


Current Time: Fri Oct 24 15:03:22 GMT 2014

Powered by FUDForum. Page generated in 0.12157 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software