How to get OUT parameter from mysql stored procedure using ZF

[dungpt29]

I am trying to build a website based on:

1) MySQL 5.5.27

2) PHP 5.4.7

3) Zend Framework 1.12.0

 

I am trying to write a function that gets value of out parameter from a stored procedure as the following:

 

public static function insertNewAcc($param1,&$param2)
{

      $options = array(Zend_Db::AUTO_RECONNECT_ON_UNSERIALIZE  => true);

      $params  = array(

                              'host'           =>  'localhost',

                         'username'       =>  'username',

                          'password'       =>  'password',

                          'dbname'         =>  'mydb',

                        'options'        =>  $options);

 

      $db = Zend_Db::factory('Pdo_Mysql', $params);

 

      $stmt = new Zend_Db_Statement_Pdo($db,"call sp_insert_newnumber(:v_in_number, @v_out_number);");

      $stmt->bindParam(":v_in_number", $param1, PDO::PARAM_INT);

      $stmt->execute();

      $rows = $stmt->fetchAll();

      $db->closeConnection();

      return $rows;
}

 

What is code to get value of @v_out_number that is an out parameter?

Note that I use Zend_Db_Adapter_Pdo_Mysql for my web application.

 

Any help would be appreciated!

Edited May 2, 2013 by dungpt29

[kicken]

I've not used Zend or done much with Mysql stored procedures, but using PDO/SQL Server the way you would do it is to bindParam the value for the out parameter also. Such as:

 

$sql = "call sp_insert_newnumber(:v_in_number, :v_out_number);"
$stmt = new Zend_Db_Statement_Pdo($db,);
$stmt->bindParam(":v_in_number", $param1, PDO::PARAM_INT);
$stmt->bindParam(":v_out_number", $param2, PDO::PARAM_INT, 11);
$stmt->execute();

After you execute it, $param2 would contain the value of the output parameter. You could try something similar and see if it works the same when using mysql. Edited May 2, 2013 by kicken

[dungpt29]

Hi kicken,

First, thank you very much for your help.

I am sure that your solution will not work. It is understandable because you are not a web developer using Zend.

I have found out the solution for this problem by myself. It is quite simple and it works fine.

By the way, anyone who has the same problem can reply to me and I am ready to share my experience.

[kicken]

By the way, anyone who has the same problem can reply to me and I am ready to share my experience.

You should just post how you ended up solving the issue, rather than wait for someone else to ask how. Could be several months before someone posts a reply asking how and then you wont be around to answer them. Post the solution now and save them and yourself the trouble.

[dungpt29]

For example, I have a stored procedure in MySQL database as the following:

DELIMITER $$

DROP PROCEDURE IF EXISTS `mydb`.`sp_insert_newnumber` $$
CREATE PROCEDURE `mydb`.`sp_insert_newnumber` (IN v_innumber INTEGER,
                                              OUT v_outnumber INTEGER)
BEGIN
     set v_outnumber = v_innumber;
     select v_innumber as in_number;
END $$

DELIMITER ;

The PHP function that retrieves the value of out parameter is as the following:

public static function  insertNewAcc($param1, &$param2)
{
    $options = array(Zend_Db::AUTO_RECONNECT_ON_UNSERIALIZE => true)
    $params = array(
                  'host'     => 'localhost',
                  'username' => 'username',
                  'password' => 'password',
                  'dbname'   => 'mydb',
                  'options'  => $options);
    $db = Zend_Db::factory('Pdo_Mysql', $params);

    $stmt = new Zend_Db_Statement_Pdo($db, "call sp_insert_newnumber(:v_innumber,
                                                                     @v_outnumber)");
    $stmt->bindParam(":v_innumber", $param1, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(); // Get result set returned by stored procedure

    $stmt->closeCursor();
    $stmt = $db->prepare('select @v_outnumber as out_number');
    $stmt->execute();
    $outnumberset = $stmt->fetchAll();
   
    $param2 = $outnumberset[0]['out_number'];// Retrieve the value of v_outnumber

    $db->closeConnection();
    return $rows;
}

Here I explain the code in detail:

 

The reason why I use @v_outnumber that is a session variable in MySQL and is passed to the output parameter of stored procedure is that the value of session variable is maintained during the connection to database that currently is held by $db. So I have to execute the second query using $db to get the value of out parameter.

 

As I know, not just as SQL Server, MySQL 5.5 does not still support the syntax as suggested by Kicken.

Edited May 3, 2013 by dungpt29