Stored Procedure implementation issue with ZF 2.0.5

For programming and general questions on Zend Framework

Stored Procedure implementation issue with ZF 2.0.5

Postby aashah on Mon Dec 31, 2012 9:56 am

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.
aashah
 
Posts: 8
Joined: Thu Dec 20, 2012 2:26 pm

Re: Stored Procedure implementation issue with ZF 2.0.5

Postby denozawr on Mon Dec 31, 2012 10:24 am

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!
denozawr
 
Posts: 5
Joined: Thu Dec 20, 2012 6:44 pm

Re: Stored Procedure implementation issue with ZF 2.0.5

Postby aashah on Mon Dec 31, 2012 12:36 pm

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.
aashah
 
Posts: 8
Joined: Thu Dec 20, 2012 2:26 pm


Return to Zend Framework

Who is online

Users browsing this forum: Google [Bot] and 3 guests

cron