PDO::rowCount() returning -1

Question

I have this routine below, which used without problems with a Mysql database. However I had to migrate to a SQL Server 2008 database, which was simple.

The only problem is, I don’t know why mine rowCount() is returning -1, even with my query working without problem, because if I give a print_r in the SQL return, all the lines that should come in the query are there.

Follows my code:

public function listar(){

    $retorno = array();

    $sql = "SELECT m.id, m.descricao, m.link, m.categoria, m.icone FROM menus AS m,    grupos AS g, permissoes AS p WHERE (g.id = p.idgrupo AND m.id = p.idmenu) AND (p.status = :pstatus AND g.status = :gstatus AND m.status = :mstatus) AND g.id = :gid ORDER BY m.ordem ;";

    $vars = array(":pstatus"=>1,":gstatus"=>1,":mstatus"=>1,":gid"=>$_SESSION['group']);

    $stmt = $this->pdo->prepare($sql);

    foreach($vars as $index => $value){
        $stmt->bindValue($index,$value);
    }

    if($stmt->execute()){
        $count = $stmt->rowCount();
        $rows = $stmt->fetchAll(PDO::FETCH_OBJ);
        $rows['msg'] = '1';
        $rows['length'] = $count;

        $i = 0;
        while($i < $count){
            foreach($rows[$i] as $index => $value){
                $rows[$i]->$index = utf8_encode($value);
            }
            $i++;
        }

        return $rows;
    } else {
        return array("msg" => '0');
    }

}

Answer 1

This is the problem when a select is sent is not guaranteed that all databases return the number of rows (it returns the number of rows affected by a DML: DELETE, INSERT, or UPDATE), the proper manual alert about him.

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.

If the last SQL statement executed by the associated Pdostatement was A SELECT statement, some databases can return the number of rows Returned by this statement. However, this behavior is not guaranteed For all databases and should not be invoked for laptops Applications.

To circumvent this situation select with Count(). Another solution would be to use the Count() php provided that the consultation does not have aggregated results. count() returns the number of rows in the array.

Answer 2

Based on the link @bfavaretto sent me, I managed to come up with a solution, follows.

On the line where the prepare() is defined, the following parameter should be added array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL). So the line of the prepare gets like this.

$stmt = $this->pdo->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

After that the rowCount() will work smoothly.

Thanks for the answers.