Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Displaying the generated SQL from a Zend\Db\Sql object

DZone's Guide to

Displaying the generated SQL from a Zend\Db\Sql object

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

If you use Zend\Db\Sql to generate your SQL, then it's useful to find out what the generated SQL looks like.

Consider code like this:

public function fetchAllWithTitleSince($title, $since)
{
    $sql = new Sql($this->dbAdapter);
 
    $select = $sql->select();
    $select->from($this->tableName);
    $select->columns(array('id', 'title', 'url', 'date_updated'));
    $select->where->like('title', "%$title%");
    $select->where->greaterThanOrEqualTo('date_created', date('Y-m-d', strtotime($since)));
 
    $statement = $this->dbAdapter->createStatement();
    $select->prepareStatement($this->dbAdapter, $statement);
    return $statement->execute();
}

To find out what the generated SQL will look like, you can use the $select's getSqlString()method:

$select->getSqlString();

For me, this results in lots of warnings along the lines of:

Lots of notices

This is less than helpful, so to avoid the warnings, you need to supply the correct platform information to the method:

    $select->getSqlString($this->dbAdapter->getPlatform());

So we now get:

Expected SQL string

This is much better!





It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}