PHP sqlsrv: passing a resource from a function

Question

PHP 5.4.14 SQL Server 2012 / SQL Client 2012 Windows 2008 R2

I have a function (simplified version) that I call to run an SQL query. It works correctly: connects to the database; launches the request and receives a valid resource. The problem is that the returned resource is null ...

function squeal($query) { $serverName = "XXXXXXXX\SQLEXPRESS"; $uid = "private"; $pwd = "private"; $connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"DBname"); /* Connect using SQL Server Authentication. */ $conn = sqlsrv_connect( $serverName, $connectionInfo); if( $conn === false ) { echo "Unable to connect.</br>"; die( print_r(sqlsrv_errors(), true)); } /* Run query */ $result = sqlsrv_query( $conn, $query, array(), array("Scrollable"=>"buffered")); if( $result === false ) { echo "Error in executing query.</br>"; die( print_r(sqlsrv_errors(), true)); } /* check resource exists for debug (still fails without these lines) */ echo("Resource=".intval($result)."</br>"); echo("Has rows=".sqlsrv_has_rows($result)."</br>"); return $result; } $tsql = "SELECT id FROM mytable"; $fred = squeal($tsql); echo("Resource=".intval($fred)."</br>"); echo("Has rows=".sqlsrv_has_rows($fred)."</br>"); 

It gives the following result ...

 Resource=8 Has rows=1 Resource=8 Warning: sqlsrv_has_rows(): 8 is not a valid ss_sqlsrv_stmt resource in <path> on line 85 Has rows= 

SQL works correctly and returns a valid resource. Upon returning from the function, he knows that Resource # 8 has been transferred (in this case), but it is empty. I use a similar method for MySQL, which works fine. My entire intranet application relies on the ability to call a function to run a request and return a resource.

Does the resource "die" when exiting the function in sqlsvr / ODBC? of course not.

I spent a couple of days clearing Google of the answers, but it can make the SQL server work outside the function. I would appreciate any suggestions.

Greetings

Answer 1

I found this fascinating problem, and when searching for a possible reason, I found this section , which actually sounds plausible.

In short: you open the $conn communication resource within the scope. Thus, it makes sense that it is destroyed when leaving this area. All resources associated with this communication resource as such automatically cease to be valid, therefore it is explained why the request resource also dies when leaving the area. If you look at this, it also makes sense - how a derived resource can continue to exist without its logical parent resource. We are just spoiled by PHP, which is usually not important, but in C # / Java, etc. This is plausible behavior.

If this assumption is true, the easiest solution is to put global $conn; to the top of your squeal function squeal that it cannot go out of scope. Depending on the rest of the code, you could implement a more elegant (and less potentially conflicting) solution, the implementation is not really kosher, because it connects inside the function and then expects the output to continue from the outside.