Cannot execute queries while other unbuffered queries are active

[daveb]

I am running PHPMaker 2024.10 and using XAMPP on my PC to run. I am getting this error trying to "summarize" some paymentsrecords using the code shown below:. The message appears on the DELETE statement.

Exception message: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

Can I change this setting? how? or is the a better way to do this?

Thanks

I have a "control" record and this is the Row_updating function. This record just records the date of the summarization

// Row Updating event
function Row_Updating($rsold, &$rsnew)
{
    global $Userid;
    $rsnew["Updated_By"] = $Userid;

//  Select records to be summarized up to the specified date

    $Mysql  = "SELECT sum(`payments`.`Debit` - `payments`.`Credit` + `payments`.`Tax`) AS `nettax`, ";
    $Mysql .= "`payments`.`Username` AS `Username`, `members`.`Activated` AS `Activated` ";
    $Mysql .= " FROM (`payments` join `members` on(`payments`.`Username` = `members`.`Username`)) ";
    $Mysql .= " WHERE `members`.`Activated` = _utf8'Y' AND payments.Date <= '" . $rsnew['Code2'] ."'";
    $Mysql .= " GROUP BY `payments`.`Username` ORDER BY `payments`.`Username` ASC, `payments`.`Date` DESC ";
    $works = ExecuteRows($Mysql);

// process array to delete individual records and replace with a summary record

    foreach ($works AS $work)     {

    // delete payment records summarized

     $Mysql2  = "DELETE FROM `payments` WHERE `Username` = '" . $work['Username'] . "' AND ";
     $Mysql2 .= " `Date` <= '" . $rsnew['Code2'] ."'";
     $workdel = ExecuteStatement($Mysql2);

    // write OSBAL record for summary date

      if ($work["nettax"] <> 0)
        {  
         $names = "`Username`, `Date`, `Debit`, `Credit`, `Tax`, `Reason`, `Remarks`,`Updated_By`";
         $values  = "'" . $work["Username"] . "', " . $rsnew["Code2"] . "', " ;
         if ($work['nettax'] > 0)
          {
           $values .= "'" . $work["nettax"] . "', " ; 
           $values .= "'0.00', " ;
          }
         $values .= "'OSBAL'" . "," ;
         $values .= "'Summarise balance'," ;                      
         $values .= "'" . $Userid ."' "; 
         $Mysql3 = "INSERT INTO `payments` ($names) VALUES ($values)";
      $work = ExecuteStatement($Mysql3); 
        }
      }
        return true;
    }

[arbei]

Are you deleting from and inserting to the same table (i.e. "payments") before you are updating? If so, do you need to do it before updating? If not, you may try move your code the Row_Updated server event (i.e. after updating).

[daveb]

I want to sum the amounts up to a certain date, and replace those records with a single summary record with "OSBAL". So I deleted first and then insert the new record with the same date. The row_updating function is based on another "parameter" table that simply records the date being used.

[arbei]

arbei wrote:

you may try move your code the Row_Updated server event (i.e. after updating).