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.

Following some help. I'm now connecting with `$pdo = new PDO('odbc=connection', 'user', 'password');` However, binding to named parameters is still not working. — Steve E., Nov 03 '16 at 23:23
Best to edit your changes into your question, optimally keeping both what you started with and what you changed to. — TallTed, Nov 04 '16 at 19:50
`$sp->execute();` returns boolean indicator of was call successful or not. If it is `false` then check what `$sp->errorInfo();` returns. Bindings seems to be OK with style `$sp= $db->prepare("EXEC procedure :param"); $sp->bindParam(':param', $param, PDO::PARAM_INT);` so PDOStatement's error info may point to problem. — Aleksey Ratnikov, Nov 07 '16 at 10:12
SQL Server parameters have a `@` prefix, not `:`. The *modified* second snippet probably throws an error that is never checked. The third snippet doesn't bind any parameters at all. — Panagiotis Kanavos, Nov 07 '16 at 14:40
[You must update to PHP >5.6 because there's a known bug in PDO ODBC that prevents parameter binding on 64-bit systems.](http://stackoverflow.com/a/38327719/4233593) — Jeff Puckett, Nov 07 '16 at 15:15
@Jeff Puckett II, that sounds very interesting. Do you have a reference for that? — Steve E., Nov 07 '16 at 22:08
Sure, see this [link](http://stackoverflow.com/a/38327719/4233593). — Jeff Puckett, Nov 07 '16 at 22:27
@Jeff Puckett II. Wow, great question and result. It looks very similar, although I'm using the FreeTDS driver so I'll do a few tests and see if it's the same issue. — Steve E., Nov 07 '16 at 22:57
@Aleksey Ratnikov, thanks. I was under the impression(from the php docs) that setting `PDO::ERRMODE_EXCEPTION` would throw errors if any. It didn't make any difference in my tests which is why it's not in the sample code. I will try checking the return value instead. — Steve E., Nov 07 '16 at 23:08
If you read the bug report, it's a matter with PDO ODBC, so it affects all drivers. Notice that the `odbc_*` functions work fine with Microsoft's driver, and they do with FreeTDS also. It's just a problem with PDO that was patched in PHP 5.6 — Jeff Puckett, Nov 08 '16 at 00:35
@Jeff Puckett II. Having tested many variations, I cannot reproduce the issue in the link you reference. Even trying the code in the question gets the correct result and not an error. Which is weird because I looked at PHP release notes and couldn't find any reference to this fix in php5.5. It appears that the last error I have relates to OUTPUT parameters and solved in question [33617760](http://stackoverflow.com/questions/33617760/php-pdo-mssql-can-not-get-ouput-parameters). I will summarise the solution in an answer soon — Steve E., Nov 08 '16 at 22:03

score 3 · Answer 1 · answered Nov 03 '16 at 02:12

3

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...

answered Nov 03 '16 at 02:12

TallTed

9,069
2
22
37

Ok, that helps a lot. I can connect with an ODBC DSN and call procedures with that syntax. However I still can't get binding parameters to work. I've turned on ODBC tracing and will see if that gives any more clues tomorrow. – Steve E. Nov 03 '16 at 06:23
I don't see any PHP code showing your efforts with bound parameters, so I can't make any useful comments there... – TallTed Nov 03 '16 at 13:29
Thanks. Although if you search for 'bind' on the question. There are some examples in the code. Possibly I have completely got it wrong and there is some other way. – Steve E. Nov 03 '16 at 22:05
To be clearer... I don't see your *PDO+ODBC*-using code with bound parameters, which should be a bit different from all the others. – TallTed Nov 04 '16 at 19:48

score 3 · Accepted Answer · answered Nov 08 '16 at 17:58

3

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);

answered Nov 08 '16 at 17:58

hector teran

369
1
5
15

That works. I also had to change the connection DSN from dblib to '$pdo = new PDO('odbc=connection', 'user', 'password');' – Steve E. Nov 09 '16 at 01:07

PHP PDO with SQL Server and prepared statements

2 Answers2

Linked