php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #65945 Nested use of pdo->prepare()->execute clears the outer resultset-object
Submitted: 2013-10-22 13:46 UTC Modified: 2014-10-21 17:56 UTC
Votes:12
Avg. Score:4.4 ± 0.9
Reproduced:9 of 9 (100.0%)
Same Version:6 (66.7%)
Same OS:3 (33.3%)
From: news at franky dot net Assigned:
Status: Not a bug Package: PDO DBlib
PHP Version: 5.4.21 OS: Mac OS X 10.8.5
Private report: No CVE-ID: None
 [2013-10-22 13:46 UTC] news at franky dot net
Description:
------------
In PHP 5.3 the test script works fine and gives me the results as you see in "expected result".

If i use PHP 5.4 or PHP 5.5, the inner pdo->prepare()->execute clears the outer PDO-Resultset unexpectedly.

Test script:
---------------
$pdo = new PDO("dblib:host=192.168.1.100;dbname=kunden;charset=UTF-8;", "kunden", "geheim");

$stmt = $pdo->prepare("select top 10 * from Kunden");
$stmt->execute();

while($row = $stmt->fetch()) {
    echo 'KundenID: ' . $row["KundenID"] . '<br />';
    
    $stmt2 = $pdo->prepare("select top 10 * from Ansprechpartner where KundenID=?");
    $stmt2->execute(array($row["KundenID"]));
    
    while($row2 = $stmt2->fetch()) {
        echo 'AnsprechpartnerID: ' . $row2["AnsprechpartnerID"] . '<br />';
    }
    
}

unset($pdo);

Expected result:
----------------
KundenID: 10
AnsprechpartnerID: 1624
AnsprechpartnerID: 1716
AnsprechpartnerID: 7823
AnsprechpartnerID: 9309
AnsprechpartnerID: 10398
AnsprechpartnerID: 18686
KundenID: 13
AnsprechpartnerID: 1621
KundenID: 15
AnsprechpartnerID: 1596
AnsprechpartnerID: 4769
AnsprechpartnerID: 92891

Actual result:
--------------
KundenID: 10
AnsprechpartnerID: 1624
AnsprechpartnerID: 1716
AnsprechpartnerID: 7823
AnsprechpartnerID: 9309
AnsprechpartnerID: 10398
AnsprechpartnerID: 18686

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-12-30 13:41 UTC] cweiske@php.net
This also happens with "normal" queries executed via $pdo->query('..'), not only with prepared statements.
 [2014-01-01 12:30 UTC] felipe@php.net
-Package: PDO related +Package: PDO DBlib
 [2014-06-30 20:21 UTC] deepdiver at owncloud dot com
We at ownCloud are facing the same issue.

Looks like the php 5.3 code base for pdo_dblib is fetch all rows within pdo_dblib_stmt_execute() - see https://github.com/php/php-src/blob/php-5.3.28/ext/pdo_dblib/dblib_stmt.c#L145

Might that be related to this comment in the freedts docs: http://www.freetds.org/userguide/samplecode.htm (search for 'Fetch All Rows!')

Any further guidance is welcome - we are happy to 'donate' developer time to this issue - if this helps ;-)
 [2014-07-03 12:29 UTC] stasismedia at gmail dot com
Same thing happening on Ubuntu 14.04, PHP 5.5.9-1ubuntu4.2

Here's a test script that doesn't require a table, if that helps:

Test script:
---------------

$dsn      = 'dblib:dbname=DB_NAME;host=DB_HOST';

$db = new \PDO($dsn, 'USER', 'PASSWORD');

$statement = $db->prepare('SELECT 1 UNION SELECT 2 UNION SELECT 3');
$statement->execute();

while($row = $statement->fetch())
{    
    $statement2 = $db->prepare('SELECT 1');
    $statement2->execute();
    $statement2->fetch();

    // Loop will exit
}
 [2014-10-21 17:56 UTC] ssufficool@php.net
-Status: Open +Status: Not a bug
 [2014-10-21 17:56 UTC] ssufficool@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

This is the behavior of MSSQL (TDS), DBLIB and FreeTDS. One statement per connection rule. If you initiate another statement, the previous statement is cancelled. 

The previous versions buffered the entire result set in memory leading to OOM errors on large results sets. 

The previous behavior can be replicated using fetchAll() and a loop if desired. Another workaround is to open 2 connection objects, one per statement.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 22:01:27 2024 UTC