I have the solution, had to add a begin/end inside the repeat to protect
my original done var and declare another inside the loop.
I'm glad we had this little talk =P
l8r
DELIMITER $$
DROP FUNCTION IF EXISTS `firescope`.`reportWhereClause`$$
CREATE [EMAIL PROTECTED] FUNCTION `reportWhereClause`(RepID
BIGINT(20) UNSIGNED) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE doneINT DEFAULT 0;
DECLARE filterSQL, filterTMP, colName VARCHAR (255);
DECLARE colID, rID BIGINT (20) UNSIGNED;
DECLARE cur1 CURSOR FOR SELECT
report_column_id, column_name
FROM
report_columns
WHERE
report_id = RepID
ORDER BY
report_column_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
SELECT CONCAT(' ', rvf.filter_operator, ' ', colName, ' ', (CASE
rvf.filter_condition WHEN 'not_equal' THEN '' WHEN 'greater_than' THEN
'' ELSE '=' END)
, ' ', , rvf.filter_value,, ' ')
INTO
filterTMP
FROM
report_filters rvf
WHERE
rvf.report_id = RepID
AND rvf.report_column_id = colID;
IF filterTMP IS NOT NULL THEN
IF filterSQL IS NULL THEN
SELECT
filterTMP
INTO
filterSQL;
ELSE
SELECT
CONCAT(filterSQL, filterTMP)
INTO
filterSQL;
END IF;
END IF;
SET filterTMP = NULL;
END;
END IF;
UNTIL done
END REPEAT;
CLOSE cur1;
RETURN filterSQL;
END$$
DELIMITER ;
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 5:13 PM
To: mysql@lists.mysql.com
Subject: RE: Function returns null when running sql manually works
OK, I know WHY it is returning null, just not WHAT to do about it.
In the inside sql, there is not always a result. So, done becomes 1 and
the repeat exits.
How can I keep from this happening? How could I make another 'done' like
variable that would not get also set to 1 if the inner sql doesn't
return a record in this pass?
thx
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 4:17 PM
To: mysql@lists.mysql.com
Subject: Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then ''
when 'greater_than' then '' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null
