MySQL: Problems with procedure parameters

Question

It seems something is wrong with my procedure. When I execute it, it should create an entry, directly modify a column and then return the id of it.

But nothing is created and it also returns nothing (not even an error is shown)

When I use hard coded values instead of using the parameters, the entry is created, but not edited and the ID is still not returned.

Could you guys have a look over it? This is the whole procedure result using the export function of phpMyAdmin:

CREATE DEFINER=`dbuser`@`localhost` PROCEDURE `createRecruitment`(IN `p_user_id` INT(11), IN `p_platform` ENUM('uplay','steam','ps4','xb1') CHARSET utf8, IN `p_activity` ENUM('pve','pvp') CHARSET utf8, IN `p_description` TEXT CHARSET utf8)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE v_id INT(11);

    INSERT INTO
        recruitments (
            user_id,
            creationDate,
            platform_id,
            activity,
            description
        )
    SELECT
        p_user_id,
        CURRENT_TIMESTAMP,
        platforms.id,
        p_activity,
        p_description
    FROM
        platforms
    WHERE
        platforms.platform = p_platform;

    SET v_id = LAST_INSERTED_ID();

    UPDATE
        recruitments
    SET
        lastActivity = creationDate
    WHERE
        id = v_id;

    SELECT v_id;
END

EDIT

It seems the main problem is the "p_platform" parameter. I wanted to limit the input to the given ENUM but it seems the WHERE platforms.platform = p_platform doesn't work proper with this.

Did you mean to have a space as the first character of the proceedure name? — RiggsFolly
– RiggsFolly, Commented Feb 9, 2016 at 12:10

msvairam · Accepted Answer · 2016-02-09 12:20:22Z

0

Use the below SP. I have use OUT v_id field and assign the value

CREATE DEFINER=`dbuser`@`localhost` PROCEDURE `createRecruitment`(IN `p_user_id` INT(11), IN `p_platform` ENUM('uplay','steam','ps4','xb1') CHARSET utf8, IN `p_activity` ENUM('pve','pvp') CHARSET utf8, IN `p_description` TEXT CHARSET utf8, OUT `v_id` INT(11))
        MODIFIES SQL DATA
        DETERMINISTIC
        SQL SECURITY INVOKER
    BEGIN


        INSERT INTO
            recruitments (
                user_id,
                creationDate,
                platform_id,
                activity,
                description
            )
        SELECT
            p_user_id,
            CURRENT_TIMESTAMP,
            platforms.id,
            p_activity,
            p_description
        FROM
            platforms
        WHERE
            platforms.platform = p_platform;

        SET v_id = LAST_INSERTED_ID();

        UPDATE
            recruitments
        SET
            lastActivity = creationDate
        WHERE
            id = v_id;


    END

answered Feb 9, 2016 at 12:20

msvairam

8725 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

cherry-wave Over a year ago

I have the experience that PHPs PDO can't work well with these parameters: stackoverflow.com/questions/23747835/…

cherry-wave Over a year ago

I tried this but it still doesn't work. See my edited question for more infos.

msvairam Over a year ago

Use v_id instead of returnid like your example link

msvairam Over a year ago

Are you able to insert recruitments table and also get the v_id ?

cherry-wave Over a year ago

Yeah as I said: If I set platform to "ps4" manually instead of using p_platform-parameter (where "ps4" is chosen from an enum) it at least creates the recruitment but doesn't return the v_id.

|

Collectives™ on Stack Overflow

MySQL: Problems with procedure parameters

EDIT

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related