List:General Discussion« Previous MessageNext Message »
From:Bryan Cantwell Date:November 13 2008 10:16pm
Subject:Function returns null when running sql manually works
View as plain text  
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;


Thread
Function returns null when running sql manually worksBryan Cantwell13 Nov
  • RE: Function returns null when running sql manually worksBryan Cantwell14 Nov
    • RE: Function returns null when running sql manually worksBryan Cantwell14 Nov