SQLSTATE[HY000]: General error: 2014

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
k1ng
Forum Newbie
Posts: 1
Joined: Thu Jul 22, 2010 7:28 am

SQLSTATE[HY000]: General error: 2014

Post by k1ng »

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

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;
	}
	
	
	
}

?>
And here is my Catalog code, where I execute my queries:

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);
	}
	 
}
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SQLSTATE[HY000]: General error: 2014

Post by Eran »

The error is pretty self explanatory and hints at the solution. You are using fetch() instead of fetchAll() for retrieving a single record, but leaving the cursor open. Read the notes at the end of the documentation on PDO fetch() - http://www.php.net/manual/en/pdostatement.execute.php
Alternatively, you can set the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute as suggested
bigboy
Forum Newbie
Posts: 1
Joined: Tue Jul 27, 2010 10:56 am

Re: SQLSTATE[HY000]: General error: 2014

Post by bigboy »

im following the php and mysql ecommerce book by christan.

im stuck on chapter 5 with the same error message.

how do you resolve it?

im using xamp, on windows vista.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: SQLSTATE[HY000]: General error: 2014

Post by josh »

Hey pytrin thanks for this. Found this on google. I had been developing with buffered queries (guess I'm a "newb" lol) and just switched my test suite. This is one of the few issues my code had lurking in the deep end.
Post Reply