петък, юни 24, 2011

PHP PDO with MySQL stored procedures - problem with selecting OUT parameter

One day lost in discovering how to call and afterwards get stored procedure OUT parameters, finally resolved.

Stored Procedure:
DELIMITER $$

DROP PROCEDURE IF EXISTS `mydb`.`proc_name` $$
CREATE PROCEDURE `mydb`.`proc_name`(
    IN i_myInput INT,
    OUT o_statusCode INT,
    OUT o_statusMsg VARCHAR(255))
BEGIN
    IF i_myInput IS NULL THEN
        SET o_statusCode = 1;
        SET o_statusMsg  = 'Error: input parameter is NULL';
    ElSE
        SET o_statusCode = 0;
        SET o_statusMsg  = 'OK';
    END IF;

    SELECT NULL as `result`;
END $$

DELIMITER ;

PHP Code:
# create PDO DB object
$mydb = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass");

$input = 5;
$proc = $mydb->prepare("CALL proc_name($input, @o_code, @o_message)");
$proc->execute();
$proc->closeCursor(); # !!!!! the line that resolved the issue

$output = $mydb->query("select @o_code, @o_message")->fetch(PDO::FETCH_ASSOC);
var_dump($output); // array('@o_code'=>'0', 'o_message'=>'OK')

Няма коментари: