Jump to content

How to get OUT parameter from mysql stored procedure using ZF


dungpt29

Recommended Posts

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 by dungpt29
Link to comment
Share on other sites

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 by kicken
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by dungpt29
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.