pdo_sqlsrv: how to recognize if fetch "false" is an error or empty recordset?

Question

The fetch method returns false both if the recordset is empty and if there is an actual error. The method described here allows you to easily recognize what is happening:

ensure that PDO fetch method result "false" is a error or empty result

It works well with pdo_mysql, however pdo_sqlsrv throws an error even when the recordset is empty. Is this the intended behaviour? Is it possible to avoid those errors?

EDIT: the error is "There are no more rows in the active result set" and it is produced when you try to fetch a recordset which is already completely fetched. This not happens with pdo_mysql, which just returns false.

EDIT 2: I have finally understood the problem more in details: the first time you fetch an empty recordset you get FALSE, then, if you fetch again, you get an error. This doesn't happen with pdo_mysql (and I am pretty sure it doesn't happen with PDO pgsql and sqlite either): you still get false without producing errors.

Here is a simplified version of the code:

// sqlsrv connection
$conn = new PDO("sqlsrv:Server=....;Database=....", "user", "password", array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ));

// mysql connection
// $conn = new PDO('mysql:host=....;dbname=....', 'user', 'password', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

$sql = "drop table if exists test;";
$res = $conn->query($sql);

$sql = "create table test (name varchar(10));";
$res = $conn->query($sql);

$sql = "insert into test (name) values ('Alice');";
$res = $conn->query($sql);

$sql = "select * from test";
$res = $conn->query($sql);

// fetch first record
$row = fetch_row_db($res);
var_dump($row);

// fetch again
$row = fetch_row_db($res);
var_dump($row);

// fetch again
$row = fetch_row_db($res);
var_dump($row);

function fetch_row_db(&$res)
{
    try {
        return $res->fetch();
    }
    catch(PDOException $e){
        echo 'Error during record fetching.';
        exit();
    }
}

OUTPUT:

MySQL
array(2) { ["name"]=> string(5) "Alice" [0]=> string(5) "Alice" } bool(false) bool(false)

sqlsrv
array(2) { ["name"]=> string(5) "Alice" [0]=> string(5) "Alice" } bool(false) Error during record fetching.

Did you set `PDO::ATTR_ERRMODE` as `PDO::ERRMODE_EXCEPTION`? — Gabriel Heming, Oct 30 '18 at 11:38
Can you provide us more information (code, PHP version, etc...)? — Gabriel Heming, Oct 30 '18 at 11:53
PHP 7.1.1 on MacOS. I have provided more details and the code to reproduce this behaviour. — Eugenio, Oct 30 '18 at 15:22
if `PDO::ERRMODE_EXCEPTION` does not produce exceptions on error then it's possibly an overlook in the SQLSRV driver. — Álvaro González, Oct 30 '18 at 15:25
@ÁlvaroGonzález not sure what you meant, what I said is that it actually does produce exceptions even if I didn't expect that. — Eugenio, Oct 30 '18 at 16:18
@Eugenio I stand corrected. PHP makes a clear distinction between good old [errors](http://php.net/trigger_error) and PHP/5+ [exceptions](http://php.net/throw) and PDO made the strange design decision of being an OOP extension that defaults to errors—thus my confusion. — Álvaro González, Oct 31 '18 at 07:19

pdo_sqlsrv: how to recognize if fetch "false" is an error or empty recordset?

0 Answers0

Linked