Re: pdo bindParam() with named parameters

On Thu, 10 Feb 2005 11:02:09 +0100, Lukas Smith <lsmith@php.net> wrote: > Hi, > > I just wanted to put in a more formal request to reconsider the > requirement to prefix named parameters with a colon. > > As in > > $stmt->bindParam(':foo', $bar); > > Here are Wez's reasons for things being the way they are: > > * "1" smells like 1, which means bind by position. This can cause > ambiguity if you decide to bind some parameters by name and others by > position (and you hav e decided to name your parameters after numbers). So if anyone is wondering, bind-by-position is just using question marks as placeholders in the SQL statement. You don't end up typing any names at all, so if the argument against colons is based on a "Why should I have to type one more character?" you might as well go all the way and type far fewer characters... $stmt = $dbh->preprare("INSERT INTO bank(name, amount) VALUES (?, ?)"); $stmt->execute(array('chequing', 145.93)); or $stmt = $dbh->preprare("INSERT INTO bank(name, amount) VALUES (?, ?)"); $stmt->bindParam(1, 'chequing'); $stmt->bindParam(2, 145.93); $stmt->execute(); (This is supported in PDO today.) > * For drivers that natively support named parameters (sqlite and > OCI), those keys are passed directly to the driver > * Some drivers (MS-SQL and ODBC/DB-2) will support parameters that That's 'DB2' please :) > have an @ prefix instead of a colon when invoking stored procedures. If > PDO assumed that ":" was always the prefix, things would get very ugly > in the code very quickly > * and lastly, performance. Why should we force PDO to have to > allocate a new copy of the string with the prefix when the programmer > can supply it as it is supposed to be sent? > > The reality will be that in most cases users will not care about the > first 3 points. The performance impact will therefore likely be slightly > against the current solution as now the colon needs to be appended in > PHP userland. I believe that the current requirement for the named parameter in bindParam() to match the named parameter in the SQL statement will help users--the colon acts as a signifier and says 'Hey, this isn't any old string here.". I realize that technically PHP / PDO could handle the 'magic' of prepending the colon at bind time, but I'm not sure that this magic would actually help the average programmer. Right now you mirror the arguments inside the SQL statement and the bindParam() / execute() calls; dropping the colon in the bindParam() / execute() calls makes it asymmetrical. Does anyone advocate optionally dropping the $ from variable names in PHP method arguments if the argument must always be a PHP variable? > Therefore I propose to handle the original need for the colon through a > 3rd optional parameter that users can set if they want to get the above > mentioned additional functionality, while keeping things simple and fast > for the vast majority of users. 3rd optional parameter, or fifth parameter (which happens to be optional)? The current signature of bindParam() is: bool PDOStatement::bindParam(mixed $parameter_name, mixed &$variable [, int $data_type [, int $length]]) (http://livedocs.php.mirrors.powertrip.co.za/en/function.pdostatement-bindparam.html) ... so if you make the :colon-oscopy parameter the 3rd parameter (first optional one), then it's forced on to everyone who wants to use a specific data type. If you make it the fifth parameter, then to use this parameter you will always have to specify data type and length. Not nice either way. Right now the signature for PDOStatement::bindParam() is remarkably clean -- it's a natural progression of the level of type specificity you want for a specific parameter. And that wouldn't affect the PDOStatement::execute() case where you pass an array of input parameters directory to the statement, e.g.: $stmt = $dbh->preprare("INSERT INTO bank(name, amount) VALUES (:name, :amount)"); $stmt->execute(array(':name' => 'chequing', ':amount' => 145.93)); I suppose you could add a second optional parameters to PDOStatement::execute(), but then the API is starting to get cluttered with a number of optional parameters for the sake of a single colon per parameter. With something like a data access abstraction, keeping the API lean, mean, and clean is a good goal to shoot for. So yet another option would be to set a PDOStatement-level or PDO-level attribute that affects all parameters. I don't know what that would do to the codepath, but the one fear I would have is that trying to combine code that has made different assumptions about their preferred colonicity becomes more difficult -- less so for the PDOStatement-level attribute, I would imagine. Personally, I'm in favour of keeping this aspect of the API as it is: use bind-by-position if you want to save your fingers from typing, and use bind-by-name (where the name of the parameter in bindParam() matches the parameter in the SQL statement exactly) when you want a more verbose method. I'm not convinced there's a need for a slightly less verbose option. (And I don't want to have to document all of the permutations that introduces... but that's just me whining and whinging.) Dan