[RESOLVED] PDO : "Cannot execute queries while other unbuffered queries are active"
Results 1 to 4 of 4

Thread: [RESOLVED] PDO : "Cannot execute queries while other unbuffered queries are active"

  1. #1
    Senior Member
    Join Date
    Jul 2007
    Location
    Montpellier, France
    Posts
    394

    resolved [RESOLVED] PDO : "Cannot execute queries while other unbuffered queries are active"

    Hi

    I'm getting an error message :
    Code:
    PDO::errorInfo()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. )
    after scouting about on the interweb, here's one thing I tried with my connection :

    PHP Code:
    try {
        
    $conn = new PDO("mysql:host=$hostname;dbname=esma"$username$password, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
            
            
    // force connection encoding to uft8
            
    $conn->exec('SET CHARACTER SET utf8');
            
        
    /*** echo a message saying we have connected ***/
        
    op('Connected to database');
            
        }
    catch(
    PDOException $e)
        {
        
    op($e->getMessage());
        } 
    I also tried

    PHP Code:
    try {
        
    $conn = new PDO("mysql:host=$hostname;dbname=esma"$username$password);
            
            
    $conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY1); 
    but that gives an additional warning :

    Code:
    Warning: PDO::setAttribute() expects parameter 1 to be long, string given in /home/
    the php version is 5.0.44

    has anyone got any idea what i can do ?
    Last edited by steamPunk; 10-27-2008 at 12:07 PM.

  2. #2
    Senior Member
    Join Date
    Jul 2007
    Location
    Montpellier, France
    Posts
    394
    when i do

    PHP Code:
        $conn = new PDO("mysql:host=$hostname;dbname=esma"$username$password);
            
            
    //$conn->setAttribute(1,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY);
            
            
    PDO::setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERYtrue); 
    as suggested here : http://php.morva.net/manual/en/ref.pdo-mysql.php

    i get the message :

    Code:
    Fatal error: Non-static method PDO::setAttribute() cannot be called statically in /home/e
    anyone ?

    this is hugely doing my head in, lol !

  3. #3
    Senior Member
    Join Date
    Jul 2007
    Location
    Montpellier, France
    Posts
    394

    still not working

    but I've narrowed down the field .....

    it seems that the order of these two bits of code is important :

    PHP Code:
    // DISPLAY ON LINE OR NOT
    if(isset($_GET['online'])&&($_SESSION['logged'] == "ok")){    
        
    $sql "UPDATE pages SET page_online='".$_GET['online']."' WHERE page_id='".$_GET['modifID']."' LIMIT 1";
        
    updateQuery($conn,$sql);
    }


    // GET CATEGORY INFO
    if(isset($_GET['cat_id'])){
        
    $_SESSION['cat_id']= $_GET['cat_id'];
        
    $sql "SELECT cat_title FROM cats WHERE cat_id='".$_SESSION['cat_id']."'";
        
    $res selectQuerySingleRow($conn,$sql);
        
    $_SESSION['cat_title'] = $res['cat_title'];
    }else{
        if(!isset(
    $_SESSION['cat_id'])){
            
    jsRefresh("admin_main.php");
        }


    in the above order it works because the UPDATE query is done and then the SELECT query

    but if i inverse the order it doesn't work because PDO seems to hog the cursor after the SELECT and it isn't freed for the UPDATE query.

    However i can't figure out how to reset the cursor after the SELECT to make it available for for the UPDATE query

    does anyone know ??


    thanks

  4. #4
    Senior Member
    Join Date
    Jul 2007
    Location
    Montpellier, France
    Posts
    394

    Prepared statements, prepared statements

    that was the answer - i've never used them before but it seems to have fixed my problem because you can reset the cursor

    so, for anyone who has the same problem ...

    you need to put some statement stuff into your query code :

    PHP Code:
    function selectQuery($conn,$sql){
        
        
    op($sql);
        
    $out = array();
        
    $sth $conn -> prepare($sql);
        
    $sth->execute();
        
    $res $sth->fetchAll();
        
    $sth->closeCursor();

        if (!
    $res) {
            
    op("\nPDO::errorInfo()");
            
    print_r($conn->errorInfo());
        }else{
            
    $i 1;                        
            foreach (
    $res as $row) {
                
    $out[$i] = array();
                foreach(
    $row as $k => $v){                                                
                    
    $out[$i][$k] = $v;
                }    
                
    $i++;
            }        
        }    
        return 
    $out;    

    hope this is of use to someone and can save them some pain !

    I think that maybe the fetchAll does the same thing as my nested foreach loops - have to check that out

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •