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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: martinpop83 at gmail dot com
New email:
PHP Version: OS:

 

 [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

Pull Requests

Pull requests:

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: Sun Oct 13 03:01:26 2024 UTC