CODEWITHSUNDEEP
mysqlstored-proceduresmysql-workbench
handicop
handicop

Reputation: 922

MySQL: gets all of procedure out parameter null

It seems like the "select into" isn't working with the following code. I can get data from "select clause" from sql query but with test procedure code, it only returns null value for every out parameter. (There's one record should be returned)

Can anyone tell me where I am doing wrong?

OS: Mac OSX DBM: MySQL WorkBench 6.1 MySQL: 5.6.13

Procedure Code

DELIMITER $$

CREATE DEFINER= PROCEDURE `POLL_NEW_MESSAGE`(OUT DEVICEID INT, OUT ORGID INT, OUT MSGDATE VARCHAR(8), OUT MSGTIME VARCHAR(6), OUT BODY TEXT, OUT TOKEN VARCHAR(64))
BEGIN
    DECLARE DEVICEID INT        DEFAULT -1;
    DECLARE ORGID INT           DEFAULT -1;
    DECLARE MSGDATE VARCHAR(8)  DEFAULT NULL;
    DECLARE MSGTIME VARCHAR(6)  DEFAULT NULL;
    DECLARE BODY TEXT           DEFAULT NULL;
    DECLARE TOKEN VARCHAR(64)   DEFAULT NULL;
    DECLARE UPDATED INT         DEFAULT 0;

    SELECT msg.device_id, msg.org_id, msg.msg_date, msg.msg_time, msg.body
    INTO DEVICEID, ORGID, MSGDATE, MSGTIME, BODY
    FROM pm_msg msg, pm_org org
    WHERE msg.status = '0'
        AND org.org_id = msg.org_id
        AND org.status = 'G';

END

Test Code1

PREPARE s FROM ' CALL `POLL_NEW_MESSAGE`(@DEVICEID, @ORGID, @MSGDATE, @MSGTIME, @BODY, @TOKEN)';
execute s;
SELECT @DEVICEID, @ORGID, @MSGDATE, @MSGTIME, @BODY, @TOKEN;

Test Code2

CALL `POLL_NEW_MESSAGE`(@DEVICEID, @ORGID, @MSGDATE, @MSGTIME, @BODY, @TOKEN);SELECT @DEVICEID, @ORGID, @MSGDATE, @MSGTIME, @BODY, @TOKEN;

Upvotes: 1

Views: 2137

Answers (3)

SGR
SGR

Reputation: 1

Name of declared parameter inside stored procedure has to be UNIQUE. (Note: these names are case insensitive) Even if name of declared parameter is same as column name from table, it returns NULL.

Upvotes: 0

handicop
handicop

Reputation: 922

Declaring out parameter within procedure with same variables names caused the problem. Once I remove all declaration of variables which are already used as out parameter of procedure, it works as supposed to.

Fixed code

DELIMITER $$

CREATE DEFINER= PROCEDURE `POLL_NEW_MESSAGE`(OUT DEVICEID INT, OUT ORGID INT, OUT MSGDATE VARCHAR(8), OUT MSGTIME VARCHAR(6), OUT BODY TEXT)
BEGIN

    SELECT msg.device_id, msg.org_id, msg.msg_date, msg.msg_time, msg.body
    INTO DEVICEID, ORGID, MSGDATE, MSGTIME, BODY
    FROM pm_msg msg, pm_org org
    WHERE msg.status = '0'
        AND org.org_id = msg.org_id
        AND org.status = 'G'
        LIMIT 1;

END

Upvotes: 2

Rahul
Rahul

Reputation: 77866

You have declared all your OUT variables as scalar type but in your select into query you are trying to make them store multiple values. The way you are doing .. you will end up getting wrong result cause you have declared the OUT variables as scalar. So, if your query returns multiple rows then you will definitely end up getting wrong result.

SELECT msg.device_id, msg.org_id, msg.msg_date, msg.msg_time, msg.body
INTO DEVICEID, ORGID, MSGDATE, MSGTIME, BODY
FROM pm_msg msg, pm_org org
WHERE msg.status = '0'
    AND org.org_id = msg.org_id
    AND org.status = 'G'
    LIMIT 1;

Suggestion is, have a table valued parameter as OUT parameter.

Upvotes: 1

Related Questions