Two techniques for retrieving SQL from Zend Framework’s database components

Zend Framework’s database components can build and run queries without the developer’s having to touch the underlying SQL. Sometimes, though, developers need the SQL for diagnosing problems or to look for efficiencies. That SQL can be accessed with a choice of two main techniques:

getQuery()

getQuery() is a method of Zend_Db_Profiler and seems, along with getLastQueryProfile(), to be the standard advice for retrieving SQL of a recent query:

query = $profiler->getLastQueryProfile();
echo $query->getQuery();

Yes, getQuery() works, but what if I’m not using the profiler, or I need to retrieve SQL before a query has run?

__toString()

The “magic method” __toString() is simple and can be used either before or after the query is run. When applied to a Zend_Db_Select object, it generates an SQL string.

From the Zend_Db_Select documentation:

$select = $db->select()->from('products');
$sql = $select->__toString();
echo "$sql\n";

// The output is the string:
// SELECT * FROM "products"

__toString() is simple to use, yet is often forgotten when discussing how to retrieve SQL from Zend_Db_Select. I recommend it.

8 replies
  1. Justin Dearing
    Justin Dearing says:

    I am curious, does the iSeries flavor of DB2 have profile and trace capabilities that allow you to do this on the DB end of things?

    Not that its not useful to do it at the application level, more idle curiosity.

    Reply
  2. Aaron Hawley
    Aaron Hawley says:

    Sure. Zend_Paginator takes a Zend_Db_Select object and then modifies it (puts FETCH FIRST 20 ROWS ONLY, or LIMIT 20, or whatever your flavor of DBMS requires). These modifications are made to the SQL at the time of sending them to the server. There’s no way to retrieve the query that was *sent* with __toString. Only asking the Profiler will give you the *actual* query used. That’s been my experience, at least.

    This *all* assumes you are interested in the row-limited SQL. I know I am!

    Reply
  3. Alan Seiden
    Alan Seiden says:

    @Aaron Hawley
    Your comment clarifies the best use of each approach. It seems that:

    __toString() is the more flexible option, because it can retrieve SQL for queries that you may or may not have run yet. The __toString() method doesn’t care if you actually run the query.

    Zend_Db_Profiler with getQuery(), on the other hand, retrieves SQL for the queries that you actually ran, including any differences created by pagination. It can only be used after the fact, but will be more accurate.

    Sound right?

    Reply
  4. Aaron Hawley
    Aaron Hawley says:

    Yes, usually while building a query you might need to print it and see how it’s coming. I should add, if a query fails to execute, I don’t believe there’s a way to retrieve it from the profiler, so I’ve had to use __toString() in that case as well.

    Reply

Trackbacks & Pingbacks

  1. […] original post here: Alan Seiden's Information Technology » Two techniques for … No […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.