How to view db queries generated by TableGateway i

Question

I am brand new to ZF2 and am trying to use a tableGateway to manage and update entries in a database. I am able to select and update items without a problem, but when inserting I get an error. Since the tableGateway class creates the query on the fly, how can I see the query itself?

$this->tableGateway->insert($data);

An error occurred during execution; please try again later. Additional information: Zend\Db\Adapter\Exception\InvalidQueryException

File:

/[redacted]/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php:220

Message:

Statement could not be executed

Answer 1

For what I wanted to do, in Dev, which was to know exactly which queries were run associated to every step in the flow of my processes, this is what I did (using PDO with MySQL / MariaDB):

1. I went to the Statement class (vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php)

2. I went to the execute method and just before it is executed I put:

   ...
   error_log("DebugQ: ".print_r($this->getSql(),1).", ".print_r($this->getParameterContainer()->getNamedArray(),1));
   try {
       $this->resource->execute();
   }
   ...
   

I wanted a quick and disposable solution to have the queries logged in the error logs. I was able to have all the queries and their conditions.

Answer 2

In my case I just covered this in one in try catch: $e->__toString() was the key

try {
    this->tableGateway->insert($data);
} catch (\Exception $e) {
    \Zend\Debug\Debug::dump($e->__toString()); exit;
}

Answer 3

Very elegant way how to see db queries is to use zend-developer-tools.

Easiest way how to use it is to install module by adding it to composer.json file

..... "repositories": [ { "type": "composer", "url": "http://packages.zendframework.com/" } ], "require": { "php": ">=5.3.3", "zendframework/zend-developer-tools": "dev-master" }

see documentation

Answer 4

Just some notices to @zdenek-machek answer:

1) To profile database queries, BjyProfiler module should be installed too.

2) (skip if you use PDO) When I used BjyProfiler last time, there was an issue with mysqli connection (buffer_results option was not passed to ProfilingStatement class). Maybe it is fixed now, or I set it up in the wrong way, but my patch is to manually pass this parameter in BjyProfiler/src/BjyProfiler/Db/Adapter/ProfilingAdapter.php:

case 'Zend\Db\Adapter\Driver\Mysqli\Mysqli':
    $statementPrototype = new Driver\Mysqli\ProfilingStatement($this->options['buffer_results']);
    break;

3) ZendDeveloperTools displays count of queries, but doesn't list them. To list in the bottom of the page, I have modified view/zend-developer-tools/toolbar/toolbar.phtml in the following way:

<!-- END Zend Developer Toolbar -->
<?php
$queryProfiles = $this->getHelperPluginManager()->getServiceLocator()
    ->get('Zend\Db\Adapter\Adapter')->getProfiler()->getQueryProfiles();

echo '<ol>';
foreach($queryProfiles as $queryObj)
{
    $query = $queryObj->toArray();
    echo '<li>';
    echo '<b>' . ($query['elapsed']*1000) . '</b> ms<br/>';
    echo $query['sql'];
    if(count($query['parameters']))
    {
        echo '<br/><i>Parameters:</i> ';
        $list = array();
        foreach($query['parameters'] as $key => $value)
            $list[] = '?'. $this->escapeHtml($key)
                      ."='". $this->escapeHtml($value) ."'";
        echo implode(', ', $list);
    }
    echo '</li>';
}
echo '</ol>';