Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

What is the difference between "db:beforeQuery" and "db:afterQuery" ?

Hi, I want to write sql-logs. In my compay, only DBAs have the privilege of the db servers. And you know, Phalcon uses PDO for db-layer. PDO accepts "?" and ":value" for later-binding. But we want to get the statements after bindParams().

Here I test with db:beforeQuery and db:afterQuery:

try {
    // event manager                                                                                 
    $evtManager = new \Phalcon\Events\Manager();
    $logger = new \Phalcon\Logger\Adapter\File($configs->database->$nodeName->logger . date("Ymd"));
    $evtManager->attach('db', function($event, $connection) use ($logger) {
        if ($event->getType() == 'afterQuery') {
            $logger->log($connection->getRealSQLStatement(), \Phalcon\Logger::INFO);
        }
    });
    $connection->setEventsManager($evtManager);
} catch(\Exception $e) {
    error_log("Db.php: event attach error");
}

But they are no differences.

beforeQuery:

UPDATE `city` SET `cn` = ?, `cpy` = ?, `prn` = ?, `prpy` = ?, `pren` = ?, `lid` = ?, `n` = ?, `c` = ?, `pn` = ?, `pc` = ?, `rn` = ?, `rc` = ?, `en` = ?, `py` = ?, `py2` = ?, `a` = ?, `apy` = ?, `apy2` = ?, `aen` = ?, `ren` = ?, `rpy` = ?, `rpy2` = ?, `ra` = ?, `rapy` = ?, `raen` = ?, `cen` = ?, `is` = ? WHERE `id` = ?

afterQuery:

UPDATE `city` SET `cn` = ?, `cpy` = ?, `prn` = ?, `prpy` = ?, `pren` = ?, `lid` = ?, `n` = ?, `c` = ?, `pn` = ?, `pc` = ?, `rn` = ?, `rc` = ?, `en` = ?, `py` = ?, `py2` = ?, `a` = ?, `apy` = ?, `apy2` = ?, `aen` = ?, `ren` = ?, `rpy` = ?, `rpy2` = ?, `ra` = ?, `rapy` = ?, `raen` = ?, `cen` = ?, `is` = ? WHERE `id` = ?

What is the problem? Something wrong with me?



83.4k
Accepted
answer

Hi, beforeQuery is executed before send the SQL statement to the database server, and afterQuery is executed after the statement is sent to the server. So you can use both events to benchmark if a query is slow or it has an acceptable speed.

afterQuery doesn't transform the query replacing the placeholders.

try {

    // event manager 
    $evtManager = new \Phalcon\Events\Manager();

    $logger = new \Phalcon\Logger\Adapter\File($configs->database->$nodeName->logger . date("Ymd"));

    $evtManager->attach('db', function($event, $connection) use ($logger) {
        if ($event->getType() == 'afterQuery') {
            $variables = $connection->getSqlVariables();
            if (count($variables)) {
                $logger->log($connection->getSQLStatement() . ' ' . join(', ', $variables), \Phalcon\Logger::INFO);
            } else {
                $logger->log($connection->getSQLStatement(), \Phalcon\Logger::INFO);
            }
        }
    });

    $connection->setEventsManager($evtManager);
} catch(\Exception $e) {
    error_log("Db.php: event attach error");
}


4.6k

@Phalcon thank u very much.