PhpMyAdmin - err 1064 - set variable in function

Question

I'm getting hard time with PhpMyAdmin. My table is :

material(id_item, id_collection,...)
primarey_key(id_item, id_collection).

I want to make a function to prevent loop in Acyclic Graph Database. So i want to force id_collection > id_item for new id_collection.

My function is (i have set // in SQL tab):

    //

CREATE or REPLACE function collections_prevent_cycle()
 BEGIN
    DECLARE max_of_both SMALLINT;
    SET max_of_both := (MAX(GREATEST(id_item, id_collection)) FROM material);

    IF NOT EXISTS(SELECT id_collection FROM material where id_collection = NEW.id_collection) THEN
        IF (NEW.id_item >= max_of_both) THEN SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'You may not insert items that doesnt exist';

        ELSEIF(NEW.id_collection <> max_of_both + 1) THEN SIGNAL'45000'
            SET MESSAGE_TEXT = 'You may not insert non stable id_collection';
        END IF;
    END IF;
    RETURN 0;
 END;

//

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'function collections_prevent_cycle() RETURN integer BEGIN DECLARE max_of_b' at line 1

I'm messing with declare but can't get rid off - Anyone could help me plz?

Fixed my first pbm of Delimiter with :

• Delimiter in PhpMyadmin

Answer 1

Finally found a solution to the error.

First :

• It seems u can't use NEW & OLD keywords in function on MYSQL. So i made a Trigger instead.

• Switched to Adminer instead of PHPmyAdmin to use DELIMITER I notice DELIMITER*(space)$$* means (space)$$ and not only $$

• SET := didn't work so I use basic SELECT ... INTO... FROM

• CREATE or Replace doesn't existe - u have to separate operations.

Here is the code i made which compile but doens't work.

DELIMITER $$
DROP trigger IF EXISTS collections_prevent_cycle $$
CREATE trigger collections_prevent_cycle 
BEFORE insert ON material FOR EACH ROW BEGIN

    DECLARE max_of_id_item SMALLINT;
    DECLARE max_of_both SMALLINT;

    SELECT MAX(GREATEST(id_item, id_collection))     
    INTO max_of_both    
    FROM material;

    SELECT MAX(id_item)
    INTO max_of_id_item
    FROM material;

    IF ((SELECT id_collection 
          FROM material 
          WHERE id_collection = NEW.id_collection) = NULL) THEN
            IF (NEW.id_item > max_of_id_item) THEN             
                SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'You may not insert items that doesnt exist';

            ELSEIF (NEW.id_collection <> max_of_both + 1) THEN             
                SIGNAL SQLSTATE '45000'            
                SET MESSAGE_TEXT = 'You may not insert non stable id_collection';
            END IF;
    END IF;
END $$
DELIMITER ;

My Goal was to force insertion this way :

• If a new collection id is added - It must be the (Max id of both collection & item) + 1
• If a new collection id is added - It must contains an existing item (id_item < max(id_item))

If anyone have an idea why trigger doesn't trigger - I'm out of gas :'(.

EDIT : I tested both sub imbricated IF (IF & ELSEIF) They work alone.

THEREFORE it is the FIRST IF which fail.

I suppose = NULL is not adequate BUT can't find the answer in the documentation of the possible outcomes for a query.

I Dig this way - If anyone have a beautiful solution I take it . Kappa.

EDIT 2 : It Works!

(SELECT count(*) 
          FROM material 
          WHERE id_collection = NEW.id_collection) = 0)

Replaced By

(SELECT count(*) 
          FROM material 
          WHERE id_collection = NEW.id_collection) = 0)

So the query returns 0 when it's empty (instead of this obvious empty ressources).

I won!