arc90 blog : Gaining Insight into Your Zend Queries

Gaining Insight into Your Zend Queries

Zend Framework is great. However, there is a limitation when using the database abstraction layer: You are unable to see the SQL query Zend is generating. Below is a patch that can be applied to the Pdo.php class. It will log all SQL queries to a file. It requires that your development directory structure includes a log/directory and a sql.log file, with the correct permissions. Of course, you can change this to fit your development environment.

-- Pdo.php.bak 2008-04-21 11:04:26.000000000 -0400
+++ Pdo.php     2008-04-21 13:35:54.000000000 -0400
@@ -227,6 +227,15 @@ class Zend_Db_Statement_Pdo extends Zend
   */
  public function _execute(array $params = null)
  {
+        /**
+         * This does not go into production code.
+         * To use:
+         *
+         * # touch sql.log
+         * # chmod 777 sql.log
+         */
+        file_put_contents("../log/sql.log", date("D M j G:i:s T Y") . ':' . $this->_stmt->queryString . "\n", FILE_APPEND);
+
      try {
          if ($params !== null) {
              return $this->_stmt->execute($params);

To install the patch, move to the Pdo.php directory and execute:

patch -p0 < PdoPatch.txt

Once the patch is installed, you can conveniently tail the sql.log file and watch each query.

# tail -f sql.log

It's not the most elegant hack, but it works fine for development. This is not recommended for production environments.

Trackback Pings (TrackBack URL for this entry)

http://www.arc90.com/cgi-bin/mt4/mt-tb.cgi/139.

Comments

I ran into this issue myself. Luckily there was someone answering questions on #zftalk.

Here's an alternative:
Once the db connection is made ($db) do this:
$_profiler = $db->getProfiler();
Then the following methods are available:
$_profiler->getTotalNumQueries();
$_profiler->getTotalElapsedSecs();
$_profiler->getQueryProfiles();

From the getQueryProfiles() you can getQuery(), getParams(), and getElapsedSecs()

Add that to your logger and bim bam bo, Roberts your mother's brother.
http://framework.zend.com/manual/en/zend.db.profiler.html

Posted on April 24, 2008 5:27 PM by clijunky

Hello clijunky,

Ah, very cool! Thanks for this tip. I will pass it along and try it out myself!

This is a much better solution and I'm glad you posted it.

Thank you.

- Ben

Posted on April 24, 2008 5:50 PM by Ben Sgro aka sk aka mr-sk

clijunky,

good find! i'm about to try it out right now. thanks for sharing.

-Dave