Improve my Zend Stored Procedure calling code.

Question

Hi,

I'm wondering how i can improve my Zend code that calls a stored procedure. At the moment i'm using a MySQL DB, and the action function in my controller below works, but it seems nasty.

public function callSPAction()
{
    $param = $this->_request->getParam('param', 0);

    $bootstrap = $this->getInvokeArg('bootstrap');
    $config = $bootstrap->getOptions();

    $mysqli = new mysqli(
        $config['resources']['db']['params']['host'],
        $config['resources']['db']['params']['root']['username'],
        $config['resources']['db']['params']['root']['password'],
        $config['resources']['db']['params']['dbname']);

    $rs = $mysqli->query(sprintf('CALL mystoredprocedure(%d)',$param));
    if(mysqli_error($mysqli))
    { 
        throw new exception(mysqli_error($mysqli), mysqli_errno($mysqli)); 
    } 
    $this->_helper->redirector('index', 'index');
}

I'd prefer to use the Zend_DB classes to call the stored procedure but i'm not sure how this can be done?

Since i'm calling a number of stored procedures, i think it would be better to create a helper class that wraps the logic for connecting to the DB. It would expose methods that would wrap the underlying stored procedure. My controller code could then just call

StoredProcedureHelper::callMyStoredProdecure($this->_request->getParam('param', 0);

Is this possible or even recommended?

Answer 1

I would favor the use of models for your data access. Also, I would advice to define your db adapter at your application's config file so it's transparent to your application code, and use PDO so your application is not tied to a particular DB manager in case you need to point to a different database in the future.

For example, instead of having all of your data access logic in your controller's action, it could be simplified as follows:

//within some controller action
$model = MyUbberCoolSuperModel();
$model->myUbberCoolMethod($params);

//And in your model, wich can extend Zend_Db_Table_Abstract
public function myUbberCoolMethod($params)
{
   $pdo = $this->getAdapter()->prepare("CALL myProcedure(:param)");
   $pdo->bindParam(':param', $param, PDO::PARAM_STR); 
   $pdo->execute();
   // Any additional logic you might want to do
}

I think this way is clearer, your controller is only resposible of invoking the method on the model, but the model handles the data access. So if anything related to the data access needs to be refactored, changed, or whatever, you know to which model you have to go instead of changing any controller actions that might impact any other things.

Hope it helps ;)

Cheers.