Home » Archived » BIRT » Problem with Mysql Stored Procedure
|
Re: Problem with Mysql Stored Procedure [message #884727 is a reply to message #884639] |
Mon, 11 June 2012 17:25 |
|
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 #884890 is a reply to message #884727] |
Tue, 12 June 2012 03:06 |
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 |
|
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
>
|
|
| |
Goto Forum:
Current Time: Fri Apr 19 03:13:38 GMT 2024
Powered by FUDForum. Page generated in 0.02181 seconds
|