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.
I've read all over the internet about the work around including replacing the dll part (dll on website that was given doesn't exist anymore). Nothing worked so far.
Could you please help me to fix this?
I'm working on a Windows machine with PHP 5.3.1
Here is my DatabaseHandler
Code: Select all
<?php
class DatabaseHandler
{
// Hold an instance of the PDO class
private static $_mHandler;
// Private constructor to prevent direct creation of object
private function __construct()
{
}
// Clear the PDO class instance
public static function Close()
{
self::$_mHandler = null;
}
// 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;
}
// Wrapper method for PDOstatement::execute() GEEN $fetchStyle in deze functie
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 fot PDOStatement::fetch()
public static function GetRow($sqlQuery, $params = null, $fetchStyle = PDO::FETCH_ASSOC)
{
// Initialize the return value to null
$result = null;
// Try to execyte 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 column value from a row
public static function GetOne($sqlQuery, $params = null)
{
// Innitialize the return value to null
$result = null;
// Try to execute an SQL or a stored procedure
try
{
$database_handler = self::GetHandler();
$statement_handler = $database_handler->prepare($sqlQuery);
$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 SQL query
catch(PDOException $e)
{
self::Close();
trigger_error($e->getMessage(), E_USER_ERROR);
}
return $result;
}
}
?>Code: Select all
<?php
// Business tier class for reading product catalog information
class Catalog
{
// Retrieves all departments
public static function GetDepartments()
{
// Build SQL query
$sql = 'CALL catalog_get_departments_list()';
// Execute the query and return the results
return DatabaseHandler::GetAll($sql);
}
// Retrieves complete details for the specified department
public static function GetDepartmentDetails($departmentId)
{
// Build SQL query
$sql = 'CALL catalog_get_department_details(:department_id)';
// Build the parameters array
$params = array (':department_id' => $departmentId);
// Execute the query and return the results
return DatabaseHandler::GetRow($sql, $params);
}
// Retrieves list of categories that belongs to a department
public static function GetCategoriesInDepartment($departmentId)
{
// Build SQL query
$sql = 'CALL catalog_get_categories_list(:department_id)';
// Build the parameters array
$params = array (':department_id' => $departmentId);
// Execute the query and return the results
return DatabaseHandler::GetAll($sql, $params);
}
// Retrieves complete details for the specified category
public static function GetCategoryDetails($categoryId)
{
// Build SQL query
$sql = 'CALL catalog_get_category_details(:category_id)';
// Build the parameters array
$params = array (':category_id' => $categoryId);
// Execute the query and return the results
return DatabaseHandler::GetRow($sql, $params);
}
/* Calculates how many pages of products could be filled by
* number of products returned by the $countSql query
*/
private static function HowManyPages($countSql, $countSqlParams)
{
// Create a has for the sql query
$queryHashCode = md5($countSql . var_export($countSqlParams, true));
// Verify if we have the query results in cache
if (isset($_SESSION['last_count_hash']) &&
isset ($_SESSION['how_many_pages']) && $_SESSION['last_count_hash'] === $queryHashCode)
{
// Retrieve the cached value
$how_many_pages = $_SESSION['how_many_pages'];
}
else
{
// Execute the query
$items_count = DatabaseHandler::GetOne($countSql, $countSqlParams);
// Calculate the number of pages
$how_many_pages = ceil($items_count / PRODUCTS_PER_PAGE);
// Save the query and its count result in the session
$_SESSION['last_count_hash'] = $queryHashCode;
$_SESSION['how_many_pages'] = $how_many_pages;
}
// Return the number of pages
return $how_many_pages;
}
// Retrieves list of products that belong to a category
public static function GetProductsInCategory($categoryId, $pageNo, &$rHowManyPages)
{
// Query that returns the number of products in the category
$sql = 'CALL catalog_count_products_in_category(:category_id)';
// Build the parameter array
$params = array (':category_id' => $categoryId);
// Calculate the number of pages required to display the products
$rHowManyPages = Catalog::HowManyPages($sql, $params);
// Calculate the start item
$start_item = ($pageNo - 1) * PRODUCTS_PER_PAGE;
// Retrieve the list of products
$sql = 'CALL catalog_get_products_in_category(:category_id,
:short_product_description_length, :products_per_page, :start_item)';
// Build the parameters array
$params = array(
':category_id' => $categoryId,
':short_product_description_length' => SHORT_PRODUCT_DESCRIPTION_LENGTH,
':products_per_page' => PRODUCTS_PER_PAGE,
':start_item' => $start_item);
// Execute the query and return the results
return DatabaseHandler::GetAll($sql, $params);
}
// Retrieves the list of products for the department page
public static function GetProductsOnDepartment($departmentId, $pageNo, &$rHowManyPages)
{
// Query that returns the number of products in the department page
$sql = 'CALL catalog_count_products_on_department(:department_id)';
// Build the parameters array
$params = array (':department_id' => $departmentId);
// Calculate the number of pages required to display the products
$rHowManyPages = Catalog::HowManyPages($sql, $params);
// Calculate the start item
$start_item = ($pageNo-1) * PRODUCTS_PER_PAGE;
// Retrieve the list of products
$sql = 'CALL catalog_get_products_on_department(
:department_id, :short_product_description_length,
:products_per_page, :start_item)';
// Build the parameters array
$params = array(
':department_id' => $departmentId,
':short_product_description_length' => SHORT_PRODUCT_DESCRIPTION_LENGTH,
':products_per_page' => PRODUCTS_PER_PAGE,
':start_item' => $start_item);
// Execute the query and return the results
return DatabaseHandler::GetAll($sql, $params);
}
// Retrieves the list of products on catalog page
public static function GetProductsOnCatalog($pageNo, &$rHowManyPages)
{
// Query that returns the number of products for the front products
$sql = 'CALL catalog_count_products_on_catalog()';
// Calculate the number of pages required to display the products
$rHowManyPages = Catalog::HowManyPages($sql, null);
// Calculate the start item
$start_item = ($pageNo-1) * PRODUCTS_PER_PAGE;
// Retrieve the list of products
$sql = 'CALL catalog_get_products_on_catalog(
:short_product_description_length,
:products_per_page, :start_item)';
// Build the parameters array
$params = array(
':short_product_description_length' => SHORT_PRODUCT_DESCRIPTION_LENGTH,
':products_per_page' => PRODUCTS_PER_PAGE,
':start_item' => $start_item);
// Execute the query and return the results
return DatabaseHandler::GetAll($sql, $params);
}
// Retrieves complete product details
public static function GetProductDetails($productId)
{
// Build SQL query
$sql = 'CALL catalog_get_product_details(:product_id)';
// Build the parameters array
$params = array (':product_id' => $productId);
// Execute the query and return the results
return DatabaseHandler::GetRow($sql, $params);
}
// Retrieves product locations
public static function GetProductLocations($productId)
{
// Build SQL query
$sql = 'CALL catalog_get_product_locations(:product_id)';
// Build the paramters array
$params = array(':product_id' => $productId);
// Execute the query and return the results
return DatabaseHandler::GetAll($sql, $params);
}
}