Non-prepared statements are also important for MySQL because the MySQL Query Cache feature cannot cache prepared statements.

Yeah, just read that please : http://netevil.org/node.php/…

I don't know if that bug has already been reported, but i met it on RC1.0 under a linux server. The query was

using PDO under Mysql(4.1) and Linux throws an exception 'SQLSTATE[HY000]: General error: 2030 This command is not supported in
the prepared statement protocol yet' in {file}

The only way to fix it was to modify the Zend/Db/Adapter/PdoAbstract.php by adding 

There is no way to change that behavior other than modify it manually in the framework source. And as said here : there is no was to prevent adapter from using prepared statements...

Related to Bill's comment: http://fr3.php.net/manual/en/…

Please categorize/fix as needed.

Of course I only found this issue after opening another one. Can someone mark ZF-4767 as duplicate. Apologies for the extra work.

Also, what's the status of this issue? I'd like to see it fixed soon. :-)

Adding the link, to stress the importance of the issue: http://nabble.com/Zend_DB-performance-issue-td1423…

I will evaluate this with the next 2 weeks.

This is still a blocker for me.

Below is some code which I started using in some of my projects to use unprepared queries and to utilize the query cache to a certain extend. The {{__call()}} is a way to debug this and to find methods I didn't cover yet.

This is not final but a work in progress and an idea. I could extend it, etc. if I get some feedback here.

<?php
/**
 * @see Zend_Db_Statement
 */
require_once 'Zend/Db/Statement.php';

class Zend_Db_Statement_Mysqli_Unprepared extends Zend_Db_Statement
{
    protected $_fetchMode, $_result;

    public function __construct($result, $fetchMode)
    {
        $this->_result    = $result;
        $this->_fetchMode = $fetchMode;
    }

    public function fetchAll($fetchMode = null)
    {
        $data = array();
        while ($row = $this->fetch($fetchMode)) {
            array_push($data, $row);
        }
        return $data;
    }

    public function fetchColumn($col = 0)
    {
        $row = $this->fetch(Zend_Db::FETCH_NUM);
        if (isset($row[$col])) {
            return $row[$col];
        }
        return false; // FIXME
    }

    public function fetch($fetchMode = null)
    {
        if ($fetchMode === null) {
            $fetchMode = $this->_fetchMode;
        }

        switch ($fetchMode) {
            default:
            case Zend_Db::FETCH_BOTH:
                $fetchMode = MYSQLI_BOTH;
                break;

            case Zend_Db::FETCH_ASSOC:
                $fetchMode = MYSQLI_ASSOC;
                break;

            case Zend_Db::FETCH_NUM:
                $fetchMode = MYSQLI_NUM;
                break;
        }

        return $this->_result->fetch_array($fetchMode);
    }

    public function __call($method, $args)
    {
        die("$method");
    }
}

Here is a patch to Zend_Db_Adapter_Abstract:

Index: library/Zend/Db/Adapter/Abstract.php
===================================================================
--- library/Zend/Db/Adapter/Abstract.php    (revision 12330)
+++ library/Zend/Db/Adapter/Abstract.php    (working copy)
@@ -132,6 +132,12 @@
     );
 
     /**
+     * @var boolean Defines whether the adapter uses prepared statemtents.
+     *              The default is 'true' to avoid a BC-break.
+     */
+    protected $_usePreparedStatements = true;
+
+    /**
      * Constructor.
      *
      * $config is an array of key/value pairs or an instance of Zend_Config
@@ -406,7 +412,7 @@
      * @param  mixed  $sql  The SQL statement with placeholders.
      *                      May be a string or Zend_Db_Select.
      * @param  mixed  $bind An array of data to bind to the placeholders.
-     * @return Zend_Db_Statement_Interface
+     * @return mixed  Zend_Db_Statement_Interface or query result.
      */
     public function query($sql, $bind = array())
     {
@@ -425,13 +431,26 @@
             $bind = array($bind);
         }
 
-        // prepare and execute the statement with profiling
-        $stmt = $this->prepare($sql);
-        $stmt->execute($bind);
+        if ($this->_usePreparedStatements === true) {
+            // prepare and execute the statement with profiling
+            $stmt = $this->prepare($sql);
+            $stmt->execute($bind);
 
-        // return the results embedded in the prepared statement object
-        $stmt->setFetchMode($this->_fetchMode);
-        return $stmt;
+            // return the results embedded in the prepared statement object
+            $stmt->setFetchMode($this->_fetchMode);
+            return $stmt;
+        }
+
+        if (!empty($bind)) {
+            throw new Zend_Db_Exception('You can\'t disable prepared statements, yet supply parameters to bind.');
+        }
+
+        $fetchClass = get_class($this) . '_Fetch';
+        Zend_Loader::loadClass($fetchClass);
+        return new $fetchClass(
+            $this->getConnection()->query($sql),
+            $this->_fetchMode
+        );
     }
 
     /**
@@ -1022,6 +1041,24 @@
     }
 
     /**
+     * Enable or disable prepared statements here.
+     *
+     * @param bool $use 'true' or 'false'.
+     *
+     * @return self
+     * @uses   self::$_usePreparedStatements
+     */
+    public function usePrepared($use)
+    {
+        if ($use === true) {
+            $this->_usePreparedStatements = $use;
+            return $this;
+        }
+        $this->_usePreparedStatements = false;
+        return $this;
+    }
+
+    /**
      * Abstract Methods
      */

And here is some code demonstrating usage:

<?php
set_include_path('.:' . dirname(__FILE__) . '/trunk/library');

require_once 'Zend/Loader.php';
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

try {
    $db = Zend_Db::factory(
        'Mysqli',
        array(
            'username' => 'root',
            'password' => '',
            'host'     => 'localhost',
            'dbname'   => 'mysql'
        )
    );
    $db->usePrepared(true);

    $select = $db->select();
    $select->from('user', array('Host', 'User'));

    var_dump($db->fetchAssoc($select));


    $db->usePrepared(false);

    $select->reset();
    $select = $db->select();
    $select->from('user', array('Host', 'User'));

    var_dump($db->fetchAssoc($select));

} catch (Exception $e) {
    die($e->getMessage());
}

I briefly ran tests and it didn't break anything, but no guarantees. ;-) I also cloned the tests for the Mysqli driver and ran them with {{usePrepared()}} and it worked. This is just an idea.

The {{usePrepared()}} call is more or less an idea to change the behaviour on run time. I also envision a config option which you can pass in with a {{Zend_Config}} object or an array.

I think by default this should be disabled but that would break BC so I guess I could live with it the other way around too. Haven't made up my mind up though, I just needed to get rid off prepared queries.

The fix looks good, by default i would enable the preparing of statements and allow to pass a config value to disable it.

Why is there the requirement that non-preparable queries must go through query?

Why not add a method called execute() ? OR- why not simply use the Statement? or the getConnection()?

I agree that this should be possible to do, but don't necessary agree with the API implementation outlined in the patch above

I attached a patch for this issue. It does only contain a fix for PDO adapters yet, other adapters will fail because there is an abstract function missing.

This issue is specifically mentioned in the Zend\Db RFC for ZFv2. However, is this something which should be fixed in ZFv1 as well?

This won't be fixed in ZFv1