PHP: How to get the output value of a MySql Stored Procedure?

Question

I try to get the output value of a MySQL stored procedure with PHP. I found some similar questions here on StackOverflow, but none of them helped.

Based on my researches, I found two ways of how to achieve this goal, however, none of them worked so far. The procedure's output is simply not caught, no matter how I tried.

FIRST ATTEMPT:

In the following case, I set AAA as the default value for the variable @name, then I pass it as a parameter to the stored procedure jani(). In the procedure, I change the value of the in-output variable to BBB, so I expect the passed AAA to be changed to BBB. Then I select the variable and try to display the result value, however, the value remains AAA.

My code:

// DEFINING THE PROCEDURE:
$sql = '
    DROP PROCEDURE IF EXISTS jani;
    CREATE PROCEDURE jani (
        INOUT output            VARCHAR(3)
    )
    BEGIN

        SET @output = "BBB";
    END';
$oDb->exec($sql);



// DEFINING THE VARIABLE TO CATCH THE OUTPUT:
$sql = 'SET @name = "AAA"';
$do = $oDb->prepare($sql);
$do->execute();



// CALLING THE PROCEDURE AND TRYING TO CATCH THE OUTPUT:    
$sql = 'CALL jani(@name)';
$do = $oDb->prepare($sql);
$do->execute();



// SELECTING THE (HOPEFULLY UPDATED) VARIABLE:
$sql = 'SELECT @name name';
$do = $oDb->prepare($sql);
$do->execute();



// DISPLAYING THE RESULT:
var_dump($do->fetchObject());

SECOND ATTEMPT:

I also followed tutorials like this here without success. The source claims that $do->bindParam() should work, but it did not help in my case. The value of the variable is still unchanged. My code implementing the second solution:

// DEFINING THE PROCEDURE:
$sql = '
    DROP PROCEDURE IF EXISTS jani;
    CREATE PROCEDURE jani (
        INOUT output            VARCHAR(3)
    )
    BEGIN

        SET @output = "BBB";
    END';
$oDb->exec($sql);



// SETTING DEFAULT VARIABLE VALUE:
$name = 'AAA';



// CALLING THE PROCEDURE AND BINDING THE VARIABLE TO ITS PARAMETER:
$sql = 'CALL jani(?)';
$do = $oDb->prepare($sql);
$do->bindParam(1, $name, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 3);
$do->execute();



// DISPLAYING THE RESULT (which is still "AAA" unfortunately):
echo $name;

Both cases return the unmodified value AAA instead of the procedure output BBB. What am I doing wrong?

Thanks in advance for all the helping comments and answers :) Have a nice day!

Why not put it in a single query ??? $sql = 'SET @name = "AAA"; CALL jani(@name); SELECT @name name;', then execute it. — user9335240
– user9335240, Commented Oct 10, 2018 at 9:56
I tried that too, but then the var_dump() result is either bool(false) (in case I use $do->fetchObject()) or array(0) { } (in case I use $do->fetchAll()) @user9335240 — janika23
– janika23, Commented Oct 10, 2018 at 10:38
Did you try putting that query in phpMyAdmin, MySQL Workbench, or in the MySQL client terminal app directly and see the result ? — user9335240
– user9335240, Commented Oct 10, 2018 at 11:17
Maybe interesting? It is one (tested) way to get output from a mysql procedure. MySQL retrieve variable from Stored Procedure in PHP PDO. It is a 'little clumsy' but it does work. ;-/ — Ryan Vincent
– Ryan Vincent, Commented Oct 10, 2018 at 12:33
@user9335240 it only worked in phpMyAdmin when I renamed the variable name (since this is a reserved keyword in SQL that I forgot) to something else and used the SELECT statement, then the desired value returned. Despite I could make it work there, my PHP code still could not get the output value from the stored procedure, so the result stayed NULL :( — janika23
– janika23, Commented Oct 12, 2018 at 16:58

janika23 · Accepted Answer · 2018-10-12 22:57:24Z

1

Unfortunately, I could not find the right solution to catch the output of my stored procedure in PHP. According to this StackOverflow answer,

It turns out that this is a bug that has been going on for a long time... since 2005!

The source I discovered by @RyanVincent's comment also cites this bug report through 2005-2013 and this bug report from 2013-present.

I still needed a solution so I decided to turn my stored procedure into a stored function instead where I could easily catch the output using a simple SELECT statement before calling the function. This way, the return value will be selected which can be passed to PHP.

THE CODE:

// DEFINING THE FUNCTION:
$sql = '
    DROP FUNCTION IF EXISTS jani;
    CREATE FUNCTION jani ()
    RETURNS VARCHAR(3) DETERMINISTIC
    BEGIN
        RETURN "BBB";
    END';
$oDb->exec($sql);



// SELECTING THE RETURN VALUE OF THE FUNCTION:
$sql = 'SELECT jani();';
$do = $oDb->query($sql);



// DISPLAYING THE RESULT:
var_dump($do->fetchAll());

Thanks again for all the comments :) I hope this solution will help someone else spending hours on fixing their code.

edited Oct 12, 2018 at 22:57

answered Oct 12, 2018 at 17:31

janika23

451 silver badge7 bronze badges

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

3 Comments

Ryan Vincent Over a year ago

It is good that you have one output so that you can just use a function in your circumstances. The issue arises when the processing is complex and you want more than one output. Alas, from the point of view of MySQL. Functions are different from Procedures. I am glad you solved your problem.

janika23 Over a year ago

What kind of disadvantages do you have in mind? Like in which situations is it better to use functions instead of stored procedures and vice versa in your experience? @RyanVincent

Ryan Vincent Over a year ago

I never mentioned disadvantages? I never mentioned about 'better' or 'worse' about using function or procedures? They both have useful 'use cases'.

Collectives™ on Stack Overflow

PHP: How to get the output value of a MySql Stored Procedure?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related