There are a number of similar questions that have already been sent. However, I did not find a way to make this code work.
I am updating the PHP code base from my own MSSQL queries to use PDO, in particular to use ODBC. Here is the old code and the two options I tried.
Old Style: Works. This creates 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 the results until I 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 results in $results being an empty array. No errors reported.
$sp= $db->prepare("EXEC procedure :param"); $sp->bindParam(':param', $param, PDO::PARAM_INT);
This results in an empty result set and does not report errors.
Using ODBC "SQL" does not 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 empty; with or without options doesn't seem to work.
System Information: Running PHP 5.5.9 on Ubuntu Trusty (14) with unixodbc and freetds installed.
I am very grateful for the working example of PHP PDO that calls stored procedures and associates parameters with MSSQL.