k1ng Posted July 22, 2010 Share Posted July 22, 2010 Hello guys, I'm working on a webshop with the help of the book: "Beginning PHP and MYSQL E-commerce " by Cristian Darie. However I got stuck with the famous 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. 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 Bellow is my DatabaseHandler: <?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; } } ?> And here is my Catalog code where I execute the queries: <?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); } } ?> Quote Link to comment Share on other sites More sharing options...
atilerturk Posted December 8, 2010 Share Posted December 8, 2010 unfortunately you get this pdo error when you run windows machine.. its a bug and has not been fixed so far... Basically you get this error when you use STORE PROCEDURES with PDO.. for example based on your code public static function GetDepartments() { // Build SQL query $sql = 'CALL catalog_get_departments_list()'; // Execute the query and return the results return DatabaseHandler::GetAll($sql); } Solution:: instead of using store procedure CALL catalog_get_departments_list() use sql query.. public static function GetDepartments() { // Build SQL query $sql = 'SELECT department_id, name FROM department ORDER BY department_id'; // // Execute the query and return the results return DatabaseHandler::GetAll($sql); } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.