getRowCount with SQLSRV driver

[pswmauro]

Version: latest (4.2.7) on windows with PHP 8.1
Correctly installed Microsoft SQL Server driver for PHP. Connection inizialized with:
$db = new Dibi\Connection([ 'driver' => 'sqlsrv', 'host' => 'localhost', 'username' => 'user', 'password' => 'password', 'database' => 'TEST', 'charset' => 'UTF-8', ]);
I can successfully exec
$result = $db->query($qry)
and
$all = $result->fetchAll();
but if use
$result->getRowCount()
it throws exception "Row count is not available for unbuffered queries".

[milo]

It is thrown by driver because the SQLServer API does not offer selected rows count function. You have to fetch all rows and count them manually.

[Sempoinus]

Hi!

I'd like to revive this issue (using Dibi 5.0.2, PHP 8.4).

There is a native SQLSRV function in PHP sqlsrv_num_rows, which allows retrieving the row count for result sets if the query is executed with the Scrollable flag.
Currently, Dibi doesn't expose this functionality for the SQLSRV driver.

Suggested implementation

In SqlsrvResult.php:

public function getRowCount(): int
{
    if ($this->scrollable) {
        $rows = sqlsrv_num_rows($this->resultSet);
        return $rows === false ? 0 : $rows;
    }
    throw new Dibi\NotSupportedException('Row count is not available for unbuffered queries.');
}

This, however, requires that the query itself is executed with the Scrollable option.

---

In the driver, query() method needs to allow passing this option:

public function query(string $sql, array $params = [], array $options = []): ?Dibi\ResultDriver 
{
    $this->affectedRows = null;
    $res = sqlsrv_query($this->connection, $sql, $params, $options); 

    if ($res === false) {
        $info = sqlsrv_errors();
        throw new Dibi\DriverException($info[0]['message'], $info[0]['code'], $sql);

    } elseif (is_resource($res)) {
        $this->affectedRows = Helpers::false2Null(sqlsrv_rows_affected($res));
        return sqlsrv_num_fields($res)
            ? $this->createResultDriver($res)
            : null;
    }

    return null;
}

Alternatively, consider splitting the query into doQuery and query for easier overriding/extensibility.

protected function doQuery(string $sql): false|resource 
{
    return sqlsrv_query($this->connection, $sql); 
}

public function query(string $sql): ?Dibi\ResultDriver 
{
    $this->affectedRows = null;
    $res = doQuery($sql); 

    if ($res === false) {
        $info = sqlsrv_errors();
        throw new Dibi\DriverException($info[0]['message'], $info[0]['code'], $sql);

    } elseif (is_resource($res)) {
        $this->affectedRows = Helpers::false2Null(sqlsrv_rows_affected($res));
        return sqlsrv_num_fields($res)
            ? $this->createResultDriver($res)
            : null;
    }

    return null;
}

---

Note:
The 'Scrollable' => SQLSRV_CURSOR_KEYSET option only works for SELECT queries.
If you run e.g. INSERT, UPDATE, or any other statement with this option, it will fail.
That’s why this should not be set as a default for all queries.

A typical pattern (to allow projects to easily set this only for SELECTs) could look like this:

public function doQuery(string $sql): ?Dibi\ResultDriver
{
    if (str_starts_with(strtoupper(trim($sql)), 'SELECT')) {
        $options['Scrollable'] = SQLSRV_CURSOR_KEYSET;
    }
    return sqlsrv_query($this->connection, $sql, [], $options ?? []);
}

This way, large projects can globally override the logic as needed, and avoid errors for non-SELECT queries.

---

Why

• rowCount() is a common requirement for paginated results, UI tables, etc.
• The change is backwards compatible: if 'Scrollable' is not set, it will throw an exception as before.
• This implementation does not affect any other functionality or SQL statements – everything else behaves as before, all values returned stay unchanged.
• It enables projects to extend row counting functionality for SELECTs without needing to implement a custom driver. Developers can either override query (in case of doQuery()) behavior globally, or set options individually at specific places in the code, so there is no need for any large-scale override.
• Only when used properly (i.e., with SELECT and the right flag) will getRowCount work as expected; otherwise, it safely throws same as now.

Given that, this should be safe for a minor (semver) release, as it doesn't break anything for existing projects and just enables additional use cases for those who need it.

---

Thanks for considering!
@milo @dg