6

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.

12
  • stackoverflow.com/a/32224294/285587 ? Commented Nov 2, 2016 at 5:53
  • Following some help. I'm now connecting with $pdo = new PDO('odbc=connection', 'user', 'password'); However, binding to named parameters is still not working. Commented Nov 3, 2016 at 23:23
  • Best to edit your changes into your question, optimally keeping both what you started with and what you changed to. Commented Nov 4, 2016 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. Commented Nov 7, 2016 at 10:12
  • 1
    Sure, see this link. Commented Nov 7, 2016 at 22:27

2 Answers 2

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);
Sign up to request clarification or add additional context in comments.

1 Comment

That works. I also had to change the connection DSN from dblib to '$pdo = new PDO('odbc=connection', 'user', 'password');'
3
+25

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

4 Comments

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.
I don't see any PHP code showing your efforts with bound parameters, so I can't make any useful comments there...
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.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.