Displaying the generated SQL from a ZendDbSql object
If you use ZendDbSql 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:
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:
This is much better!
Is realy that hard in zf2?
Im working with zf1 and i just do:
echo $select;
I'm using zend2.2 and using the statement->getSql() command to view the generated sql, but find that it is not printing out the fully generated sql.
I do the following:
$sql = "SELECT * FROM mytable where code = :code and name = ':name'"
$params = array(
':name'=>'Name',
':code'=>1234,
);
$statement = $dbAdapter->createStatement($sql, $params);
$statement->prepare();
echo $statement->getSql();
// prints out: SELECT * FROM mytable where code = :code and name = ':name'
My reasoning is the following:
> I like the freedom of writing a sql statement – rather than building a sql statement like you have done above with the select() where() constructs
> I want the protection of sql injection + improved performance by using prepared statements
> I want to have minimal abstraction layers in my php-sql development & would like a direct way to view my generated sql (not logger plugin)
Its great that ->getSql() exists, but not great that it doesn't print out the generated code. Then I would straight-out include my parameters in the sql … but then it seems I will lose out on the performance / protection of prepared statements.
I am fairly new to Zend & still trying to find the right way forward in my php-sql logic that I don't find too abstracted.
This is now effected by http://framework.zend.com/security/advisory/ZF2013-03. If you define the platform as described above you will still get a notice because it is attempting to use the platforms quoteValue() method. There is not currently a way to set it so that the getSqlString() method will use the quoteTrustedValue() method to build the string and return it. Because of this the only way I could find to get the raw sql string was to suppress the notice as follows:
@$select->getSqlString($this->dbAdapter->getPlatform());