-1

MariaDB 10.3.14

I have a function that generates the root id in a hierarchy:

Hierarchy

The function is this:

CREATE FUNCTION `getRootId`(GivenID INT) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE ch INT;
DECLARE root INT;
SET ch = GivenID;

WHILE ch >= 0 DO
    SELECT id, parent_id INTO root, ch  FROM
    (SELECT id, parent_id FROM pctable WHERE id = ch) A;
END WHILE;
RETURN root;
END

I want to be able to provide the id, parent_id and table name as variables to this function, so i can easily be able to use it in different hierarchy tables easily.

something like this:

CREATE FUNCTION `getRootId`(GivenID INT,id_column_name varchar(45),parent_id_column_name varchar(45),target_table_name varchar(45))

One function to be used for both Topics, Indicators ....

enter image description here

I'm new to advanced SQL coding, so apologies if this is a newbie question.

Many thanks.

Updates:

I switched to procedure to use prepared statements:

CREATE DEFINER=`root`@`localhost` PROCEDURE `getRootIdFlex`(IN GivenID INT,IN id_name varchar(45),IN parent_name varchar(45),IN tbl_name varchar(45) , OUT root varchar(1000))
DETERMINISTIC
BEGIN
DECLARE id_or_parent INT;

SET @tbl_name = tbl_name;
SET @id_name = id_name;
SET @parent_name = parent_name;

SET id_or_parent = GivenID;

SET @s := CONCAT('SELECT ?, ? INTO root, id_or_parent FROM (SELECT ?, ? FROM ? WHERE ? = id_or_parent) A');
PREPARE stmt FROM @s;
WHILE id_or_parent >= 0 DO
    EXECUTE stmt USING @id_name, @parent_name, @id_name, @parent_name, @tbl_name, @id_name;
END WHILE;
END

But when i execute the procedure this way:

SET @root := NULL;
CALL getRootIdFlex(11, 'Topic_id', 'Topic_Topic_id','Topic', @root);
SELECT @root;

I get:

Error Code: 1327. Undeclared variable: root

The root is declared as OUT variable. Why this error ?

  • See PREPARE and EXECUTE. – Rick James Dec 11 '19 at 21:28
0

I fixed it, here is the original function with some edits to make it readable. I changed the while condition to stop at NULL, because that's the endpoint in my hierarchy.

CREATE FUNCTION `getRootId`(GivenID INT) RETURNS int(11)
DETERMINISTIC
BEGIN
SET @root = NULL;
SET @tmp_parent = GivenID;
WHILE @tmp_parent IS NOT NULL DO
    SELECT id, parent_id INTO @root, @tmp_parent  FROM
    (SELECT id, parent_id FROM pctable WHERE id = @tmp_parent) A;
END WHILE;
RETURN @root;
END

In order to have variables inside a prepared statement you need to use PROCEDURES so functions will not do it:

CREATE PROCEDURE `getRootIdFlex`(IN GivenID INT,IN id_name varchar(45),IN parent_name varchar(45),IN tbl_name varchar(45) , OUT root varchar(1000))
DETERMINISTIC
BEGIN
SET @tmp_parent = GivenID;
SET @tmp_root := 111;

SET @s := CONCAT('SELECT ',id_name,',',parent_name, ' INTO @tmp_root, @tmp_parent FROM (SELECT ',id_name,',',parent_name, ' FROM ',tbl_name,' WHERE ',id_name,' = @tmp_parent) A');

PREPARE stmt FROM @s;
WHILE @tmp_parent IS NOT NULL DO
    EXECUTE stmt;
END WHILE;
SET root := @tmp_root;
END

Key point here is: Make sure to add '@' symbol in your variables everywhere to avoid confusion with table columns in your SQL queries.

Thanks.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.