PDO SQLite query zero result issue

Question

I've had a look around but can't seem to find any information on this. I'm not sure if it's an issue with my code or a known issue with in-memory SQLite databases and PDO.

Basically, after inserting a single row into an in-memory SQLite database table, I'd expect that a query that doesn't match the inserted item to return zero rows. However, the following code gives a single row (false) but no actual PDO error code.

<?php

    // Create the DB
    $dbh = new PDO('sqlite::memory:');
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Data we'll be using
    $name = 'Entry';

    // Create DB table
    $dbh->query('
        CREATE TABLE
            Test
            (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(50) NOT NULL
            )
    ');

    // Insert data
    $stmt = $dbh->prepare('
        INSERT INTO
            Test
            (
                name
            )
        VALUES
            (
                :name
            )
    ');

    $stmt->bindParam(':name', $name, PDO::PARAM_STR, 50);
    $stmt->execute();

    // Check data has actually been inserted
    $entries = $dbh->query('
        SELECT
            *
        FROM
            Test
    ')->fetchAll(PDO::FETCH_ASSOC);

    var_dump($entries);

    // Query DB for non-existent items
    $stmt = $dbh->prepare('
        SELECT
            *
        FROM
            Test
        WHERE
            name != :name
    ');

    $stmt->bindParam(':name', $name, PDO::PARAM_STR);
    $stmt->execute();

    // How many rows returned
    echo $stmt->rowCount();

    // Actual data returned
    var_dump($stmt->fetch(PDO::FETCH_ASSOC));
?>

I've managed to work-around the problem with some hackery but it'd be nice to not have to do this:

<?php

    echo (
        (0 == $stmt->rowCount()) || 
        (
            (1 == $stmt->rowCount()) && 
            (false === (($row = $stmt->fetch(PDO::FETCH_ASSOC)))) && 
            ('0000' == array_pop($dbh->errorInfo()))
        )
    ) ? 'true' : 'false';

?>

Can anyone help or point out any glaring mistakes that I may have made?

Answer 1

PDOStatement::rowCount() returns

... the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Welcome to PDO, where the easy stuff works and the not-so-easy stuff ruins your day. SQLite is one of the drivers that doesn't have a reliable "how many rows are in my result set?" function. From the comments:

As of SQLite 3.x, the SQLite API itself changed and now all queries are implemented using "statements". Because of this, there is no way for PDO to know the rowCount of a SELECT result because the SQLite API itself doesn't offer this ability.

A return of false from PDOStatement::fetch() is a guarantee of "nothing came back," and your checking code is entirely sane, if a bit hard to read. You may wish to consider wrapping or deriving from PDO and PDOStatement for your own sanity.

(Disclaimer: I am a PDO fanboy.)