Why Pass Parameters by Reference in the SQLSRV Driver?
Last week at JumpIn Camp we spent quite a bit of time focusing on the SQL Server Driver for PHP. As developers worked to build SQL Server support into their applications, they had lots of questions about how both the SQLSRV and PDO_SQLSRV drivers work under the hood. One of the questions that came up was “When using the SQLSRV driver to execute parameterized queries, why do I have to pass parameters by reference?”
To begin to answer that question, let’s take a look at a simple code snippet illustrates the problem. The example below executes a parameterized query using the sqlsrv_prepare and sqlsrv_execute functions and produces some unexpected results:
$server = 'serverName';
$connOptions = array("Database"=>"TestDB", "UID"=>"user_name", "PWD"=>"password");
$conn = sqlsrv_connect($server, $connOptions);$var1 = 1;
$var2 = 2;
$var1 = $var2;//Prepare and execute a parameterized query
$tsql = "SELECT * FROM Table_1 WHERE id = ?";
$stmt = sqlsrv_prepare($conn, $tsql, array($var1)); // driver issues warning here
sqlsrv_execute($stmt);//fetch data here
//Modify the value of the variable for the parameter
$var1 = 3;//Check the values of the parameter variable and the other variable
echo "\$var1 = $var1 (should be 3)<br>"; // Value is 3.
echo "\$var2 = $var2 (should be 2)<br>"; // Value is 3.
First, note that the parameter $var1 has been passed to sqlsrv_prepare by value. If you execute the code above, you will see a warning something like this:
Warning: Variable parameter 1 not passed by reference (prefaced with an &). Variable parameters passed to sqlsrv_prepare or sqlsrv_query should be passed by reference, not by value. For more information, see sqlsrv_prepare or sqlsrv_query in the API Reference section of the product documentation. in C:\inetpub\wwwroot\test1.php on line 12
The reason for this warning is to help you avoid the unexpected results, which occur if you modify the value of the parameter after statement preparation. Note that the value of $var1 is changed to 3 after the statement is executed, but that the value of $var2 should still be 2 since it has not been updated. However, inspecting these values show that they are both 3…unexpected indeed!
Fortunately, fixing this issue is easy…just pass the parameter by reference. Changing the call to sqlsrv_prepare so that it looks like this…
$stmt = sqlsrv_prepare($conn, $tsql, array( & $var1));
…will produce the expected values at the end of the script (and the driver won’t issue a warning).
It is worth noting that you will potentially run into the issue if you are using sqlsrv_prepare/sqlsrv_execute to re-execute the same query with changed parameter values each time. The same unexpected behavior doesn’t occur if you use the sqlsrv_query function (but it is not recommended that you use this function to re-execute queries with different parameter values for performance reasons). Also note that the PDO_SQLSRV driver does not exhibit this behavior with any of it’s execution functions.
The SQL Server Driver for PHP team understands that passing parameters by reference is not ideal. However, when this issue was first encountered, the team consulted PHP experts and decided that passing parameters by reference was the best solution given the potential for outcomes of such unexpected behavior. With that said, the team is continuing to investigate solutions that would produce expected behavior without passing parameters by reference.
As usual, your thoughts and comments are welcome.
Thanks.
-Brian
Comments
- Anonymous
January 23, 2012
Brian,What's the best way to call sqlsrv_prepare() with an unknown number of parameters at runtime? For example, I want to have a function batchInsert() that takes a variable number of columns and inserts multiple records with it?