Zend PDO error - how can I debug it?

Question

I have a weird problem using Zend Framework and Mysql. I generated a query:

SELECT events.idUser, szForename, szLastname, readers.szName, idZoneFrom, events.dtTime FROM events, users, readers WHERE events.idUser = users.idUser AND events.idReader = readers.idReader AND dtTime >= '2010:02:15 0:00:00' AND dtTime < '2010:02:16 0:00:00' ORDER BY dtTime

The query works ok if I run it in some console like pma, navicat or shell client but when I try to run it using a model that extends Zend_Db_Table by commend

$arResult = $this->getDefaultAdapter()->query($szQuery)->fetchAll();

it goes with 1064 error:

enter code here

An error occurred
Application error
Exception information:

Message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Stack trace:

#0 C:\xampp\htdocs\projekty\doors2\library\Zend\Db\Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\xampp\htdocs\projekty\doors2\library\Zend\Db\Adapter\Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 C:\xampp\htdocs\projekty\doors2\library\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT zoneName...', Array)
#3 C:\xampp\htdocs\projekty\doors2\application\models\Zones.php(24): Zend_Db_Adapter_Pdo_Abstract->query('SELECT zoneName...')
#4 C:\xampp\htdocs\projekty\doors2\application\models\Events.php(87): Application_Model_Zones->getZoneInfo(NULL)
#5 C:\xampp\htdocs\projekty\doors2\application\controllers\IndexController.php(52): Application_Model_Events->getEventsList(NULL, '02/15/2010')
#6 C:\xampp\htdocs\projekty\doors2\library\Zend\Controller\Action.php(513): IndexController->eventsAction()
#7 C:\xampp\htdocs\projekty\doors2\library\Zend\Controller\Dispatcher\Standard.php(295): Zend_Controller_Action->dispatch('eventsAction')
#8 C:\xampp\htdocs\projekty\doors2\library\Zend\Controller\Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#9 C:\xampp\htdocs\projekty\doors2\library\Zend\Application\Bootstrap\Bootstrap.php(97): Zend_Controller_Front->dispatch()
#10 C:\xampp\htdocs\projekty\doors2\library\Zend\Application.php(366): Zend_Application_Bootstrap_Bootstrap->run()
#11 C:\xampp\htdocs\projekty\doors2\public\index.php(26): Zend_Application->run()
#12 {main}  

Request Parameters:

array (
  'controller' => 'index',
  'action' => 'events',
  'module' => 'default',
  'idUser' => '0',
  'dt' => '02/15/2010',
)

How can I debug it to find the problem?? Or maybe you know what could I have done wrong?

ps. I use the same db user both for php and query testing...

Pls post the SQL query started by `SELECT zoneName...`, seem that query is the cause of error, not the posted query — Bang Dao, Aug 14 '10 at 15:21
Yup, I just found an error in a loop operating on the result set. Just a silly mistake in passed parameter name gave me null instead of id. Anyway, thanks for help. :) — Tomasz Kapłoński, Aug 14 '10 at 16:19

score 3 · Answer 1 · answered Oct 19 '10 at 11:49

Your SQL statement have reserved words, as I can see. You should try to build your conection so it can automatically escape identifiers or you should do it your self, manually. For the first option, try this:

  $options = array(Zend_Db::AUTO_QUOTE_IDENTIFIERS => false);
  $params = array(
      'host'           => 'host',
      'username'       => 'user',
      'password'       => 'secret',
      'dbname'         => 'db',
      'options'        => $options
  );
  $db = Zend_Db::factory('Pdo_Mysql', $params);

Damn... I've searched a lot for this syntax... Zend documentation sucks cuz of the excess of info. Thanks a lot. Was in need to start an adapter to log don't mess with defaultAdapter commits/rollbacks. Thanks again! — Gilkan Solizaris, Mar 05 '21 at 13:28

score 0 · Answer 2 · answered Nov 03 '10 at 14:57

If you break your query into multiple lines you may get a more useful line number for where the syntax error is. This is how I format my queries. It makes them much easier to read, maintain and debug.

SELECT
    events.idUser,
    szForename,
    szLastname,
    readers.szName,
    idZoneFrom,
    events.dtTime
FROM events,
    users,
    readers
WHERE events.idUser = users.idUser
    AND events.idReader = readers.idReader
    AND dtTime >= '2010:02:15 0:00:00'
    AND dtTime < '2010:02:16 0:00:00'
ORDER BY dtTime

score 0 · Answer 3 · answered Nov 11 '10 at 16:08

Also, as for permissions, mysql uses a user+host based permission system. So a certain user may have access to a database resource from localhost, but not from other systems.

Check http://dev.mysql.com/doc/refman/5.0/en/grant.html for information on how to alter mysql user permissions.

score 0 · Answer 4 · answered Dec 14 '10 at 11:57

If you are using zend, you should be at least preparing any values that are passed in. If the statement works in the command line then there should not be a problem with reserved words (unless for some reason the versions of mysql are distinct, of course).

Zend PDO error - how can I debug it?

4 Answers4