Thursday, 4 July 2013

Calling SQL Server with PHP/PDO, be careful with parameter positions

Take the following code:

$command=$connection->createCommand('EXECUTE procSetUserImage :userid, :filename, :ipaddress');
$command->bindValue(':userid', $userid);
$command->bindValue(':filename', $filename);
$command->bindValue(':ipaddress', $_SERVER['REMOTE_ADDR']);

And this stored procedure:

CREATE PROC procSetUserImage @userid INT, @ipaddress VARCHAR(20), @filename VARCHAR(20)

Would you expect it to work? I did but it doesn't, the filename is passed into the ipaddress parameter and vice-versa. This is because the names in the PHP are just substitution placeholders and not symbolic links to the actual parameters in the stored procedures.

In other words make sure you put them in the right order!
Post a Comment