Re: pdo bindParam() with named parameters

Dan Scott wrote:

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.)

No the point is that I am likely to store the names of my fields inside an array maybe like so: // [name] = type fields['foo'] = PARAM_TYPE_LOB; I then do something like implode(', ', array_keys($fields)) to get the list of fields in an insert statement. Anyways the point is that nowhere in the rest of the code will that colon be more than a huge burden. (like if you store your schema in xml, build an ORM etc).

    * 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 :)

Give us a non ODBC client API and we can talk :-)

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

Do you really think any of the situations that require users to not use a colon are going to be relevant than a very small number of people? Also having another parameter around should make it just as clear, without letting people fall into an obvious trap and creating the above mentioned problems.

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.

Then make it the fifth. 1) Its fairly unlikely that people will reference parameters by number when they are using named parameters. 2) the bulk of the PHP world doesnt do stored procedures 3) the theoretical performance gains are likely to actually be a performance loss in 99% of the real world uses, let alone a hassle for most and stumbling block for new users

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.

For 99% of the users the colon is nothing more than a hurdle. Therefore it should probably be called "academic clean".

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.

Or you dont provide everything through the quick API. Again this wouldnt affect the bulk of the userbase anyways.

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.

Well it always seemed more obvious to me to specify the types and anything else that does not change during each iteration in the prepare call and then use only the name in the bindParam() along with the value.

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.)

Well here is the key point. You assume its all about typing. I am taking things from a perspective where you centrally manage your schema and dont type in things by hand in your real app. This is where things are certainly moving. So the main thing is that developers will first hand type things for their initial tests and then build their queries (especially those boring INSERT and UPDATE statements which fit the prepare/execute idea most perfectly) using fairly simple query builders. Finally my proposal doesnt really make things all that much harder to those who handtype things and reduced the wtf-factor for newbies. regards, Lukas