CODEWITHSUNDEEP
phppdo
Walhalla
Walhalla

Reputation: 396

Query does not return a result

I converted a web-based application from MySQLi to PDO, because I want to use Microsoft SQL-Server as database too. PDO works like a charm with MySQL database. Now I tried it for the first time with MS-SQL and it does not. Almost every query has to be updated. It is very frustrating.

The simple code below drives me nuts:

$ComputerGUID = "5BEC3779-B002-46BA-97C4-19158C13001F";

$SqlSelectQuery = "SELECT computermapping.PrinterGUID, 
                case when computerdefaultprinter.PrinterGUID IS NOT NULL then 1 else 0 end AS isDefaultPrinter
                FROM computermapping 
                LEFT JOIN computerdefaultprinter ON computerdefaultprinter.ComputerGUID = computermapping.ComputerGUID 
                AND computerdefaultprinter.PrinterGUID = computermapping.PrinterGUID
                WHERE computermapping.ComputerGUID = :ComputerGUID";
$SqlStatement  = $pdo->prepare( $SqlSelectQuery );
$SqlStatement -> bindValue( ":ComputerGUID", $ComputerGUID, PDO::PARAM_STR );
$SqlStatement->execute();

$SelectQueryNumRows = $SqlStatement->rowCount();
IF ( $SelectQueryNumRows > 0 ) {

    $Data = $SqlStatement->fetchAll(PDO::FETCH_ASSOC);

} ELSE {
    echo "The query did not return a result ...";
}

It is working fine with MySQL and returns me a result.

With Microsoft SQL-Server it get NO RESULT. (The query did not return a result ...)

Running the same query in Microsoft SQL Server Management Studio is working fine too:

Result in Microsoft SQL Server Management Studio

Monitoring the query when running the code with SQL Server Profiler shows the following:

Monitoring with Microsoft SQL Server Profiler

Upvotes: 0

Views: 107

Answers (2)

Zhorov
Zhorov

Reputation: 29943

For most drivers, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. But if you are using PHP Driver for SQL Server, as is mentioned in the documentation, if the last SQL statement executed by the associated PDOStatement was a SELECT statement, a PDO::CURSOR_FWDONLY cursor returns -1 and a PDO::CURSOR_SCROLLABLE cursor returns the number of rows in the result set.

You need to prepare the statement like this:

$SqlStatement  = $pdo->prepare(
   $SqlSelectQuery, 
   array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)
);

Upvotes: 0

u_mulder
u_mulder

Reputation: 54831

As the manual mentions:

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.

So, you should not use rowCount. But as you fetch all data - use it:

$SqlStatement->execute();

$Data = $SqlStatement->fetchAll(PDO::FETCH_ASSOC);
// in case of no data - empty array will be returned

if ($Data) {
    // process $Data;
} else {
    echo "The query did not return a result ...";
}

Also note that $Data can be false in case of errors and if required you should process this value of $Data.

Upvotes: 2

Related Questions