[FIXED] Can't tell if a Cake\ORM\Query was executed

Issue

I have a custom model method executing a query, and I need it to return either true or false depending on whether it was successful. There's no clear answer, so I'm trying to figure out how it works myself. Here's my code:

// build and execute the query
$query = $myTable->query();
$query->insert(array_keys(reset($data)));
$query->clause('values')->setValues($data);
$query->epilog('ON DUPLICATE KEY UPDATE `price`=VALUES(`price`)');
$statement = $query->execute();

Debugging the above looks like this:

'$statement' => object(Cake\Database\Statement\MysqlStatement) {
    [protected] _statement => object(PDOStatement) {
        queryString => 'INSERT INTO `table` (`id`, `value_id`, `price`) VALUES (...) ON DUPLICATE KEY UPDATE `price`=VALUES(`price`)'
    }
    [protected] _driver => object(Cake\Database\Driver\Mysql) {

        'connected' => true

    }
    [protected] _hasExecuted => false
    [protected] _bufferResults => true
},
'$query' => object(Cake\ORM\Query) {

    '(help)' => 'This is a Query object, to get the results execute or iterate it.',
    'sql' => 'INSERT INTO `table` (`id`, `value_id`, `price`) VALUES (...) ON DUPLICATE KEY UPDATE `price`=VALUES(`price`)',
    'params' => [
        // ...
    ],
    'defaultTypes' => [
        // ...
    ],
    'decorators' => (int) 0,
    'executed' => true,
    'hydrate' => true,
    'buffered' => true,
    'formatters' => (int) 0,
    'mapReducers' => (int) 0,
    'contain' => [],
    'matching' => [],
    'extraOptions' => [],
    'repository' => object(App\Model\Table\SomeTable) {
        // ...
    }

}

Initial attempts:

• getting $statement->errorCode is null, not a 0
• getting $statement->_hasExecuted is null
• getting $query->executed throws Notice Error: Undefined property: Cake\ORM\Query::$executed (although it seems like it's there)
• what's interesting, in the debug output above, $statement->_hasExecuted is false and $query->executed is true

My thought process is this: I call $query->execute(), which inside of it calls $this->_connection->run($this), which creates and executes a \Cake\Database\StatementInterface. In my case it's a MysqlStatement extended from a PDOStatement. Inside the execute() call, it explicitly sets _hasExecuted to true; then it calls $this->_statement->execute(), which, being an implementation of that same Cake\Database\StatementInterface::execute(), apparently runs that exact execute method, including the _hasExecuted=true portion.

As far as I understand, that is the statement that is finally returned and shown in the debug above. And it has _hasExecuted set to false. So was it not executed?

This confuses me and I need your help. How do I tell for sure that the query was executed successfully? I can't look for lines affected count, because it may be 0. I asked on CakePHP Support Slack and was advised to check the debug panel or re-query the table later to confirm the entries were created, but I need this for an automated code test, so none of those would work.

Solution

The MySqlStatement::excute() method doesn't change the _hasExecuted flag, and it also doesn't call the parent method. This could be an oversight, it could also be by design, not changing the flag for example allows to iterate the statement more than once (which involves issuing the query again), you could open an issue over at GitHub for clarification, or try to summon the ORM Guru aka Lorenzo via Slack.

The Query::$executed property really doesn't exist, what you're looking at there is custom debug information that doesn't reflect the object structure, the executed key is based on the internal _iterator property.

If executing the query doesn't trigger an exception, then it has run successfully, ie without errors on the DBMS side, unless of course you've changed the connection's error mode to silent or warning mode, that's when you could/would use errorCode(), which otherwise won't be available as the exception would prevent the statement to be available in the first place.

Whether no DBMS error means that your query did exactly what it was intended to do, might be a different question, and depends on what exactly the query does, and is something that you need to figure out on a case by case basis. Your only options for actual verification are looking at the number of affected rows, and/or querying the database afterwards.

Answered By - ndm