Doctrine - How to print out the real sql, not just the prepared statement?

Question

Doctrine is not sending a "real SQL query" to the database server : it is actually using prepared statements, which means :

• Sending the statement, for it to be prepared (this is what is returned by $query->getSql())
• And, then, sending the parameters (returned by $query->getParameters())
• and executing the prepared statements

This means there is never a "real" SQL query on the PHP side — so, Doctrine cannot display it.

A working example:

$qb = $this->createQueryBuilder('a');
$query=$qb->getQuery();
// SHOW SQL: 
echo $query->getSQL(); 
// Show Parameters: 
echo $query->getParameters();

You can check the query executed by your app if you log all the queries in mysql:

http://dev.mysql.com/doc/refman/5.1/en/query-log.html

there will be more queries not only the one that you are looking for but you can grep for it.

but usually ->getSql(); works

Edit:

to view all the mysql queries I use

sudo vim /etc/mysql/my.cnf 

and add those 2 lines:

general_log = on
general_log_file = /tmp/mysql.log

and restart mysql

I have created a Doctrine2 Logger that does exactly this. It "hydrates" the parametrized sql query with the values using Doctrine 2 own data type conversors.

<?php


namespace Drsm\Doctrine\DBAL\Logging;
use Doctrine\DBAL\Logging\SQLLogger,
    Doctrine\DBAL\Types\Type,
    Doctrine\DBAL\Platforms\AbstractPlatform;
/**
 * A SQL logger that logs to the standard output and
 * subtitutes params to get a ready to execute SQL sentence

 * @author  [email protected]
 */
class EchoWriteSQLWithoutParamsLogger implements SQLLogger

{
    const QUERY_TYPE_SELECT="SELECT";
    const QUERY_TYPE_UPDATE="UPDATE";
    const QUERY_TYPE_INSERT="INSERT";
    const QUERY_TYPE_DELETE="DELETE";
    const QUERY_TYPE_CREATE="CREATE";
    const QUERY_TYPE_ALTER="ALTER";

    private $dbPlatform;
    private $loggedQueryTypes;
    public function __construct(AbstractPlatform $dbPlatform, array $loggedQueryTypes=array()){
        $this->dbPlatform=$dbPlatform;
        $this->loggedQueryTypes=$loggedQueryTypes;
    }
    /**
     * {@inheritdoc}
     */
    public function startQuery($sql, array $params = null, array $types = null)

    {
        if($this->isLoggable($sql)){
            if(!empty($params)){
                foreach ($params as $key=>$param) {
                    $type=Type::getType($types[$key]);
                    $value=$type->convertToDatabaseValue($param,$this->dbPlatform);
                    $sql = join(var_export($value, true), explode('?', $sql, 2));
                }

            }
            echo $sql . " ;".PHP_EOL;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function stopQuery()
    {

    }
    private function isLoggable($sql){
        if (empty($this->loggedQueryTypes)) return true;
        foreach($this->loggedQueryTypes as $validType){
            if (strpos($sql, $validType) === 0) return true;
        }
        return false;
    }
}

Usage Example:; The following peace of code will echo on standard output any INSERT,UPDATE,DELETE SQL sentences generated with $em Entity Manager,

/**@var  \Doctrine\ORM\EntityManager $em */
$em->getConnection()
                ->getConfiguration()
                ->setSQLLogger(
                    new EchoWriteSQLWithoutParamsLogger(
                        $em->getConnection()->getDatabasePlatform(),
                        array(
                            EchoWriteSQLWithoutParamsLogger::QUERY_TYPE_UPDATE,
                            EchoWriteSQLWithoutParamsLogger::QUERY_TYPE_INSERT,
                            EchoWriteSQLWithoutParamsLogger::QUERY_TYPE_DELETE
                        )
                    )
                );