Page 1 of 1

MYSQL Buffered Query Problem

Posted: Thu Sep 03, 2009 3:43 am
by Vicki_UK
Hi,

I am reading Christian Darie's book Beginning PHP and MYSQL E-Commerce and I have stumbled upon a problem. I keep getting the following error:

ERRNO: 256
TEXT: 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.
LOCATION: C:\piarossini\business\database_handler.php, line 100, at September 2, 2009, 4:07 pm
Showing backtrace:
trigger_error("SQLSTATE[HY000]: General error: 2014 Cannot execute queries whil...", "256") # line 100, file: C:\piarossini\business\database_handler.php
DatabaseHandler.GetAll("CALL catalog_get_departments_list()") # line 13, file: C:\piarossini\business\catalog.php
Catalog.GetDepartments() # line 23, file: C:\piarossini\presentation\departments_list.php
DepartmentsList.init() # line 16, file: C:\piarossini\presentation\smarty_plugins\function.load_presentation_object.php
smarty_function_load_presentation_object(Array[2], Object: Application) # line 5, file: C:\piarossini\presentation\templates_c\%%A5^A5A^A5A1C73D%%departments_list.tpl.php
include("C:\piarossini\presentation\templates_c\%%A5^A5A^A5A1C73D%%depart...") # line 1869, file: C:\piarossini\libs\smarty\Smarty.class.php
Smarty._smarty_include(Array[2]) # line 44, file: C:\piarossini\presentation\templates_c\%%41^412^412F4E3D%%store_front.tpl.php
include("C:\piarossini\presentation\templates_c\%%41^412^412F4E3D%%store_...") # line 1256, file: C:\piarossini\libs\smarty\Smarty.class.php
Smarty.fetch("store_front.tpl", null, null, true) # line 1106, file: C:\piarossini\libs\smarty\Smarty.class.php
Smarty.display("store_front.tpl") # line 27, file: C:\piarossini\index.php


This is my database handler file:

Code: Select all

 
<?php
// Class providing generic data access functionality
class DatabaseHandler
{
  // Hold an instance of the PDO class
  private static $_mHandler;
 
  // Private constructor to prevent direct creation of object
  private function __construct()
  {
  }
 
  // Return an initialized database handler
  private static function GetHandler()
  {
    // Create a database connection only if one doesn’t already exist
    if (!isset(self::$_mHandler))
    {
      // Execute code catching potential exceptions
      try
      {
        // Create a new PDO class instance
        self::$_mHandler =
          new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD,
                  array(PDO::ATTR_PERSISTENT => DB_PERSISTENCY));
 
        // Configure PDO to throw exceptions
        self::$_mHandler->setAttribute(PDO::ATTR_ERRMODE,
                                       PDO::ERRMODE_EXCEPTION);
      }
      catch (PDOException $e)
      {
        // Close the database handler and trigger an error
        self::Close();
        trigger_error($e->getMessage(), E_USER_ERROR);
      }
    }
 
    // Return the database handler
    return self::$_mHandler;
  }
 
  // Clear the PDO class instance
  public static function Close()
  {
    self::$_mHandler = null;
  }
 
  // Wrapper method for PDOStatement::execute()
  public static function Execute($sqlQuery, $params = null)
  {
    // Try to execute an SQL query or a stored procedure
    try
    {
      // Get the database handler
      $database_handler = self::GetHandler();
 
      // Prepare the query for execution
      $statement_handler = $database_handler->prepare($sqlQuery);
 
      // Execute query
      $statement_handler->execute($params);
    }
    // Trigger an error if an exception was thrown when executing the SQL query
    catch(PDOException $e)
    {
      // Close the database handler and trigger an error
      self::Close();
      trigger_error($e->getMessage(), E_USER_ERROR);
    }
  }
 
  // Wrapper method for PDOStatement::fetchAll()
  public static function GetAll($sqlQuery, $params = null,
                                $fetchStyle = PDO::FETCH_ASSOC)
  {
    // Initialize the return value to null
    $result = null;
 
    // Try to execute an SQL query or a stored procedure
    try
    {
      // Get the database handler
      $database_handler = self::GetHandler();
 
      // Prepare the query for execution
      $statement_handler = $database_handler->prepare($sqlQuery);
 
      // Execute the query
      $statement_handler->execute($params);
     
      // Fetch result
      $result = $statement_handler->fetchAll($fetchStyle);
    }
    // Trigger an error if an exception was thrown when executing the SQL query
    catch(PDOException $e)
    {
      // Close the database handler and trigger an error
      self::Close();
      trigger_error($e->getMessage(), E_USER_ERROR);
    }
 
    // Return the query results
    return $result;
  }
 
  // Wrapper method for PDOStatement::fetch()
  public static function GetRow($sqlQuery, $params = null,
                                $fetchStyle = PDO::FETCH_ASSOC)
  {
    // Initialize the return value to null
    $result = null;
 
    // Try to execute an SQL query or a stored procedure
    try
    {
      // Get the database handler
      $database_handler = self::GetHandler();
 
      // Prepare the query for execution
      $statement_handler = $database_handler->prepare($sqlQuery);
 
      // Execute the query
      $statement_handler->execute($params);
 
      // Fetch result
      $result = $statement_handler->fetch($fetchStyle);
    }
    // Trigger an error if an exception was thrown when executing the SQL query
    catch(PDOException $e)
    {
      // Close the database handler and trigger an error
      self::Close();
      trigger_error($e->getMessage(), E_USER_ERROR);
    }
 
    // Return the query results
    return $result;
  }
 
  // Return the first column value from a row
  public static function GetOne($sqlQuery, $params = null)
  {
    // Initialize the return value to null   
    $result = null;
 
    // Try to execute an SQL query or a stored procedure
    try
    {
      // Get the database handler
      $database_handler = self::GetHandler();
 
      // Prepare the query for execution
      $statement_handler = $database_handler->prepare($sqlQuery);
 
      // Execute the query
      $statement_handler->execute($params);
 
      // Fetch result
      $result = $statement_handler->fetch(PDO::FETCH_NUM);
 
      /* Save the first value of the result set (first column of the first row)
         to $result */
      $result = $result[0];
    }
    // Trigger an error if an exception was thrown when executing the SQL query
    catch(PDOException $e)
    {
      // Close the database handler and trigger an error
      self::Close();
      trigger_error($e->getMessage(), E_USER_ERROR);
    }
 
    // Return the query results
    return $result;
  }
}
?>
 
I have tried the solution suggested on his site of downloading and replacing the php_pdo_mysql.dll file but it didn't work. I am brand new to this so do not have much knowledge on the subject.
I hope someone can help me as it would be much appreciated.

Thanks

Vicki

Re: MYSQL Buffered Query Problem

Posted: Thu Oct 01, 2009 5:05 pm
by kiwiburger
I have also found this problem as well. I have contacted Cristian Darie via linkedin.com and am awaiting a reply. If I find the answer I will post it to this forum. I am using xampp on windows. I noticed that this problem has been introduced recently as used this code a couple of months ago and it worked with the solution mentioned in the errata.

Thanks
Kiwi

Re: MYSQL Buffered Query Problem

Posted: Tue Oct 13, 2009 3:41 am
by nhal
I had the same problem but worked around it by installing xampp 1.7.1 which comes with the following;
Apache 2.2.11
MySQL 5.1.33
PHP 5.2.9

The solution on Cristian Darie's errata page works with this version of xampp. Hopes this helps until Cristian comes up with a solution for xampp 1.7.2 :-)