How to create mysql stored function at module

Ankhaa
2008-12-17
2013-04-25
  • Ankhaa
    Ankhaa
    2008-12-17

    Hi all,
    i using a mysql stored function in my module, but how to create function in database at module installation?
    Xoopscube installation don't recognizing the mysql's "DELIMITER $$" command :( and returning this error.

    ERROR:
    DELIMITER $$ DROP FUNCTION IF EXISTS `ufn_getDepIDs`$$ CREATE FUNCTION `ufn_getDepIDs`(pHosp_cd varchar(7)) RETURNS TEXT DETERMINISTIC READS SQL DATA BEGIN DECLARE done INT DEFAULT 0 is not a valid SQL!

    For example my stored function like following:

    DELIMITER $$

    DROP FUNCTION IF EXISTS `ufn_getDepIDs`$$

    CREATE FUNCTION `ufn_getDepIDs`(pCd varchar(7))
    RETURNS TEXT
    DETERMINISTIC
    READS SQL DATA
    BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE sReturn TEXT DEFAULT '';
    DECLARE tmp VARCHAR(64);

    DECLARE cur1 CURSOR FOR
    Select
    D.Col1
    From Table1 CD
    Left Join Table2 D On D.Col_CD = CD.Col_cd
    Where CD.Col_cd = pCd;

    DECLARE CONTINUE HANDLER FOR not found SET done = 1;

    OPEN cur1;

    REPEAT
    FETCH cur1 INTO tmp;
    IF NOT done THEN
    BEGIN

    IF (LENGTH(sReturn) = 0) THEN
    SET sReturn = CONCAT(sReturn,tmp);
    ELSE
    SET sReturn = CONCAT(sReturn,',', tmp);
    END IF;

    END;
    END IF;
    UNTIL done
    END REPEAT;

    CLOSE cur1;

    RETURN sReturn;
    END$$

    DELIMITER ;

    Is there any way?
    Thanks.