Execute MSSQL stored procedure via Zend Framework

Question

Request: Please show me a working example of how to retrieve(in an array?) returned rows from a Stored Procedure on a MSSQL server using Zend Framework and passing parameters to the stored procedure.

Explanation: I can't find anything in the ZF docs about how to execute SP on MSSQL servers. I'm aware that it is possible via plain PHP. However, since my project uses ZF, I'd like not to have to have extra code to open a connection to the server, etc. etc. Digging through the ZF code I see things like "bindParam" and "execute" and "prepare". However I have no idea how these work or what I am to do with them. So of course a bit of explanation to the requested example code would be very nice.

I'm using Zend_Db_Adapter_Pdo_Mssql by the way.

Thanks!

Answer 1

Here's what I did using the php5-sybase driver on Debian. This is plain PHP, so probably that's not what you want, but maybe it will give you some leads. Even doing this via plain PHP was surprisingly tricky for me!

<?php

$db = mssql_connect('vm8', 'user', 'password');
mssql_select_db('myDatabase', $db); 
$stmt = mssql_init('dbo.usp_square_input', $db);

$var = 9;
mssql_bind($stmt, '@x', $var, SQLINT1, false, false, 50);
$result = mssql_execute($stmt);
if ($result) {
  $row = mssql_fetch_array($result, MSSQL_NUM);
  echo $row[0];
  mssql_free_result($result);
}

?>

NOTE: That "vm8" thing cames from a file called /etc/freetds/freetds.conf, and nothing would work until I created the "vm8" alias in there.

DIDN'T WORK (although non-SP stuff was fine!?!?!).

$db = mssql_connect('192.168.8.8', 'user', 'password');

But after I created "vm8" section (my own name) inside /etc/freetds/freetds.conf like so....

[vm8]
    host = 192.168.8.8
    port = 1433
    tds version = 8.0

Then my stored procedures worked once I changed the connect hostname to use that section heading:

$db = mssql_connect('vm8', 'user', 'password');

Whew.

For reference here was my SP:

CREATE PROCEDURE [dbo].[usp_square_input] 
   @x bigint
AS 
SELECT @x * @x AS squared ;

Answer 2

i am sorry to say you can't run stored Procedure in current state of ZF AFAIK why ..... take a look here

What’s missing?

What about some of the long-running feature requests for Zend Db, like stored procedures, character sets, and support for queries that can’t run as prepared statements? What about clarifying the difference between a Model and a data layer?

Source : http://www.phparch.com/2010/06/25/zend-db-2-0-kicks-off/

update : after i had answered you , i got this link from google , and i think it worth mentioning

http://zend-framework-community.634137.n4.nabble.com/mssql-pdo-lastInsertID-with-a-stored-procedure-td672685.html

Answer 3

When you said that there is no result from MS SQL STORED PROC - it is not correct (if it returns any value of course).

But it also returns inserted, updated or deleted applied counts if there are such operations.

And if you make a temp table at the beginning of the proc - try to guess what you would get.

So you need to insert SET NOCOUNT ONN at the begging of the proc to do not return such results.

Answer 4

Ok, after spendig a couple hours digging the problem I came up with the following code - I do not use pure PHP, still using the framework accordingly. Seens to be fine and run ok, I am even getting also the resultset back! Hope it helps:

class Model_DbTable_Cases extends Zend_Db_Table_Abstract 
{
  // ... code ...

public function addRelocationServiceCase($maincase_id, $product_id)
{
  try {
    $stmt = $this->getAdapter()->prepare("EXEC Web_AddRelocationCase ?, ?");    

    $stmt->bindParam(1, $maincase_id, PDO::PARAM_STR);
    $stmt->bindParam(2, $product_id, PDO::PARAM_STR);
    $stmt->execute();

    $rows = $stmt->fetchAll();

    die(var_dump($rows));
    Zend_Debug::dump($rows);

    $stmt->closeCursor();
  }
  catch (Zend_Db_Adapter_Exception $e) {
    print $e->__toString();
  }
  catch (PDOException $e) {
    print $e->__toString();
  }
  catch (Zend_Exception $e) {
    print $e->__toString();
  }     
}
}   

Answer 5

Doesn't something like this work?

$statement = $adapter->prepare("yourSpHere()");
$statement->execute();
$results = $statement->fetchAll();