This actually brings up an issue I just ran into today. The first problem I
found appears to be a bug in the PDO::MySQL adapter, in that certain queries
passed to it with parameter binding characters (?) will fail even if you are
not binding parameters to the statement. It fails saying that the parameters
were not passed to the binding. I have no idea why it is doing this, but I did
isolate it down to a single insert statement with two inserts combined, that
caused the problem. When I took out the second statement, it worked correctly.
This code will fail on PHP 5.2.6 and Zend Framework 1.8.3:
$sql = "insert into configuration (configuration_id, configuration_title,
configuration_key, configuration_value, configuration_description) values
('573','Server to use','MODULE_SHIPPING_UPS_SERVER','production','Use the test
or production UPS server?'), ('557','Internal
Comments','ACCOUNT_COMMENTS','true','Display internal (admin only) comments in
the customer\'s account')"; $db->query($sql);
It works correctly with the Mysqli adapter, just not the PDO adapter. It will
work if I remove the question mark after the UPS server text, so that appears
to be what is confusing it, even though I am not passing in any bound
parameters. Also, if I remove the send insert and just do a single insert, it
works correctly. I don't think this is a problem with ZF itself, as it appears
to be failing inside the PDO adapter.
I also tested the code by passing it directly to the Zend_Db_PDO adapter exec()
function and it did not fail, which is to be expected. But the exec() function
is only present for PDO adapters, not for any of the other adapters.
Anyway, I have two solutions to fix this. I can either use the Mysqli adapter
(which I am doing now), or I can work around it by directly calling the exec()
function of the PDO adapter. Which brings me to the point of my email - I think
there should be an exec() function at the highest level of the Zend_Db_Adapter
class structure, so it can be used on all databases. There are two reasons for
this:
1. On some databases like MySQL, using prepared statements when you simply
wish to execute a native query, means that the query cache will be bypassed.
Most sites are not yet using MySQL 5.1 or higher, so this is a real problem (it
is on our site).
2. Bypassing the prepared statements in either PDO adapter or the Mysqli
adapter will make the code run faster, since none of the overhead of preparing
the statement is actually necessary, and the query can be executed directly
Although the more I think about it, perhaps the correct solution is to change
the internal implementation of the Zend_Db_Adapter_Abstract::query() function
such that if there are no bound parameters passed in, that it will directly
execute the query and not do the prepared statement thing. Internally this
function ends up doing this to execute the actual query:
// prepare and execute the statement with profiling $stmt =
$this->prepare($sql); $stmt->execute($bind);
Which is a LOT of extra work for now reason, if the the bind array is empty,
since creating a prepared statement is NOT free, and if it is only used for a
single query and tossed away, it is huge waste of resources (not to mention it
bypasses the query cache on MySQL 5.0 and earlier). So I am thinking the code
should be something like this:
// prepare and execute the statement with profiling if ($bind) {
$stmt = $this->prepare($sql); $stmt->execute($bind); }
else { $this->exec($sql); }
Of course the exec() function does not exist in the Mysqli class, and does not
handle query profiling. It does exist in the PDO_MYSQL class though. Which is
why I think the exec() function should be added to the abstract adapter class
and implemented in all the drivers, and then this code would work. Then the
developer can choose to call exec() directly if needed, or call query() with no
parameters.
Either way, from a performance standpoint I think there should be a function in
the Zend_Db_Adapter class tree that will execute a SQL statement directly
without any parameter binding, for situations where this is needed.
Regards,
Kendall Bennett, CEO
A Main Hobbies
424 Otterson Drive, Suite 160
Chico, CA 95928
1-800-705-2215 (Toll-Free)
1-530-894-0797 (Int'l & Local)
1-530-894-9049 (Fax)
http://www.amainhobbies.com
________________________________
From: till <[email protected]>
Date: Sun, 14 Jun 2009 12:06:13 -0700
To: Matthew Ratzloff <[email protected]>
Cc: Tobias Gies <[email protected]>, Benjamin Eberlei
<[email protected]>, Zend Framework General <[email protected]>,
<[email protected]>
Subject: Re: [fw-general] A Zend_Db Adapter for ext/mysql (mysql_*)
Don't want to hijack this thread -- but we recently developed an
ext/mysqli-based adapter which doesn't use prepared statements. Does
anyone have any interest in the code -- I'd look into opensourcing it
then.
Till
On Sun, Jun 14, 2009 at 5:52 PM, Matthew
Ratzloff<[email protected]> wrote:
> Sure. I never said it should be Zend namespaced, you know. ;-)
> -Matt
> On Sat, Jun 13, 2009 at 4:02 PM, Tobias Gies <[email protected]>
> wrote:
>>
>> Hi,
>>
>> Matthew, I don't think this should be included in the Zend namespace.
>> Ben, you have done some excellent work on this adapter, I think it's
>> great, but in my opinion, we shouldn't include adapters for "outdated"
>> libraries in the framework. It might fit nicely into ZendX, but I'd
>> rather not have it included in the main framework.
>>
>> Best regards,
>> Tobias
>>
>> 2009/6/13 Matthew Ratzloff <[email protected]>:
>> > CLA? Proposal? Inclusion in the framework? :-)
>> >
>> > -Matt
>> >
>> > On Saturday, June 13, 2009, Benjamin Eberlei <[email protected]>
>> > wrote:
>> >> Hello everyone,
>> >>
>> >> I have a project that i slowly want to migrate to Zend Framework. This
>> >> project
>> >> uses ext/mysql all over the place and therefore requires an adapter of
>> >> this
>> >> type to keep the number of database connections small. I wrote this
>> >> adapter
>> >> using soley mysql_* functions, which emulates prepared statements and
>> >> works
>> >> exactly like any other Zend_Db adapter, even works with Zend_Db_Table.
>> >>
>> >> Its passing all unittests of the Zend_Db test suite so should be pretty
>> >> stable.
>> >>
>> >> If anyone needs this, i put it up on GitHub:
>> >> http://github.com/beberlei/Zend_Db-Adapter-for-ext-mysql/tree/master
>> >> --
>> >> Benjamin Eberlei
>> >> http://www.beberlei.de
>> >>
>> >
>
>
