Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: Cannot Execute Stored Procedure Multiple Times

    First off I am using PEAR DB to access MySQL. The problem I have is executing a stored procedure inside a foreach loop. The error is get is 2014 Commands out of sync; you can't run this command now. This is peculiar because I know I was freeing up the resultset and wasn't doing anything out of order. I could run a query first, then run a stored procedure, but once I tried to run the second stored procedure, I get the error message. The only way I could get the script to work was disconnecting and reconnecting each time I ran the sp. Here is the script I currently have, hopefully somebody can shed some light on why this is happening. By the way, the SP only returns one row based on the UserID supplied.

    PHP Code:
    <?php
        
    include 'DB.php';
        include 
    'db_params.php';


        
    $conn =& DB::connect ($dsn);
        if (
    DB::isError ($conn))
        die (
    "Cannot connect: " $conn->getMessage () . "\n");
        
    $date1="2008/09/15";
        
    $date2="2008/09/18";

            
    $conn->setFetchMode (DB_FETCHMODE_ASSOC);
            
    $result=$conn->query("SELECT EmployeeID FROM employees WHERE Active='Y'");

            if (
    PEAR::isError($result)) {
                echo 
    $result->getMessage() . ':<br /> ' $result->getDebugInfo().'<br />';
            }
            echo 
    "<table class='excel'>";
            echo 
    "<tr class='header'>";
            echo 
    "<td class='header'>Name</td>";
            echo 
    "<td class='header'>Piece</td>";
            echo 
    "<td class='header'># Days</td>";
            echo 
    "<td class='header'>Complete</td>";
            echo 
    "<td class='header'>XSA</td>";
            echo 
    "<td class='header'>XSR</td>";
            echo 
    "<td class='header'>XCANC</td>";
            echo 
    "<td class='header'>NLS</td>";
            echo 
    "<td class='header'>NLS MDU</td>";
            echo 
    "<td class='header'>Total Calls</td>";

            while (
    $row $result->fetchRow()) {
                
    $array[] = $row["EmployeeID"];
            }
                
    $result->free();

            foreach (
    $array as $id) {
                
    $conn =& DB::connect ($dsn);
                
    $conn->setFetchMode (DB_FETCHMODE_ASSOC);
                
    $res=$conn->query("call StaffWagesNoSplit('$id','$date1','$date2')");
                if (
    PEAR::isError($res)) {
                    echo 
    $res->getMessage() . ':<br /> ' $res->getDebugInfo().'<br />';
                }
                
    $x $res->fetchRow();
                
    $res->free();
                
    $conn->disconnect();

                
    $conn =& DB::connect ($dsn);
                
    $conn->setFetchMode (DB_FETCHMODE_ASSOC);
                
    $res=$conn->query("call StaffWagesWithSplit('$id','$date1','$date2')");
                if (
    PEAR::isError($res)) {
                    echo 
    $res->getMessage() . ':<br /> ' $res->getDebugInfo().'<br />';
                }
                
    $y $res->fetchRow();
                
    $res->free();
                
    $conn->disconnect();
                    echo 
    "<tr class='excel'>";
                    echo 
    "<td class='excel'>".$x["Name"]."</td>";
                    echo 
    "<td class='excel'>"."$".number_format(($x['Piece']+$y['Piece']),2)."</td>";
                    echo 
    "<td class='excel'>".$x['# Days']."</td>";
                    echo 
    "<td class='excel'>".($x['Complete']+$y['Complete'])."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['XSA']+$y['XSA']),0)."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['XSR']+$y['XSR']),0)."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['XCANC']+$y['XCANC']),0)."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['NLS']+$y['NLS']),0)."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['NLS MDU']+$y['NLS MDU']),0)."</td>";
                    echo 
    "<td class='excel'>".($x['Total Calls']+$y['Total Calls'])."</td>";
                    echo 
    "</tr>";


            }
            echo 
    "</table>";

    ?>

  2. #2
    Join Date
    Oct 2008
    Posts
    4

    solved

    After some lengthy research, it appears the stored procedures produce multiple result sets, so simply freeing the result set is not enough. I switched from using PEAR DB to MDB2, I loaded the Function module so I could use the function executeStoredProc, and I added a while loop containing res->nextResult to clear up the added result sets from a storedProcedure. Here is my modified code.

    PHP Code:
    <?php
        
    // include class files
        
    include 'MDB2.php';
        include 
    'db_params.php';


        
    $conn =& MDB2::connect ($dsn);
        
    $conn->loadModule('Function');
        if (
    MDB2::isError ($conn))
        die (
    "Cannot connect: " $conn->getMessage () . "\n");
        
    $date1="2008/09/15";
        
    $date2="2008/09/18";

            
    $conn->setFetchMode (MDB2_FETCHMODE_ASSOC);
            
    $result=$conn->query("SELECT EmployeeID FROM employees WHERE Active='Y'");

            if (
    PEAR::isError($result)) {
                echo 
    $result->getMessage() . ':<br /> ' $result->getDebugInfo().'<br />';
            }
            echo 
    "<table class='excel'>";
            echo 
    "<tr class='header'>";
            echo 
    "<td class='header'>Name</td>";
            echo 
    "<td class='header'>Piece</td>";
            echo 
    "<td class='header'># Days</td>";
            echo 
    "<td class='header'>Complete</td>";
            echo 
    "<td class='header'>XSA</td>";
            echo 
    "<td class='header'>XSR</td>";
            echo 
    "<td class='header'>XCANC</td>";
            echo 
    "<td class='header'>NLS</td>";
            echo 
    "<td class='header'>NLS MDU</td>";
            echo 
    "<td class='header'>Total Calls</td>";

            while (
    $row $result->fetchRow()) {
                
    $array[] = $row["employeeid"];
            }
                
    $result->free();

            foreach (
    $array as $str) {
                
    $res=$conn->executeStoredProc('StaffWagesNoSplit',array("'".$str."'","'".$date1."'","'".$date2."'"));

                if (
    MDB2::isError($res)) {
                            echo 
    $res->getMessage() . ':<br /> ' $res->getDebugInfo().'<br />';
                }

                if (
    $res) {
                    
    $x=$res->fetchRow();
                     while (
    $res->nextResult())
                     
    $res->free();
                }

                
    $res=$conn->executeStoredProc('StaffWagesWithSplit',array("'".$str."'","'".$date1."'","'".$date2."'"));

                if (
    MDB2::isError($res)) {
                            echo 
    $res->getMessage() . ':<br /> ' $res->getDebugInfo().'<br />';
                }

                if (
    $res) {
                     
    $y=$res->fetchRow();
                     while (
    $res->nextResult())
                     
    $res->free();
                }
                    echo 
    "<tr class='excel'>";
                    echo 
    "<td class='excel'>".$x["name"]."</td>";
                    echo 
    "<td class='excel'>"."$".number_format(($x['piece']+$y['piece']),2)."</td>";
                    echo 
    "<td class='excel'>".$x['# days']."</td>";
                    echo 
    "<td class='excel'>".($x['complete']+$y['complete'])."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['xsa']+$y['xsa']),0)."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['xsr']+$y['xsr']),0)."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['xcanc']+$y['xcanc']),0)."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['nls']+$y['nls']),0)."</td>";
                    echo 
    "<td class='excel'>".number_format(($x['nls mdu']+$y['nls mdu']),0)."</td>";
                    echo 
    "<td class='excel'>".($x['total calls']+$y['total calls'])."</td>";
                    echo 
    "</tr>";


            }
            echo 
    "</table>";

    ?>
    Last edited by Jeramie902; 01-06-09 at 13:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •