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 follows) that I call to run a SQL query. It works correctly: connects to DB; runs query and obtains a valid resource. The problem is that the resource that gets returned 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 output...

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 is working correctly and returns a valid resource. On return from the function it knows it has been passed resource #8 (in this instance) but it is empty. I use a similar method for MySQL that works perfectly. My whole intranet app relies on being able to call a function to run a query and get a resource back.

Does the resource 'die' on leaving the function in sqlsvr/ODBC? surely not.

I have spent a couple of days now scouring google for answers but can only get SQL server to work outside of a function. I would appreciate any suggestions

Cheers

Answer 1

I found this a fascinating problem, and while searching for a possible cause I found this topic which actually sounds plausible.

In short: you open connection resource $conn within function scope. As such it makes sense that it is destroyed when leaving that scope. All resources stemming from that connection resource as such are automatically no longer valid, so that explains why the query resource also dies upon exiting the scope. If you look at it this way it makes sense too - how can a derived resource continue to exist without its logical parent resource. We're just spoilt by PHP that it usually doesn't care about this, but in C#/Java etc. it's plausible behaviour.

If this assumption is correct, the simplest solution is to put global $conn; at the beginning of your squeal function so it cannot get out of scope. Depending on the rest of the code you could implement a more elegant (and less potentially conflicting) solution, the implementation isn't really kosher anyway that it connects within a function and then expects followups to survive outside.