CODEWITHSUNDEEP
phpmysqlsqlstored-procedurespdo
TheMethod
TheMethod

Reputation: 3001

Unbuffered query error with PDO and stored procedures

I am re factoring a project and part of what I am doing is replacing inline SQL with calls to stored procedures. I have run into an issue. I am using PHP version 5.3.5 and MySQL version 5.0.7

The project has members, members can have requests. I want to get all requests for a specific member and return them as an array of Request objects.

I have created a static method that calls a stored proc that returns all request ids, which I in turn use to instantiate new Request objects. The problem is I am getting an errror regarding unbuffered queries, here is the error:

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 have done as the error message suggested and used fetchAll but I am still gettting this problem. Here is a simplified version of the relevant PHP:

class Request
{
    public function __construct($request_id)
    {
        //as you can see this is still inline 
        $db = Database::get_instance();
        $sql = "SELECT field1,field2,field3 FROM requests WHERE request_id = ?";
        $stmt = $db->prepare($sql);
        $stmt->execute(array($request_id));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if(!empty($row))
        {
            //populate properties
            foreach($row as $key=>$value)
            {
                $this->$key = $value; 
            }   
        }

    }

    public static function get_requests_by_member_id($member_id)
    {
        //array to hold results
        $requests = array();
        //this is just a PDO object
        $db = Database::get_instance();
        $sql = "CALL get_requests_by_member_id(?)";
        $stmt = $db->prepare($sql);
        /bind the param 
        $stmt->bindParam(1,$member_id,PDO::PARAM_INT,10);
        $stmt->execute();
        //iterate through adding new Request objects to the array       
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row)
        {
            //here is where the problem occurs
            $requests[] = new self($row['request_id']);
        }

        return $requests;   

    }

}

And a simplified version of my stored proc:

delimiter $$

CREATE PROCEDURE get_requests_by_member_id(member_id_in INT)
BEGIN
    SELECT request_id 
    FROM requests 

    WHERE cr.member_id = member_id_in;
END$$
delimiter ;

As I noted above the problem occurs when I try to instantiate a new Request to add to the array. This appears to cause the cursor problem that I am seeing however because I used fetchAll() should that not resolve the problem? Is there a best practice for handing this type of scenario? Any advice would be very much appreciated. Thanks much!

Upvotes: 0

Views: 1230

Answers (1)

slashingweapon
slashingweapon

Reputation: 11307

When you are done with a statement, you should call closeCursor(), like so:

$stmt->closeCursor();

By closing the cursor, you make the connection available for new requests. See the PHP documentation on PDOStatement::closeCursor for details.

Upvotes: 4

Related Questions