Msphpsql: sqlsrv_next_result() causes "The active result for the query contains no fields" error in v5.1.1

[theodorejb]

I have a generic class for executing queries and returning selected results. It worked fine with v5.1.0 and previous SQLSRV driver releases, but with v5.1.1 I am getting an error saying "The active result for the query contains no fields."

Before running the test script, create a Users table like this:

CREATE TABLE Users (
    user_id int IDENTITY PRIMARY KEY,
    name varchar(50) NOT NULL
);

Here's the code that demonstrates the problem. In v5.1.0 and prior, the call to sqlsrv_next_result() for the second query returned null, but in v5.1.1 it returns false.

$connection = sqlsrv_connect('Computer-Name\SQLEXPRESS', ['Database' => 'MyDatabase']);

// Still works
$result = query("INSERT INTO Users (name) VALUES (?); SELECT SCOPE_IDENTITY() AS RowID;", ['Michael']);
$affected = $result->getAffected(); // 1
$id = $result->getFirst()['RowID']; // e.g. 1

// Worked in v5.1.0 and previous releases, but in v5.1.1 causes the following error:
// "Failed to get next result: The active result for the query contains no fields."
$result2 = query("UPDATE Users SET name = ? WHERE user_id = ?", ['Christopher', 0]);
$affected2 = $result2->getAffected(); // should be 0

function query(string $sql, array $params): Statement
{
    global $connection;
    $stmt = sqlsrv_query($connection, $sql, $params);

    if ($stmt === false) {
        throw new Exception('Query failed: ' . sqlsrv_errors()[0]['message']);
    }

    $result = new Statement($stmt);
    $result->execute();
    return $result;
}

class Statement
{
    /** @var resource */
    private $stmt;
    private $affected;

    public function __construct($stmt)
    {
        $this->stmt = $stmt;
    }

    public function execute()
    {
        $next = null;
        $this->affected = -1;

        // get affected row count from results prior to select result
        do {
            $affectedRows = sqlsrv_rows_affected($this->stmt);

            if ($affectedRows === false) {
                throw new Exception('Failed to get affected row count: '.  sqlsrv_errors()[0]['message']);
            }

            if ($affectedRows === -1) {
                // reached SELECT result
                break; // so that getIterator will be able to select the rows
            }

            if ($this->affected === -1) {
                $this->affected = 0;
            }

            $this->affected += $affectedRows;
        } while ($next = sqlsrv_next_result($this->stmt));

        if ($next === false) {
            throw new Exception('Failed to get next result: ' . sqlsrv_errors()[0]['message']);
        }

        if ($affectedRows !== -1) {
            $this->close(); // no results, so statement can be closed
        }
    }

    public function getIterator(): \Generator
    {
        // only fetch rows if the statement is open
        if (is_resource($this->stmt)) {
            while ($row = sqlsrv_fetch_array($this->stmt, SQLSRV_FETCH_ASSOC)) {
                yield $row;
            }

            $this->close();
        }
    }

    public function getFirst(): ?array
    {
        $row = $this->getIterator()->current();

        if ($row !== null) {
            $this->close(); // don't leave the SQL statement open
        }

        return $row;
    }

    public function getAffected(): int
    {
        return $this->affected;
    }

    public function close()
    {
        if (!sqlsrv_free_stmt($this->stmt)) {
            throw new Exception('Failed to close statement:' . sqlsrv_errors()[0]['message']);
        }
    }
}

In case it helps, I'm using PHP 7.2 RC5 on Windows 10.

[jansor]

I think this is releated with each other, this issue has the same problems, i am having exact the same problem as mentioned here, only difference is that i run PHP7.1.11 on Windows 10

[david-puglielli]

Hi @theodorejb and @jansor, the behaviour in the latest release was changed because of inconsistencies in the way null result sets were reported previously - details in issue #507. Now, if you try calling sqlsrv_next_result() or nextRowset() on a null result set, the error The active result for the query contains no fields is returned. This happens when the result set is null rather than empty - the difference is that a null result set has no rows or fields, while an empty result set would have no rows but a nonzero number of fields. An UPDATE statement would return a null result set, since nothing is being retrieved, for example, while a SELECT statement that returns no records would return an empty but non-null result since it would have fields associated with it.

If you are calling sqlsrv_next_result() on a result that is nonempty to begin with, the error won't be returned unless you first go past the end and then call sqlsrv_next_result (the first time you go past the end, sqlsrv_next_result() returns null rather than an error). If the result set is empty but non-null, sqlsrv_next_result would return null again. But if the result set is null to begin with, the error is returned immediately on calling sqlsrv_next_result. The do...while loop in your execute() function is fine, but you may want to change the if block that follows, as it will throw an exception whenever your statement returns a null result.

[theodorejb]

@david-puglielli How can I check whether the result set is null so I can avoid calling sqlsrv_next_result() and causing an error?

[yukiwongky]

@theodorejb @jansor After discussing with the team we have concluded this new behavior is a regression in the 5.1.1-preview release. We will have this fixed in the next preview release near the end of the month. We apologize for any inconvenience.

[yitam]

Fixed and merged #595