2

I am struggling with a MYSQL (5.5.35) stored procedure that returns multiple result sets, I wonder if anyone can explain what is going wrong. The SQL is as per the following

SELECT 't1' as tableName, tbl1.* FROM table1 tbl1;
SELECT 't2' as tableName, tbl2.* FROM table2 tbl2;
SELECT 't3' as tableName, tbl3.* FROM table3 tbl3;
SELECT 't4' as tableName, tbl4.* FROM table4 tbl4;
SELECT 't5' as tableName, tbl5.* FROM table5 tbl5;
SELECT 't6' as tableName, tbl6.* FROM table6 tbl6;

Then I have some PHP that does this...

 $sth =$dbh->prepare('CALL getAllRecords()');
 $sth->execute();

 $i = 1;
 do {
    echo "<h3>$i Set</h3>";
    $i++;
    $rows = $sth->fetchAll();
 } while ($sth->nextRowset());

One would expect to see the following in the browser

1 Set
..
6 Set

But instead it runs through 7 result sets which in turn causes an exception

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error'

Am I missing something really simple here?

Zoidberg
  • 21
  • 3
  • Which line throws the exception? fetchAll? – Joachim Isaksson Mar 26 '14 at 16:17
  • Yes it is the fetchAll – Zoidberg Mar 26 '14 at 16:29
  • I'd not consider myself an expert in any sense, but I can honestly not see anything obviously wrong with the code. – Joachim Isaksson Mar 26 '14 at 17:07
  • @JoachimIsaksson Me either, I cannot see for the life of me why I am hitting that do/while loop a seventh time :( - THe only thing that I can see might cause an issue is that the last 2 out of the 6 data sets are empty but even then, I just test $rows, I still shouldn't get into that loop 7 times. – Zoidberg Mar 26 '14 at 17:19

1 Answers1

0

Appears to be similar to https://stackoverflow.com/a/26381914/2208000. The solution there was to check the column count, and if it's 0, break the loop. That worked for me, but your mileage may vary.

Community
  • 1
  • 1
Nick Van Dusen
  • 35
  • 3