Updating lots of database fields in a single row

[Sam Kington]

On 23 Jan 2013, at 11:09, Abigail <abigail at abigail.be> wrote:

> I'd say that dogmas are poor practise.
> 
> 
> Good practise is actually *knowing* when you should use placeholders,
> and when there's no need.
> 
> Because someone who knows can actually be trusted to do variable
> interpolation in places where placeholders cannot be used. Unlike
> someone who goes "variable interpolation is baaaaaaad".

If you work in an environment where some of the developers aren't rock stars, a general rule of "doesn't use placeholders in database queries == code smell" may well be useful. My inclination would be to say that using placeholders in database queries is at a similar level to mandating use strict in all but one-liner or terrifyingly ancient code.

More interesting, though: aren't bind variables also more convenient in many circumstances?

I mean, sure, this is safe:

if ($status eq 'foo') {
    $dbh->do("UPDATE table SET status='$status' WHERE id=$id");
}

But I think I'd often find myself using them even if I didn't need to, just because of the printf-type convenience.

For the same reason that I'll shift to printf or sprintf for complex log messages:

warning(sprintf('Found grommet %s instead of expected grommet %s: %s was %s (%.2f)',
    $grommet, $expected_grommet, $category_thing, $category_value, $some_other_diagnostic
));

rather than

warning("Found grommet $grommet instead of expected grommet $expected_grommet"
    ." $category_thing was $category_value (" . printable_value($some_other_diagnostic) . ")");

I think the first version is more readable than the second, especially so when you start involving more complex variables.

Similarly, saying

$dbh->do('UPDATE table SET foo=?, bar=?, baz=? WHERE toto=? AND tata=?',
    ...);

is IMO more readable that intermingling SQL and Perl variables.

Sam
-- 
Website: http://www.illuminated.co.uk/