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

Question

We're using Doctrine, a PHP ORM. I am creating a query like this:

$q = Doctrine_Query::create()->select('id')->from('MyTable');

and then in the function I'm adding in various where clauses and things as appropriate, like this

$q->where('normalisedname = ? OR name = ?', array($string, $originalString));

Later on, before execute()-ing that query object, I want to print out the raw SQL in order to examine it, and do this:

$q->getSQLQuery();

However that only prints out the prepared statement, not the full query. I want to see what it is sending to the MySQL, but instead it is printing out a prepared statement, including ?'s. Is there some way to see the 'full' query?

Answer 1

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

Answer 2

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  [e-mail]
 */
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
                        )
                    )
                );

Answer 3

There is no other real query, this is how prepared statements work. The values are bound in the database server, not in the application layer.

See my answer to this question: In PHP with PDO, how to check the final SQL parametrized query?

(Repeated here for convenience:)

Using prepared statements with parametrised values is not simply another way to dynamically create a string of SQL. You create a prepared statement at the database, and then send the parameter values alone.

So what is probably sent to the database will be a PREPARE ..., then SET ... and finally EXECUTE ....

You won't be able to get some SQL string like SELECT * FROM ..., even if it would produce equivalent results, because no such query was ever actually sent to the database.

Answer 4

Solution:1
====================================================================================

function showQuery($query)
{
    return sprintf(str_replace('?', '%s', $query->getSql()), $query->getParams());
}

// call function  
echo showQuery($doctrineQuery);

Solution:2
====================================================================================

function showQuery($query)
{
    // define vars              
    $output    = NULL;
    $out_query = $query->getSql();
    $out_param = $query->getParams();

    // replace params
   for($i=0; $i<strlen($out_query); $i++) {
       $output .= ( strpos($out_query[$i], '?') !== FALSE ) ? "'" .str_replace('?', array_shift($out_param), $out_query[$i]). "'" : $out_query[$i];
   }

   // output
   return sprintf("%s", $output);
}

// call function  
echo showQuery($doctrineQueryObject);

Answer 5

getSqlQuery() does technically show the whole SQL command, but it's a lot more useful when you can see the parameters as well.

echo $q->getSqlQuery();
foreach ($q->getFlattenedParams() as $index => $param)
  echo "$index => $param";

To make this pattern more reusable, there's a nice approach described in the comments at Raw SQL from Doctrine Query Object.

Answer 6

Maybe it can be useful for someone:

// Printing the SQL with real values
$vals = $query->getFlattenedParams();
foreach(explode('?', $query->getSqlQuery()) as $i => $part) {
    $sql = (isset($sql) ? $sql : null) . $part;
    if (isset($vals[$i])) $sql .= $vals[$i];
}

echo $sql;