From: Cantwell, Bryan (bcantwellfirescope.com)
Date: Thu Nov 13 2008 - 17:12:59 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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:bcantwellfirescope.com]
Sent: Thursday, November 13, 2008 4:17 PM
To: mysqllists.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 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 IF;

UNTIL done END REPEAT;

CLOSE cur1;

return filterSQL;

END;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]