[VolcomMky]

So I didn't think this would become a problem because it was working fine on my GoDaddy Linux Shared server, but since I have grown I have moved to a Dedicated linux server on godaddy.

Since it's on a new server, I started realized I was getting this error:

Array
(
    [0] => HY000
    [1] => 2014
    [2] => 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 do have multiple pages that have loops with another query inside, like so:

Transaction:
- Image A
- Image B
- Image C
- Agent 1
= Agent Information
- Agent 2
= Agent Information
- Transaction Details

So therefore a simple Join would not do.
Is there a file/setting I can change to allow the queries or do I have to manually go in 1 by 1 to each of the 260 PDO::Prepare statements and add MYSQL_ATTR_USE_BUFFERED_QUERY=>true?
Only reason I am trying other solutions first is because I dont know if it will mess up some of my loops.

I did not use PDO::fetchAll's, i used

foreach($db->query() as $row)

Thanks in advance!

[VolcomMky]

I even thought about extending the PDO Class like so, but failed.

<?php
class CPDO extends PDO
{
   public function prepare($query)
   {
      $this->closeCursor();
      parent::prepare($query);
   }
}
?>

[Dormilich]

closeCursor() is a method of PDOStatement, not of PDO.

[AdaHacker]

VolcomMky, on 18 August 2011 - 08:52 PM, said:

Is there a file/setting I can change to allow the queries or do I have to manually go in 1 by 1 to each of the 260 PDO::Prepare statements and add MYSQL_ATTR_USE_BUFFERED_QUERY=>true?

You should be able to set the attribute once on the PDO object using PDO::setAttribute() rather than passing it to each prepare() call.

This post has been edited by AdaHacker: 19 August 2011 - 06:03 AM

[Ace26]

function insertMessage(Message $message)
  {
     $query = "CALL sp_insert_message(:sender, :reciepient,:subject, :dateSent, :body, :status, :type)";
	if(!isset($this->connObj))
	   $this->connObj = $this->connectToDB($this->DSN, $this->dbUser, $this->pwd);
	
	$stmtObj = $this->connObj->prepare($query);
	
	$stmtObj->bindParam(':sender', $message->getSender(), PDO::PARAM_STR, 30);
	$stmtObj->bindParam(':reciepient', $message->getReciepient(), PDO::PARAM_STR, 30);
	$stmtObj->bindParam(':subject', $message->getSubject(), PDO::PARAM_STR,100);
	$stmtObj->bindParam(':dateSent', $message->getDateOfSending());
	$stmtObj->bindParam(':body', $message->getBody());
	$stmtObj->bindParam(':status', $message->getStatus());
	$stmtObj->bindParam(':type', $message->getMessageType());
	
	$stmtObj->execute();
	$rowCount = $stmtObj->rowCount();
	
        //EMPHASIS HERE!
	$stmtObj = null;
	$this->connObj = null;
	
	return $rowCount;
  }

That method above is contained in a data access class I wrote for one of my projects and there are lots more like that in the same class.

If you noticed the "EMPHASIS HERE" comment you'll see that I nulled the connection object after execution ends in that method. Before I introduced that piece of code, I was getting exactly the same error you are when ever I tried to execute another method that connected to the database.

Now with that in place the connection object is destroyed thereby leaving room for other queries using the same connection attributes to connect and execute.

Hence, though I don't understand exactly the "loopy" thing you are up and about, i'll suggest that after every execution of database querying using a connection object, destroy or close that connection so that other querying blocks of code with the same connection attributes can connect and execute.

That's how I solved mine.

This post has been edited by Ace26: 19 August 2011 - 07:25 AM

[AdaHacker]

Ace26, on 19 August 2011 - 10:21 AM, said:

Hence, though I don't understand exactly the "loopy" thing you are up and about, i'll suggest that after every execution of database querying using a connection object, destroy or close that connection so that other querying blocks of code with the same connection attributes can connect and execute.

That's not really a good solution. The proper way to address this is to either manually close the cursor when you're done fetching data (assuming you don't fetch all the rows) or to just use a buffered query. If you're going to query the same database again later, closing the connection does nothing but introduce unnecessary overhead.

That solution also doesn't address the problem at hand. The OP is looping over a query result set and issuing other queries inside the loop at the same time. In this case, closing the connection would completely defeat the purpose because he still needs the rest of the results. The proper way to do this is either to use a buffered query (if the result set will fit in memory) and reuse the same connection for queries in the loop or to stick with the unbuffered query and open additional connections for the queries he needs to run inside the loops.

[VolcomMky]

@Dormilich - That completely got past me, thanks for pointing it out. Changed and no success.

@AdaHacker - My current connection is set up like this.

$db = new PDO(DSN, DB_USER, DB_PASSWORD,array(MYSQL_ATTR_USE_BUFFERED_QUERY=>1));

Would this be correct or would I have to call the PDO::setAttribute after I initiate the connection?


@Ace26 - I understand that I can null/falsify/unset the objects after running them. To clarify my "loopy" situation, I basically mean that I will run a loop to go through records, but each record has child records in a different database.

Scenario: Your a Parent with 3 children

Looking at a school roster, there is a list of parents (loop through parents)
Under the parents name, it shows their children of the parents (2nd call to the database)

[Dormilich]

@Ace26: bindParam() would complaine here, you need a referencable variable as parameter. use bindValue() for such cases.

@VolcomMky: depending on the HTML output, I’d try a JOIN so I can fetch the parents and children at once (you might even try PDO::FETCH_NAMED)

and one more thing:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

to actually get any ocurring errors.

This post has been edited by Dormilich: 19 August 2011 - 09:33 AM

[AdaHacker]

VolcomMky, on 19 August 2011 - 12:10 PM, said:

$db = new PDO(DSN, DB_USER, DB_PASSWORD,array(MYSQL_ATTR_USE_BUFFERED_QUERY=>1));

Would this be correct or would I have to call the PDO::setAttribute after I initiate the connection?

Close, but what you've written there won't quite work. MYSQL_ATTR_USE_BUFFERED_QUERY is a class constant, not a global constant. The options array should be array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>1). If you use that, it ought to work.

This post has been edited by AdaHacker: 19 August 2011 - 10:13 AM

[Ace26]

AdaHacker, on 19 August 2011 - 05:52 PM, said:

That solution also doesn't address the problem at hand. The OP is looping over a query result set and issuing other queries inside the loop at the same time. In this case, closing the connection would completely defeat the purpose because he still needs the rest of the results. The proper way to do this is either to use a buffered query (if the result set will fit in memory) and reuse the same connection for queries in the loop or to stick with the unbuffered query and open additional connections for the queries he needs to run inside the loops.

I also had a "loopy" situation in one of the methods of my data access classes where for every record returned, child records had to be gotten to initialize a class. I tried the query buffering solution all to no avail as I ended up with the same error message. Till I went about it the way I described. I guess I didn't describe it well.

@Dormilich, I understand that the variable argument to bindParam() is passed by reference but it's never complained and I don't see what's wrong with that code.

@VolcomMky Can you paste your code here so we could see?

[VolcomMky]

This is where the script breaks on my "Review" page.

foreach($db->query('SELECT * FROM tms_doc_logs WHERE document='.$_REQUEST['_f'].' ORDER BY ID ASC') as $log) // Breaks Here

Before the above line is hit, the database gets hit once.

$_d = $db->prepare('SELECT a.*,b.username,b.first_name,b.last_name,b.company,b.email,b.phone
						 FROM tms_doc_data AS a 
						 INNER JOIN tms_users AS b 
						 ON a.owner=b.id
						 WHERE a.id=?');
$_d->execute(array($_REQUEST['_f']));
$data = $_d->fetch(PDO::FETCH_ASSOC);

This post has been edited by VolcomMky: 19 August 2011 - 10:53 AM

[Dormilich]

Ace26, on 19 August 2011 - 07:35 PM, said:

@Dormilich, I understand that the variable argument to bindParam() is passed by reference but it's never complained and I don't see what's wrong with that code.

if you leave error reporting to the default "silent", you won’t ever get an error message.

@VolcomMky: what happens if you turn on PDO’s error reporting?

This post has been edited by Dormilich: 19 August 2011 - 10:55 AM

[VolcomMky]

AdaHacker, on 19 August 2011 - 10:13 AM, said:

Close, but what you've written there won't quite work. MYSQL_ATTR_USE_BUFFERED_QUERY is a class constant, not a global constant. The options array should be array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>1). If you use that, it ought to work.

I made this change and it looked like it opened up another page or 2 to work properly, but still there a other pages getting the problem.

[VolcomMky]

Dormilich - I have put your suggestion in and am still testing til I get a error.

What I mean by that is for some reason my server does not support the DateTime class and alot of my pages use that, so when I comment out the DateTime calls, the loops (that were erroring) are now working properly.


I don't want to say I wasted your guys time and I dont think you did, I have already commented out the DateTime calls and thats how I learned of the PDO Error, but I restored the original code and forgot about the DateTime and in my last post thought I was still having issues but looks like I do, but not with PDO anymore.

So I will continue testing and hopefully get the DateTime class installed and see how it flows.

As for now, I appreciate your help and will be back in case something is still acting up.

[Dormilich]

DateTime is available as of PHP 5.2, but most of its useful methods only since 5.3.