2

Is it possible to get around mysql_unbuffered_query()'s limitation of having one query running at a time by opening a second connection?

For example, the following code is giving me the error:

mysql_select_db(): Function called without first fetching all rows from a previous unbuffered query

        $feedData =  mysql_unbuffered_query($sql, $this->_unbufferedDbManager->db->connection);

        while ($record = mysql_fetch_assoc($feedData)) {
            File::fputcsv($this->_fileHandle, $record, $this->delimiter, $this->enclosure);       

            $sql = "UPDATE   transactions
                    SET      feed_transmit_date = '$this->today'
                    WHERE    transaction_id = " . $record['transaction_id'];
            $this->dbManager->DbQuery($sql);

            print_r($this->_unbufferedDbManager->db->connection);
            print_r($this->dbManager->db->connection);
        }

The two print_r()'s at the end output: Resource id #637Resource id #639

DbManager is an old pear data access layer

Note: I would have used a mysql_unbuffered_query tag, but I recently opened a bounty that put me below the "create new tags privilege."

webbiedave
  • 48,414
  • 8
  • 88
  • 101
Parris Varney
  • 11,320
  • 12
  • 47
  • 76
  • Each connection/handle to the database gets its own context, so yes, you can open multiple connections and have independent queries running on each. Just be careful to specify the proper connection handle in all the mysql_*() calls or you'll get very weird/bad results from mixing operations between the two. – Marc B May 12 '11 at 21:54
  • Since it seems you have 2 different db connections (different resource ids) this should be working. On a sidenote, would it perhaps be best to do the `UPDATE`s all at once after the loop so that the unbuffered query is quickly done with all its fetching? – Fanis Hatzidakis May 12 '11 at 22:01
  • @Fanis - That's what we tried originally, and it was definitely a more efficient method. The problem is it became difficult too monitor for errors, the original query is selecting from a different table and datbase than the update query, and they're a little more complex than I put as an example for this question. Maybe I'm just getting a misleading error message and I'm barking up the wrong tree for a solution. – Parris Varney May 13 '11 at 14:15
  • I tried recreating some of it but didn't get your error when using 2 differenet mysql connections. I'm assuming the `mysql_select_db()` that triggers the error happens in the `$this->dbManager->DbQuery($sql);` call? – Fanis Hatzidakis May 13 '11 at 17:50
  • It does, yea. At first I thought maybe it was because I was trying to write to a table locked by the unbuffered query, so I created a new table to write to, just to check. Same results. – Parris Varney May 13 '11 at 18:03
  • This is a longshot, but it can't be the case that `DbQuery()` tries to use the existing connection but it somehow has a new one by the time you print_r them both? – Fanis Hatzidakis May 18 '11 at 11:23
  • The answer was in DbManager, like you said. It kept different resource IDs, but used the same connection. Using mysql_connect() over the DbManager worked. If you leave an answer I can accept it. Thanks for you help. – Parris Varney May 18 '11 at 15:55

1 Answers1

2

On PMV's suggestion I'm writing this answer. In summary, it was an issue of the different database classes using the same connection even though they reported different resource ids. See the question's comments for more.

Fanis Hatzidakis
  • 5,282
  • 1
  • 33
  • 36
  • 1
    pear's DbManager always uses the same connections even though multiple object instances instances have different resource IDs. – Parris Varney May 18 '11 at 20:42