3

I'm using PDO against MSSQL, and need to run nested queries. They are all prepared statements. If I try to use the fetch() method, it inner queries fail immediately, so I used fetchAll(). So, I get something like this, with Programs, Products and Budgets:

$pgm_stmt->execute();
$pgm_res = $pgm_stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($pgm_res as $pgmrow) {
    $prod_stmt->execute(array($pgmrow['ID']));
    $prod_res = $prod_stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($prod_res as $prodrow) {
        $bdgt_stmt->execute(array($pgmrow['ID'], $prodrow['ID']));
        $bdgt_res = $bdgt_stmt->fetchAll(PDO::FETCH_NUM);
        foreach ($bdgt_res as $bdgtrow) {
            ... work here
        }
    }
}

OK, everything works the first time through, but when it loops back for the 2nd program, the product result set gets corrupted somehow. When I dump the $prod_res variable right after the fetchAll(), the values are randomly assigned from other parts of memory, bits of other arrays, etc. Of course it fails because the $prodrow['ID'] value is undefined, because that whole result set is mangled.

Can someone help me troubleshoot this? I'm stumped.

Thanks.

Mike Nicewarner
  • 51
  • 3
  • 4
    Are you sure you need that nested stuff instead of some joins? – PeeHaa Nov 15 '12 at 19:05
  • 2
    It would be difficult without seeing the queries. – Madara's Ghost Nov 15 '12 at 19:18
  • Yes, I need the nested stuff. The queries are actually quite complex with a very normalized structure. Trying to put all of them together would be horrific. – Mike Nicewarner Nov 15 '12 at 19:41
  • I understand the difficulty of figuring it out without the queries, but I cannot share that information. Suffice it to say that the outer query looks a lot like "select * from program". The middle query looks a lot like "select * from product where program = ?". You get the idea. – Mike Nicewarner Nov 15 '12 at 19:42
  • It is strange that each time I refresh the page, the middle result set changes to some new messed up array. Sometimes some elements of the original structure are left, but elements are missing and bogus ones added. – Mike Nicewarner Nov 15 '12 at 19:43
  • 1
    Wut? Are you saying that your queries are "too complex" for joins? What does the fact that the tables are normalized have to do with joins? If anything is *horrific* as you state, it is running queries in a loop. – PeeHaa Nov 15 '12 at 19:55
  • Using PDO connecting to MySQL, I don't get the corruption. The same code, to the letter, works just fine with MySQL. – Mike Nicewarner Nov 15 '12 at 20:36
  • What version of PHP are you using? And what driver at this connection, dblib? I did ran some tests and have some different examples for making this cycle -- some without needing to fetchAll... But there's a bug on PDO-DBLIB extension for PHP 5.4+ that might also be the problem. – Capilé May 01 '13 at 14:09
  • Having the same issue with PHP5.6 + MSSQL - seems to be a bug? – lifeofguenter Oct 05 '16 at 14:44

1 Answers1

1

Not a bug, but a feature, see: https://bugs.php.net/bug.php?id=65945

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.

Community
  • 1
  • 1
lifeofguenter
  • 1,121
  • 1
  • 13
  • 22