Re: [fw-db] Zend_Db enhancements

On Jul 1, 2010, at 5:42 AM, FeIn wrote:

> I have a couple of questions. First question is related to having
> the possibility to provide arrays in $bind parameter of
> Zend_Db_Adapter_Abstract::query.

This is a lot harder than it may seem.

First, how would you handle the case of multiple executions of the
same prepared query, with different parameter values?

$stmt = $adapter->prepare("SELECT FROM x WHERE y = ? AND z IN (?)");
$stmt->execute(array(1, array(1,2,3)));
$stmt->execute(array(2, array(1,2,3,4,5,6)));

In your plan, your decorator must rewrite the parameter placeholders
prior to prepare(), but you don't know how many elements in the array
of values until execute-time. You would have to discard the prepared
query handle and re-prepare a modified query during every execution,
which would spoil the performance benefit of re-using the prepared
statement.

Second, how would you tell the difference between a valid use of array
binding and an invalid use?

$stmt = $adapter->prepare("SELECT FROM x WHERE y = ? AND z IN (?)");
$stmt->execute(array(array(1,2,3), 1)); // is this valid? no.

You would have to write a parser that knew that it's okay to bind an
array to the placeholder for IN(), but the placeholder for = only
accepts a single value. ...Or does it? SQL allows you to compare
tuples:

$stmt = $adapter->prepare("SELECT FROM x WHERE (y, z) = (?)");
$stmt->execute(array(array(1,1))); // should this be supported? I
don't think so.

Finally, how do you propose to tell the difference between parameter
placeholders and string literals that happen to look like parameter
placeholders?

$stmt = $adapter->prepare("SELECT FROM x WHERE y = ? AND symbol =
'?'");
$stmt->execute(array(1, '$')); // does this need two params? no.

$stmt = $adapter->prepare("SELECT FROM x WHERE y = ? AND tag =
':foo'");
$stmt->execute(array(1, ':foo'=>'performance')); // does this need
two params? no.

The solution I use for binding arrays to IN() predicates is to prepare
as many placeholders as there are elements in my array. It just takes
a couple of lines of PHP code to produce the syntax:

$zlist = array(1,2,3);
$placeholders = join(",", array_fill(0, count($zlist), "?"));
$sql = "SELECT * FROM x WHERE y = ? AND z IN ({$placeholders})";
$stmt = $adapter->prepare($sql);
$stmt->execute(array_merge(array(1), $zlist));

But I do this on a case-by-case basis so I'm sure it's working as I
intend. That helps me avoid all the problems from corner cases that I
described above.

Regards,
Bill Karwin