Aura.Sql and binding methods

[Paul M. Jones]

Hi everybody,

tl;dr: New Aura.Sql branch "nobind" removes binding methods from ExtendedPdo and replaces them with a perform() method. It's a BC break on the "Google beta" of Aura.Sql. This is your chance to comment before a merge.

* * *

Rasmus Schultz, who contributed the backport of Aura.Sql_Query to PHP 5.3 along with some other improvements, contacted me about Aura.Sql ExtendedPdo not working as he expected it should. While I disagreed with some of his solution ideas, I did understand the source of his confusion. Basically, allowing ExtendedPdo to carry bound values along with it for "the next query" seemed out of place to him, and on further consideration I agree with his assessment there.

One of the ExtendedPdo goals is to allow a query-and-bind in a single call, notably through the fetch*() methods, but also (originally) via query() and exec() to support things like INSERT and UPDATE with bound values. It seemed at the time the only way to do that was to carry bound values on the ExtendedPdo instance, thus the binding methods on the class.

However, after talking with Rasmus Schultz and playing around a bit with the code, I realized we could have a new method called perform() in addition to the PDO-native query() and exec(). The new perform() method takes a $statement along with $values to bind, and does the binding/preparation/execution for us. This allows us to revert to the native PDO query() and exec() behaviors without automatic binding, but also allows the query-and-bind behavior I'd like to see (via the perform() method).

You can see this new work at <https://github.com/auraphp/Aura.Sql/tree/nobind>.

Those are the "pros". The "cons" are that if we merge this, it will be a BC break. There are no more bind-related methods on ExtendedPdo (bindValue(), bindValues(), and getBindValues() are gone). Previously you could bind values in advance and then call query(), exec(), or prepare() to have those values bound for you, but no longer: you have to use perform() with a $statement and the $values you want to bind in order to get that now.

A BC break in "Google beta" release should not be a big deal, but I know a lot of you guys are using Aura.Sql via the develop-2 branch, and I don't want to spring a change on you without warning. In theory, what you would need to do to convert over to the new behavior is:

- instead of calling bindValues() etc. and query(), keep your bind $values in an array of your own, then call perform($statement, $values) to get back a PDOStatement

- instead of calling bindValues() etc. and exec(), keep your bind $values in an array of your own, thencall perform($statement, $values) to get back a PDOStatement, and check the returned $sth->rowCount to get the count of affected rows

- instead of calling bindValues() etc. and prepare(), call prepareWithValues($statement, $values) to get back a PDO statement; this will bind the values and do array-value replacement. It is probably not suitable for repeated use

- calls to fetch*() are unchanged

Comments? Questions? Criticism?


p.s. Rasmus, if you're reading, no "aggregation" comments please. ;-)



--
Paul M. Jones
pmjo...@gmail.com
http://paul-m-jones.com

Modernizing Legacy Applications in PHP
http://mlaphp.com

[Paul M. Jones]

On Feb 21, 2014, at 11:47 AM, Paul M. Jones <pmjo...@gmail.com> wrote:

> - instead of calling bindValues() etc. and exec(), keep your bind $values in an array of your own, thencall perform($statement, $values) to get back a PDOStatement, and check the returned $sth->rowCount to get the count of affected rows

That kind of sucked, so I just added a new fetchAffected() method that returns the number of affected rows. This means you can use fetchAffected() instead of exec() for "execute the statement with bound values and return the count of affected rows".

[Paul M. Jones]

It's been what, three weeks? Last chance to comment on this:

> On Feb 21, 2014, at 11:47 AM, Paul M. Jones <pmjo...@gmail.com> wrote:
>

> tl;dr: New Aura.Sql branch "nobind" removes binding methods from ExtendedPdo and replaces them with a perform() method. It's a BC break on the "Google beta" of Aura.Sql. This is your chance to comment before a merge.

Unless I hear Otherwise I'm going to merge it. :-)