Stored Procedure implementation issue with ZF 2.0.5

[aashah]

Hi All,


I have started to work with the Zend Framework 2.0.5. In which, all database operation performed through stored procedure. At a time of user login, I am validating user email address. For that, I have added one method in service file and created required procedure as follows,

User Controller :

$result = $this->getUserService()->validateUser($_REQUEST);


File : zf2_tutorial\module\ZfcUser\src\ZfcUser\Service\User.php

/* $data is the user provided login email address */
public function validateUser(array $data)
{
$results = $this->dbAdapter->query("CALL userlogin(".$data['identity'].")", Adapter::QUERY_MODE_EXECUTE);
$row = $results->current();
print_r($row,1);
}

Stored Procedure :

DELIMITER $$

USE `zf2_tutorial`$$

DROP PROCEDURE IF EXISTS `userlogin`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `userlogin`(IN email1 VARCHAR(255))
BEGIN
SELECT * FROM USER WHERE email=email1;
END$$

DELIMITER ;


Please share your views if I am missing or used any wrong syntax.

Thanks,
Ankit Shah.

[denozawr]

I call stored procedures (ms sql server) like this

Code: Select all

<?php
namespace API\Model;
use Zend\Db\Adapter\Adapter;

class Depots
{
   /**
    * @var Zend\Db\Adapter\Adapter
    */
   protected $adapter;

   public function __construct(Adapter $adapter)
   {
      $this->adapter = $adapter;
   }

   public function getDepots($dsp, $type, $sort)   
   {
      $dsp = isset($dsp)?$dsp:'null';
      $type = isset($type)?$type:'null';
      $sql = "exec Depots_get_sp @status='A', @dsp=".$dsp.", @dpt_Status=".$type.", @sortorder=".$sort;
      $statement = $this->adapter->query($sql);
      $result = $statement->execute();
      return $result;
   }   
}

It's working!

[aashah]

Thanks denozawr for reply and sharing your views.

For now, I am developing my stored procedures with MySql but got the error of database adapter which is resolved now(referenced to your shared code).

Now, I need to convert my Mysql stored procedures into MsSql stored procedures.

Is there any major change take place if we convert MySql stored procedure to MsSql?


Again thanks for sharing your views.


Regards,
Ankit Shah.