PDO dblib over freetds resets fetch of query on sql server 2000 if another query is issued inside a fetch loop

Question

Ok, so we've got a new server with

• Debian Wheezy 32BIT
• PHP 5.5.18
• FreeTDS 0.91

This PHP app needs to talk to an old SQL server 2000 server. We used the old code from our previous server (PHP 5.2 and older FreeTDS - can't get the version). We connect to SQL server 2000 through PDO using dblib driver.

We're experiencing weird behaviour with the fetch function. Basically if we issue a query during a fetch loop on the same pdo connection object, the main query gets reset and next fetch call will return false even if there are still records to be fetched.

// PSEUDO CODE
// Here the main query
$q = $sql7->query("SELECT TOP 5 * FROM News ORDER BY Data Desc");
while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
    // Looping through the results
    echo "<h1>Main query</h1>";
    print_r($row);

    // Issue a query on the same pdo connection
    $subq = $sql7->query("SELECT TOP 1 * FROM News WHERE IDNews = " . $row['IDNews'] . " ");
    while ($subResult = $subq->fetch(PDO::FETCH_ASSOC)) {
        echo "<h1>Inner query</h1>";
        print_r($subResult);
    }

    // Here the main query $q->fetch(PDO::FETCH_ASSOC) will answer false on the next iteration
    // if we remove the subq, the main query loops just fine
    echo "<hr>";
}

Same code on a Windows PHP with pdo_sqlserver driver works just fine.

It doesn't matter the type of fetch that we pass as argument of fetch function.

PHP doesn't throw any warning or error.

I really don't know what's going on here.

Answer 1

As of: reference (PHP BUG SITE)

This is the behavior of MSSQL (TDS), DBLIB and FreeTDS. One statement per connection rule. If you initiate another statement, the previous statement is cancelled.

The previous versions buffered the entire result set in memory leading to OOM errors on large results sets.

So, it seems that it was the previous versions of PHP (5.3 and previous) that were not conforming to the TDS behaviour. We need to refactor the code then.