PHP PDO with SQL Server and prepared statements

Question

There are a number of similar questions to this already posted. However I have not found a way to get this code working.

I am updating a PHP codebase from native MSSQL queries to use PDO, specifically to use ODBC. Here is the old code and two variations I have tried.

Old style: Works, This produces an array of expected results.

$db = mssql_connect('connection', 'user', 'password');
mssql_select_db('database', $db);

$sp = mssql_init('procedure', $db);
$param=1;
$results=[];
mssql_bind($sp,'@param',$param,SQLINT4,FALSE,FALSE);
$spRes = mssql_execute($sp);

while ($row = mssql_fetch_array($spRes, MSSQL_ASSOC)) $results[] = $row; 
mssql_free_statement($sp);
var_dump(results);

Using T-SQL with PDO almost works: I get results as long as I don't try to bind any parameters.

$pdo = new PDO($'dblib:host=connection', 'user', 'password');
$pdo->query('use database');

$sp= $db->prepare("EXEC procedure");
$sp->execute();

while ($row = $sp->fetch(PDO::FETCH_BOUND)) $results[] = $row; 
$sp->closeCursor();
var_dump(results);

Produces an array of many expected results. However any attempt to bind parameters leads to $results being an empty array. No errors are reported.

$sp= $db->prepare("EXEC procedure :param");
$sp->bindParam(':param', $param, PDO::PARAM_INT);

This leads to an empty result set, and reports no errors.

Using ODBC "SQL" doesn't seem to work at all:

$pdo = new PDO($'dblib:host=connection', 'user', 'password');
$pdo->query('use database');

$sp= $db->prepare("CALL procedure");
$sp->execute();

while ($row = $sp->fetch(PDO::FETCH_BOUND)) $results[] = $row; 
$sp->closeCursor();
var_dump(results);

$results is empty; with or without parameters, it doesn't seem to work.

System details: Running PHP 5.5.9 on Ubuntu Trusty (14) with unixodbc & freetds installed.

I would really appreciate a working example of PHP PDO calling stored procedures and binding parameters with MSSQL.

Answer 1

Try this..

$result = array();

$sp= $db->prepare("EXECUTE dbo.procedure :param");
$sp->bindParam(":param", $param, PDO::PARAM_INT);
$sp->execute();

$result = $sp->fetchall(PDO::FETCH_OBJ);

Answer 2

Try changing this --

$sp= $db->prepare("CALL procedure");

-- to this --

$sp= $db->prepare("{ CALL procedure () }");

See the ODBC documentation on Procedure Call Escape Sequence, and the PHP PDO documentation for more...