php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #69592 allow 0-column rowsets to be skipped automatically
Submitted: 2015-05-07 09:19 UTC Modified: 2017-10-17 18:23 UTC
Votes:6
Avg. Score:4.8 ± 0.4
Reproduced:6 of 6 (100.0%)
Same Version:4 (66.7%)
Same OS:6 (100.0%)
From: martinpop83 at gmail dot com Assigned: adambaratz (profile)
Status: Closed Package: PDO DBlib
PHP Version: Irrelevant OS: Linux
Private report: No CVE-ID: None
 [2015-05-07 09:19 UTC] martinpop83 at gmail dot com
Description:
------------
MS SQL usually return results for every query. For INSERT/UPDATE/DELETE return empty results and number of affected rows. It can be turn off by SET NOCOUNT ON and results will be returned only after SELECT queries.

pdo_dblib ignore SET NOCOUNT ON and still creating empty result sets (rowsets).

With pdo_sqlsrv (Windows server) and mssql (Linux server, FreeTDS) is everything ok.


Test script:
---------------
$pdo = new PDO("dblib:host=<host>;dbname=<dbname>", "username", "password");

$stmt = $pdo->query("
    SET NOCOUNT ON
    
    DECLARE @tempTable TABLE(value VARCHAR(20), find BIT)
    
    INSERT INTO @tempTable(value, find) VALUES('textA', 1)
    INSERT INTO @tempTable(value, find) VALUES('textB', 0)
    
    SELECT * FROM @tempTable
    ");

do
{
    print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
}
while ($stmt->nextRowset());



Expected result:
----------------
Array
(
    [0] => Array
        (
            [value] => textA
            [find] => 1
        )

    [1] => Array
        (
            [value] => textB
            [find] => 0
        )
)


Actual result:
--------------
Array
(

)

Array
(
    [0] => Array
        (
            [value] => textA
            [find] => 1
        )

    [1] => Array
        (
            [value] => textB
            [find] => 0
        )
)


Patches

Add a Patch

Pull Requests

Pull requests:

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-07-07 12:18 UTC] mark dot blackman at db dot com
Is this PR likely to get merged anytime soon?
 [2016-09-14 16:00 UTC] adambaratz@php.net
-Summary: PDO_DBLIB MSSQL ignore SET NOCOUNT ON +Summary: allow 0-column rowsets to be skipped automatically -Type: Bug +Type: Feature/Change Request
 [2016-09-14 16:00 UTC] adambaratz@php.net
I think there's a misunderstanding about how SET NOCOUNT works. According to the docs[1], it works specifically on row count. The mssql extension suppressed the rowsets you're referring to by skipping any with a column count of 0[2]. The posted PR replicates this behavior. It can be merged once it's cleaned up for PHP7.

In the meantime, you can workaround this issue in userland by calling \PDOStatement::columnCount():

$rowsets = [];
do {
  if ($statement->columnCount()) {
    $rowsets[] = $statement->fetchAll();
  }
} while ($statement->nextRowset());

Generally speaking, functionality that can be implemented cleanly in userland should be kept there, but this is a meaningful convenience for people migrating from mssql.

---
[1] https://msdn.microsoft.com/en-us/library/ms189837.aspx
[2] https://github.com/php/php-src/blob/PHP-5.6/ext/mssql/php_mssql.c#L1363
 [2017-03-01 22:22 UTC] adambaratz@php.net
-Status: Open +Status: No Feedback
 [2017-10-17 18:23 UTC] adambaratz@php.net
-Status: No Feedback +Status: Closed -Assigned To: +Assigned To: adambaratz
 [2017-10-17 18:23 UTC] adambaratz@php.net
Fixed for PHP 7.3.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 07:01:29 2024 UTC